主从复制
原理:
mysql 无需借助第三方工具,而是其自带的同步复制功能,另外一点,mysql 的主从
复制并不是从硬盘给上文件直接同步,而是逻辑的 binlog 日志同步到本地的应用执行的过
程。 数据从一个 mysql 数据库(master)复制到另一个 mysql 数据库(slave),在
master 与 slave 之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程
(SQL 线程和 IO 线程)在 slave 端,另一个线程(I/O 线程)在 master 端。要实现 mysql
的主从复制,首先必须打开 master 端的 binlog 记录功能,否则将无法实现。因为整个复
制过程实际上就是 slave 从 master 端获取 binlog 日志,然后再在 slave 上以相同顺序执
行获取的 binlog 日志中的记录的各种 SQL 操作。
原理图:
主从复制用途:
- 实时灾难备份,故障切换
- 读写分离,分担master的负载
- 备份,防止业务受影响
主从复制条件:
- master开启binlog日志
- master和slave的server-id不同
- slave主动连接master
主从复制存在的问题:
- master宕机后数据库的数据可能丢失
- 主库写压力时会出现复制延时的可能,导致数据不能完全备份
解决方法:
- 半同步复制-克服数据丢失
- 并行复制-克服复制延
环境:
master(主数据库): test3(172.25.1.3)
slave (从数据库): test4(172.25.1.4)
主从数据库版本最好一致
主从数据库内的数据需保持一致
master---slave 配置相同如下
Master(test3):
[root@test3 ~]# ls
mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@test3 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@test3 ~]# rm -rf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@test3 ~]# yum install * -y
[root@test3 ~]# vim /etc/my.cnf
[root@test3 ~]# /etc/init.d/mysqld start
//打开数据库
[root@test3 ~]# grep "temporary password" /var/log/mysqld.log
[root@test3 ~]# mysql_secure_installation
[root@test3 ~]# mysql -p //登陆数据库
slaver(test4)
[root@test4 ~]# ls
mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@test4 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@test4 ~]# rm -rf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@test4 ~]# ls
[root@test4 ~]# yum install * -y
[root@test4 ~]# vim /etc/my.cnf
[root@test4 ~]# /etc/init.d/mysqld start //打开数据库,可能出现报错
Initializing MySQL database:
2018-10-18T06:10:52.204846Z 0
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2018-10-18T06:10:52.206242Z 0 [ERROR] --initialize specified but
the data directory has files in it. Aborting.
2018-10-18T06:10:52.206268Z 0 [ERROR] Aborting
[FAILED]
//报错原因是已经有了 data 数据
解决方案:
[root@test4 ~]# vim /etc/my.cnf
[root@test4 ~]# cd /var/lib/mysql
[root@test4 mysql]# ls
ib_buffer_pool ibdata1 ib_logfile0
[root@test4 mysql]# ll //确实有数据
total 110596
-rw-r----- 1 mysql mysql 215 Oct 18 13:18 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Oct 18 13:18 ibdata1
-rw-r----- 1 mysql mysql 50331648 Oct 18 13:18 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Oct 18 13:18 ib_logfile1
[root@test4 mysql]# cd ..
[root@test4 lib]# mv mysql mysql.back //备份数据
解释:由于这里是练习,故便于实验将原本数据删除
[root@test4 mysql]# rm -rf *
[root@test4 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@test4 mysql]# grep password /var/log/mysqld.log
2018-10-18T06:21:22.660701Z 1 [Note] A temporary password is
generated for root@localhost: a,NqiGtfZ7D&
[root@test4 mysql]# mysql_secure_installation
test3 给用户名和可链接网段及授权密码(创建用户并授权)
[root@test3 ~]# mysql -p
Enter password:
mysql> grant replication slave on *.* to repl@'172.25.1.%' identified by '@Caoxingxing123'; //master给slave做授权,让slave连接
Query OK, 0 rows affected, 1 warning (0.39 sec)
mysql> quit
Bye
其中,master_user 为 repl
test4(从库)尝试连接
[root@test4 mysql]# mysql -u repl -p -h 172.25.1.3
Enter password: //输入 master 的数据库内中设置的验证密码
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.1.3' (113)
//此时可能会有报错
//报错原因有可能是没关防火墙。分别将防火墙关闭,再次尝试连接。
[root@test3 ~]# /etc/init.d/iptables stop
[root@test4 ~]# /etc/init.d/iptables stop
[root@test4 ~]# mysql -u repl -p -h 172.25.1.3 //连接成功。
从库连接主库
登陆slave数据库 mysql 会话,执行同步 SQL 语句(需要主服务器主机名,登陆凭
据,二进制文件的名称和位置),其中 master_log_file 和 master_log_pos 参
数不固定,其根据主库参数写。
master: //查看master的二进制日志
slave:
[root@test4 ~]# mysql -p
mysql>change master to master_host='172.25.1.3',master_user='repl',master_password='@ Caoxingxing123',master_log_file='mysql-bin.000005',master_log_pos=738; //slave进行change
Query OK, 0 rows affected, 2 warnings (1.25 sec)
mysql> start slave; //启动 slave 同步进程
Query OK, 0 rows affected (0.16 sec)mysql> show slave status\G //查看 slave 状态
接下来通过验证,证明是否可以实现主从复制的功能,证明方式有两种,分别如下:
1.在 master 数据库的 westos 数据库的一张数据表中插入数据,slave 的
westos 数据库的相同数据表中查看是否有新增的相同数据,从而来验证能否实
现主从复制功能。
2.关闭 slave,然后再修改 master 内容,看 slave 内容是否也相应修改。
验证 1:
未插入时:
master:
salve:
现在给主数据库创建数据库westos,并插入数据
主 mysql:
mysql> create database westos;
mysql> use westos //切换到 westos 数据库
Database changed
mysql> create table userlist(
-> username varchar(10) not null,
-> password varchar(20) not null); //创建表userlist 及插入数据
此时slave:
可见,列表 userlist 中的数据已经由master同步到 slave,实现主从复制功能。
验证 2:
slave:
mysql> stop slave; //停掉slave
master:
mysql> insert into userlist values ('lib',678);
mysql> insert into userlist values ('lily',789);
mysql> select * from userlist; //查看列表的数据内容
slave:
mysql> select * from userlist;
此时,slave 并未将数据同步过来,同样也可以说明其实现了主从复制功能。
基于 GTID 的主从复制
优点:
因为不用手工设置日志偏移量, 可以很方便地进行故障转移,如果启用
log_slave_updates 那么从库不会丢失主库上的任何修改。
缺点:
对执行的 SQL 有一定限制,仅支持 MySQL 5.6 之后的版本, 而且不建议使用早期 5.6 版本。
配置master和slave:
在两边的配置文件/etc/my.cnf 都加上:
gtid_mode=ON
enforce-gtid-consistency=true
并重启数据库
master:
[root@test3 ~]# vim /etc/my.cnf
[root@test3 ~]# /etc/init.d/mysqld restart
slave:
[root@test4 ~]# vim /etc/my.cnf
[root@test4 ~]# /etc/init.d/mysqld restart
[root@test4 ~]# mysql -p //关掉 slave
mysql> change master to master_host='172.25.1.3' master_user='repl', master_password='@Caoxingxing123',
MASTER_AUTO_POSITION=1;
mysql> start slave; //开启 slave
mysql> show slave status\G; //查看slave 的状态,状态正常
master:
给 master 数据库插入内容进行测试。
mysql> use westos
Database changed
mysql> create table gtidlist(
-> username varchar(10) not null,
-> password varchar(20) not null);
mysql> insert into gtidlist values ('gtid1',111);
mysql> insert into gtidlist values ('gtid2',222);
mysql> select * from gtidlist;
slave:
由上可见,数据可以同步过来,实现了主从复制。