MySQL数据库主从复制详解及实例

一、定义

  主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

二、作用

1.做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2.架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率,提高单个机器的I/O性能。
3.读写分离，使数据库能支撑更大的并发。
  1）在从服务器可以执行查询工作，降低主服务器压力;（主库写，从库读）
  2）在从服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）

三、原理

  实现整个主从复制，需要由slave服务器上的IO进程和Sql进程共同完成。要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己slave端完全顺序的执行日志中所记录的各种操作。

步骤一：主库db的更新事件(update、insert、delete)被写到binlog
步骤二：从库发起连接，连接到主库
步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.
步骤五：还会创建一个SQL线程，从relay log里面读取内容，将更新内容写入到slave的db.

四、GTID复制（基于事务ID复制）

1.GTID定义

  全局事务标识：global transaction identifiers，是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。

2.工作原理

1）master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2）slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3）sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4）如果有记录，说明该GTID的事务已经执行，slave会忽略。
5）如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

3.部署实例

1）实验环境
两台机器：
192.168.246.129 mysql-master
192.168.246.128 mysql-slave

2）实验基础
1>关闭防火墙和selinux
2>添加域名

#vim /ect/hosts //追加
192.168.246.129 master
192.168.246.128 slave

3>安装MySQL
4>重启查看是否成功

#systemctl start mysqld
#systemctl enable mysqld
#netstat -lntp | grep 3306
tcp6  0   0 :::3306    ::: *      LISTEN     11669/mysqld

3）主机操作

[root@mysql-master ~]# vim /etc/my.cnf  	 //在[mysqld]下添加
server-id=1   								//定义server id master必写 
log-bin = mylog 							//开启binlog日志，master比写
gtid_mode = ON    							//开启gtid
enforce_gtid_consistency=1   				//强制gtid
[root@mysql-master ~]# systemctl restart mysqld
[root@mysql-master ~]# mysql -uroot -p'QFljc,521'
mysql> grant all on *.* to 'slave'@'%' identified by 'Qf@12345';		//可修改权限
mysql> flush privileges;

4）从机操作

[root@mysql-slave ~]# vim /etc/my.cnf  		//添加如下配置
server-id=2									//标识，防止复制出错
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE				//可无，定义配置
relay-log-info-repository=TABLE				//可无，定义配置
[root@mysql-slave ~]# systemctl restart mysqld
[root@mysql-slave ~]# mysql -uroot -p'QFljc,521'
mysql> \e
change master to
master_host='master',     					 //主机地址,最好用域名
master_user='slave',     					//主服务上面创建的用户
master_password='Qf@12345', 				//用户密码
master_auto_position=1;						//启动变量布尔值
-> ;
mysql> start slave;
mysql> show slave status\G					//查看slave状态，均为yes即为成功

5）测试
主机：

mysql > insert into test.t1(1);

从机：

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+

五、Binlog日志主从复制

1.环境

两台机器
192.168.246.135 mysql-master
192.168.246.136 mysql-slave

2.准实验前备工作

1）关闭防火墙和selinux
2）安装MySQL并修改密码
3）添加域名解析
#vim /ect/hosts    //追加
192.168.246.129 master
192.168.246.128 slave

3.主服务器：

[root@mysql-master ~]#vim /etc/my.cnf		//添加配置
[mysqld]
log-bin=/var/log/mysql/mysql-bin		//开启binlog日志，并设置存储位置
server-id=1
[root@mysql-master ~]# mkdir /var/log/mysql
[root@mysql-master ~]# chown mysql.mysql /var/log/mysql
[root@mysql-master ~]# systemctl restart mysqld
[root@mysql-master ~]# mysql -uroot -p'QFljc,521'
mysql> GRANT ALL ON *.*  TO  'repl'@'%'  identified by 'Qf@12345';
mysql> flush privileges;
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

4.从服务器：

[root@mysql-slave ~]#vim /etc/my.cnf		
[mysqld]
server-id=2
[root@mysql-slave ~]# systemctl restart mysqld
[root@mysql-slave ~]# mysql -uroot -p'QFljc,521'
mysql> \e
CHANGE MASTER TO
MASTER_HOST='mysql-master',			//主服务器域名
MASTER_USER='repl',					//用户买名
MASTER_PASSWORD='Qf@12345!',		//密码
MASTER_LOG_FILE='mysql-bin.000001',	//日志文件名
MASTER_LOG_POS=849;				//日志内容位置
    -> ;
mysql> start slave;
mysql> show slave status\G

5.测试

主服务器：

mysql> create table dai.aa(id int);
mysql> insert into dai.aa values(111);
mysql> insert into dai.aa values(222);

从服务器：

mysql> select * from dai.aa;
+------+
| id   |
+------+
|  111 |
|  222 |
+------+
2 rows in set (0.01 sec)