MySQL主从复制架构

常见问题解决方案

☆ 常见问题1：MySQL无法启动

  1. # service mysqld start
  2. Redirecting to /bin/systemctl start mysqld.service
  3. Failed to start mysqld.service: Unit not found.

出现以上问题的主要原因在于/etc/init.d目录中没有mysqld这个文件换句话说，就是你没有cp mysql.server脚本

  1. # cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
  2. 记住：/etc/init.d目录中的文件叫什么名字，我们service的时候就应该使用什么名字
  3. # service mysqld start

☆ 常见错误2：MySQL没有安装在/usr/local/mysql目录中，service也无法启动

  1. # service mysqld start
  2. 无法启动的原因可能是你的MySQL并没有安装在/usr/local/mysql目录中，因为/etc/init.d/mysqld脚本中的basedir与datadir默认指向的都是/usr/local/mysql

所以如果我们更换了mysql的安装位置，则必须更改/etc/init.d/mysqld脚本中basedir与datadir目录

  1. # vim /etc/init.d/mysqld
  2. basedir=你的安装路径
  3. datadir=你的安装路径/data

☆ 常见问题3：因为my.cnf配置文件导致mysql无法启动

  1. # vim my.cnf
  2. [mysqld]
  3. basedir=/usr/local/mysql            =>    安装路径
  4. datadir=/usr/local/mysql/data        =>    数据目录
  5. socket=/tmp/mysql.sock                =>    GLIBC默认就是/tmp/mysql.sock
  6. port=3310
  7. log-error=/usr/local/mysql/slave.err    =>     错误日志到底放在哪里
  8. relay-log=/usr/local/mysql/data/relaylog
  9. server-id=100
  10. character_set_server=utf8mb4

启动报错：

  1. Starting MySQL.2020-08-31T07:17:06.554270Z mysqld_safe error: log-error set to '/usr/local/mysql/slave.err', however file don't exists. Create writable for user 'mysql'.
  2.  ERROR! The server quit without updating PID file (/usr/local/mysql/data/slave.itcast.cn.pid).

产生以上问题的主要原因在于mysql这个用户对/usr/local/mysql文件夹没有写入权限

  1. # ll -d /usr/local/mysql
  2. drwxr-xr-x 11 7161 31415 174 Aug 31 15:16 /usr/local/mysql
  3. 发现文件拥有者位置与所属组位置都是两个数字，正常应该是文件拥有者的名称与文件所属组的名称。但是由于GLIBC已经提前打包了，我们解压后，如果在我们系统中，找不到原文件对应的文件拥有着与所属组，则以两个数字代替文件拥有者与所属组的显示。

解决方案：

方案一：建议把错误日志，丢在数据目录中

  1. # vim my.cnf
  2. ...
  3. log-error=/usr/local/mysql/data/slave.err

方案二：直接更改/usr/local/mysql目录的权限

  1. # chown -R mysql.mysql /usr/local/mysql

④ 配置MASTER-SLAVE主从同步

a. 在MASTER主服务器中创建一个账号，专门用于实现数据同步

MySQL5.7及以下版本：

  1. mysql> grant replication slave on *.* to 'slave'@'10.1.1.%' identified by '123';

MySQL新版本中：

  1. mysql> create user 'slave'@'10.1.1.%' identified by '123';
  2. mysql> grant replication slave on *.* to 'slave'@'10.1.1.%';
  3. mysql> flush privileges;

b. 在MASTER中锁表，然后查看二进制文件的名称及位置

  1. mysql> flush tables with read lock;
  2. mysql> show master status;
  3. +---------------+----------+--------------+------------------+-------------------+
  4. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  5. +---------------+----------+--------------+------------------+-------------------+
  6. | binlog.000002 |      597 |              |                  |                   |
  7. +---------------+----------+--------------+------------------+-------------------+

c. 在SLAVE从服务器中，使用change master to指定主服务器，并实现数据同步

  1. mysql> change master to master_host='10.1.1.10',master_user='slave',master_password='123',master_port=3306,master_log_file='binlog.000002',master_log_pos=597;
  3. master_host：主机的IP地址
  4. master_user：主机的user账号
  5. master_password：主机的user账号密码
  6. master_port：主机MySQL的端口号
  7. master_log_file：二进制日志文件名称
  8. master_log_pos：二进制日志文件位置

技巧：主从复制的change master to语句记不住怎么办？答：求帮助，mysql> help change master to;

CHANGE MASTER TO
MASTER_HOST=’master2.example.com’,
MASTER_USER=’replication’,
MASTER_PASSWORD=’password’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’master2-bin.001’,
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

d. 启动slave数据同步

  1. mysql> start slave;
  2. mysql> show slave status\G

主从复制 - 图1

常见问题解决方案

在配置主从时，一般遇到错误，大部分都是change master to语句写错了（80%），解决方案：

  1. mysql> stop slave;
  2. mysql> reset slave;
  3. mysql> change master to master_host='10.1.1.10',master_user='slave',master_password='123',master_port=3306,master_log_file='binlog.000002',master_log_pos=597;
  5. mysql> start slave;

⑤ 主MASTER服务器解锁

  1. mysql> unlock tables;
  2. Query OK, 0 rows affected (0.00 sec)

总结：

  1. 主从复制必须保证两台数据库实例的server-id不一致
  2. 主服务器必须开启二进制日志；从服务器必须开启中继日志
  3. 主从复制搭建必须保证初始数据一致
  4. 主服务器必须要给从服务器创建一个复制用户，并授予复制权限
  5. Master—>Slave架构，主写会同步到从；而从写不会同步到主

6、SLAVE从服务器不小心写入数据解决方案

正常情况下：

MASTER既可以读，也可以写。但是SLAVE从服务器，只能执行读取操作。一旦我们在SLAVE从服务器中写入数据，则主从架构会失败。

SLAVE：

  1. # show slave status\G

主从复制 - 图2

遇到以上问题：如果数量较少，还可以通过跳过当前语句的方式解决。但是如果从服务器写入数据过多，则以上架构必须要重新搭建了！

解决方案：

问：

如果由于人为操作或者其他原因直接将数据更改到从服务器导致数据同步失效，怎么解决？

答：可以通过变量sql_slave_skip_counter临时跳过事务进行处理

  1. SET GLOBAL sql_slave_skip_counter = N      N代表跳过N个事务
  2. 举例说明：
  3. mysql> SET GLOBAL sql_slave_skip_counter=1;
  4. mysql> stop slave;
  5. mysql> start slave;
  6. 注意：
  7. 1. 跳过事务应该在slave上进行
  8. 2. 传统的AB复制方式可以使用变量：sql_slave_skip_counter，基于GTIDs的方式不支持

四、基于GTIDs的AB复制架构(M-S)

1、GTIDs概述

  • 什么是GTIDs以及有什么特点？
  1. GTIDs（Global transaction identifiers）全局事务标识符，是mysql 5.6新加入的一项技术
  2. 当使用GTIDs时，每一个事务都可以被识别并且跟踪
  3. 添加新的slave或者当发生故障需要将master身份或者角色迁移到slave上时，都无需考虑是哪一个二进制日志以及哪个position值，极大简化了相关操作
  4. GTIDs是完全基于事务的，因此不支持MYISAM存储引擎
  5. GTID由source_id和transaction_id组成：
    1）source_id来自于server_uuid,可以在auto.cnf中看到
    2）transation_id是一个序列数字，自动生成.
  • 使用GTIDs的限制条件有哪些？
  1. 不支持非事务引擎（MyISAM），因为可能会导致多个gtid分配给同一个事务
  2. create table … select 语句不支持（主库语法报错）
  3. create/drop temporary table 语句不支持
  4. 必须使用enforce-gtid-consistency参数
  5. sql-slave-skip-counter不支持(传统的跳过错误方式)
  6. GTID复制环境中必须要求统一开启和GTID或者关闭GTID
  7. 在mysql 5.6.7之前，使用mysql_upgrade命令会出现问题

2、基于GTIDs的主从复制

在生产环境中，大多数情况下使用的MySQL5.6基本上都是从5.5或者更低的版本升级而来，这就意味着之前的mysql replication方案是基于传统的方式部署，并且已经在运行，因此，接下来我们就利用已有的环境升级至基于GITDs的Replication

〇 思路

  1. 修改配置文件支持GTIDs (主+从)
  2. 重启数据库 (主+从)
  3. 为了保证数据一致性，master和slave设置为只读模式 (主+从)
  4. 从服务器上重新配置同步 （从）

3、基于GTIDs的主从复制实践

① 修改配置文件支持GTIDs

MASTER => my.cnf

  1. # vim my.cnf
  2. ...
  3. gtid-mode=on
  4. log-slave-updates=1
  5. enforce-gtid-consistency

SLAVE => my.cnf

  1. # rm -rf data/binlog.*
  2. # vim my.cnf
  3. ...
  4. log-bin=/usr/local/mysql/data/binlog    =>   必须要开启二进制
  5. gtid-mode=on
  6. log-slave-updates=1
  7. enforce-gtid-consistency
  8. skip-slave-start     =>    当MASTER主服务器GTIDs没有启动时，跳过SLAVE服务器的启动

说明：
1）开启GITDs需要在master和slave上都配置gtid-mode，log-bin，log-slave-updates，enforce-gtid-consistency（该参数在5.6.9之前是—disable-gtid-unsafe-statement）
2）其次，slave还需要增加skip-slave-start参数,目的是启动的时候，先不要把slave起来，需要做一些配置
3）基于GTIDs复制从服务器必须开启二进制日志！

② 重新启动mysqld服务

  1. # service mysqld restart

③ 主从配置只读模式

  1. mysql> set @@global.read_only=ON;

④ SLAVE重新配置change master to

  1. mysql> stop slave;
  2. mysql> reset slave;
  3. mysql> change master to master_host='10.1.1.10',master_user='slave',master_password='123',master_port=3306,master_auto_position=1;
  5. 注意：
  6. 1.确保有复制用户
  7. 2.主要区别于传统复制的参数是：master_auto_position=1
  9. mysql> start slave;
  10. mysql> show slave status\G

⑤ 关闭主从服务器的只读模式

  1. mysql> set @@global.read_only=OFF;

测试验证（往主服务器中写入部分数据，验证一下）

4、SLAVE从服务器不小心写入数据解决方案

方法一：跳过事务

  1. 指定需要跳过的GTIDs编号
  2. SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
  3. 开始一个空事务
  4. BEGIN;
  5. COMMIT;
  6. 使用下一个自动生成的全局事务ID。
  7. SET GTID_NEXT='AUTOMATIC';
  9. 举例说明：
  10. mysql> stop slave;
  11. mysql> SET @@SESSION.GTID_NEXT= '13e36f00-eb70-11ea-91a8-000c29d1f40a:1'/*!*/;
  12. mysql> BEGIN;
  13. mysql> commit;
  14. mysql> SET @@SESSION.GTID_NEXT= 'AUTOMATIC';    
  15. mysql> start slave;
  16. mysql> show slave status\G
  18. 说明：需要跳过哪个事务，需要手动查看relaylog文件得到
  19. [root@slave1 data]# ../bin/mysqlbinlog relay.000003|less
  20. 。。。。
  21. # at 756
  22. #181015 12:04:45 server id 10  end_log_pos 817 CRC32 0x5374f49e         GTID [commit=yes]
  23. SET @@SESSION.GTID_NEXT= '044e6392-cf9b-11e8-a748-000c294ca304:3'/*!*/;

方法二：重新同步data目录，重新change master to…