Mysql-主从同步配置

一、基本过程：

1）数据库应用开启BINLOG二进制日志功能

2）主数据库写入BINLOG日志

3）备数据库拉取BINLOG日志

4）备数据库重放BINLOG日志

5）主从同步完成

二、BINLOG日志功能启停

1）查询是否开启BINLOG

show variables like ‘%log_bin%’;

2）开启BINLOG，编辑My.ini文件

log-bin="DESKTOP-EVRO81H-bin" #日志命名格式

binlog-format=ROW #日志记录格式

三、基本原理

1）主数据库应用中数据事务提交时会同步写入Binlog日志

2）备数据库会根据配置的间隔时间对主数据库的Binlog进行探测，若发生改变，则开始一个I/O线程，对主数据库发起请求二进制日志的事件

3）届时，主数据库会为每一个I/O线程启动一个dump线程，并向其发送二进制事件。并保存至备节点本地服务器上的中继日志中

4）备节点启动SQL线程读取中继日志文件中的二进制日志，进行重复，使得其数据和主节点的保持一致

5） I/OThread 和 SQLThread 将进入睡眠状态，等待下一次被唤醒。

四、主从同步配置（半同步）

1）编辑主库的my.ini文件

[mysqld]

log-bin=/data/3306/mysql-bin.log

log-bin-index=binlog.index

server-id=1

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=6  #并发线程数，推荐设置（8-16）32 核物理机的情况

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

#推荐开启半同步复制。（主备都需要加）

plugin-load="semisync_master.so;semisync_slave.so"

rpl_semi_sync_master_enabled=ON

rpl_semi_sync_slave_enabled=ON

rpl_semi_sync_master_wait_no_slave=ON

修改配置需要重启数据库生效

[root@db01 ~]# /etc/init.d/mysqld restart      

[root@db01 ~]# systemctl restart mysqld

2）记录主库binlog日志的pos值

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

3）主库创建复制用户如rep用

mysql> CREATE USER 'rep'@'10.XX.XX.XX' IDENTIFIED BY 'rep';

mysql> grant replication slave on *.* to rep@'10.XX.XX.XX';

4）主库进行全备份

mysqldump -uroot -h127.0.0.1 -p123456  -P3307  --flush-privileges  --single-transaction --master-data=2 --flush-logs --triggers  --routines  --events  --all-databases > fullbackup.sql

5）备库导入主库全备份

Mysql -uroot -p -Pxxx -hxxx < fullbackup.sql

6）备库修改配置文件

vim /etc/my.cnf

#在[mysqld]标签下开启server_id  从库server_id不可与主库相同，从库之间可以一致

server-id=2

log-bin=/data/3306/mysql-bin.log

log-bin-index=binlog.index

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=6 #并发线程数，推荐设置（8-16）32 核物理机的情况

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

#开启半同步复制

plugin-load="semisync_master.so;semisync_slave.so"

rpl_semi_sync_master_enabled=ON

rpl_semi_sync_slave_enabled=ON

rpl_semi_sync_master_wait_no_slave=ON

#忽略同步系统表

replicate_ignore_db=information_schema replicate_ignore_db=performance_schema

replicate_ignore_db=mysql

replicate_ignore_db=sys

#跳过所有报错，避免错误影响同步（配置此项之后需要添加监控，获取报错）

slave-skip-errors=all

随后重启数据库

7）备库配置主库信息

mysql> change master to

master_host='172.XX.XX.XX',     #主库ip

master_user='rep',              #主从复制用户

master_password='redhat',        #主从复制用户密码

master_log_file='mysql-bin.000003',     #binlog

master_log_pos=120,            #binlog 起始点

master_port=3306;            #数据库端口

8）备库开启salve模式，正式成为备库

mysql> start slave;

查看slave状态（yes开启成功）

mysql> show slave status\G

9）验证主备同步

主库创建表，在从库能查询到证明能正常同步