mysql5.6双主架构搭建及常见错误解决方法

需求：在原有的一个库的基础上增加一个库
思路：新建一个库做成原库的从库，待数据同步完成做成双主架构

操作系统：centos7
数据库版本：mysql5.6
服务器 原库master1 新库master2

以下步骤两台服务器均执行：
centos7默认安装了mariadb-libs需要先卸载
检查安装包
rpm -qa | grep mariadb
卸载
yum -y remove mariadb*

配置yum源（安装哪个版本就把哪个enable打开）

Enable to use MySQL 5.5

[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Enable to use MySQL 5.6

[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Enable to use MySQL 5.7

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/enabled=1gpgcheck=1gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql[mysql-tools-preview]name=MySQLToolsPreviewbaseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.5-community]
name=MySQL Cluster 7.5 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.6-community]
name=MySQL Cluster 7.6 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

yum clean all
yum makecache
安装数据库
yum -y install mysql-community-server
创建文件夹
mkdir -p /home/mysql/data
修改文件夹属性
chown -R mysql:mysql /home/mysql/data
修改数据库配置文件
vim /etc/my.cnf
添加以下内容：

[client]
port = 3306
socket = /home/mysql/mysql.sock

[mysqld]
server-id = 1 #master1和master2不可相同， master2设置为2
auto_increment_offset = 1 #master1和master2不可相同， master2设置为2
auto_increment_increment = 2
datadir=/home/mysql/data
socket=/home/mysql/mysql.sock
pid-file=/home/mysql/mysql.pid
log-error=/home/mysql/error.log

log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
binlog-format=ROW
binlog-row-image=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集
init-connect=‘SET NAMES utf8’ #连接时执行的SQL
character-set-server=utf8 #服务端默认字符集
wait_timeout=1800 #请求的最大连接时间
interactive_timeout=1800 #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式
max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K

skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 16M

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

配置文件参数根据需求设置

启动数据库
systemctl start mysql
设置root密码
mysqladmin -u root password

将原库所有库数据备份，并恢复到新库。
备份：
mysqldump -h127.0.0.1 -S /home/app/mysql/mysql.sock -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases cs-data information_schema kcwl kcwl_master1 kcwl_master2 kcwl_slb kcwl_xn kcwlacc kcwl-bak kcwlbiz kcwlhbl kcwlx lw_test mysql newcrm sampledb scenery test test001 test_kcwl test_tx tmp tmp1 <full_backup.sql
#mysql库必须备份并恢复，否则两个库用户和授权不统一

如果你不同步其他库，需要在需要在my.cnf中添加f中添加下面这些（不同步哪个库就加上哪个库）
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%
replicate-wild-ignore-table=union_log%.%
replicate-wild-ignore-table=test.%

恢复：
mysql -uroot -p 登录数据库
source /home/full_backup.sql
做授权：
grant replication slave on . to ‘slave’@‘master1IP’ identified by ‘123456’; #必须为*.*
查看master_log_file ,master_log_pos这两个参数
more full_backup.sql
mysql> CHANGE MASTER TO MASTER_HOST=‘master1IP’,MASTER_USER=‘slave’,MASTER_PASSWORD=‘123456’,MASTER_AUTO_POSITION=0,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000018’,MASTER_LOG_POS=191;
mysql> start slave;
mysql> show slave status\G;
显示有如下且无报错状态则正常：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此时可在master1插入一条数据，测试2库是否同步。
若无问题继续一下操作。

如果是物理备份还原：
首先将物理备份拷贝到 mysql/data目录下
然后执行 innobackupex --defaults-file=/etc/my.cnf --apply-log /mysql/data

把/software/2019-03-05_05-02-01目录下的所有文件拷贝到datadir目录，改成mysql的属主属组，
2、然后执行innobackupex --defaults-file=/etc/my.cnf --apply-log /software/mysql/data 这是logfile文件的属主属组是root 改成mysql
3、一般数据库需要升级，mysql_upgrade

以下为原库执行：
做授权：
mysql> grant replication slave on . to ‘slave’@‘master2IP’ identified by ‘123456’; #必须为*.*
mysql> CHANGE MASTER TO MASTER_HOST=‘master1IP’,MASTER_USER=‘slave’,MASTER_PASSWORD=‘123456’,MASTER_AUTO_POSITION=0,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000018’,MASTER_LOG_POS=191;
#master_log_file ,master_log_pos这两个参数在master2库内执行show master status;获得。
mysql> start slave;
mysql> show slave status\G;
显示有如下且无报错状态则正常：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

检查双库show slave status\G;如无异常报错，则配置成功

一般同步模式的跳过命令
stop slave;
set global sql_slave_skip_counter =1;
start slave;
show slave status\G

node3 [localhost] {msandbox} (test) > stop slave;
Query OK, 0 rows affected (0.05 sec)

node3 [localhost] {msandbox} (test) > set gtid_next=“00014520-2222-2222-2222-222222222222:3”;
Query OK, 0 rows affected (0.00 sec)

node3 [localhost] {msandbox} (test) > begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

node3 [localhost] {msandbox} (test) > set gtid_next = ‘AUTOMATIC’;
Query OK, 0 rows affected (0.00 sec)

node3 [localhost] {msandbox} (test) > start slave;
Query OK, 0 rows affected (0.03 sec)

node3 [localhost] {msandbox} (test) > show slave status\G;

安装：（两个工具都是percona-tools中的）
yum -y install https://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum -y install yum install percona-toolkit

情景一：
跳过错误gtid事物(办法一)
由于是运行在GTID模式下，所以不支持sql_slave_skip_counter语法，如果想跳过，就必须把事务ID设置为空值。
Retrieved_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-2
Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1
根据在show slave stauts\G中获得的信息，观察Retrieved_Gtid_Set和Executed_Gtid_Set这两行内容，第一行代表接收到的事务，第二行代表已经执行完的事务。
也就是说，在执行cf716fda-74e2-11e2-b7b7-000c290a6b8f:2这个事务时报错了，这时，只需跳过这个错误事务就可，如下所示：
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> SET GTID_NEXT=‘cf716fda-74e2-11e2-b7b7-000c290a6b8f:2’;
Query OK, 0 rows affected (0.01 sec)
mysql> begin;commit;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> SET GTID_NEXT=“AUTOMATIC”;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
show slave status \G;
跳过错误gtid事物(办法二)从库执行
pt-slave-restart -h 127.0.0.1 -uroot -p123456 --skip-count 100
或
pt-slave-restart -h 127.0.0.1 -uroot -p123456 --error-numbers=1236

主从进程必须正常，否则无法将检查表同步到从库(解决办法一跳过错误事物，二从库建检查表)且不一致的表必须有主键或唯一索引，否则执行会很慢，且修复不成功
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters -h主库地址 -P3306 -uroot --ask-pass --recursion-method=processlist
在主库执行，填写主库IP地址和登录信息
pt-table-sync --print --sync-to-master h=‘从库地址’,P=3306,u=root --ask-pass > ./sync.sql
在主库执行，填写从库IP地址和登录信息
pt-table-sync --print --sync-to-master h=‘从库地址’,P=3306,u=root --ask-pass
或
pt-table-sync --execute --sync-to-master h=‘从库地址’,P=3306,u=root --ask-pass

手动创建checksums表语句
create database percona default character set utf8;
CREATE TABLE IF NOT EXISTS percona.checksums (db char(64) NOT NULL,tbl char(64) NOT NULL,chunk int NOT NULL,chunk_time float NULL,chunk_index varchar(200) NULL,lower_boundary text NULL,upper_boundary text NULL,this_crc char(40) NOT NULL,this_cnt int NOT NULL,master_crc char(40) NULL,master_cnt int NULL,ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY(db, tbl, chunk),INDEX ts_db_tbl (ts, db, tbl)) ENGINE=InnoDB;

情景二：
mysql主二报错：
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000026’ at 1744412540, the last event read from ‘/var/lib/mysql/mysql-bin.000026’ at 1744412540, the last byte read from ‘/var/lib/mysql/mysql-bin.000026’ at 1744416768.’
读取binlog日志错误
解决办法：
主二上停止slave;
mysql>stop slave;

登录主一：
mysql> show master status;
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| mysql-bin.000027 | 12371 | | | 359ba917-1346-11e6-8583-00163e161146:1-3844526,
46cfede8-dc45-11e8-b9de-00163e1627f2:4-103939 |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)
记录master的bin的位置:
mysql> show master status;
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| mysql-bin.000028 | 231 | | | 359ba917-1346-11e6-8583-00163e161146:1-3844526,
46cfede8-dc45-11e8-b9de-00163e1627f2:4-103939 |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

因为刷新日志file的位置会+1，即File变成为:mysqld-bin.000028

主二重新读取binlog进行同步

mysql> change master to master_log_file=‘mysql-bin.000028’, master_log_pos=231;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;

Query OK, 0 rows affected (1 min 15.11 sec)

mysql>
mysql> change master to master_log_file=‘mysql-bin.000028’, master_log_pos=231;
Query OK, 0 rows affected (0.19 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
注意：必须停止slave
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.46.173.141
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000028
Read_Master_Log_Pos: 6148
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 4661
Relay_Master_Log_File: mysql-bin.000028
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

状态为均为yes，问题解决

情况三：1418错误
因为CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,ALTER FUNCTION,CALL, DROP PROCEDURE, DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况。

为了解决这个问题，MySQL强制要求：
在主服务器上，除非子程序被声明为确定性的或者不更改数据，否则创建或者替换子程序将被拒绝。
这意味着当创建一个子程序的时候，必须要么声明它是确定性的，要么它不改变数据。

第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:
1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
2.MySQL启动时，加上–log-bin-trust-function-creators选贤，参数设置为1
3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1