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

Here is an example of backing up this actual Wiki's database:

[jamie@www.thebestlinux.com:~]$ sudo mysqldump -v -u wikidbadmin -p MyWikiDB >MyWikiDB_03-04-2023.sql
Enter password: 
-- Connecting to localhost...
-- Retrieving table structure for table actor...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table archive...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table bot_passwords...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table category...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table categorylinks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table change_tag...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table change_tag_def...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table comment...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table content...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table content_models...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table external_user...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table externallinks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table filearchive...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table image...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table imagelinks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table interwiki...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table ip_changes...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table ipblocks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table ipblocks_restrictions...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table iwlinks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table job...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table l10n_cache...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table langlinks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table log_search...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table logging...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table math...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table module_deps...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table oathauth_users...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table objectcache...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table oldimage...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table page...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table page_props...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table page_restrictions...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table pagelinks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table protected_titles...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table querycache...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table querycache_info...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table querycachetwo...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table recentchanges...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table redirect...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table revision...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table revision_actor_temp...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table revision_comment_temp...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table searchindex...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table site_identifiers...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table site_stats...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table sites...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table slot_roles...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table slots...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table templatelinks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table text...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table trackbacks...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table updatelog...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table uploadstash...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table user...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table user_former_groups...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table user_groups...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table user_newtalk...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table user_properties...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table watchlist...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table watchlist_expiry...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...
[jamie@www.thebestlinux.com:~]$

General Information

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