一、mysql 主从同步
1.1 什么是mysql 主从同步
实现数据主从同步的服务结构,结构中分为两种角色
主服务器:接收客户端访问的数据库服务器
从服务器:自动从主库服务器同步到本机的数据库服务器
1.2 mysql 主从同步工作原理
- Mster,记录数据更改操作
-启用binlog日志
-设置binlog日志格式
-设置server_id - Slave 运行2个线程
Slave_IO 复制master主机 binlog日志文件里的SQL到本机的relay-log文件里
Slave_sql 执行本机的relay-log 文件里的SQL语句,重现Master的数据操作
1.3 配置mysql主从同步
1.3.1 服务器角色
192.168.4.50 客户端(client)
192.168.4.51 主(master) 运行数据库服务器 管理员root本机能够登陆
192.168.4.52 从(slave) 运行数据库服务器 管理员root本机能够登陆1.3.2 配置主服务器 192.168.4.51
1 启用binlog日志文件
vim /etc/my.conf
[mysq]
server_id=51
log-bin=master51
:wq
[root@51 ~]# systemctl restart mysqld
[root@51 ~]# ls /var/lib/mysql/master51.*
/var/lib/mysql/master51.000001 /var/lib/mysql/master51.index
[root@51 ~]# mysql -uroot -p123456 -e "show master status"
2 用户授权
[root@51 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
3 查看binlog日志信息(master状态)
mysql> show master status;
1.3.3 配置从服务器 192.168.4.52
1 指定server_id(不允许与主库server_id相同)
vim /etc/mysql
[mysq]server_id=52
2 指定主服务器信息
mysql> change master to
—> master_host="192.168.4.51", //指定主库IP
—> master_user="repluser", //指定主库授权用户
—> master_password="123qqq...A", //指定主库授权密码
—> master_log_file="master51.000002", //日志文件
—> master_log_pos=441; //偏移位置
3 启动slave 进程
mysql> start slave;
Master信息会自动保存到 /var/lib/mysql/master.info
若要更改master信息,应先停止stop slave;
4 查看进程信息(查看slave状态,确认IO线程、SQL线程是否运行)
mysql> show slave status\G;
Slave_IO_Running: Yes //查看IO线程是否出错
Slave_SQL_Running: Yes //查看SQL线程是否出错
从库相关文件
[root@52 ~]# cat /var/lib/mysql/
master.info //主库信息
relay-log.info //中继日志信息
主机名-rely-bin.xxxx //中继日志
主机名-rely-bin.index//索引文件
1.4 验证配置
1 在主服务器上添加访问数据连接的用户
mysql> create database db7;
mysql> grant all on db7.* to webuser@"%" identified by "123qqq...A";
2 在客户端连接主服务器 对数据做操作
[root@50 ~]# mysql -h192.168.4.51 -uwebuser -p"123qqq...A"
mysql> show grants;
mysql> use db7;
mysql> create table db7.t1(id int);
mysql> insert into db7.t1 values(10101);
mysql> insert into db7.t1 values(10101);
3 在从服务器本机上查看是否和主服务器的数据一致
[root@52 ~]# mysql -uroot -p"Taren1.com"
mysql> show databases;
mysql> select * from db7.t1;
二、 mysql主从同步模式
MYSQL 主从同步结构模式
一主一从
一主多从
给当前主服务器51再配置1个从数据库服务器53
1 在服务器53 上运行mysql服务且管理员能够本机登陆
2 在没有配置为从服务器之前,要有主服务器上的数据
[root@51 ~]# mysqldump -uroot -p123456 db7 > /root/db7.sql
[root@51 ~]# scp /root/db7.sql root@192.168.4.53:/root
[root@53 ~]# mysql -uroot -pTaren1.com
mysql> create database db7;
mysql> use db7;
mysql> source /root/db7.sql;
3 设置主机53的server_id
vim /etc/my.conf
[mysqld]
server_id=53
[root@53 ~]# systemctl restart mysqld
4 指定主服务器信息
[root@53 ~]# mysql -uroot -pTaren1.com
mysql> change master to
master_host="192.168.4.51",
master_user="repluser",
master_password="123qqq...A",
master_log_file="master51.000002",
master_log_pos=1821;
5 启动slave进程
mysql> start slave;
6 查看进程信息
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7 客户端测试
1 在客户端50主机连接和数据库服务访问数据
[root@50 ~]# mysql -h192.168.4.51 -uwebuser -p"123qqq...A"
mysql> insert into db7.t1 values(8888888);
mysql> create table db7.t2(name char(10));
mysql> select * from db7.t1;
mysql> select * from db7.t2;
2 分别在2个服务器本机查看数据(可以看到和主服务器一样
的数据)
mysql> select * from db7.t1;
mysql> select * from db7.t2;
主从从
给当前的从服务器192.168.4.52,配置一个从服务器192.168.4.54
1 配置52
1.1 启用binlog日志
vim /etc/my.cnf
log-bin=master52
log_slave_updates
[root@52 ~]# systemctl restart mysqld
[root@52 ~]# mysql -uroot -p"Taren1.com"
mysql> show slave status\G;
1.2 用户授权
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
1.3 查看binlog日志信息
mysql> show master status;
2配置54
52备份数据
[root@52 ~]# mysqldump -uroot -pTaren1.com db7 > /root/db7.sql
[root@52 ~]# scp /root/db7.sql root@192.168.4.54:/root/
54使用备份文件恢复数据
[root@54 ~]# mysql -uroot -pTaren1.com
mysql> create database db7;
mysql> use db7;
mysql> source /root/db7.sql
mysql> show tables;
2.1 指定server_id
vim /etc/my.cnf
server_id=54
[root@54 ~]# systemctl restart mysqld
2.2 指定主服务器信息
[root@52 ~]# mysql -uroot -p"Taren1.com"
mysql> grant all on db7.* to webuser@"%" identified by "123qqq...A";
[root@54 ~]# mysql -uroot -pTaren1.com
mysql> change master to master_host="192.168.4.52",master_user="repluser",master_password="123qqq...A",master_log_file="master52.000002",master_log_pos=732;
2.3 启动slave进程
mysql> start slave;
2.4 查看进程信息
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3 测试配置
3.1 在客户端连接主服务器51存数据
[root@50 ~]# mysql -h192.168.4.51 -uwebuser -p"123qqq…A"
mysql> insert into db7.t1 values(123456789);
3.2 在从服务器52 和 54 主机上都可以看到和主服务器一致的数据
[root@52 ~]# mysql -uroot -p"Taren1.com" -e “select * from db7.t1;”
[root@54~]# mysql -uroot -p"Taren1.com" -e “select * from db7.t1;”
主主结构
MYSQL 主从同步复制模式
异同步模式
主库执行完一次事务后,立即将结果返回给客户端,并不关心从库是否已经接收并处理
全同步复制
主库执行完一次事务后,且所有从库都执行了该事务后才返回给客户端
半同步复制
介于异步复制和全同步复制之间
主库执行完一次事务后,等待至少一个从库接收到并写到relay-log中才返回给客户端
案例:启用数据库服务器52的半同步复制模式
步骤:
1 查看是否允许动态加载模块
mysql> show variables like “have_dynamic_loading”;
2 加载模块
用户需要有super权限
mysql> install plugin rpl_sync_master soname “semisync_master.so”; //主库执行
mysql> install plugin rpl_sync_slave soname “semisync_slave.so”; //从库上执行
3 查看加载信息
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like “%semi%”;
4 启用模块
mysql> set global rpl_semi_sync_master_enabled = 1; //主库上执行
mysql> set global rpl_semi_sync_slave_enabled = 1; //从库上执行
5 查看是否启用
mysql> show variables like “rpl_semi_sync_%_enabled”; //查看是否启用
6 修改配置文件
[root@52 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1 //主库上的配置
plugin-load=rpl_semi_sync_slave=semisync=slave.so
rpl_semi_sync_slave_enabled=1 //从库上的配置
[root@52 ~]# systemctl restart mysqld
主库配置选项( 适用于Master服务器)
/etc/my.conf
选项 | 用途 |
---|---|
binlog_do_db=数据库名 | 只允许同步的库 |
binlog_ignore_db=数据库名 | 不允许同步的库 |
从库配置选项(适用于Slave服务器)
选项 | 用途 |
---|---|
log_slave_updates | 记录从库更新,允许链式复制(a-b-c) |
relay_log=dbsvr2-relay-bin | 指定中继日志文件名 |
replicate_do_db=数据库名 | 仅复制指定库,其他库将被忽略,此选项可设置多条(省略时复制所有库) |
replicate_ignore_db=test | 不复制那些库,其他库将被省略,ignore-db与do-db只需选择其中一种 |
故障分析
问题现象1
Slave_IO 线程没有运行
-报错:Slave_IO_Running:No
mysql > show slave status\G;
Slave_IO_Running:No
Last_IO_Error: 报错信息
原因分析
-连接不上 master数据库服务器
解决办法
-检查物理连接(ping)、检查授权用户
-禁用防火墙、关闭SElinux
-binlog日志文件指定错误(日志名或pos节点)
mysql> stop slave;
mysql> change master to 选项=值;
mysql> start slave;
问题现象2
Slave_SQL 线程没有运行
-报错:Slave_SQL_Running:No
mysql> show slave status\G;
Slave_SQL_Running:No
Last_IO_Error: 报错信息
故障分析及排除
- 原因分析
-执行本机中继日志里的sql命令时,sql命令适用的库、表或记录在本机不存在
解决办法
mysql> stop slave;
mysql> … //创建或恢复需要用到的库或表
mysql> start slave;