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

Source: Internet
Author: User
Tags prepare

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.