MySQL主从同步

****1 mysql主从同步
	1.1 相关概念：
		1 主从同步介绍（什么是主从同步） 存储数据的服务结构
		分为两种角色：
			主数据库服务器：接收客户端连接的服务器
			从数据库服务器：自动同步主数据库服务器的数据到本机
		2 主从同步工作过程？
			主服务器必须启用binlog日志
			从服务器会有IO线程和SQL线程
	IO线程的工作任务：复制master主机 binlog日志文件里的SQL命令到本机的relay-log文件里。
	SQL线程的工作任务：执行本机relay-log文件里的SQL语句，实现与Master数据一致。
	
	1.2 配置 mysql主从同步
		1 拓扑结构
		2 配置mysql主从同步
			2.1 配置主服务器（master）具体配置如下：192.168.4.51
			启用binlog日志

]# vim /etc/my.cnf
server_id=51
log_bin
:wq
[root@host51 ~]# systemctl restart mysqld
用户授权(给从服务器拷贝sql命令时连接使用的用户)
[root@host51 ~]# mysql -uroot -p123qqq…A
mysql> grant replication slave on . to repluser@"%" identified by “123qqq…A”;
查看日志信息
mysql> show master status;
±------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±------------------±---------±-------------±-----------------±------------------+
| host51-bin.000001 | 441 | | | |
±------------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
mysql>
2.2 配置从服务器（slave）192.168.4.52 具体步骤如下:
1 指定server_id 并重启数据库服务
]# vim /etc/my.cnf
[mysqld]
server_id=52
:wq
[root@host52 ~]# systemctl restart mysqld
[root@host52 ~]# setenforce 0
[root@host52 ~]# systemctl stop firewalld
2 确保与主数据库服务器数据一致
在主服务器51 做数据的完全备份
[root@host51 ~]# mysqldump -uroot -p123qqq…A --master-data -B bbsdb db1 db3 > /root/bak.sql
[root@host51 ~]# scp /root/bak.sql root@192.168.4.52:/root/
在从服务器52 使用备份文件恢复数据
[root@host52 ~]# mysql -uroot -p123qqq…A < /root/bak.sql
[root@host52 ~]# mysql -uroot -p123qqq…A -e ‘show databases’
[root@host52 ~]# grep host51 /root/bak.sql
CHANGE MASTER TO MASTER_LOG_FILE=‘host51-bin.000001’, MASTER_LOG_POS=441;
[root@host52 ~]#
3 数据库管理员登录服务，指定主服务器信息
mysql> change master to master_host=“192.168.4.51” , master_user=“repluser” , master_password=“123qqq…A” ,
master_log_file=“host51-bin.000001” , master_log_pos=441 ;
4 启动slave进程
mysql> start slave;
5 查看进程状态
MySQL> show slave status \G
Master_Host: 192.168.4.51
Slave_IO_Running:Yes
Slave_SQL_Running: Yes
3 测试主从同步配置
3.1 在主服务添加客户端连接使用的用户
mysql> grant select , insert , update ,delete on db3.* to yaya@"%" identified by “123qqq…A”;
mysql> grant select , insert , update ,delete on db1.* to yaya@"%";
[root@host52 ~]# mysql -uroot -p123qqq…A -e ‘select user from mysql.user’
3.2 在客户端50连接连接主服务器，存储数据
[root@host50 ~]# mysql -h192.168.4.51 -uyaya -p123qqq…A
mysql> show grants;
mysql> insert into db3.user(name,uid)values(“xxx”,999);
Query OK, 1 row affected (0.01 sec)
mysql> insert into db3.user(name,uid)values(“xxx2”,999);
Query OK, 1 row affected (0.01 sec)
mysql> insert into db3.user(name,uid)values(“xxx3”,999);
Query OK, 1 row affected (0.02 sec)
mysql> exit
3.3 客户端50 连接从服务器也可以看到同样的数据。
mysql> select name,uid from db3.user where name like ‘x%’;
±-----±-----+
| name | uid |
±-----±-----+
| xxx | 999 |
| xxx2 | 999 |
| xxx3 | 999 |
±-----±-----+
3 rows in set (0.00 sec)
mysql>
1.3 排错
3从服务器数据库目录下会自动创建相关的配置文件
相关的配置文件（里存储的是什么信息）
]# cd /var/lib/mysql/
master.info 记录主服务器服务器信息
主机名-relay-bin.编号 中继日志文件 （存放io线程拷贝的sql命令）
主机名-relay-bin.index 中继日志所有文件 （保存当前已有中继日志文件）
relay-log.info 日志文件 （记录中继日志信息的文件）

	1.3 排错

查看报错信息 show slave status \G
Last_IO_Error: io线程的报错信息
Last_SQL_Error: sql线程的报错信息

	把从数据库服务器还原，重新配置。
	  把相关的配置文件删除
	  重启数据库服务	

[root@host52 mysql]# rm -rf master.info relay-log.info host52-relay-bin.00000* host52-relay-bin.index
[root@host52 mysql]# systemctl restart mysqld
[root@host52 mysql]# mysql -uroot -p123qqq…A
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
52 ]# vim /var/lib/mysql/auto.cnf
52 ]# systemctl restart mysqld
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Client requested master to start replication from position > file size’
51 show master status ;
52 stop slave;
change master to master_log_file=“日志名” , master_log_pos=偏移量；
start slave;
show slave status \G
Last_SQL_Error: Could not execute Write_rows event on table db3.user; Duplicate entry ‘52’ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log localhost-bin.000001, end_log_pos 1206 老师我这么也有问题
52 drop database db3.user;
51 mysqldump -uroot -p密码 db3 user > /root/user.sql
scp /root/user.sql root@192.168.4.52:/root/
52 stop slave;
use db3;
source /root/user.sql
start slave;
show slave status \G
mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

检查 /etc/my.cnf 文件 指定 server_id=52 吗 ？
指定server_id 后 重启 mysqld服务 再执行 change master to 然后再执行start slave;
SLAVE_IO_Running:Connecting

52 cat /var/lib/mysql/master.info
[root@host52 ~]# mysql -h192.168.4.51 -urepluser -p123qqq…A
51 HE 52 firewalld 都要关闭
2 mysql主从同步模式
2.1 mysql主从同步结构类型
一主一从 51 52
一主多从
主从从结构
2.2 配置mysql一主多从同步结构： 把数据库服务器53也配置为51的从数据库服务器
51的配置 数据库服务要运行着 binlog日志是启用的 有授权用户repluser
从服务器53的配置
安装MySQL服务软件并运行服务
1 指定server_id 并重启数据库服务
2 确保与主服务器数据一致
[root@host51 ~]# mysqldump -uroot -p123qqq…A --master-data -B bbsdb db1 db3 mysql > /root/four.sql
[root@host51 ~]# scp /root/four.sql root@192.168.4.53:/opt/
[root@host53 ~]# mysql -uroot -p123qqq…A < /opt/four.sql
[root@host53 ~]# systemctl restart mysqld
3 测试主服务器的授权用户
[root@host53 ~]# mysql -h192.168.4.51 -urepluser -p123qqq…A
4 管理员root登录 指定主服务器信息
[root@host53 ~]# grep -i “host51-bin” /opt/four.sql
CHANGE MASTER TO MASTER_LOG_FILE=‘host51-bin.000001’, MASTER_LOG_POS=3119;
[root@localhost ~]#
mysql> change master to master_host=“192.168.4.51” , master_user=“repluser” , master_password=“123qqq…A” , master_log_file=“host51-bin.000001” , master_log_pos=3119;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
5 启动slave 进程
mysql> start slave ;
6 查看状态
mysql> show slave status \G
Master_Host: 192.168.4.51
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试：50 连接51 存储数据
在52 和 53 服务器上也能看到同样的数据
[root@host50 ~]# mysql -h192.168.4.51 -uyaya -p123qqq…A
mysql> insert into db1.t3
mysql> insert into db1.t3 values (“yyy”,“yyy@163.com”);
mysql> insert into db1.t3 values (“yyy”,“yyy@163.com”);
mysql> insert into db1.t3 values (“yyy”,“yyy@163.com”);
[root@host50 ~]# mysql -h192.168.4.52 -uyaya -p123qqq…A -e ‘select * from db1.t3’
[root@host50 ~]# mysql -h192.168.4.53 -uyaya -p123qqq…A -e ‘select * from db1.t3’
配置mysql 主从从同步结构 环境准备
把53 恢复为独立的数据库服务器
20 cd /var/lib/mysql
21 rm -rf master.info relay-log.info -bin.
22 systemctl restart mysqld
23 mysql -uroot -p123qqq…A -e ‘show slave status \G’
24 mysql -uroot -p123qqq…A -e ‘show databases’ 只保留默认的4个库
29 mysql -uroot -p123qqq…A -e ‘select user,host from mysql.user’ 删除自己添加的授权用户
30* mysql -uroot -p123qqq…A -e ‘delete from mysql.user where user not in (“root”,“mysql.sys”)’
32 mysql -uroot -p123qqq…A -e ‘flush privileges’

	54主机   netstat  -utnlp  | grep  3306
	55主机   netstat  -utnlp  | grep  3306

*****关闭所有服务器的 selinux 和 firewalld
具体配置如下：
配置主服务器192.168.4.53
[root@host53 mysql]# vi /etc/my.cnf
[mysqld]
server_id=53
log_bin=master53
:wq
[root@host53 mysql]# systemctl restart mysqld
[root@localhost mysql]# mysql -uroot -p123qqq…A
mysql> grant replication slave on . to repluser2@"%" identified by “123qqq…A”;
mysql> show master status;
±----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±----------------±---------±-------------±-----------------±------------------+
| master53.000001 | 442 | | | |
±----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
mysql>
配置数据库服务器192.168.4.54
把其配置为192.168.4.53 的从服务器
同时做192.168.4.55的主服务器

]# vim /etc/my.cnf
[mysqld]
server_id=54
log_bin=master54
log_slave_updates 启用级联复制功能
:wq
]# systemctl restart mysqld
]# mysql -uroot -p密码
mysql> change master to master_host=“192.168.4.53” , master_user=“repluser2” , master_password=“123qqq…A” , master_log_file=“master53.000001” , master_log_pos=442;
mysql> start slave ;
mysql> show slave status \G
mysql> grant replication slave on . to repluser3@"%" identified by “123qqq…A”;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
±----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±----------------±---------±-------------±-----------------±------------------+
| master54.000001 | 442 | | | |
±----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
mysql>
配置数据库服务器192.168.4.55 做主机192.168.4.54的从服务器
指定server_id 并重启服务
测试54的授权用户repluser3
管理登录 指定主服务器信息
启动slave 进程
查看状态
]# vim /etc/my.cnf
[mysqld]
server_id=55
:wq
]# systemctl restart mysqld
~]# mysql -h192.168.4.54 -urepluser3 -p123qqq…A
mysql> exit;
]# mysql -uroot -p密码
mysql> change master to master_host=“192.168.4.54” , master_user=“repluser3” , master_password=“123qqq…A” , master_log_file=“master54.000001” ,master_log_pos=442;
mysql> start slave;
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试主从从同步结构的配置
在主服务器53存储数据
mysql -uroot -p123qqq…A -e ‘create database bbsdb’
mysql -uroot -p123qqq…A -e ‘create table bbsdb.t1(id int)’
mysql -uroot -p123qqq…A -e ‘insert into bbsdb.t1 values(119)’
mysql -uroot -p123qqq…A -e ‘select * from bbsdb.t1’
在服务器54 和 55 上能看到同样的数据
]# mysql -uroot -p123qqq…A -e ‘select * from bbsdb.t1’
2.4 配置mysql主主同步结构（也被称为互为主从）（课后练习）
把数据库服务器56 和 57 配置为主主结构
验证方式 2台服务器的IO线程的SQL线程都是Yes状态

2.5 MySQL主从同步数据复制模式
	2.5.1  数据复制模式
		异步复制模式（默认）
		半同步复制模式
		休息一下 到 16：52上课  
	2.5.2  配置半同步数据复制模式
		查看是否允许加载模块

mysql> show variables like ‘have_dynamic_loading’;
±---------------------±------+
| Variable_name | Value |
±---------------------±------+
| have_dynamic_loading | YES | 允许
±---------------------±------+
1 row in set (0.02 sec)
mysql>
主服务器要启用半同步复制模式
加载master模块
启用master模块
命令行加载与启用
mysql> install plugin rpl_semi_sync_master SONAME “semisync_master.so”;
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like ‘%semi%’; 查看模块是否安装成功
±---------------------±--------------+
| plugin_name | plugin_status |
±---------------------±--------------+
| rpl_semi_sync_master | ACTIVE |
±---------------------±--------------+
1 row in set (0.00 sec)
mysql>
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> show variables like “rpl_semi_sync_%enabled"; 模块是否启用成功
±-----------------------------±------+
| Variable_name | Value |
±-----------------------------±------+
| rpl_semi_sync_master_enabled | ON |
±-----------------------------±------+
1 row in set (0.00 sec)
永久配置/etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
:wq
从服务器55要启用半同步复制模式
加载slave模块
启用slave模块
mysql> install plugin rpl_semi_sync_slave SONAME “semisync_slave.so”;
Query OK, 0 rows affected (0.04 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like ‘%semi%’;
±--------------------±--------------+
| plugin_name | plugin_status |
±--------------------±--------------+
| rpl_semi_sync_slave | ACTIVE |
±--------------------±--------------+
1 row in set (0.01 sec)
mysql> show variables like "rpl_semi_sync%_enabled”
-> ;
±----------------------------±------+
| Variable_name | Value |
±----------------------------±------+
| rpl_semi_sync_slave_enabled | ON |
±----------------------------±------+
1 row in set (0.05 sec)
mysql>
永久配置 vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
:wq

		192.168.4.54 主机既是主 又是 从 ，2种类型的模块都要安装和启用
		  安装模块

mysql> install plugin rpl_semi_sync_slave SONAME “semisync_slave.so”;
mysql> install plugin rpl_semi_sync_master SONAME “semisync_master.so”;
启用模块
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> set global rpl_semi_sync_master_enabled=1;
永久配置 vim /etc/my.cnf
[mysqld]
plugin-load=“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
:wq****