Mysql (mysqldump) – Backup database
Backing up the database is no doubt a must activity for any software system, which involves Data Base (of course). For taking backup of MySQL db there are few utilities which are suitable for different types of DBs like Innodb, MyISAM and ARCHIVE tables.
For Innodb tables mysqldump is quite useful and popular utility which dumps all data in SQL statements. Backing up and restoring is quite simple because of output SQL statements. There is whole lot of options provided by this utility to format your SQL dump e.g.
--add-drop-database
which add a DROP DATABASE statement before each CREATE DATABASE statement,
--tables
using which you can specify which table you want to dump etc. Below are few more very basic mysqldump examples:
1. mysqldump of a database in some file. By default in include all the create table statements and it data in INSERT statements:
#mysqldump -u user_name -ppassword db_name > filename.sql
2. mysqldump of database along with Procedures. Functions:
#mysqldump -u user_name -ppassword -R db_name > filename.sql
or
#mysqldump -u user_name -ppassword --routines db_name > filename.sql
3. mysqldump of database along with Triggers:
#mysqldump -u user_name -ppassword --triggers db_name > filename.sql
see complete reference for mysqldump here
There is a small problem with this dump file. When you use mysqldump file it creates SQL statement alphabetically i.e. tables starting with ‘A’ will be dumped first, then ‘B’ and so on.
If you have relations between tables (which we generally do have) and there are foreign keys defined it is problematic to restore database.
Conside a case where i have two tables, customer and account_customer, account_customer has foreign key for customer table. In the regular dump file account_customer will be dumper before customer table and will give error while restoration of db, because this will check for customer table first.
Solution:
Add following line in the beginning of dump file
SET FOREIGN_KEY_CHECKS=0;
and add
SET FOREIGN_KEY_CHECKS = 1;
at the end of file.
There are few smarter commands :
Create file to execute prior to the dump file:
shell> echo 'SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; ' > pre.sql
Create a file to execute after the dump file is imported:
shell> echo 'SET FOREIGN_KEY_CHECKS = 1; COMMIT; SET AUTOCOMMIT = 1; ' > post.sql
shell> cat pre.sql dumpfile.sql post.sql | mysql --user=username --password database
Copy data from one mysql db to another:
shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Would you be interested in exchanging blogrolls links with my site? Please email me if you are interested