Backup and Restore MySQL Database through command line
by Nyro, Tuesday 18 March 2008 at 04:51:46 AM :: Programmation
When you want backup a MySQL database, you don't have many solutions.
You can use PhpMyAdmin to backup the database but you will figured out that the export doesn't work so good: there is some information lacks like the Foreign key or other things like that. You'll also occur timeout problem when trying to backup -and restore- huge database.
You can also read a fex pages in the MySQL doc and you'll find Bash command to do exactly what you want. In only 1 line you'll be able to backup you're whole database in one text file.
Here is the backup command:
mysqldump --user=USER --password=PASS --add-drop-table DATABASENAME
This command show you all the MySQL query to execute to retrieve your entire database: the CREATE instructions, adding the constraints if needed and of course, the INSERT lines. The option --add-drop-table add the instruction to delete the table just before their creation: very useful to don't worry about cleaning the database before the backup.
If you want save more than one database, you have to replace DATABASENAME by --databases DATABASENAME1 DATABASENAME2 DATABASENAME3.
And if you want save all the database in you server, you can use --all-databases instead. Be sure the user used in the command line have access to all the databases you want back up.
Right now the command show you the query. Even if you're Flash Gordon, you can't do anything with that. A simple pipe to a file will save everything for you:
mysqldump --user=USER --password=PASS --add-drop-table DATABASENAME > DUMP.sql
To restore the database -or create it in an other server-, you have simply to upload the file saved just before and run this command in the same place of this file:
mysql -u USER --password=PASS DATABASENAME < DUMP.sql
No comment :: 7 945 views