mysql_mysqldump []

This is an old revision of the document!

Dumping and Importing database

To take a mysql dump you should not be logged into mysql, you will just need to be in the terminal. Use the following command to export the db to the directory you are currently in:

Viewing databases outside of mysql

You can view your mysql databases without logging into mysql with the following command:

mysql -e "show databases"

or

mysqlshow

Database Engine

Check the db engines before you dump. This will allow you to find the most efficient way of dumping the tables
Replace “database name” with the name of your database

mysql -e 'SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = "database name"

E.g Output:

+------------------------+--------+
| TABLE_NAME             | ENGINE |
+------------------------+--------+
| wp_commentmeta         | MyISAM |
| wp_comments            | MyISAM |
| wp_links               | MyISAM |
| wp_options             | MyISAM |
| wp_popularpostsdata    | InnoDB |
| wp_popularpostssummary | InnoDB |
| wp_postmeta            | MyISAM |
| wp_posts               | MyISAM |
| wp_term_relationships  | MyISAM |
| wp_term_taxonomy       | MyISAM |
| wp_terms               | MyISAM |
| wp_usermeta            | MyISAM |
| wp_users               | MyISAM |
+------------------------+--------+

Creating A DUMP

You will need to dump the db based on the storage engine:

INNODB

source: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction

mysqldump -u user -p --single-transaction mydatabase > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql

Using PV to see progress:

mysqldump -u user -p --single-transaction mydatabase | pv > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql

myISAM

source: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_lock-tables

mysqldump -u user -p --lock-tables mydatabase > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql

Using PV to see progress:

mysqldump -u user -p --lock-tables mydatabase | pv > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql

HYBRID

–lock-all-tables will provide a consistent backup for hybrid databases

mysqldump -u user -p --lock-tables mydatabase > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql

Using PV to see progress:

mysqldump -u user -p --lock-tables mydatabase | pv > /my/backup/directory/mydatabase_date.$(date +"%Y-%m-%d").sql

Basic Dump

You can then perform a dump with:

mysqldump -u root -p database > filename.sql

You can also use the following to add date to the mysql dump:

mysqldump database > mysql_dump_name_backup.$(date +"%Y-%m-%d").sql

Dumping with progress report

mysqldump database | pv > mysql_dump_name_backup.$(date +"%Y-%m-%d").sql

Importing a database

Once you have taken the dump you can then import the db using the following commands.
If you haven’t already prepared a blank database then you will need this command first:

mysqladmin -u root -p create databasename

Once you have created the db you can import with:

mysql -u root -p mytestdb2 < mytestdb.sql