How to Backup and Restore Large Databases in MySQL
Most who use MySQL have a nifty program called PhpMyAdmin. There is a good chance that if you are a user of that program, you’re here because you tried to backup or restore a large database through the web based interface and failed. This is a common situation, lucky for us there’s a very easy, fast, and reliable way to do this through the command line! If you’re a Linux user who has SSH access, follow the steps below.
Backing up the Database
Fire up SSH and type the following. Replace USER with your MySQL username, DBNAME with the name of the database you want to back up, and /path/to/backupname.sql to where you want the database backup file to be saved (directory must exist).
You’ll be prompted for your MySQL password. It’ll get to work after you type that in and press enter, it may take a few moments (or longer if your database is really large).
When you’re returned to the command line you now have a backup file where you specified above. This file is a complete copy of the database at the time you ran the command, the second your MySQL table is updated your backup will be out of date. I suggest you take backups at regular intervals depending on your situation (I will write a guide on how to automatically do backups one day).
Restoring Your Database Backup
Follow these 3 commands to restore your database easily.
First we need to drop your current database so there are no conflicts when we’re restoring
Now we’ll remake the database, because it doesn’t exist anymore
And finally, we’ll populate the fresh new database with your backup file.
The variables you should change above are pretty straight forward. If you get errors importing your data, it’s most commonly because your max_allowed_packet isn’t set high enough in /etc/my.cnf - if you have root access, you can go in and make that variable higher. If you don’t have root access, seek support from your web host.
Shouldn’t you have a –opt in there?
Some mysqldump options are shorthand for groups of other options. –opt and –compact fall into this category. For example, use of –opt is the same as specifying –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset. Note that as of MySQL 4.1, all of the options that –opt stands for also are on by default because –opt is on by default.
To reverse the effect of a group option, uses its –skip-xxx form (–skip-opt or –skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:
To select the effect of –opt except for some features, use the –skip option for each feature. For example, to disable extended inserts and memory buffering, use –opt –skip-extended-insert –skip-quick. (As of MySQL 4.1, –skip-extended-insert –skip-quick is sufficient because –opt is on by default.)
To reverse –opt for all features except index disabling and table locking, use –skip-opt –disable-keys –lock-tables.
When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, –disable-keys –lock-tables –skip-opt would not have the intended effect; it is the same as –skip-opt by itself.
mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the –quick option (or –opt, which enables –quick). –opt (and hence –quick) is enabled by default as of MySQL 4.1 to enable memory buffering, use –skip-quick.
If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the –opt or –extended-insert option. Use –skip-opt instead.
Comment by Denver Prophit Jr — November 24, 2008 @ 12:45 am
Please edit my last post. It should be –opt
Comment by Denver Prophit Jr — November 24, 2008 @ 12:47 am
Ok remove last comment and it should be double dash opt
Comment by Denver Prophit Jr — November 24, 2008 @ 12:48 am
i typed mysqldump -u USER -p DBNAME < C:\XXX.sql on my command line
It gives me an error C:\XXX.sql file is too large for notepad . Use another editor to edit the file.
Comment by Soniya — April 3, 2009 @ 3:32 pm
sorry i typed
mysqldump -u user -p DBName > C:\XXX.sql
Comment by Soniya — April 3, 2009 @ 3:33 pm