71 users online (1 members and 70 guests)  

Thread: Dump mysql


  Results 1 to 6 of 6

Related

  1. Replies: 1
  2. Mod with mysql    Forum: Website Scripts Forum
    Replies: 0
  3. Need Pro MYSQL HELP    Forum: Databases
    Replies: 1
  4. Mysql help please    Forum: PHP Forum
    Replies: 1
  1. #1
    davidmook's Avatar
    New User

    Status
    Offline
    Join Date
    May 2006
    Posts
    10

    Dump mysql

    I wish to know how to dump data in database into sql file. And also how to import data from sql file to mysql

  2. #2
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: Dump mysql

    I believe this page should be of assistance here ...

  3. #3
    uthus's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Feb 2006
    Location
    Ft Hood
    Posts
    75

    Re: Dump mysql

    The down and dirty dump:
    mysqldump -umyusername -pmypassword database_name > file.sql
    You only have to use the -u if your uid cannot write to the db. The -p is necessary only when mysql requires a password.

    Importing:
    mysql -umyusername -pmypassword < file.sql
    This assumes that the first line of the file is 'USE database_name;' The same rules for -u and -p apply.

    I use this all the time as I'm creating front-ends to projects.

    hth

    uthus
    Last edited by uthus; 06-16-2006 at 08:17 PM.

  4. #4
    RobZGurl's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2006
    Location
    Kentucky
    Posts
    1

    Question Re: Dump mysql


    Hi
    I was just trying to figure out what your post meant??

    Squirrell's seem to get confused when their nuts arent in a ROW......
    Thanks
    Cya

  5. #5
    davidmook's Avatar
    New User

    Status
    Offline
    Join Date
    May 2006
    Posts
    10

    Re: Dump mysql

    Quote Originally Posted by RobZGurl

    Hi
    I was just trying to figure out what your post meant??

    Squirrell's seem to get confused when their nuts arent in a ROW......
    Thanks
    Cya
    I mean is how can dump all data in mysql database to sql file

  6. #6
    uthus's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Feb 2006
    Location
    Ft Hood
    Posts
    75

    Re: Dump mysql

    Hi,

    MySQL will dump to a file if you tell it to. It puts everything in SQL statements that are human readable. For example, a database that I'd been practicing with:
    Code:
    -- MySQL dump 9.11
    --
    -- Host: localhost    Database: books
    -- ------------------------------------------------------
    -- Server version    4.0.23a
    
    --
    -- Table structure for table `book_reviews`
    --
    
    CREATE TABLE book_reviews (
      isbn varchar(13) NOT NULL default '',
      review text,
      PRIMARY KEY  (isbn)
    ) TYPE=MyISAM;
    
    --
    -- Dumping data for table `book_reviews`
    --
    
    
    --
    -- Table structure for table `books`
    --
    
    CREATE TABLE books (
      isbn char(13) NOT NULL default '',
      author char(30) default NULL,
      title char(60) default NULL,
      price float(4,2) default NULL,
      PRIMARY KEY  (isbn)
    ) TYPE=MyISAM;
    
    --
    -- Dumping data for table `books`
    --
    
    INSERT INTO books VALUES ('0-672-31697-8','Michael Morgan','Java 2 for Professional Developers',34.99);
    INSERT INTO books VALUES ('0-672-31745-1','Thomas Down','Installing Debian GNU/Linux',24.99);
    INSERT INTO books VALUES ('0-672-31509-2','Pruitt, et al.','Teach Yourself GIMP in 24 Hours',24.99);
    INSERT INTO books VALUES ('0-672-31769-9','Thomas Schenk','Caldera OpenLinux System Administration Unleashed',49.99);
    INSERT INTO books VALUES ('0-672-31862-8','Steve Litt','Samba Unleashed',49.99);
    
    --
    -- Table structure for table `customers`
    --
    
    CREATE TABLE customers (
      customerid int(10) unsigned NOT NULL auto_increment,
      name char(30) NOT NULL default '',
      address char(40) NOT NULL default '',
      city char(20) NOT NULL default '',
      PRIMARY KEY  (customerid)
    ) TYPE=MyISAM;
    
    --
    -- Dumping data for table `customers`
    --
    
    INSERT INTO customers VALUES (1,'Julie Smith','25 Oak Street','Airport West');
    INSERT INTO customers VALUES (2,'Alan Wong','1/47 Haines Aveneue','Box Hill');
    INSERT INTO customers VALUES (3,'Michelle Arthur','357 North Road','Yarraville');
    
    --
    -- Table structure for table `order_items`
    --
    
    CREATE TABLE order_items (
      orderid int(10) unsigned NOT NULL default '0',
      isbn char(13) NOT NULL default '',
      quantity tinyint(3) unsigned default NULL,
      PRIMARY KEY  (orderid,isbn)
    ) TYPE=MyISAM;
    
    --
    -- Dumping data for table `order_items`
    --
    
    INSERT INTO order_items VALUES (1,'0-672-31697-8',2);
    INSERT INTO order_items VALUES (2,'0-672-31769-9',1);
    INSERT INTO order_items VALUES (3,'0-672-31769-9',1);
    INSERT INTO order_items VALUES (3,'0-672-31509-2',1);
    INSERT INTO order_items VALUES (4,'0-672-31745-1',3);
    
    --
    -- Table structure for table `orders`
    --
    
    CREATE TABLE orders (
      orderid int(10) unsigned NOT NULL auto_increment,
      customerid int(10) unsigned NOT NULL default '0',
      amount float(6,2) default NULL,
      date date NOT NULL default '0000-00-00',
      PRIMARY KEY  (orderid)
    ) TYPE=MyISAM;
    
    --
    -- Dumping data for table `orders`
    --
    
    INSERT INTO orders VALUES (1,3,69.98,'2000-04-02');
    INSERT INTO orders VALUES (2,1,49.99,'2000-04-15');
    INSERT INTO orders VALUES (3,2,74.98,'2000-04-19');
    INSERT INTO orders VALUES (4,3,24.99,'2000-05-01');
    Note that there are 5 tables: book_reviews, books, customers, order_items, and orders. All tables have data except book_reviews.

    I'm not familiar with Oracle, Postgress, or Infomix, but I do know that MS Access dumps data as a comma (or tab, etc) delimited text file. I assume that MS SQL server does the same thing.

    hth

    U



Tags for this Thread