mysqldump – 25 tips for DBAs

by nivas on December 1, 2014

1) Is mysqldump text backup or binary backup
It is a text backup . If you open the backup file you will see all the statements that can be used to recreate databases and objects . It also has the insert statements to populate the tables with data

2) What is the syntax for mysqldump ?
mysqldump -u [uname] -p[pass] –databases [dbname][dbname2] > [backupfile.sql]

3) How to backup all databases using mysqldump ?
mysqldump -u root -p –all-databases > backupfile.sql

4) How to backup specific databases using mysqldump ?
mysqldump -u root -p –databases school hospital > backupfile.sql

5) How to backup specific tables using mysqldump ?
mysqldump –user=root –password=mypassword -h localhost databasename table_name_to_dump table_name_to_dump_2 > dump_only_two_tables_file.sql

6) I do not want the data .How to get DDL only ?
mysqldump -u root -p –all-databases –no-data > backupfile.sql

7) How much time does a mysqldump backup take ?
It actually depends on the size of the database. A database of 100 GB size might take 2 hours or more

8) How to backup remote database that resides on other server ?
mysqldump -h 172.16.25.126 -u root -ppass dbname > dbname.sql
9) What is the significane of –routines option ?
The output generated by using –routines containsCREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. If you have procedures or functions you need to use this option

10) How to list all the available options in mysqldump
mysqldump –help
11) What are the most common options used in mysqldump ?

All-databases
Databases
Routines
Single-transaction (It will not lock tables) – Always use for innodb databases
Master-data – replication (Ignore for now)
No-data – It will dump a blank database without data
12) Are triggers backed up by default ?
Yes

13) What is the significance of single transaction option ?
–singletransaction option avoids any locks during backup for innodb databases. No locking during backup if we use this option

14) What is the most common command used to backup using mysqldump ?
nohup mysqldump –socket=mysql.sock –user=user1 –password=pass –single-transaction –flush-logs –master-data=2 –all-databases –extended-insert –quick –routines > market_dump.sql 2> market_dump.err &

15) How to compress a backup taken by mysqldump ?
Note: Compression might slow down the backup
Mysqldump [options] | gzip > backup.sql.gz

16) Is mysqldump ideal for large databases ?
Depending on your hardware, including available RAM and hard drive speed, an appropriate database size is between 5GB and 20GB. While it is possible to use mysqldump to back up a 200GB database, this single thread approach takes time to execute

17) How to restore the backup taken by mysqldump ?
a) Use source method
b) Mysql –u root –p < backup.sql

18) I want to record the errors in a log during recovery .. I also want to see the execution time of recovery ?
Time Mysql –u root –p < backup.sql > backup.out 2>&1

19) How to know if the restore is running ?
Show full processlist

20) What is something you have to do if the database is huge ?
Run it in the background using nohup

21) Can I take mysqldump backup on windows and restore to linux server ?
Yes

22) How do I transfer files onto the target server ?
a) Use scp
b) Use sftp
c) Use winscp

23) What happens if I use source on a big backup file to restore ?

If you source a big backup file , it might take forever to run  . Better way of handling this situation is using nohup to run in the background

Alternate solution is to use screen in unix

24) Does mysqldump contain drop database by default ?

You may need to add  the option –add-drop-database

25) How to extract one database backup out of a multiple database backup. (Assume the database name is test)

sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql

 

 

Comments on this entry are closed.

Previous post:

Next post: