Mysqldump-25 Tips for DBAs

The author is Nivas, the original address is mysqldump–25 tips for DBAs

2. Mysqldump is a text backup or a binary backup

   insert 语句来使用数据构成表。

4. What is the grammar of mysqldump?

    mysqldump -u [uname] -p[pass] –databases [dbname][dbname2] > [backupfile.sql]

6. How do I back up all the databases using mysqldump?

   mysqldump -u root -p –all-databases > backupfile.sql

8. How do I back up the specified database using mysqldump?

   mysqldump -u root -p –databases school hospital > backupfile.sql

10. How do I back up a specified table 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

12. I don't want data, how do I get DDL only?

    mysqldump -u root -p –all-databases –no-data > backupfile.sql

14. How long does it take to mysqldump a backup at a time?

    这依赖于数据库大小，100 GB 大小的数据库可能花费两小时或更长时间

16. How do I back up a remote database located on another server?

    mysqldump -h 172.16.25.126 -u root -ppass dbname > dbname.sql

18. What does the –routines option mean?

    CREATE PROCEDURE 和 CREATE FUNCTION 语句用于重新创建 routines。如果你有 procedures 或 functions 你需要使用这个选项

20. How do I list all the options in mysqldump?

    mysqldump –help

22. What are the options commonly used in mysqldump?

    dump 一个没有数据的空白数据库

24. Will all the default triggers be backed up?

    是的

26. What is the meaning of the single transaction option?

    –singletransaction 选项避免了 innodb databases 备份期间的任何锁，如果你使用这个选项，在备份期间，没有锁

28. What are the common commands for using mysqldump backup?

    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 &

30. How do I compress a backup using mysqldump?

    注意: 压缩会降低备份的速度Mysqldump [options] | gzip > backup.sql.gz

32. Mysqldump is it ideal to back up a large database?

    依赖于你的硬件，包括可用的内存和硬盘驱动器速度，一个在 5GB 和 20GB 之间适当的数据库大小。 虽然有可能使用  mysqldump 备份 200GB 的数据库，这种单一线程的方法需要时间来执行。

34. How can I restore a backup by using mysqldump?

    • Ways to use source data
    • Mysql–u Root–p < Backup.sql

36. During recovery I want to log errors into the logs, I also want to see the execution time of the recovery?

    Time Mysql –u root –p < backup.sql > backup.out 2>&1

38. How do I know if the recovery is in progress?

    显示完整的进程列表

40. If the database is huge, what do you have to do?

    使用 nohup 在后台运行它

42. Can I use mysqldump backup on Windows and then restore it on a Linux server?

    是的

44. How do I transfer files to the target server?

    • Using SCP
    • Using SFTP
    • Using WINSCP

46. What happens if I use a huge backup file source to recover?

    如果你的一个数据库备份文件来源，它可能需要很长时间运行。处理这种情况更好的方式是使用 nohup 来在后台运行。也可使用在 unix 中的 screen 代替

48. By default, does the mysqldump include the drop database?

    你需要添加 –add-drop-database 选项

50. How to extract a database backup from a multi-database backup (assuming the database name is test)?

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

Mysqldump-25 Tips for DBAs