MySQL Backup Restore

MySQL: Exporting, Backup and Restore from the Linux command line.

This note briefly covers how to export a database out of MySQL and back in. This is useful for moving databases between test environments. Exporting a Database

To export a database, use the mysqldump utility normally located in your mysql/bin directory . For example, to export all the tables and data for a database named guestdb:

mysqldump guestdb > guestdb.txt

This will create a text file containing all the commands necessary to recreate all the tables and data found in guestdb. However, what if I want to export only one table? To do this the command is modified as follows assuming guestTbl is the table to be exported.:

mysqldump guestdb guestTbl > guestdb.txt

One important point. The data created in the txt file assumes that no table exists on your destination server. The --add-drop-table option adds code to drop a table if it exists in the new database when the file is imported. Also, not login is used in this example. In many cases, you need to log in. To be able to perform the command. So a complete example of the command line looks like this.

mysqldump -p --user=username --add-drop-table guestdb guestTbl Enter password: ********

The -p option tells MySQL to prompt for a password. The --user option sets your username, and --add-drop-table adds the code necessary to drop a table if it exists during import. The output from the command looks like this:

guestdb.txt

1. MySQL dump 8.11
3. Host: localhost Database: guestdb
4. --------------------------------------------------------
5. Server version 3.23.28-gamma

2. Table structure for table 'guestTbl'

DROP TABLE IF EXISTS guestTbl; CREATE TABLE guestTbl ( msgID int(11) NOT NULL auto_increment, name varchar(30), email varchar(30), msgFrom varchar(30), msgDate timestamp(14), msgBody text, PRIMARY KEY (msgID) );

2. Dumping data for table 'guestTbl'

INSERT INTO guestTbl VALUES (1,'Test User1','testuser@test.com','Smallville USA' ,20010101182157,'This is teh body for message 1.'); INSERT INTO guestTbl VALUES (2,'Test User2','testuser2@test.com','Smallville USA ',20010101182352,'This is teh body for message 2.'); INSERT INTO guestTbl VALUES (3,'Test User3','testuser3@test.com','Smalltown USA' ,20010101182451,'This is teh body for message 3.'); INSERT INTO guestTbl VALUES (4,'Test User4','testuser4@test.com','Smallcity USA' ,20010101182519,'This is teh body for message 4.'); Importing the Database

With the data in a text file, its time to import the data back into MySQL. This can be done by passing the commands contained in the text file into the MySQL client. For example:

mysql -p --user=username < guestdb.txt

This passes all the commands in the file into the mysql client just like you were typing them in.

Note: While developing this tip, it was noted that the command created were missing a use statement. You made need to add a use database; statement to the text file to make the command work. Backing Up and Restoring a MySQL Database

To make a database backup using the MySQL command-line utilities, execute the following command:

$ mysqldump -u username -p --opt dbname > dump.sql

If the MySQL programs are not in your path, you will need to manually specify the location of the mysqldump program:

$ /usr/local/mysql/bin/mysqldump -u username -p --opt dbname > dump.sql

Download the dump.sql and keep it in a safe place (CDR, Zip disk, etc).

If you need to restore your database, you can do so like this:

$ mysql -u username -p --database=dbname < dump.sql