Build MySQL master-slave copy and delete database data recovery strategy

Building Master-slave replication

Host: [Mysqld]Add VIM/ETC/MY.CNF under##Set server_id, generally set to Ipserver_id=8# #Replication filtering: The database that needs to be backed up, output binlogbinlog-do-db=testdb#Replication filtering: Databases that do not need to be backed up, not output (MySQLLibrary is generally out of sync) binlog-ignore-db=mysql# Turn on the binary log to prepare for SlaveAs other SlaveThe MasterWhen using Log-bin=master-log-1binlog_cache_size = 1M# Master-Slave copy format (mixed,statement,row, the default format is statement) binlog_format=mixed# Binary logs are automatically deleted/The number of days to expire. The default value is 0, which means it is not automatically deleted. Expire_logs_days=7#slave_skip_errors=1062# # Relay_logConfiguring the Relay Log#Relay_log=edu-mysql-relay-bin#Log_slave_updatesRepresents slaveWrite replication events into your binary log#Log_slave_updates=1# Prevent data changes (Except for special threads)#Read_only=1Then create a user in the host to grant synchronization permissions,Specifically for replication: GRANT REPLICATION slave,replication CLIENT on *. * to ' sync_user ' @ ' 192.168.0.% ' identified by ' root ';Then show Master Status\g;Mysql>Show Master status\g;*************************** 1. Row *************************** file:master-log-1.000002 position:584 binlog_do_db:testdbSlave machine:##Set server_id, generally set to Ipserver_id=8# #Replication filtering: The database that needs to be backed up, output binlogbinlog-do-db=testdb# #Replication filtering: Databases that do not need to be backed up, not output (MySQLLibrary is generally out of sync) binlog-ignore-db=mysql# #Turn on the binary log to prepare for SlaveAs other SlaveThe MasterWhen using Log-bin=master-log-1binlog_cache_size = 1M# #Master-Slave copy format (mixed,statement,row, the default format is statement) binlog_format=mixed# #Binary logs are automatically deleted/The number of days to expire. The default value is 0, which means it is not automatically deleted. Expire_logs_days=7And then reboot,After logging in, configure the master-slave relationship group. Master_log_fileTake the show master status above in real timeThe fileThe value of the field, Master_log_posTake positionThe value, Last view show slave status\g; mysql> show slave status\g;*************************** 1. Row ******** Slave_io_state:waiting for Master to send event master_host:192.168.0.7 Master_user:sync_user maste r_port:3306 connect_retry:60 master_log_file:master-log-1.000002 read_master_log_pos:120 Relay_Log_File: mysqld-relay-bin.000002 relay_log_pos:286 relay_master_log_file:master-log-1.000002 Slave_io_running:yes Slave_SQL _running:yes which  These two are Yes Then there's no problem. Slave_io_ Running:yes slave_sql_running:yes direct start master-slave synchronization       

DB Quick rollback recovery to prevent the deletion of the database.In the event of a deletion or other dangerous operations, such as update without conditional changes (you do not open the update protection), then the escape is certainly not the first option, you must first try to remedy Ah.

@1: 一小时延迟从库

　　一小时间隔后重新连上主机把所有的数据全部同步过来,然后立马断开,这个从库会与主库保持1个小时的数据差距. 在搭好的主从同步的从机上执行: 

mysql >mysql# MySQL

 　　 But here's one drawback:A deleted library incident or other update occurred within a short period of time when the library was synchronized on the connected main library No conditional disaster,  This time is simply unable to recover.  so it's best to open a double 1 hour delay sync:  That is, the first slave to perform one time delay 1 hours command change master Span class= "Hljs-title" >to master_delay=3600; 
 master to Span class= "hljs-attr" >master_delay=3600; 
  So now the two slave machines have a half-hour synchronization interval, 2. The other is full-scale backup +  incremental backup ( timed copy with hard disk)        

 

Build MySQL master-slave copy and delete database data recovery strategy