Shulou(Shulou.com)06/01 Report--

The following article is about the detailed methods of implementing two-way backup of MySQL. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe that after reading the detailed methods to achieve MySQL two-way backup, you will certainly benefit.

MySQL two-way backup is also known as master master backup, that is, both MySQL services are Master, and either service is the Slave of the other service.

Prepare for

Server

MySQL server version IP address masterA5.6.41192.168.1.201masterB5.6.41192.168.1.202

Note:

The version of the backup MySQL server should be consistent as far as possible, and different versions may not be compatible with the binary log format.

Concrete operation

Be careful

Pay attention to the consistency of the data on both sides during the operation!

MasterA configuration

My.cnf

[mysqld] # Server uniquely identifies the database that server-id=1# binary log file name log-bin=mysql-bin# needs to back up, multiple databases are used to separate the databases to be replicated by binlog-do-db=piumnl#, and multiple databases are used to separate replicate-do-db=piumnl# relay log file name relay_log=mysqld-relay-bin# to manually start synchronization service Avoid data log asynchrony caused by sudden downtime skip-slave-start=ON# mutual master slave need to join this line log-slave-updates=ON# disable symbolic links to prevent security risks, but do not add symbolic-links=0# can not add # resolve-[Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0master-info-repository=tablerelay-log-info-repository=tablerelay-log-recovery=1# can not add # disable dns parsing Will invalidate the domain name used when authorizing the skip-host-cacheskip-name-resolvesql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESmasterB configuration

My.cnf

# no longer explain the various configuration items [mysqld] server-id=2log-bin=mysql-binbinlog-do-db=piumnlreplicate-do-db=piumnlrelay_log=mysql-relay-binskip-slave-start=ONlog-slave-updates=ONsymbolic-links=0# resolve-[Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0MasterMatsu repositoryTablerayLayMou.com Log Maofu.com 1skipcomposer CacheskipMustLayMou NameMuyi Vesqlstores modeboxes Notification ENGINESuBSTITUTION STRICTION trans tables create backup users

Both masterA and masterB need to create backup users:

Create user 'rep'@'%' identified by' rep'; # create an account grant replication slave on. * to 'rep'@'%'; # grants the account master-slave backup permission for any table in any database

Note:

Under Linux, MySQL turns off grant_priv permissions for root@% users, so if you log in remotely, the authorization will fail. Here, the backup user account and password are not the same. To simplify the operation, restart the server with the same account and password.

Restart the server

Turn on backup

MasterA

View masterB status

Show master status\ Gbot # you need to focus on File and Position values here

Turn on backup

Stop slave;# master_log_file is the File value of the first step # master_log_pos is the Position value change master to master_host=, master_user=, master_port=, master_password=, master_log_file='mysql-log.000003', master_log_pos=154;start slave of the first step.

View the result

Show slave status\ Gposition # check the two most important items, both of which must be Yes, and one must be No to check the error log file to see what the problem is # Slave_IO_Running: Yes# Slave_SQL_Running: Yes

MasterB

Repeat the operation of masterA in reverse

test

Insert data into masterA and masterB respectively, and check to see if the expected data appears on another server in time.

Problem MySQL Slave Failed to Open the Relay Log

There should be a problem with the relay log, but try the following

Stop slave;flush logs;start slave;Got fatal error 1236 from master when reading data from binary log

When pulling logs from the main library, it is found that the first file in the mysql_bin.index file of the main library does not exist.

# reset the following operations # if the binary log or relay log has other functions, do not do the following reset master;reset slave;flush logs

Is there anything you don't understand about the above detailed method of implementing MySQL two-way backup? Or if you want to know more about it, you can continue to follow our industry information section.

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.