mysql主从同步,主从同步模式

一、什么是mysql主从同步
主：正在被客户端访问的数据库服务器，被称作主库服务器。
从：自动同步主库上的数据的数据库服务器，被称作从库服务器。
二、配置mysql主从同步
2.1 拓扑图
数据库服务器 192.168.4.10 做主库
数据库服务器 192.168.4.20 做从库
2.2 环境准备
主从同步未配置之前，要保证从库上要有主库上的数据。
禁用selinux ]# setenforce 0
关闭防火墙服务]# systemctl stop firewalld
物理连接正常 ]# ping -c 2 192.168.4.51/52
数据库正常运行，管理可以从本机登录
2.3 配置mysql主从同步
基本构建思路
1. 确保数据相同
– 从库必须要有主库上的数据。
2. 配置主服务器
– 启用 binlog 日志及设置格式,设置 server_id, 授权用户
3. 配置从服务器
– 设置 server_id ,指定主数据库服务器信息
4. 测试配置
– 客户端连接主库,写入的数据,在连接从库的时候也能够访问到。
2.3.1 配置主库(192.168.4.51)
1. 做用户授权
mysql> grant replication slave on .
-> to
-> repluser@”192.168.4.52”
-> identified by “123456”;
2. 启用binlog日志
]# vim /etc/my.cnf
[mysqld]
server_id=51
log-bin=master51
binlog-format=”mixed”
…
]# systemctl restart mysqld
]# ls /var/lib/mysql/master51.*
/var/lib/mysql/master51.000001 /var/lib/mysql/master51.index
3. 查看正在使用binlog日志信息
]#mysql -uroot -p123456
mysql> show master status;
2.3.2 配置从库
1. 验证主库的用户授权
]# mysql -h192.168.4.51 -urepluser -p123456
mysql> show grants;
mysql> quit;
2. 指定server_id
]# vim /etc/my.cnf
[mysqld]
server_id=52
:wq
]# systemctl restart mysqld
3.数据库管理员本机登录，指定主数据库服务器的信息
]#mysql -uroot -p123456
mysql> change master to
-> master_host=”192.168.4.51”,
-> master_user=”repluser”,
-> master_password=”123456”,
-> master_log_file=”master51.000001”,
-> master_log_pos=154;
4.启动slave进程
mysql> start slave;
5.查看进程状态信息
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
相关命令
mysql> show slave status; # 显示从库状态信息
mysql> show master status; #显示本机的binlog日志文件信息
mysql> show processlist; #查看当前数据库服务器上正在执行的程序
mysql> start slave ; #启动slave 进程
mysql> stop slave ; #停止slave 进程
2.4 在客户端测试主从同步配置
2.4.1 在主库服务器上添加连接用户
]# mysql -uroot -p123456
mysql> grant all on bbsdb.* to webadmin@”%” identified by “123456”;
mysql>select user,host from mysql.user where user=”webadmin”;
mysql> quit;
2.4.2 客户端使用主库的授权用户，连接主库服务器，建库表插入记录
254]# mysql -h192.168.4.51 -uwebadmin -p123456
mysql> 建库 建表 插入记录
2.4.3 在从库本机，使用管理员登录查看是否有和主库一样库表记录
52]# mysql -uroot -p123456
mysql> select * from 库.表；
2.4.4 客户端使用主库的授权用户,连接从库服务器，也可以看到新建的库表及记录
254]# mysql -h192.168.4.52 -uwebadmin -p123456
mysql> select * from 库.表；
三、mysql主从同步的工作原理
3.1 从库数据库目录下的文件
文件名 说明
master.info 记录主库信息
主机名-relay-bin.XXXXXX 中继日志文件，记录主库上执行过的sql命令
主机名-relay-bin.index 索引文件，记录当前已有的中继日志文件
relay-log.info 中继日志文件，记录当前使用的中继日志信息
3.2 从库IO线程 和SQL线程的作用
IO线程 把主库binlog日志里的sql命令记录到本机的中继日志文件
SQL线程 执行本机中继日志文件里的sql命令，把数据写进本机。

mysql> show processlist;
Binlog Dump //主库binlog日志发生改变了，这个线程会通知从库可以过来同步了
3.2.1 IO线程报错原因
从库连接主库失败（ping grant firewalld selinux）
从库指定主库的日志信息错误（日志名 偏移量）
Last_IO_Error: 报错信息
修改步骤：
mysql> stop slave;
mysql> change master to 选项=”值”;
mysql> start slave;
3.2.2 SQL线程报错原因
执行本机中继日志文件里的sql命令,用到库或表在本机不存在。
Last_SQL_Error: 报错信息
3.2.3 设置从库暂时不同步主库的数据
在从库上把slave 进程停止
mysql> stop slave;
3.2.4把从库恢复成独立的数据库服务器
]# rm -rf /var/lib/mysql/master.info
]# rm -rf 主机名-relay-bin.XXXXXX 主机名-relay-bin.index relay-log.info
]# systemctl restart mysqld
四、mysql主从同步结构模式
一主一从 *
一主多从 *
主从从[mysqld]
log_slave_updates #级联复制
relay_log=中继日志文件名
replicate_do_db=库名列表 #只同步的库
replicate_ignore_db=库名列表 #只不同步的库
主主结构（又称作互为主从）
五、mysql主从同步常用配置参数
5.1 主库配置文件my.cnf 使用的参数
选项 用途
binlog_do_db=name 设置 Master 对哪些库记日志
binlog_ignore_db=name 设置 Master 对哪些库不记日志
]#vim /etc/my.cnf
[mysqld]
binlog_do_db=库名列表 #只允许同步库
Binlog_Ignore_DB=库名列表 #只不允许同步库
]# systemctl restart mysqld
5.2 从库在配置文件my.cnf 使用的参数
选项 用途
log_slave_updates 记录从库更新,允许链式复制( A-B-C )
relay_log=dbsvr2-relay-bin 指定中继日志文件名
replicate_do_db=mysql 仅复制指定库,其他库将被忽略,此选项可设置多条(省略时复制所有库)
replicate_ignore_db=test 不复制哪些库,其他库将被忽略, ignore-db 与 do-db 只需选用其中一种
]# vim /etc/my.cnf
[mysqld]
log_slave_updates #级联复制
relay_log=中继日志文件名
replicate_do_db=库名列表 #只同步的库
replicate_ignore_db=库名列表 #只不同步的库
]# systemctl restart mysqld
5.3 配置mysql主从从结构
主库 192.168.4.51
从库 192.168.4.52 （ 做51主机从库）
从库 192.168.4.53 （ 做52主机从库）
要求：客户端访问主库51 时 创建库表记录 在52 和53 数据库服务器都可以看到
5.3.1环境准备
主从同步未配置之前，要保证从库上要有主库上的数据。
禁用selinux ]# setenforce 0
关闭防火墙服务]# systemctl stop firewalld
物理连接正常 ]# ping -c 2 192.168.4.51/52
数据库正常运行，管理可以从本机登录
5.3.2配置主从同步
一、配置主库51
1. 用户授权
mysql> grant replication slave on . to repluser@’192.168.4.52’ identified by “123456”;
2. 启用binlog日志
vim /etc/my.cnf
[mysqld]
server_id=51
log-bin
binlog-format=”mixed”
3. 查看正在使用的日志信息
mysql> show master status;
二、配置从库52
1.用户授权
mysql> grant replication slave on . to repluser@’192.168.4.53’ identified by ‘123456’;
2.启用binlog日志，指定server_id 和 允许级联复制
vim /etc/my.cnf
[mysqld]
server_id=52
log-bin
binlog-format=”mixed”
log_slave_updates
3.查看正在使用的日志信息
mysql> show master status;
4.验证主库的授权用户
mysql -h192.168.4.51 -urepluser -p123456
5.管理员登.录指定主库信息
mysql> change master to
-> master_host=”192.168.4.51”,
-> master_user=”repluser”,
-> master_password=”123456”,
-> master_log_file=”host51-bin.000001”,
-> master_log_pos=154;
6.启动slave进程
mysql> start slave;
7.查看进程状态信息
mysql> show slave status\G;
三、配置从库53
1.验证主库的授权用户
]# mysql -h192.168.4.52 -urepluser -p123456
2.指定server_id
[mysqld]
server_id=53
3.管理员登录指定主库信息
mysql> change master to
-> master_host=”192.168.4.52”,
-> master_user=”repluser”,
-> master_password=”123456”,
-> master_log_file=”host53-bin.000001”,
-> master_log_pos=452;
4.启动slave进程
mysql> start slave;
查看进程状态信息
mysql> show slave status\G;
5.3.3 客户端验证配置
1 在主库上授权访问client库的用户
mysql> grant all on . to client@’%’ identified by “123456”;
2 客户端使用授权用户连接主库，建库、表、插入记录
mysql> create database lijunDB;
3 客户端使用授权用户连接2台从库时，也可以看到主库上新的库表记录
[root@host52 mysql]# mysql -uroot -p’123456’
mysql> show databases;

[root@host53 mysql]# mysql -uroot -p’123456’
mysql> show databases; [结果同上]
六、mysql主从同步复制模式
复制模式介绍
• 异步复制( Asynchronous replication )
– 主库在执行完客户端提交的事务后会立即将结果返给
客户端,并不关心从库是否已经接收并处理。
• 全同步复制( Fully synchronous replication )
– 当主库执行完一个事务,所有的从库都执行了该事务
才返回给客户端。
• 半同步复制( Semisynchronous replication )
– 介于异步复制和全同步复制之间,主库在执行完客户
端提交的事务后不是立刻返回给客户端,而是等待至
少一个从库接收到并写到 relay log 中才返回给客户端
6.1 半同步赋值模式配置
查看是否可以动态加载模块
– 默认允许
mysql> show variables like “have_dynamic_loading”;
6.1.1 命令行加载插件
一、安装模块
1.主库安装的模块
mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so’;
2.从库安装的模块
mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so’;
3.查看系统库下的表，模块是否安装成功
mysql> select plugin_name,plugin_status from
information_schema.plugins where
plugin_name like ‘%semi%’;

二、启用半同步复制模式
1.启用主库半同步复制模式
mysql> set global rpl_semi_sync_master_enabled=1;
2.启用主库半同步复制模式
mysql> set global rpl_semi_sync_slave_enabled=1;
3.查看半同步复制模式是否启用
mysql> show variables like “rpl_semi_sync_%_enabled”;
修改配置文件/etc/my.cnf 让安装模块和启用的模式永久生效。
6.1.2 配置文件加载插件
1.主库配置文件
vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
2.从库配置文件
vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
3.既做主又做从
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