mysql 主从复制 和基于gtid的mysql主从复制

主从复制

原理:

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/f

[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/f

[root@test4 ~]# /etc/init.d/mysqld start //打开数据库,可能出现报错

Initializing MySQL database:

-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).

-10-18T06:10:52.206242Z 0 [ERROR] --initialize specified but

the data directory has files in it. Aborting.

-10-18T06:10:52.206268Z 0 [ERROR] Aborting

[FAILED]

//报错原因是已经有了 data 数据

解决方案:

[root@test4 ~]# vim /etc/f

[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

-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 (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/f 都加上:

gtid_mode=ON

enforce-gtid-consistency=true

并重启数据库

master:

[root@test3 ~]# vim /etc/f

[root@test3 ~]# /etc/init.d/mysqld restart

slave:

[root@test4 ~]# vim /etc/f

[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:

由上可见,数据可以同步过来,实现了主从复制。