MySQL配置主从同步

实现步骤：

1．在主服务器上，必须开启二进制日志机制和配置一个独立的ID
2．在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
3．在开始复制进程前，在主服务器上记录二进制文件的位置信息
4．如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照，可通过mysqldump导出并同步数据
5．配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

　　

------主机my.cnf配置文件-----

log-bin=mysql-bin
server-id=1
binlog_format=ROW #复制模式
max_binlog_size=1G #超过max_binlog_size或超过6小时会切换到下一序号文件
sort_buffer_size=2097152
group_concat_max_len=10240
replicate-do-db = xdb
binlog-do-db=xdb
log_bin=/var/lib/mysql/mysql-bin.log #默认路径可修改
expire_logs_days=7 #日志过期时间，设置为0则永不过期
binlog_cache_size=256M #二进制日志缓冲大小,通过show status like 'binlog_%';查看调整写入磁盘的次数，
最好max_binlog_cache_size = 256M
relay_log_recovery=1 #当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。
sync_binlog= 1 #二进制日志（binary log）同步到磁盘的频率
innodb_flush_log_at_trx_commit = 1 #每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。
#————————————————
event_scheduler=ON
max_connections=2000

---创建复制账户(主备机都需要)，并查看当前master的位置点----------

 注：配置文件修改后需要执行systemctl restart mysqld

------备机my.cnf配置文件-----

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#relay_log=/var/log/mysql-relay-bin  #如果写relay-bin-log，需要新建指定目录，并分配权限给mysql
server-id=2
replicate-do-db = xdb
log-bin=mysql-bin
binlog-do-db=xdb
expire_logs_days = 7
max_binlog_size = 1G
sort_buffer_size=2097152
group_concat_max_len=10240

 注：配置文件修改后需要执行systemctl restart mysqld

------从库执行同步操作-----

注意：执行同步前，请先备份主库sql并同步从库，否则后面slave同步时日志会持续报错。操作步骤如下：

mysql > flush tables with read lock;

[root@localhost]# mysqldump -uroot -p'123456' --all-databases > /home/mysql_bak_$(date +%F).sql

mysql > use 指定数据库;

mysql > source /home/mysql_bak.sql;

mysql > unlock tables;

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='172.x.x.x', 
    -> MASTER_USER='user',
    -> MASTER_PASSWORD='password', 
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=882;

　　

 stop slave;  #停止从库同步进程

 start slave;  #开户从库同步进程

附1：主从同步流程图

附2：binlog及其分类

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

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

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

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

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

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 的监控的情况都不方便。

【常见问题】

1. 错误码2061，主从同步报错用户认证方式失败

Last_IO_Errno: 2061
Last_IO_Error: Error connecting to source 'replica@192.168.1.75:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决办法：

mysql> alter user 'replica'@'%' identified with mysql_native_password BY '新密码';

mysql> flush privileges;

mysql> select host,user,plugin from mysql.user;　　

 2. 主从同步中断，重置数据

主库执行如下命令：

flush tables with read lock;      #中止所有表写入操作
reset master;                     #重置主从状态
show master status;               #记录log_file和log_position
mysqldump -uroot -p'******' callcenter > /home/callcenter.sql;          #导出主库备份文件，再导入从库

　　

从库执行如下命令：

stop slave;
CREATE DATABASE `callcenter` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
source /home/callcenter.sql;
start slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.75',MASTER_USER='replica',MASTER_PASSWORD='******',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1111;
show slave status\G;

　　

unlock tables;      #恢复主库数据写入

　　

3. 主从同步异常，报错信息如下图所示：

 解决办法：

mysql> select * from performance_schema.replication_applier_status_by_worker\G

 

对比主从库里不一致的数据，从库做删除操作，再次尝试执行同步

【注】：一般导致这种情况是因为主库没有执行flush tables with read lock; #中止所有表写入操作

 

4.   Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Could not find first log file name in binary log index file'

解决办法 ： systemctl restart mysql

 

5. ERROR 1802 (HY000): CHANGE REPLICATION SOURCE cannot be executed when the replica was stopped with an error or killed in MTA mode. Consider using RESET REPLICA or START REPLICA UNTIL.

6. Got fatal error 1236 from source when reading data from binary log: 'Client requested source to start replication from position > file size'

解决办法：reset slave all  

 

附：my.cnf配置参考 

[mysqld]
user = mysql
#bind-address		= 127.0.0.1
#mysqlx-bind-address	= 127.0.0.1
key_buffer_size		= 16M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
server-id=1
log_bin=mysql-bin
binlog_format=row
character-set-server = UTF8MB4
lower_case_table_names=0
innodb_log_file_size=1G
default-storage-engine=INNODB
#default_authentication_plugin=mysql_native_password
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
innodb_ft_min_token_size=1
ngram_token_size=1
max_connections=30000
max_connect_errors=10000
binlog-do-db=callcenter
binlog-do-db=uincti
binlog-do-db=db0
binlog-do-db=db1
expire_logs_days = 7
max_binlog_size = 1G
#sort_buffer_size为2M
sort_buffer_size=2097152
group_concat_max_len=10240
innodb_buffer_pool_size=2G