Mysql 主备双库方案

一、简介

1、我们为什么要用主从复制？

主从复制目的：可以做数据库的实时备份，保证数据的完整性；
可做读写分离，主服务器只管写，从服务器只管读，这样可以提升整体性能。
原理图

1.png

从上图可以看出，同步是靠log文件同步读写完成的。

2、Binlog

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

3、Server-id

• mysql同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，因此server-id一定要有的
• 每一个同步中的slave在master上都对应一个master线程，该线程就是通过slave的server-id来标识的；每个slave在master端最多有一个master线程，如果两个slave的server-id 相同，则后一个连接成功时，前一个将被踢掉。 这里至少有这么一种考虑slave主动连接master之后，如果slave上面执行了slave stop；则连接断开，但是master上对应的线程并没有退出；当slave start之后，master不能再创建一个线程而保留原来的线程，那样同步就可能有问题；
• 在mysql做主主同步时，多个主需要构成一个环状，但是同步的时候有要保证一条数据不会陷入死循环，这里就是靠server-id来实现的

二、master 配置(本文采用的是docker mysql部署，默认已经部署了mysql)

1、修改master mysql配置文件，添加配置

server-id = 1
log-bin = /var/lib/mysql/log-bin
expire_logs_days = 10 #日志保存时间
binlog-ignore-db=information_schema #这是mysql默认的数据库，忽略掉
binlog-ignore-db=mysql              #这是mysql默认的数据库，忽略掉
binlog-ignore-db=performance_schema #这是mysql默认的数据库，忽略掉
binlog-do-db = workstation    #需要同步的数据库，如果需要同步所有数据库，可不设置
binlog-do-db = workstation1   #可以设置多个binlog-do-db参数

2、重启mysql，进入mysql容器

# 查看service_id
show variables like 'server_id%';
# 查看log_bin
show variables like 'log_bin%';

如下图，server_id 和 log_bin已生效

2.png

3、master mysql操作

# 创建账户，允许用户 "slave_02" 密码 "123456" ip不限的用户来读取数据
grant replication slave on *.* to  'slave_02'@'%' identified by '123456';
# 查询用户信息
select user, host from mysql.user;
# 查看master的状态，此时log-bin的位置是1284，从这个点开始备份
show master status;

3.png

• 注意：如果一开始master和slave的数据或库表不一致，需要先备份master数据，将数据同步到slave

三、slave 配置

1、修改slaver mysql配置文件，添加配置

server-id = 2
log-bin = /var/lib/mysql/log-bin
expire_logs_days = 10
binlog-ignore-db=information_schema #忽略默认的information_schema数据库
binlog-ignore-db=mysql              #忽略默认的mysql数据库
binlog-ignore-db=performance_schema #忽略默认的performance_schema数据库
replicate-do-db=workstation         #设置需要同步的数据库跟master一样，如果要复制所有，可不设置
replicate-do-db=workstation1        #可以设置多个replicate-do-db参数
log-slave-updates=1   # slave中的log_bin中也会记录复制master的sql，不设置则不记录 级联复制A->B->C 则B必须设置
slave-skip-errors=all
slave-net-timeout=60

2、配置连接master信息(master_host如果是docker起的mysql，本地连接需要本地真实ip)

# 连接脚本
# master_host: 本机ip
# master_port: master 的port
# master_user: 刚刚在master上创建的用户
# master_password: 创建时的密码
# master_log_file: log_bin的日志文件
# master_log_pos: 同步的起始位置
change master to master_host='127.0.0.1', master_port=3301, master_user='slave_02', master_password='123456', master_log_file='log-bin.000001'
    ,master_log_pos=1284;

3、启动slave

# 启动从节点
mysql> start slave

4、查看slave状态

# 查看slave状态
mysql> show slave status\G

4.png

上图所示Slave_IO_Running和Slave_SQL_Running 都为Yes说明启动成功

• 注意：如果用docker的同一个image启动两个mysql的container有可能造成UUIDS重复，删除slave的/var/lib/mysql/auto.cnf 重启即可

四、此时在master库中进行创建表，增加数据，slave中会同步相应操作，打开log_bin日志，发现数据库所有的操作都会记录相关日志

如果想配置双master，可以在master库中配置上述 <三>步骤所示的slave信息，这样两个mysql即互为主从，就是双master，可以再多配置一个mysql当做slave。操作一样，本文不再多余配置。

以上就是mysql的的双master或一master一slave或双master一slave的方法，希望对你有所帮助！

附：mysql配置文件的简单解释

[mysqld]
skip-name-resolve
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin
# 为服务器分配id，可以自定义，不区分大小，起标识作用。不同数据库节点分配不同的id
server-id=1
binlog_format=mixed
# replicate-do-db/replicate-ignore-db这两个设置针对从服务器，当两台数据库服务器并非互为主从关系时，从服务器如此设置
# replicate-do-db = ...这项设置指定需要同步数据的数据库
replicate-do-db=eleting
# replicate-ignore-db = ...这项设置指定不需要同步数据的数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
# binlog-do-db/binlog-ignore-db这两个设置针对主服务器，当两台数据库服务器并非互为主从关系时，主服务器如此设置
# binlog-do-db = ... 这项设置指定需要同步数据的数据库
binlog-do-db=eleting
# binlog-ignore-db = ... 这项设置指定不需要同步数据的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
relay_log=mysqld-relay-bin
log-slave-updates = ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid