Introduction to MySQL two-way backup method (with code)-Mysql Tutorial-php.cn

This article brings you an introduction to the MySQL two-way backup method (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

MySQL two-way backup is also called master-master backup, that is, both MySQL services are Master, and any one of them is the Slave of another service.

Preparation

Server

##masterB 5.6.41 192.168.1.202

MySQL Server	Version	IP Address
masterA	5.6.41	192.168.1.201

Note:

The backup MySQL server version should be kept consistent as much as possible. Different versions may have binary log formats. Not compatible.

Specific operation

Attention

During the operation, pay attention to the consistency of the data on both sides! ! !

masterA configuration

my.cnf

[mysqld] # 服务器唯一标识 server-id=1 # 二进制日志文件名 log-bin=mysql-bin # 需要备份的数据库，多个数据库用 , 分隔 binlog-do-db=piumnl # 需要复制的数据库，多个数据库用 , 分隔 replicate-do-db=piumnl # 中继日志文件名 relay_log=mysqld-relay-bin # 手动启动同步服务，避免突然宕机导致的数据日志不同步 skip-slave-start=ON # 互为主从需要加入这一行 log-slave-updates=ON # 禁用符号链接，防止安全风险，可不加 symbolic-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: 0 master-info-repository=table relay-log-info-repository=table relay-log-recovery=1 # 可不加 # 禁用 dns 解析，会使授权时使用的域名无效 skip-host-cache skip-name-resolve sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Copy after login

masterB configuration

my.cnf

# 不再解释各个配置项 [mysqld] server-id=2 log-bin=mysql-bin binlog-do-db=piumnl replicate-do-db=piumnl relay_log=mysql-relay-bin skip-slave-start=ON log-slave-updates=ON symbolic-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: 0 master-info-repository=table relay-log-info-repository=table relay-log-recovery=1 skip-host-cache skip-name-resolve sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Copy after login

Create backup user

masterA & masterB both need to create a backup user:

create user 'rep'@'%' identified by 'rep'; # 创建一个账户 grant replication slave on *.* to 'rep'@'%'; # 授予该账户对任意数据库任意表的主从备份权限

Copy after login

Note:

root@%usergrant_privPermissions, so if it is a remote login, authorization failure will occur

Restart the server

Enable backup

masterA

View masterB status

show master status\G; # 此处需要关注 File 和 Position 值

Copy after login

Enable backup

stop slave; # master_log_file 就是第一步操作的 File 值 # master_log_pos 就是第一步操作的 Position 值 change master to master_host=, master_user=, master_port=, master_password=, master_log_file='mysql-log.000003', master_log_pos=154; start slave;

Copy after login

View the results

show slave status\G; # 查看最重要的两项，两个都必须为 Yes ，有一个为 No 都要去查看错误日志文件，看看什么地方存在问题 # Slave_IO_Running: Yes # Slave_SQL_Running: Yes

Copy after login

masterB

Reverse the operation of masterA

Test

Insert data into masterA and masterB respectively, and check whether the expected data appears on the other server in time

Problem

MySQL Slave Failed to Open the Relay Log

This should be a problem with the relay log. You can try the following operations

stop slave; flush logs; start slave;

Copy after login

Got fatal error 1236 from master when reading data from binary log

When pulling the log 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 master; reset slave; flush logs;

Copy after login

. Use

MySQL Tutorial]

The above is the detailed content of Introduction to MySQL two-way backup method (with code). For more information, please follow other related articles on the PHP Chinese website!

Related labels：

mysql Backup and restore

source：segmentfault.com

Previous article：How are SQL statements executed in MySQL? Next article：Introduction to Redis’s method of implementing flash sales (with code)

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn