MySQL主从同步
Master,记录数据更改操作
启用binlog日志;设置binlog日志格式;设置server_id
Slave,运行两个线程
Slave_IO:复制master主机binlog日志文件里的SQL到本机的relay-log文件里
Slave_SQL:执行本机relay-log文件里的SQL语句,重现Master的数据操作
确保数据相同(从库必须要有主库的数据,可用备份的方法)—>主服务器(启用binlog日志、授权用户、查看当前正使用的日志)—>从服务器(设置server_id,指定主库信息)—>测试(客户端连接主库写入数据,在从库上也能查询到)
步骤一:配置主服务器(51)
# vim /etc/my.cnf
[mysqld]
log_bin=master51
server_id=51
binlog_format="mixed"
# systemctl restart mysqld
# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq..A";
mysql> show master status; //要记住日志文件和偏移位置
mysql> system mysqlbinlog /var/lib/mysql/master51.000001 | grep -i grant GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*19DE595CEDBD7B65D0F6E430826E2D100F1831C9'
(grant也会写入到binlog日志中)
步骤二:配置从服务器(52)
# mysql -h192.168.4.51 -urepluser -p123qqq..A
mysql> show grants;
mysql> quit
# vim /etc/my.cnf
[mysqld]
server_id=52 //不能与其他主机id相同
# systemctl restart mysqld
# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="repluser",
-> master_password="123qqq..A",
-> master_log_file="master51.000001",
-> master_log_pos=441;
mysql> show slave status\G;
mysql> start slave;
mysql> show slave status\G;
排错可以看Last_IO_Error、Last_SQL_Error字段
步骤三:测试主从同步效果(50)
51:
# mysql -uroot -p123456
mysql> create database db1;
mysql> create table db1.a(id int);
mysql> grant select,insert on db1.* to yaya100@"%" identified by "123qqq..A";
50:
# mysql -h192.168.4.51 -uyaya100 -p'123qqq..A'
mysql> show grants;
mysql> insert into db1.a values(100),(101),(200);
mysql> select * from db1.a;
52:
# mysql -uroot -p123456
mysql> select * from db1.a;
如果把从服务器(52)的库或者表删掉,客户端(50)往主服务器(51)写入数据,从服务器的SQL线程会直接宕掉
master.info:主库信息
relay-log.info:中继日志文件
主机名-relay-bin.xxxxxx:中继日志
主机名-relay-bin.index:索引文件
重启从服务器的数据库服务,存在这四种文件,所以依然会同步
从服务器(52)还原成独立数据库服务器:
# cd /var/lib/mysql
# rm -rf mysql52-relay-bin.*
# rm -rf master.info
# rm -rf relay-log.info
# systemctl restart mysqld
# mysql -uroot -p123456 -e "show slave status\G" //-e:在命令行输SQL命令
重新设置回从服务器:
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="repluser",
-> master_password="123qqq..A",
-> master_log_file="master51.000001",
-> master_log_pos=1852;
//偏移量可以根据需求,取当前主服务器使用的binlog日志中1852之前的,但是必须存在于当前使用的binlog日志文件中
mysql> start slave;
mysql> show slave status\G;
设置临时不同步: stop slave—>客户端写入数据—>从服务器不同步
主从同步常用配置参数
1.写在master数据库服务器配置文件里的参数(对所有slave服务器生效)
binlog_do_db=name---------------设置master对哪些库记日志
binlog_ignore_db=name----------设置master对哪些库不记日志
2.写在slave数据库服务器配置文件里的参数(只对从服务器自己生效)
log_slave_updates----------------记录从库更新,允许链式复制(A-B-C)
relay_log=xxxxx-relay-bin-----指定中继日志文件名
replicate_do_db=name---------仅复制指定库,其他库将被忽略,此选项可设置多条
replicate_ignore_db=name----不复制哪些库,其他库将被忽略(与do_db二选一)
根据需求,在配置文件/etc/my.cnf添加
[mysqld]
选项=值
例:
主服务器(51)
允许同步的库
# vim /etc/my.cnf
[mysqld]
binlog_do_db=db1,db2
# systemctl restart mysqld
# mysql -uroot -p123456
mysql> show master status;
不允许同步的库
# vim /etc/my.cnf
[mysqld]
#binlog_do_db=db1,db2
binlog_ignore_db=db9
# systemctl restart mysqld
# mysql -uroot -p123456
mysql> show master status;
从服务器(52)
允许同步的库
# vim /etc/my.cnf
[mysqld]
replicate_do_db=db2
# systemctl restart mysqld
# mysql -uroot -p123456
mysql> show slave status\G;
不允许同步的库
# vim /etc/my.cnf
[mysqld]
#replicate_do_db=db2
replicate_ignore_db=studb
# systemctl restart mysqld
# mysql -uroot -p123456
mysql> show slave status\G;
主从同步模式
1.结构模式
主从同步结构:
单向复制:主—>从
链式复制:主—>从—>从
互为主从:主<—>主(两台数据库服务器不能同时被访问,一般应用在高可用集群)
一主多从:从<—主--->从
一主多从
51:
# mysqldump -uroot -p123456 -A > /root/db1.sql
# scp /root/db1.sql 192.168.4.53:/root
mysql> show master status;
53:
# mysql -uroot -p123456
mysql> create database db1;
mysql> use db1;
mysql> source /root/db1.sql
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="repluser",
-> master_password="123qqq..A",
-> master_log_file="master51.000003",
-> master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
50:
# mysql -h192.168.4.51 -uyaya100 -p'123qqq..A'
mysql> insert into db1.a values(777);
mysql> insert into db1.a values(777);
mysql> insert into db1.a values(777);
mysql> select * from db1.a;
52:
mysql> select * from db1.a;
53:
mysql> select * from db1.a;
主从从
要在既做主库又做从库的服务器中,添加log_slave_updates,记录从库更新,允许链式复制(A-B-C)
53(先还原成独立数据库服务器):
# cd /var/lib/mysql
# rm -rf master.info
# rm -rf mysql53-relay-bin.*
# rm -rf relay-log.info
# systemctl restart mysqld
# mysql -uroot -p123456 -e "show slave status\G" |grep -i yes
52(要做51的从库,也要开binlog日志,做用户授权):
# vim /etc/my.cnf
[mysqld]
log-bin=master52
log_slave_updates
binlog_format="mixed"
server_id=52
# systemctl restart mysqld
# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq..A";
mysql> show master status;
mysql> show slave status\G;
53(配置成52的从服务器):
# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.52",
-> master_user="repluser",
-> master_password="123qqq..A",
-> master_log_file="master52.000001",
-> master_log_pos=441;
50(测试):
# mysql -h192.168.4.51 -uyaya100 -p'123qqq..A'
mysql> insert into db1.a values(777);
mysql> insert into db1.a values(777);
mysql> insert into db1.a values(777);
mysql> select * from db1.a;
52:
mysql> select * from db1.a;
53:
mysql> select * from db1.a;
复制模式
异步复制
主库执行完一次事务后,立即将结果返给客户端,并不关心从库是否已经接收并处理
全同步复制
当主库执行完一次事务后,且所有从库都执行了该事务后才返回给客户端
半同步复制
主库在执行完一次事务后,等待至少一个从库接收到并写到relay log中才返回给客户端
查看数据库服务是否允许动态加载模块:
mysql> show variables like "have_dynamic_loading";
查看是否安装了模块:
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
安装模块(如果两台都有主从关系两个模块都安装):
51:
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%";
52:
mysql> install plugin rpl_semi_sync_slave
-> soname "semisync_slave.so";
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
启用半同步复制(如果两台都有主从关系两个都启用)
51:
mysql> set global rpl_semi_sync_master_enabled = 1;
mysql> show variables like "rpl_semi_sync_%_enabled";
52:
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> show variables like "rpl_semi_sync_%_enabled";
永久启用半同步复制:
# 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