mysql主从复制

概述：

主从同步

mysql的主备库通过binlog日志保持一致，主库本地执行完事务，binlog日志落盘后即返回给用户；备库通过拉取主库binlog日志来同步主库的操作。默认情况下，主库与备库并没有严格的同步，因此存在一定的概率备库与主库的数据是不对等的。

当主从同步开启的时候，slave上会创建2个线程。

I/O线程，该线程连接到master机器请求binlog，该I/O线程接收到binlog内容后，再将内容写到本地的relaylog
SQL线程。该线程读取I/O线程写入的relaylog。并对slave数据库进行

半同步复制

半同步特性的是为了保证在任何时刻主备数据一致的问题。相对于异步复制，半同步复制要求执行的每一个事务，都要求至少有一个备库成功接收后，才返回给用户。实现原理也很简单，主库本地执行完毕后，等待备库的响应消息(包含最新备库接收到的binlog(file,pos))，接收到备库响应消息后，再返回给用户，这样一个事务才算真正完成。

在主库实例上，有一个专门的线程(ack_receiver)接收备库的响应消息，并以通知机制告知主库备库已经接收的日志，可以继续执行。

规划：

主： 172.16.41.167
备： 172.16.41.171
备份用户：rep171
密码：Rep171++'

基础

# 初始化  5.7  
mysqld  --initialize-insecure --user=mysql
'''必须保证是空库
--basedir=
--datadir=
--initialize  密码记录在日志中  grep 'temporary password' /var/log/mysqld.log
--initialize-insecure空密码
'''

# 删除默认账号   select host,user,file_priv,shutdown_priv,grant_priv from mysql.user;
grant all on *.* to 'xxx'@'localhost' identified by 'xxx' with grant option;
alter user 'xxx'@'localhost'  password expire never;
drop user root@localhost;
drop  user 'mysql.session'@localhost;
drop  user 'mysql.sys'@localhost;
flush privileges;

mkdir /var/log/mysql
chown  mysql.mysql /var/log/mysql

主配置

expire_logs_days=15                         # 日志保存天数
log_error=/var/log/mysql/log-error
slow_query_log=ON
slow_query_log_file=/var/log/mysql/query.log

skip-name-resolve
character-set-server=utf8
collation-server=utf8_general_ci
explicit_defaults_for_timestamp=true
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log_timestamps = SYSTEM                     # 同步系统时间
connect_timeout = 60                        # 连接超时
interactive_timeout = 120                   # 交互超时
max_allowed_packet=20M                      #  最大允许数据包 
max_connections = 2000
require_secure_transport=on          # 强制ssl

# 密码过期复杂度
plugin_load_add='validate_password.so'
validate_password_length=11
validate_password_mixed_case_count=3
validate_password_number_count=4
validate_password_special_char_count=1
default_password_lifetime=90

#general_log = 0                             # 逻辑日志关闭（默认）
#general_log_file=/var/log/mysql/general.log

server-id=167

innodb_buffer_pool_size = 8G                # 设置50%~80%

# 半同步复制
plugin_load_add="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
# rpl-semi-sync-slave-enabled = 1

log_bin=/var/log/mysql/master-bin
max_binlog_size=512m
#binlog_ignore_db=mysql
#binlog_format=MIXED 
binlog_format=ROW

show global variables like "binlog%";
show global variables like "%binlog_format%";  # 在数据库中查看binlog模式

备配置

expire_logs_days=15                         # 日志保存天数
log_error=/var/log/mysql/log-error
slow_query_log=ON
slow_query_log_file=/var/log/mysql/query.log

skip-name-resolve
character-set-server=utf8
collation-server=utf8_general_ci
explicit_defaults_for_timestamp=true
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log_timestamps = SYSTEM                     # 同步系统时间
connect_timeout = 60                        # 连接超时
interactive_timeout = 120                   # 交互超时
max_allowed_packet=20M                      #  最大允许数据包 
max_connections = 2000
require_secure_transport=on       # 强制ssl

# 密码过期复杂度
plugin_load_add='validate_password.so'
validate_password_length=11
validate_password_mixed_case_count=3
validate_password_number_count=4
validate_password_special_char_count=1
default_password_lifetime=90

server-id=171

innodb_buffer_pool_size = 8G                # 设置50%~80%

# 半同步复制
plugin_load_add="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

slave_skip_errors=1032,1062,,1007,1008 ,1819,1539,ddl_exist_errors
relay_log=/var/log/mysql/relay-bin
max_relay_log_size=512m
# 设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句(use mysql; update test.table1 ...)被忽略。
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=test.%
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
log-bin=0   # 关闭binlog日志

# 并行复制
slave_parallel_type= LOGICAL_CLOCK
slave_parallel_workers=4
# slave_preserve_commit_order=1
# 开启并行复制后，如果relay log中有3个事务A,B,C，他们在relay log中的顺序是A->B->C，而它们的last_commited相同，也就是说他们可以并行回放，那么在从库上，这3个事务，提交的顺序可能就不再是A->B->C，设置slave_preserve_commit_order=ON，保证从库回放relay log事务的顺序与主库完全相同 ，能够保证从库回放relay log事务的顺序与这些事务在relay log中的顺序完全一致，也就是与主库提交的顺序完全一致
# 在 8.0.18 及之前版本，从库只有开启binlog的两个参数（log_bin，log_slave_updates）
#

部署：

主库授权

grant replication slave on *.* to 'rep171'@'172.16.41.171' identified by  'Rep171++';
flush privileges;
show master status;                         # 查看二进制日志状态

不锁表备份

mysqldump  -u root -h 172.16.41.167 -p --master-data=2 --skip-lock-tables --single-transaction -F -A >/data/data.`date +%F`.sql
# mysqldump  -u 账号 -p  --master-data=2 --skip-lock-tables --single-transaction  --add-drop-database  -F -B 库1 库2  >sql.`date +%F`.sql


#  --databases  -B     
#  --flush-logs  -F 

# --lock-all-tables
# --lock-all-tables 使用的是 FLUSH TABLES READ LOCK 全局读锁，锁定所有 database 下的所有表

# --lock-tables 使用的是 LOCK TABLES tb1 READ,tb2 READ 锁定一个 database 下的所有表，处理完再锁另一个 database 的所有表
# --skip-lock-tables 不锁表
#--master-data=2 在导出的时候同时生成binlog文件名和位置在导出的文件开头。，binlog的文件和位置可以从这里拿到。
#--single-transaction 通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照(innodb)

# --master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
# --master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
# --dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
# --dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；
# 在从库上执行备份时，即--dump-slave=2，这时整个dump过程都是stop io_thread的状态

导入备份数据

 mysql -uroot -p  </data/data.2020-05-18.sql

配置从库

# reset slave;
change master to MASTER_HOST='172.16.41.167',master_ssl=1,  \
MASTER_USER='rep171',   \
MASTER_PASSWORD='Rep171++',   \
MASTER_PORT=3306,    \
MASTER_LOG_FILE='master-bin.000024',    \
MASTER_LOG_POS=154;

start slave;
show slave status\G                            # 查看中继日志状态
show processlist;　
#验证
#            Slave_IO_Running: Yes
#            Slave_SQL_Running: Yes
#            Seconds_Behind_Master

问题：

账户创建报密码问题

# 查看 mysql 初始的密码策略：
SHOW VARIABLES LIKE 'validate_password%'; 
# 修改设置密码强度等级
set global validate_password_policy=LOW        # 只验证密码长度
# 或按要求设置大小写字母 + 阿拉伯数字 + 特殊字符

主从库同一镜像，UUID一致，导致同步错误。
编辑 /var/lib/mysql/auto.cnf，更改uuid
ssl问题无法连接

没有证书要生成证书

mysql_ssl_rsa_setup  -d /data/mysql
show variables like 'have%ssl%';
show variables like 'require_secure_transport%';

组从连接要打开master_ssl

change master to master_ssl=1;

JAVA jdbc不能设置 userssl=fasle

同步错误

show slave status\G
# Last_SQL_Errno
stop slave;
set global sql_slave_skip_counter=1;           # 同步指针忽略一个
start slave;

编辑配置文件

slave_skip_errors=1032,1062,1007,1008            　# 根据错误号跳过指定错误

show  global variables   like '%slave_skip%';

同步错误号

1005：创建表失败
1006：创建数据库失败
1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1009：不能删除数据库文件导致删除数据库失败
1010：不能删除数据目录导致删除数据库失败
1011：删除数据库文件失败
1012：不能读取系统表中的记录
1020：记录已被其他用户修改
1021：硬盘剩余空间不足，请加大硬盘可用空间 
1022：关键字重复，更改记录失败
1023：关闭时发生错误
1024：读文件错误
1025：更改名字时发生错误
1026：写文件错误
1032：记录不存在
1036：数据表是只读的，不能对它进行修改
1037：系统内存不足，请重启数据库或重启服务器
1038：用于排序的内存不足，请增大排序缓冲区
1040：已到达数据库的最大连接数，请加大数据库可用连接数
1041：系统内存不足
1042：无效的主机名
1043：无效连接
1044：当前用户没有访问数据库的权限
1045：不能连接数据库，用户名或密码错误
1048：字段不能为空
1049：数据库不存在
1050：数据表已存在
1051：数据表不存在
1054：字段不存在
1065：无效的SQL语句，SQL语句为空
1081：不能建立Socket连接
1114：数据表已满，不能容纳任何记录
1116：打开的数据表太多
1129：数据库出现异常，请重启数据库
1130：连接数据库失败，没有连接数据库的权限
1133：数据库用户不存在
1141：当前用户无权访问数据库
1142：当前用户无权访问数据表
1143：当前用户无权访问数据表中的字段
1146：数据表不存在
1147：未定义用户对数据表的访问权限
1149：SQL语句语法错误
1158：网络错误，出现读错误，请检查网络连接状况
1159：网络错误，读超时，请检查网络连接状况
1160：网络错误，出现写错误，请检查网络连接状况
1161：网络错误，写超时，请检查网络连接状况
1062：字段值重复，入库失败
1169：字段值重复，更新记录失败
1177：打开数据表失败
1180：提交事务失败
1181：回滚事务失败
1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库
1205：加锁超时
1211：当前用户没有创建用户的权限
1216：外键约束检查失败，更新子表记录失败
1217：外键约束检查失败，删除或修改主表记录失败
1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器
1227：权限不足，您无权进行此操作
1235：MySQL版本过低，不具有本功能

1236: 无法找到主节点binlog索引文件

# 起因 停电启后从库报错
# Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

#  查看关机时指针位置
ls -l /data/mysql/data/xxx.*   # 查看binlog日志时间  是文件最后写入时间
# 重启不会 flush logs

show master logs;
show binlog events in  'xxx.xxxxxx' from 0 limit 4;  # from指定pod位置 limit指定显示行数

# 或 mysqlbinlog /data/mysql/data/xxx.xxxxxx  > /tmp/x.txt

# 在从库 手动调整指针位置
stop slave;
change master to master_log_file='xxx.xxxxxx',master_log_pos=xxx
start slave;
show slave status\G          

                  、