MySQL 学习五 部署mysql 主从同步

一、什么是mysql主从同步
主：正在被客户端访问的数据库，被称作主服务器
从：自动同步主库上的数据的服务器，被称作从库服务器
二、配置mysql主从同步
2.1 数据库服务器：192.168.4.52 做主库
数据库服务器：192.168.4.51 做从库
客户端：192.168.4.53
2.2 环境准备
主从同步未配置前，要保证从库上要有主库上的数据。
禁用selinux #setenforce 0
关闭防火墙服务 # systemctl stop firewalld
物理连接正常 # ping -c 2 192.168.4.52/51
数据库正常运行，管理可以从本机登录。
2.3 配置mysql主从同步
2.3.1 配置主库192.168.4.52
a、做用户授权
mysql> grant replication slave on . to
-> lisi@“192.168.4.51”
-> identified by “123456”;
b、启用binlog日志
#vim /etc/my.cnf (添加以下三行)
log-bin=master52
server_id=52
binlog-format=“mixed”
## systemctl restart mysqld
#ls /var/lib/mysql/master52.*

c、查看正在使用binlog日志信息
mysql -u root -p 123456
mysql> show master status; 现在正在使用的主库binlog日志文件；
2.3.2 配置从库 192.168.4.51
a、验证主库的用户授权
51]# mysql -h 192.168.4.52 -ulisi -p123456
mysql> show databases;
b、指定server_id
51] # vim /etc/my.cnf
[mysqld]
#skip_grant_tables
validate_password_policy=0
validate_password_length=6
server_id=51 添加改行；
#binlog-format=“mixed” 日志记录格式
#log-bin=pc51 日志名
#systemctl restart mysqld
c、数据库管理员从库本地登录，指定主数据库服务器的信息
51】mysql> change master to
-> master_host=“192.168.4.52”, 主库Ip地址
-> master_user=“lisi”, 主库授权用户名
-> master_password=“123456”, 授权用户密码
-> master_log_file=“master52.000001”, 主库binlog日志文件名
-> master_log_pos=154; binlog日志文件偏移量；
51】 mysql> show slave status\G; （Slave_IO_Running: No Slave_SQL_Running: No）
d、启动slave进程
51】 mysql> start slave;
e、查看进程状态信息
51】 mysql> show slave status\G; (Slave_IO_Running: Yes Slave_SQL_Running: Yes)
如果这里出现错误，查看 Last_IO_Error : 和 Last_SQL_Error 找出原因;
mysql > stop slave; 再重新指定数据库服务器的信息

相关命令：
mysql> show master status; 显示本机的binlog日志文件信息
mysql> show slave status; 显示从库状态信息
mysql> show processlist; 查看当前数据库服务器上正在执行的程序；
mysql > start slave ；启动slave进程
mysql >stop slave ；停止slave进程

2.4 在客户端测试主从同步配置
2.4.1 在主库服务器上（192.168.4.52）添加访问数据时，使用连接用户
52】mysql> grant all on bbsdb.* to webadmin@"%" identified by “123456”;
mysql> select user,host from mysql.user where user=“webadmin”;

2.4.2 客户端使用主库的授权用户，连接主库服务器，建库表插入记录
53】# mysql -h 192.168.4.52 -uwebadmin -p123456
MySQL [(none)]> show grants;
MySQL [(none)]> show grants;
MySQL [(none)]> create database bbsdb;
MySQL [bbsdb]> create table bbsdb.t1(id int(4));
MySQL [bbsdb]> insert into t1 values (888),(201);
52] mysql> show databases;
mysql> use bbsdb;
mysql> show tables;
mysql> desc t1;
mysql> select * from t1;
2.4.3 在从库本机，使用管理登录查看是否有和主库一样库表记录及授权用户
51】mysql> select user,host from mysql.user;
mysql> show databases;
mysql> select * from bbsdb.t1;
2.4.4 客户端使用主库的授权用户，连接从库服务器，也可以看到新建的库表及记录
53】 mysql> update bbsdb.t1 set id=564; 批量改数据
51】 mysql> select * from bbsdb.t1;
53】# mysql -h 192.168.4.51 -u webadmin -p123456 客户端同样可以访问从库
MySQL [(none)]> show databases;

三、Mysql 主从同步的工作原理
从库数据库目录下的文件：
# cd /var/lib/mysql # ls
（mysql> change master to 这个sql命令）产生的 master.info :记录主库信息。
master.info : 记录主库信息
主机名-relay-bin.xxxxxxx 中继日志文件，记录主库上执行过的sql命令；
主机名-relay-bin.index 索引文件，记录当前已有的中继日志文件；
relay-log.info 中继日志文件，记录当前使用的中继日志信息；

#cat master.info
#cat relay-log.info
#cat pc51-relay-bin.index
#mysqlbinlog pc51-relay-bin.000002 |grep insert
从库IO 和SQL线程的作用：
IO 线程：把主库binlog日志里的sql命令记录到本机的中继日志文件；
SQL 线程 ：执行本机中继日志文件里的sql命令，把数据写进本机。
（主从同步复制过程：
1）在每个事务更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。
2）Slave将master的binary log复制到其中继日志。首先slave开始一个工作线程（I/O），I/O线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。
3）Sql slave thread（sql从线程）处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于os缓存中，所以中继日志的开销很小。 ）
IO 线程报错原因：从库连接主库失败（ping 、 grant 授权 、 firewalld、 selinux ）
从库指定主库的日志信息错误（日志名、 偏移量）
Last_IO_Error:报错信息
修改步骤：mysql > stop slave;
mysql > change master to 选项=‘值’；
mysql >start slave;
SQL 线程报错原因 ：执行本机中继日志文件里的sql命令，用到库或表在本机不存在。
（停掉服务，把主库的完全备份拷贝到从库上）
Last_SQL_Error:报错信息
四、 设置从库暂时不同步主库的数据？
---->在从库上把slave进程停止 mysql > stop slave;
把从库恢复成独立的数据库服务器？
删除从库数据库目录下的master.info；
51] ls /var/lib/mysql/
#rm -rf /var/lib/mysql/master.info
#rm -rf relay-log.info #rm -rf pc51-relay-bin.index # rm -rf pc51-relay-bin.00000*
#systemctl restart mysqld
mysql > show slave status;
五 、mysql主从同步结构模式 从库192.168.4.54
单项复制 ： 一主一从
一主多从 ：
链式复制 ：主从从 （需要配置级联复制）
互为主从：主主结构 （一般不用，容易发生写冲突，常和第三方软件连用）
52主库做用户授权
mysql> grant replication slave on . to
-> lisi@"%"
-> identified by “123456”;
mysql > show master status;
配置54从库：
54]# mysql -h 192.168.4.52 -ulisi -p123456
# vim /etc/my.cnf
[mysqld]
server_id=54 添加改行；
#systemctl restart mysqld
54】mysql> change master to
-> master_host=“192.168.4.52”, 主库Ip地址
-> master_user=“lisi”, 主库授权用户名
-> master_password=“123456”, 授权用户密码
-> master_log_file=“master52.000001”, 主库binlog日志文件名
-> master_log_pos=1356; binlog日志文件偏移量；

将主库上的数据备份拷贝给54从库：
52] #mysqldump -uroot -p123456 bbsdb > /root/ddbsd.sql
#ls /root/ddbsd.sql
scp /root/ddbsd.sql 192.168.4.54:/root
54] #ls /root/ddbsd.sql
#mysql -uroot -p123456
mysql > create database bbsdb;
mysql > use ddsdb;
mysql > source /root/ddbsd.sql;
mysql > show tables;

六、 mysql主从同步常用配置参数
主库服务器在my.cnf配置文件设置的参数
#vim /etc/my.cnf
[mysqld]
主库配置选项：（适用于master服务器）
Binlog_Do_DB=库名列表 只允许同步库；
Binlog_Ignore_DB=库名列表 不允许同步的库
#systemctl restart mysqld
52】 mysql> reset master; 还原日志；
mysql> show master status;

从库服务器在my.cnf配置文件设置的参数
适用于slave服务器：
#vim /etc/my.cnf
[mysqld]
log_slave_updates 记录从库更新，允许级联复制（A-B-C）
relay_log=中继日志名 指定中继日志文件名
replicate_do_db=库名列表 仅复制指定库，其他库将被忽略，此选项可设置多条（省略时复制所有库）
replicate_ignore_db=库名列表 不复制哪些库，其他库将被忽略， ignore-db 与do-db 只需选其中一种
#systemctl restart mysqld

++++++例子：配置mysql主从从结构：
主库：192.168.4.52 ；从库 ：192.168.4.51 （做52从库）； 从库 ：192.168.4.54 （做51从库）。
要求： 客户端访问主库52时 创建库表记录在52和53数据库服务器都可以看到。
+++一、环境准备
主从同步未配置前，要保证从库上要有主库上的数据。
禁用selinux #setenforce 0
关闭防火墙服务 # systemctl stop firewalld
物理连接正常 # ping -c 2 192.168.4.52/51/54
数据库正常运行，管理可以从本机登录。
++++二、配置主从同步
2.1 配置主库52
52】mysql> select * from mysql.user where user=“lisi”\G; 查看哪些用户权限；
mysql> select user ,host from mysql.user; 查看授权用户；
mysql> delete from mysql.user where user=“lisi”; 删除授权的用户
mysql> flush privileges; 刷新权限；

用户授权
mysql> grant replication slave on . to yaya@"%" identified by “123456”;
启用binlog日志

vim /etc/my.cnf

[mysqld]
log-bin=db52 日志名以master52开头；
server_id=52
binlog_format=“mixed”
#systemctl restart mysqld
#rm -rf /var/lib/mysql/master52.* 删除之前的日志文件
#ls /var/lib/mysql/db52.*
查看正在使用的日志信息
52】 mysql -uroot -p
mysql> show master status;

2.2 配置从库51
用户授权
51] mysql -u root -p
mysql> grant replication slave on . to user54@“192.168.4.54” identified by “123456”;

启用binlog日志，指定server_id 和 允许级联复制
#vim /etc/my.cnf
server_id=51
log-bin=db51 启用Binlogri日志
log_slave_updates 允许级联复制
binlog-format=“mixed”
#systemctl restart mysqld
#ls /var/lib/mysql/db51.*

查看正在使用的日志信息
51] # mysql -u root -p
mysql> show master status;
验证主库的授权用户
#mysql -h 192.168.4.52 -u yaya -p123456 能连接上说明52授权有效，然后退出
51】 # mysql -u root -p
mysql> select * from mysql.user where user=“user54”\G;
mysql> select user ,host from ,mysql.user;
管理员登录指定主库信息
mysql> change master to
-> master_host=“192.168.4.52”,
-> master_user=“yaya”,
-> master_password=“123456”,
-> master_log_file=“db52.000001”,
-> master_log_pos=154;

启动slave进程
mysql> start slave;
查看进程状态信息
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

2.3 配置从库54
验证主库的授权用户
54】# mysql -h 192.168.4.51 -u user54 -p123456 连接上然后退出
指定server_id
#vim /etc/my.cnf
server_id=54
#systemctl restart mysqld
54】# mysql -u root -p
mysql> show slave status; 查看54是否是别的主机的从库，若是删除
#cd /var/lib/mysql
#rm -rf /var/lib/mysql/master.info 删除主库文件
#rm -rf relay-log.info 删除从库文件
#rm -rf /var/lib/mysql/pc54-relay-bin.* 删除从库日志文件
#systemctl restart mysqld
#mysql -u root -p
mysql> show slave status;

管理员登录指定主库信息
sql> change master to
-> master_host=“192.168.4.51”,
-> master_user=“user54”,
-> master_password=“123456”,
-> master_log_file=“db51.000001”,
-> master_log_pos=154;

启动slave进程
mysql> start slave;
查看进程状态信息
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
+++三、客户端验证
3.1 在主库上授权访问gamedb库的用户
52】mysql -u root -p 123456
mysql> grant all on gamedb.* to dada@"%" identified by “123456”;
3.2 客户端使用授权用户连接主库，建库，表，插入记录
53] # setenforce 0
#systemctl stop firewalld.service
#mysql -h 192.168.4.52 -u dada -p
MySQL [(none)]> show databases;
MySQL [(none)]> create database gamedb;
MySQL [(none)]> create table gamedb.t1(id int(4));
MySQL [(none)]> create table gamedb.t2(id int(4));
MySQL [(none)]> insert into gamedb.t1 values (12);
MySQL [(none)]> insert into gamedb.t1 values (23);
MySQL [(none)]> insert into gamedb.t1 values (45);
MySQL [(none)]> insert into gamedb.t2 values (12);
MySQL [(none)]> insert into gamedb.t2 values (23);
MySQL [(none)]> insert into gamedb.t2 values (45);

3.3 客户端使用授权用户连接两台从库的时候，也可以看到主库上新建的库表记录
53] # mysql -h 192.168.4.51 -u dada -p 通过51从库访问
MySQL [(none)]> show databases;
# mysql -h 192.168.4.54 -u dada -p 通过54从库访问
MySQL [(none)]> show databases;
七 、mysql主从同步复制模式
异步复制（Asynchronous replication）（默认的模式）:
主库执行完一次事务后，立即将结果返回给客户端，并不关心从库是否已经接收并处理；
全同步复制（Fully synchronous replication）:
当主库执行完一次事务后，且所有从库都执行了该事务后才返回给客户端；
半步复制（Semisynchronous replication）
介于一部复制和全同步复制之间；
当主库在执行完一次事务之后，等待至少一个从库接收到并写道relay log 中才返回给客户端。

模式配置
查看是否允许动态加载模块（默认允许）：
mysql> show variables like “have_dynamic_loading”;
命令行加载插件 —用户需有SUPER 权限
52】主库安装模块 mysql> show variables like “have_dynamic_loading”;
mysql> install plugin rpl_semi_sync_master SONAME ‘semisync_master.so’;
命令 关键字 模块名

51】从库安装模块 mysql> show variables like “have_dynamic_loading”;
mysql> install plugin rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
查看系统库下的表，模块是否安装成功：
select plugin_name ,plugin_status from information_schema.plugins where plugin_name like ‘%semi%’;

mysql> select plugin_name ,plugin_status
-> from information_schema.plugins （plugin 插件）
-> where
-> plugin_name like ‘%semi%’；
启用半同步复制：
查看半同步复制模式是否启用 ()
mysql> show variables like ‘rpl_semi_sync_%_enabled’;

主库上启用半同步模式：mysql> set global rpl_semi_sync_master_enabled=1;
从库上启用半同步模式：mysql> set global rpl_semi_sync_slave_enabled=1;
重新查看：
mysql> show variables like ‘rpl_semi_sync_%_enabled’;
修改配置文件/etc/my.cnf 让安装模块跟启用模式永久有效。
（注意事项：一定要先开启动态加载模块，否在配置文件启动容易失败）
52】主库上 #vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
#systemctl restart mysqld
51】从库上 #vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
#systemctl restart mysqld
即做主又做从，则：
#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
#systemctl restart mysqld