mysql/mariadb主从服务器实现负载均衡

一、MYSQL\MARIADB REPLICATION

什么是mysql replication？

​ 1.Replication可以实现将数据从一台数据库服务器（master）复制到一或多台数据库服务（slave）
​ 2.默认情况下属于异步复制，无需维持长连接
​ 3.通过配置，可以复制所有的库或者几个库，甚至库中的一些表
​ 4.是MySQL内建的，本身自带的

replication原理是什么呢？

简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作

DML:SQL操作语句，update, insert，delete
Relay log :中继日志
REPLICATION的作用
1、Fail Over 故障切换
2、Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份
3、High Performance高性能，可以多台slave,实现读写分离
REPLICATION如何工作
整体上来说，复制有3个步骤：
(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2) slave将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave重做中继日志中的事件，修改salve上的数据。

mysql主从复制中：
第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。
REPLICATION的常用方案
1、一主多从,M-S

【注】一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中。
在Master上<多台同步影响IO>
2、M-S-S,一主，一中继，多从

【注】Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志
3、M-M（互为主从）

4、一从多主

优点：节省成本，将多个master数据自动化整合
缺点：对库和表数据的修改较多

---

二、部署mariadb主从同步，M-S模型

环境准备

主机名	IP	系统/mariadb版本
localhost	192.168.43.111	CentOS7.6/5.5.35
TSL	192.168.43.112	CentOS7.6/5.5.35

创建需要同步的数据库

MariaDB [(none)]> create database HA;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use HA;
Database changed
MariaDB [HA]> create table haha(id int(10),name varchar(20));
Query OK, 0 rows affected (0.01 sec)

配置server.cnf

[root@localhost ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
log-bin=mysql-bin-master   #启动二进制日志
server-id=1   #本机数据库ID标识
binlog-do-db=HA  #从服务器复制的库, 二进制需要同步的数据库名
binlog-ignore-db=mysql    不可以被从服务器复制的库

重启mariadb

[root@localhost ~]# systemctl restart mariadb

授权：

MariaDB [(none)]> grant replication slave on *.* to 'slave'@'192.168.43.112' identified by '123456';

查看信息状态：

MariaDB [(none)]> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| mysql-bin-master.000001 |      555 | HA           | mysql            |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

查看二进制日志：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PWjiuFpx-1583987638495)(C:\Users\pc\AppData\Roaming\Typora\typora-user-images\image-20200312115134574.png)]

导出同步的数据库：

[root@localhost ~]# mysqldump -uroot -p123456 HA >ha.sql

将导出的数据库传给从服务器

[root@localhost ~]# scp ha.sql 192.168.43.112:/root

在从服务器导入数据库：

MariaDB [(none)]> create database HA;
Query OK, 1 row affected (0.00 sec)
[root@TSL ~]# mysql -uroot -p123456 HA < ha.sql

修改配置文件server.cnf

[root@TSL ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=2

进行授权：

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> change master to master_host='192.168.43.111',master_user='slave',master_password='123456';
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status \G; 查看状态

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Is5Hen3Z-1583987638506)(C:\Users\pc\AppData\Roaming\Typora\typora-user-images\image-20200312120116046.png)]

Slave_IO_Running ：一个负责与主机的io通信

Slave_SQL_Running：负责自己的slave mysql进程

两个为YES 就成功了！

再到服务器上查看状态：

MariaDB [HA]> show processlist \G;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xYTbxJVh-1583987638515)(C:\Users\pc\AppData\Roaming\Typora\typora-user-images\image-20200312120441811.png)]

插入数据测试同步：

在主上

MariaDB [HA]> insert into haha values(1,'xiaogang');
Query OK, 1 row affected (0.02 sec)

MariaDB [HA]> select * from haha;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaogang |
+------+----------+
1 row in set (0.00 sec)

在从上

MariaDB [HA]> select * from haha;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaogang |
+------+----------+
1 row in set (0.00 sec)

关于一些报错思路的解决基本步骤：

重启从服务器，再查看状态：

停止从服务器slave stop;

开启从服务器slave start;

1、二进制日志没有开启

2、iptables没有开放端口

3、对应的主机IP地址写错了

sql线程出错

主从服务器数据库结构不同意，出错或，数据少，可以手动解决创建插入，再更新slave状态。

---

三、部署mariadb M-S-S模型

环境准备

主机名	主从	IP	版本
localhost	master	192.168.43.112	mariadb5.5.35
TSL	slave中继	192.168.43.111	mariadb5.5.35
linuxprobe	slave	192.168.43.215	mariadb5.5.35

【注】这里你可以为了方便，可以配置三台linux的ip为连续的，比较顺手一点。

部署master======>TSL1:
在主服务器上授权用户：

MariaDB [(none)]> grant replication slave on *.* to repl@'192.168.43.%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
#授权成功

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

修改配置，增加如下：

[root@TSL ~]# vim etc/my.cnf.d/server.cnf
[mysqld]
server-id=1
binlog-do-db=HA
log-bin=mysql-bin-master
binlog-ignore-db=mysql
sync-binlog=1
binlog-format=row
#保存退出

重启服务

[root@TSL ~]# systemctl restart mariadb

导出主服务器HA库完整备份， 拷贝到 中继服务器 和slave服务器

[root@TSL ~]# mysqldump -uroot -p123456 -B HA > ha.sql
[root@TSL ~]# scp ha.sql 192.168.43.111:/root
[root@TSL ~]# scp ha.sql 192.168.43.215:/root

在中继服务器和slave服务器ls查看


说明拷贝成功

部署slave中继======> TSL:
导入数据库ha.sql

[root@localhost ~]# mysql -u root -p123456 <ha.sql

配置server.cnf

[root@localhost ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=2
#开启bin-log:
log-bin=mysql-bin-slave1
log-slave-updates=1  #把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台slave通过中继slave读取到相应数据变化
binlog-format=row

授权：

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.43.112',master_user='repl',master_password='123456';
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.02 sec)

查看中继服务的状态

MariaDB [(none)]> show slave status \G;

再授权一个给slave（linuxprobe）

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.43.215' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

部署slave=======>linuxprobe
导入数据库ha.sql

[root@linuxprobe ~]# mysql -uroot -p123456 <ha.sql

配置server.cnf

[root@linuxprobe ~]# vim /etc/my.cnf.d/server.cnf

[mysqld]
server-id=3
log-bin=mysql-bin-slave2
binlog-format=row

重启服务

[root@linuxprobe ~]# systemctl restart mariadb

指定slave中继服务作为linuxprobe的主：

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.43.111',master_user='repl',master_password='123456';
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

查看从服务器的状态
MariaDB [(none)]> show slave status \G;

在master上创建表和插入数据测试：
MariaDB [HA]> create table haha(id int(10),name varchar(20));
Query OK, 0 rows affected (0.01 sec)
在中继和slave查看：
在中继
MariaDB [HA]> show tables;
±-------------+
| Tables_in_HA |
±-------------+
| haha |
±-------------+
1 row in set (0.00 sec)
在slave
MariaDB [HA]> show tables;
±-------------+
| Tables_in_HA |
±-------------+
| haha |
±-------------+
1 row in set (0.00 sec)
插入数据：

MariaDB [HA]> insert into haha values(1,'xiaohua');
Query OK, 1 row affected (0.00 sec)

查看：
在中继
MariaDB [HA]> select * from haha;
±-----±--------+
| id | name |
±-----±--------+
| 1 | xiaohua |
±-----±--------+
1 row in set (0.01 sec)
在slave
MariaDB [HA]> select * from haha;
±-----±--------+
| id | name |
±-----±--------+
| 1 | xiaohua |
±-----±--------+
1 row in set (0.01 sec)

A:如果我不想中继储存数据呢？只是进行一个中继转发，我们又该怎么做呢？
B:中继服务器之所以能查到数据，是因为存储引擎的问题，我们修改表的引擎，再插入数据测试，看看结果如何！
修改表的引擎：

MariaDB [HA]> set sql_log_bin=off;
Query OK, 0 rows affected (0.01 sec)

MariaDB [HA]> alter table haha engine=blackhole;
Query OK, 1 row affected (0.01 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [HA]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)

【注】这里一定要先关闭日志记录！！！
插入数据测试：

MariaDB [HA]> insert into haha values(2,'xiaoliu');
Query OK, 1 row affected (0.01 sec)

在中继和slave查看
在中继

MariaDB [HA]> select * from haha;
Empty set (0.01 sec)

在slave

MariaDB [HA]> select * from haha;
+------+---------+
| id   | name    |
+------+---------+
|    1 | xiaohua |
|    2 | xiaoliu |
+------+---------+
2 rows in set (0.00 sec)

排错：
错误1：MariaDB [(none)]> show slave status \G;

导致错误的原因有一下几点：
1.、网络不同
2、账户密码错误
3、防火墙
4、mariadb配置文件问题
5、连接服务器时的语法错误
6、主服务器mariadb权限
在这里是防火墙的原因，添加防火墙端口即可：

[root@TSL ~]# firewall-cmd --add-port=3306/tcp --permanent
success
[root@TSL ~]# firewall-cmd --reload
success
[root@localhost ~]# firewall-cmd --add-port=3306/tcp --permanent
success
[root@localhost ~]# firewall-cmd --reload
success

再查看

错误2：

解决不同步问题：
添加master_port
master_log_file
master_log_pos

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> change master to master_host=‘192.168.43.112’,master_user=‘repl’,master_password=‘123456’,master_port=3306,master_log_file=‘mysql-bin-master.000001’,master_log_pos=324;
Query OK, 0 rows affected (0.01 sec)

解决成功
当然，这里不能列出各种错误，需要根据实际的环境来排错，希望这里对大家有帮助！