I want to set up below command for daily MySQL database backup.suppose We have more than 50 MySQL database in server.

I am using below command to take backup of all databases on server.

Note: I am using --add-drop-database because if I remove, I will get error if already db is there in case of doing refresh replication etc.

shell> mysqldump -u --single-transaction --master-data=2 --all-databases --add-drop-database --routines --triggers --events -p > backup.sql

I will use backup.sql file for restoring on another server in below scenarios.

Scenario1:- In case of production server crash/disk corruption and in any disaster situation.

Scenario2:- Suppose, I want to configure Master-slave replication.

Scenarios3:- In case of Point-in-Time recovery I will use this backup and binary logs which was created post backup. because i used --master-data option which will apply flush tables with read lock at global level to take binary log position and file(coordinates)

Note:- if I have Mixed table in db then I should use below flush tables with read lock because --master-data will use flush tables with read lock for small duration when dump is going to start. due to --single-transaction,no read/writes are blocked once the dump started.

But the issue is, in MySQL 8.0+, they have removed access from user to drop mysql system database.we can't restore dump on another server if I want to restore. because, if I take fresh installed mysql server still there will be mysql system database. Refer documentation link

So I am using below command to mitigate the issue and fulfilling the above scenarios.

First I will take mysql database backup separately. using below command.

shell> mysqldump -u -p mysql > users_backup.sql

and for user created database i will use below command.

shell> mysqldump -u --single-transaction --master-data=2 --databases --add-drop-database dbname1 dbanme2 --routines --triggers --events -p > backup.sql

Question 1:- For above command --> What will be the steps if I have more than 50/100 databases? how we can solve this issue?

Question 2:- Do we have another options in MySQL 8.0+ to take all databases backup like we used to do before 8.0 and it should fulfill the scenario?

Before MySQL 8.0 we can use only below command to solve all issues along with above scenarios.

shell> mysqldump -u --single-transaction --master-data=2 --all-databases --add-drop-database --routines --triggers --events -p > backup.sql

  • If your servers are running on Linux, your best option is PERCONA XTRABACKUP (By far, the best backup solutions available out there). Jun 29 '20 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.