Backup MySQL database using mysqldump & mysqlpump

mysqldump and mysqlpump backup.md

Backup Using mysqldump

Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in the XML, delimited text, or CSV format.

Note: By default, mysqldump command does not dump the information_schema database, performance_schema, and MySQL Cluster ndbinfo database. If you want to include the information_schema tables, you must explicitly specify the name of the database in the mysqldump command, also include the —skip-lock-tables option.

mysqldump -uroot -p  --all-databases > all-databases.sql
mysqldump -uroot -p dbname > dbname.sql
mysqldump -uroot -p dbname table1 table2 > dbname_table_1_2.sql

# without database structure
mysqldump -uroot -p dbname –no-create-info > dbname.sql

More Options

mysqldump -uroot -p -A -R -E --triggers --single-transaction > dump.sql

1. A For all databases (you can also use --all-databases)
2. R For all routines (stored procedures & triggers)
3. E For all events
4. --single-transaction Without locking the tables i.e., without interrupting any connection (R/W).

Compress output using gzip

mysqldump -uroot -p --single-transaction --quick --skip-lock-tables db | gzip > "db_$(date +"%FT%H%M").sql.gz"

Backup Using mysqlpump

MySQL 5.7.8 introduced much improved version of mysqldump, It’s called mysqlpump, mysqlpump is much faster than mysqldump with parallel threads capabilities.

• Supports parallel MySQL logical backup, The resource usage efficiency and high performance backups
• Much better orchestration possible – You can backup selected databases, tables, stored programs and user accounts etc.
• By default mysqlpump will not backup performance_schema, sys schema, ndbinfo by default, You have to name them with –databases or –include-databases option
• mysqlpump does not dump INFORMATION_SCHEMA schema.
• Faster secondary indexes creation, The indexes created only after inserting rows !

Some examples :

mysqlpump -uuser -p --default-parallelism=4 dbname > pump.sql
mysqlpump -u root -p dbname > pump_$(date '+%Y-%m-%H-%M-%S').sql
mysqlpump -u root -p dbname --default-parallelism=4  > pump_$(date '+%Y-%m-%H-%M-%S').sql 
mysqlpump -u root -p --parallel-schemas=4:db1,db2 --default-parallelism=6  > backup_$(date '+%Y-%m-%H-%M-%S').sql
mysqlpump -u root -p --databases dbname.table1 > pump.table1$(date '+%Y-%m-%H-%M-%S').sql

Restore

mysql -u root -p < dump.sql