Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The realization method of two-way backup of MySQL

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

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 in authorization, skip-host-cacheskip-name-resolvesql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

MasterB 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: 0MasterMatsushi repositoryTablerayMok. Maofu.com loglogrecovery1skipcombe Cacheskipwashi Cacheskipwashi NameMuyi Vesqlstores no longer explain the configuration items ENGINEN SUBSTITUTION

Create backup user

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:

MySQL under Linux 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. The same account and password are used here to simplify the operation.

Restart the server

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

.

Use. Insert, update and delete operations, there will be no backup (this is a giant pit)!

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report