mysql主从同步

一、背景

wordpress和mysql处于同一台服务器上（阿里云），为保证数据安全性，搭建一台slave从服务器同步数据（从库位于内网windows10虚拟机的docker容器中），实现从库从阿里云的主库中同步数据的想法，并开启slave 的binlog日志，随时可从slave中恢复数据

二、主从同步定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

1.通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
2.提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

三、什么是 Binlog

MySQL 的二进制日志可以说 MySQL 最重要的日志了，它记录了所有的 DDL 和 DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有 1%的性能损耗。二进制有两个最重要的使用场景:

其一：MySQL Replication 在 Master 端开启 Binlog，Master 把它的二进制日志传递给 Slaves 来达到 Master-Slave 数据一致的目的。

其二：自然就是数据恢复了，通过使用 MySQL Binlog 工具来使恢复数据。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的 DDL 和 DML(除了数据查询语句)语句事件。

四、Binlog 的分类

MySQL Binlog 的格式有三种，分别是 STATEMENT,MIXED,ROW。在配置文件中可以选择配置 binlog_format= statement|mixed|row。三种格式的区别：

1. statement：

语句级，binlog 会记录每次一执行写操作的语句。相对 row 模式节省空间，但是可能产生不一致性，比如“update tt set create_date=now()”，如果用 binlog 日志进行恢复，由于执行时间不同可能产生的数据就不同。

优点：节省空间。

缺点：有可能造成数据不一致。

2. row：

行级， binlog 会记录每次操作后每行记录的变化。

优点：保持数据的绝对一致性。因为不管 sql 是什么，引用了什么函数，他只记录执行后的效果。

缺点：占用较大空间。

3. mixed：

statement 的升级版，一定程度上解决了，因为一些情况而造成的 statement 模式不一致问题，默认还是 statement，在某些情况下譬如：当函数中包含 UUID() 时；包含 AUTO_INCREMENT 字段的表被更新时；执行 INSERT DELAYED 语句时；用 UDF 时；会按照 ROW 的方式进行处理

优点：节省空间，同时兼顾了一定的一致性。

缺点：还有些极个别情况依旧会造成不一致，另外 statement 和 mixed 对于需要对 binlog 的监控的情况都不方便。

五、主从同步机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

六、配置的基本步骤

1、在主服务器上，必须开启二进制日志机制和配置一个独立的ID

2、在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号

3、在开始复制进程前，在主服务器上记录二进制文件的位置信息

4、如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）

5、配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

七、配置流程

友情提示：请尽量确保主从库大版本基本一致，这样可以减少很多麻烦，强扭的瓜不甜，强扭的5.7和8.0版本的数据库它也不甜🤔🤔🤔

1）主库配置及数据备份导出

1.更改主服务器的my.cnf配置文件（或者自定义的mysqld.cnf），开启bin-log

[mysqld]
log-bin=mysql-bin
server-id=1
expire_logs_days =10
max_binlog_size=100M
binlog_format=row

2.修改配置文件后重启mysql，进入mysql中查看master是否开启bin-log

ocker exec -it mysql /bin/bash
mysql -uroot -p
show master status;

如果提示为空值，如下:

mysql> show master status;
Empty set (0.00 sec)

#如果开启成功的话，log_bin的value为on，检查my.cnf是否修改正确，格式是否正确

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.00 sec)

3.开启成功记下主服务器的二进制日志信息

File的值是当前使用的二进制日志的文件名，Position是该日志里面的位置信息（不需要纠结这个究竟代表什么），记住这两个值，会在下面配置从服务器时用到。

注意：如果之前的服务器并没有配置使用二进制日志，那么使用上面的sql语句会显示空，在锁表之后，再导出数据库里的数据（如果数据库里没有数据，可以忽略这一步）

4.登入主服务器中的mysql，创建用于从服务器同步数据使用的帐号

mysql -uroot -p
CREATE USER 'slave'@'%' identified by 'yourpassword';
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* 'slave'@'%' identified by 'yourpassword';
FLUSH PRIVILEGES;

5.加锁并且导出master数据库数据

mysql > flush tables with read lock;
mysql > exit;
#退出mysqldump命令导出数据 ～/master_db.sql 路径可自定义
mysqldump -uroot -p --all-databases --lock-all-tables > ~/master_db.sql

解锁数据库(解锁数据库最好在从库成功连接主库之后，再进行解锁，如果在从库连接成功之前，主库有数据写入，那么从库的数据会和主库不一致，导致一些问题。)

mysql > unlock tables;

2）从库的配置

1.使用主从同步来备份

把主服务器的数据复制到从服务器上，然后备份从服务器的数据，在数据量不是很大的时候使用mysqldump命令，对于很大的数据库，就直接备份数据文件。

我这里是使用navicate运行sql文件

2.配置从库的my.cnf文件

建议开启从库的bin-log日志，这样万一主库灾难性的毁灭，从库随时可成为主库

3.从库遇问题

从库是部署在windows虚拟机上的docker，网络模式为桥接。

从库没有映射配置文件到宿主机中，也就是linux环境下修改不了my.cnf，其实是可以修改的，因为find / -name my.cnf可以找到docker中的my.cnf文件，修改后是一样的效果，本着折腾的原则，进入docker的mysql容器中，vim my.cnf文件发现bash: vim: command not found

所以我们需要apt-get update一下，然后apt-get install vim，但是update时失败，如下

百度得知应该是DNS的问题，返回宿主机linux环境中获取宿主机的DNS

nmcli dev show | grep 'IP4.DNS'

修改docker的daemon.json文件，加入如下配置

{
    "dns": ["192.168.23.2","144.144.144.144"]
}

重新进入mysql容器中，更新，install成功

4. 继续修改我们的my.cnf文件

重启从库，并检查从库bin_log是否开启成功

5.设置连接到master主库

#8.0版本之前使用：
CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=POS;
#8.0版本后使用REPLICATION，MASTER已废弃
CHANGE REPLICATION SOURCE TO SOURCE_HOST='master_host_name', SOURCE_USER='replication_user_name', SOURCE_PASSWORD='replication_password', SOURCE_LOG_FILE='recorded_log_file_name', SOURCE_LOG_POS=POS;

master_host_name ——–> master库ip
replication_user_name——–>创建的用于同步的slave账号
replication_password——–>密码
recorded_log_file_name——–>主库binlog文件名(见上)
POS——–>Position日志里面的位置信息(见上)

6.启动并查看slave连接状态

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G

上面的两个进程都显示YES则表示配置成功

第一个是负责读取主库的二进制文件，并写道从库的中继日志中

第二个是负责将中继日志转换成sql语句并执行

引文：安装参考

踩坑点：
- 主库数据导出前要加锁，或者停掉相关服务，防止数据库有新的写入
- 将主库数据导入从库时，去主库show master status一下，保证binlog文件名和pos值是正确的
- 尽量大版本相同的数据库进行主从同步

这里只是提供简单的主从同步设置，5.7版本中的主从同步还有很多细节设置，譬如bin-log日志Max_size的设置等等，可以后期进行一个设置

#mysql 5.7
[mysqld]
#server-id需要保证唯一性 不可与其他从服务器相同 如果为0会拒绝所有从服务器连接
server-id=1
log_bin=master-bin
#binlog的索引文件 可以不配置 默认会根据上面配置增加index后缀
log-bin-index=master-bin.index
#需要同步的数据库
#binlog-do-db=test
#不需要同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
#二进制日志大小设置
max_binlog_size=500M
# # 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
#为每个session分配的内存，在事务过程中用来存储二进制日志的缓存(可以不配置)
#binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row，默认格式是statement)
# binlog_format=mixed