Shulou(Shulou.com)05/31 Report--

How to carry out mysql backup and recovery discussion, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1. My current backup plan is as follows:

A (master)-> B (slave) performs real-time synchronization and makes a full backup on B (slave) at 3 am every Sunday, Monday to Saturday.

Incremental backup, choose the time of incremental backup, and modify flexibly according to business needs. When the DB fails, or the server business logic appears

Major bug, when the player complaints are more serious, then we need to recover the data.

2. My current recovery plan is as follows:

First stop all servers and first do a full backup restore on B (slave):

Mysql-uroot-paired movies *

< allbackup.sql 然后选择时间点进行增量恢复: mysqlbinlog --start-date="2011-06-15 14:00:00" --stop-date="2011-06-15 17:30:00" mysql-bin.[0-9]* |mysql -uroot -p**** 这样所有的，所有的表单都恢复到了正常状态。 3，这样做的问题是： 相当麻烦，很痛苦。如果只是db_account中的一个表单 tb_account出现了问题，其他的数据库均正常。那么这样做就太折腾了， 因为全备份对所有的数据库都生效，这样的恢复当然也是对所有的数据库生效。那么恢复之后，要在B(slave)上找到想要的恢复后的数据， 导入到A(master)中，而其他的数据都不能保持不动。痛苦！！！ 4，改进后的方案为： 对每个数据库进行全备份，不用原来的对所有的数据库备份的做法（即下面的做法）： dump -h $HOST -u $USER -p$PASSWORD --opt --all-databases --flush-logs >

$BAKDIR/$DATESTR.sql

In this way, the backup data of each database will be generated in a sql file accordingly, that is, the number of sql files in the original backup directory will be increased from one to N, so that

Can go to restore the specific database, which database problems to restore which database, where will not click where, my mother no longer have to worry about my study.

Saved a lot of trouble. That is, you can write like this:

Mysqldump-h $HOST-u $USER-p$PASSWORD-- flush-logs db_account > account.sql

Mysqldump-h $HOST-u $USER-p$PASSWORD-- flush-logs db_test1 > test1.sql

Mysqldump-h $HOST-u $USER-p$PASSWORD-- flush-logs db_test2 > test2.sql

Mysqldump-h $HOST-u $USER-p$PASSWORD-- flush-logs db_test3 > test3.sql

What about incremental backups?

What if I restore the db_account based on incremental steps in time? There's a way.

Mysqlbinlog-- start-date= "2011-06-15 14:00:00"-- stop-date= "2011-06-15 17:30:00"-d db_account mysql-bin. [0-9] *

You can use-d to specify the database for incremental recovery, so that you can perform full backup recovery and incremental backup recovery of the specified database.

5. A little worry:

That is, when you make a full backup of a specific database db_account, flush-logs, the incremental data of db_account will be deleted. So is it possible to:

5.1Increment data in two databases in one mysql-bin file

5.2, the incremental data of one database is in two mysql-bin files

If there are the above possibilities, will there be any hidden dangers or problems in flush-logs? For example, db_account was deleted during flush-logs.

A file, but there is incremental data from other databases in this file. Or flush-logs is not based on file deletion, but on data deletion, in all

The incremental data of db_account is found in the file, and then deleted. When a file is found to have no data, the file is deleted. Everyone knows that.

There are quite a lot of mysql-bin files, and if you don't delete and clean up, the hard drive will explode one day.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.