User Tools

Site Tools


mysql_mysqldump_database

Basic Dump

NOTE: Before taking a dump, check the table engines!!!
If table engines are MyISAM, a write AND read lock will be placed on the tables, which means there will be “interruption” of service involved.
Change 'databasename' to the name of the database you are looking to dump

select engine,count(*),sum(index_length+data_length)/1024/1024 from information_schema.tables where table_schema = 'databasename' group by engine;


You can then perform a dump with:
mysqldump -u root -p database > filename.sql

Recommended:
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

Note: It may be benefitial to run the mysql dump in a screen session depending on how large the database is.
To view database size please visit: https://lukeslinux.co.uk/mysql_database_sizes


Importing

Once you have created the db you can import with:

mysql -u root -p mytestdb2 < mytestdb.sql


Using PV to import - Progress report

pv database_file.sql | mysql database_name




Advanced mysql dump

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


mysql_mysqldump_database.txt · Last modified: 2024/05/23 07:26 by 127.0.0.1

Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki