MySQL主从架构

mysql replication

在实际生产环境中，如果对数据库的读和写都在一个数据库服务器中操作。无论是在安全性、高可用性，还是高并发等各个方面都是完全不能满足实际需求的，因此，一般来说都是通过主从复制（master-slave）的方式来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案来进行部署与实施

什么是MySQL replication

主从复制是指当master(主)库的数据发生变化的时候，变化会实时的同步到一个或多个slave(从)库。

默认情况下属于异步复制，无需维持长连接。

通过配置，可以复制所有的库或者几个库，甚至库中的一些表。

replication是MySQL内建的，本身自带

原理

主服务器更新数据》》从服务器建立IO线程请求同步》》主服务器建立binary log dump线程传输binlog》》从服务器IO线程接受binlog并将其写入relay log中》》从服务器建立sql线程》》sql线程解析中继日志中的内容为sql语句并执行sql语句

作用

读写分离，提供查询服务

使用主从复制，让主库负责写，从库负责读。这样，即使主库进行数据更新操作出现了锁表的情景，通过读从库也可以保证业务的正常运作。

实时灾备，用于故障切换。

当系统中某个节点发生故障时，可以方便的故障切换，实现高可用（HA）。例如，做数据的热备，slave作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

水平扩展数据库的负载能力

随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能

支持类型

Statement：即基于语句的复制，会将对数据库操作的sql语句写入到binlog中，效率比较高。

row：即基于行的复制，会将每一条数据的变化写入到binlog中。

mixed：即混合模型的复制，statement与row的混合，MySQL会根据执行的SQL语句选择日志保存方式。即交替使用行和语句、由mysql服务器自行判断

常见方案

一主一从

主写，从读

一主多从

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。
一主多从，Master负责写操作，其他slave负责读，这种架构最大问题I/O压力集中，多台slave需要从master上同步数据，影响master的IO性能

级联复制

级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。
例如，使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates

互为主从

双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中
很多人误以为这样可以做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性

多主一从

多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上

主从复制模式

异步模式
MySQL默认模式，从服务器是否接收到二进制日志，与主服务器没有关系

半同步模式
至少有一个从服务器接受到二进制日志，反馈给主服务器，主服务器才提交事务

全同步模式
全部的从服务器，反馈成功与否，主服务器才提交事务

部署一主一从

环境

192.168.13.14  master
MySQL13.13   slave

时间同步

ntpdate ntp1.aliyun.com

yum -y install ntp

vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
##server 127.127.1.0  //local clock,就和本地系统时间同步。127.127.1.0在这里是一个IP地址，不是网段
##127.127.1.0 为第8层。ntp和127.127.1.0同步完后，就变成了9层。  ntp是层次阶级的。同步上层服务器的stratum 大小不能超过或等于16

systemctl restart ntpd

systemctl enable ntpd
##master和阿里云时间同步

crontab -l
*/10 * * * * /usr/sbin/ntpdate 192.168.13.14
##slave定时和master同步

主服务器创建授权用户

mysql> grant replication slave on *.* to slave@192.168.13.13 identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置my.cnf

vim /etc/my.cnf
log-bin=/data/mysql/log/mytsql-bin-master 
##启用二进制日志
server-id=1     数据库服务器id标识
binlog-do-db=haha   ##可以被服务器复制的库
binlog-do-db=books  ##可以被服务器复制的库

重启MySQL
systemctl restart mysqld

查看master状态信息

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      154 | haha         |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


查看二进制日志
mysql> show binlog events\G

导出数据库

mysqldump -uroot -p123456 -B haha > haha.sql

将sql文件给slave
scp haha.sql root@192.168.13.13:~

查看slave版本
mysql> show variables like ‘%version%’;

测试连接到主服务器
mysql -uslave -p123456 -h 192.168.13.14

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec) 
##只有复制权限，看不到其它库

导入sql文件，和master数据保持一致
mysql -uroot -p123456 < haha.sql

mysql> use haha;
Database changed

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | chm  |
+------+------+
1 row in set (0.00 sec)

修改my.cnf配置文件

systemctl stop mysqld

vim /etc/my.cnf
server-id=2   ##从服务器id号，不能和主id相同，如果是多个从服务器，每个从服务器必须有一个唯一的server-id值
relay-log=/data/mysql/log/relay-log-bin   ##中继日志文件的路径和名称
relay-log-index=/data/mysql/log/slave-relay-bin.index    ##中继日志索引文件的路径和名称

systemctl start mysqld

数据库里从配置
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.13.14',master_user='
slave',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=154;Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
             Slave_IO_Running: No  
##由于从服务器的虚拟机是复制的主服务器，uuid相同，所以IO线程无法正常启动，需要从库删除auto.cnf文件，重启数据库
            Slave_SQL_Running: Yes
             
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.13.14',master_user='
slave',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
##删除/data/mysql/data/auto.cnf，重启mysqld后，重试，两线程全部运行

主服务插入数据测试同步

mysql> insert into test values(2,"zex");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | chm  |
|    2 | zex  |
+------+------+
2 rows in set (0.00 sec)
##master的更新同步到了slave中

流程

Master端
同步Master端的原始数据到所有Slave端
开启binlog日志，保持server-id唯一
配置Slave验证授权用户，权限replication slave

Slave端
开启relay日志，保持server-id唯一
执行change master语句，生成master.info文件
启动Slave复制（start slave）

Master端全备数据库同步到Slave端
在开始做主从复制之前（start slave前），需要把Master原有的数据都先同步到所有的Slave，否则在做同步复制之时，因为原有数据不一致导致同步失败。

排错

主从数据库没有同步 
先上Master库： 
mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。 
mysql>show master status; 也正常。 
再到Slave上查看 
mysql> show slave status\G 
Slave_IO_Running: Yes 
Slave_SQL_Running: No 
可见是Slave不同步

忽略错误，继续同步
适用于数据相差不大，数据要求不严格
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
##表示跳过一步错误，后面的数字不变
mysql> start slave;
mysql> show slave status\G

重新做主从，完全同步
适用于数据相差较大，要求数据完全统一
mysql> flush tables with read lock;
##先进入主库，进行锁表，防止数据写入

mysqldump -uroot -p123456 dbname > dbname.bak.sql
##进行数据备份（数据备份需要定期进行）

mysql> show master status;
##查看master状态,记下当前的binlog名和position

scp dbname.bak.sql root@192.168.13.13:/tmp
##将备份文件传到从服务器，进行数据恢复

msyql> stop slave;
##停止从库的状态

mysql> source /tmp/dbname.bal.sql
##导入数据备份

mysql> change master to master_host='192.168.13.14',master_user='
slave',master_port=3306,master_password='123456',master_log_file='binlogfilename',master_log_pos=position;
##设置从库同步

mysql> start slave;
##重新开启从同步

mysql> show slae status\G
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
##查看同步状态

mysql> unlock tables;
##主库解除锁表操作

I/O线程出错
 网络连通问题
 防火前没有放开端口
 对应的主机 IP地址写错了

SQL线程出错
主从服务器数据库结构不统一

部署双向主从

环境

192.168.13.14     已完成master，需要slave设置
MySQL13.13     已完成slave，需要master设置

配置13.13my.cnf
vim /etc/my.cnf
server-id=2
relay-log=/data/mysql/log/relay-log-bin
relay-log-index=/data/mysql/log/slave-relay-bin.index
log-bin=/data/mysql/log/mysql-bin-master  
##想做主服务器，必须得有二进制日志
binlog-do-db=haha  ##想要同步的库

创建主从复制的授权用户
mysql> grant replication slave on *.* to slave@'192.168.13.14' identified by '123456';

mysql> flush privileges;  ##一定要刷新授权表

systemctl restart mysqld

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      154 | haha         |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

测试slave使用账号登陆

mysql -uslave -p123456 -h 192.168.13.13

修改13.14my.cnf

vim /etc/my.cnf
server-id=1
log-bin=/data/mysql/log/mysql-bin-master
binlog-do-db=haha
relay-log=/data/mysql/log/relay-log-bin
relay-log-index=/data/mysql/log/slave-relay-bin.index

systemctl restart mysqld

13.14库里从配置
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.13.13',master_user='
slave',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=610;Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
 					Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

插入数据测试

mysql> insert into test values(3,"xmm");
Query OK, 1 row affected (0.01 sec)

mysql> select * from haha.test;
+------+------+
| id   | name |
+------+------+
|    1 | chm  |
|    2 | zex  |
|    3 | xmm  |
+------+------+
3 rows in set (0.00 sec)

部署级联复制

环境

192.168.13.14   master
192.168.13.13   slave中继
192.168.13.34   slave

同步时间

三台服务器时间一致，关闭防火墙

在主服务上授权用户

mysql> grant replication slave on *.* to repl@'192.168.13.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

修改主服务配置文件

vim /etc/my.cnf
server-id=1
binlog-do-db=haha
log-bin=/data/mysql/log/mysql-bin-master
sync-binlog=1
binlog-format=row
##sync-binlog：此参数表示每写缓冲多少次就同步到磁盘；sync_binlog=1表示同步写缓冲和磁盘二进制日志文件，不使用文件系统缓存，在使用innodb事务引擎时，在复制环境中，为了保证最大的可用性，都设置为“1”，但会对影响io的性能。

systemctl restart mysqld

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      154 | haha         |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出master的haha库的完整备份，给中继和slave

mysqldump -uroot -p123456 -B haha > haha.sql

scp haha.sql root@192.168.13.13:~

scp haha.sql root@192.168.13.34:~

部署中继服务器13.13

mysql -uroot -p123456 < haha.sql

vim /etc/my.cnf
server-id=2
log-slave-updates=1
log-bin=/data/mysql/log/mysql-bin-slave1
relay-log=/data/mysql/log/relay-log-bin
relay-log-index=/data/mysql/log/slave-relay-bin.index
##log-slave-updates此参数控制slave数据库是否把master接收到的binlog并在本slave执行的内容记录到slave的二进制日志中，在级联复制环境中，这个参数是必须的
##把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里，这样才能使第三胎slave通过中继slave读取到相应数据变化

systemctl restart mysqld

配置13.13为13.14的从服务器
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.13.14',master_user='repl',master_password='123456',master_log_file='mysql-bin-master.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看中继服务状态
mysql> show slave status\G
Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

13.13再授权一个用户给13.34

mysql> grant replication slave on *.* to 'repl'@'192.168.13.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看中继服务器当前binlog文件及位置

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      154 | haha         |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 ##相对于13.34，13.13就是它的master，但是不同于主从，因为13.13不插入数据，它变动的数据来源于13.14

部署13.34

mysql -uroot -p123456 < haha.sql

vim /etc/my.cnf
server-id=3
relay-log=/data/mysql/log/relay-log-bin
relay-log-index=/data/mysql/log/slave-relay-bin.index

systemctl restart mysqld

指定13.13中继服务器为13.34的主
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.13.13',master_user='
repl',master_password='123456',master_log_file='mysql-bin-slave1.000001',master_log_pos=600;Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看13.34从服务状态
mysql> show slave status\G
Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

插入数据测试
mysql> insert into test values(2,"zex");
Query OK, 1 row affected (0.01 sec)

mysql> select * from haha.test;
+------+------+
| id   | name |
+------+------+
|    1 | chm  |
|    2 | zex  |
+------+------+
2 rows in set (0.00 sec)
##中继服务和slave均同步到master数据