In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.