两台服务器之间mysql数据库怎么做同步_MySQL高可用之主从复制

最近公司业务量有点大，服务器I/O访问频率过高，之前单节点MySQL有点扛不住压力了，于是我找老板又搞了一台服务器，准备上MySQL的主从复制和读写分离，做多库的存储，提高单个机器的性能，老板欣然同意！

最重要的是，在搭建MySQL读写分离期间，有了这篇水文，很快啊，希望读者大大们耗子尾汁，多多点赞，谢谢朋友们！

主从复制

在业务复杂的系统中，有这么一个情景，有一句sql语句「需要锁表」，导致「暂时不能使用读的服务」，那么就很影响运行中的业务。

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

另外，可以做数据的热备份。

再一个，像我们的业务，业务量上来了，需要「做架构的扩展」，做多库的存储，「降低磁盘I/O访问的频率」，提高单个机器的I/O性能。

什么是主从复制

MySQL 主从复制是指「数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点」。

MySQL 默认采用「异步复制方式」，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

MySQL主从复制原理

1. 「master服务器将数据的改变记录二进制binlog日志」。当master上的数据发生改变时，则将其改变写入二进制日志中；
2. 「slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变」，如果发生改变，则开始一个I/O Thread请求master二进制事件；
3. 同时「主节点为每个I/O线程启动一个dump线程」，用于向其发送二进制事件，并「保存至从节点本地的中继日志中」，从节点将启动「SQL线程」从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。

也就是说：

• 「从库」会生成两个线程，一个「I/O线程」，一个「SQL线程」；
• 「I/O线程会去请求主库的binlog」，并将得到的binlog写到本地的「relay-log」(中继日志)文件中；
• 「主库」会生成一个「log dump线程」，用来「给从库I/O线程传binlog」；
• 「SQL线程会读取relay log文件中的日志」，并解析成sql语句逐一执行。

MySQL主从复制形式

一主一从

一个主节点，一个从节点，简单方便，稍后我们就搭建这种一主一从的主从复制模式。

主主复制

主主复制：「互为主从」

• 容易产生的问题：数据不一致；因此慎用
• 考虑要点：自动增长id

一个节点id使用奇数：

auto_increment_offset=1
auto_increment_increment=2

另一个主节点id使用偶数：

auto_increment_offset=2
auto_increment_increment=2

一主多从

常用于「扩展系统读取性能」，因为「读是在从库读取」的。

多主一从

从MySQL 5.7版本开始支持，也叫「多源复制」，数据流向是从多个主库同步数据到一个从库：

多应用于：

• 数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。
• 读写分离，从库只用于查询，提高数据库整体性能。

级联复制

在主从复制的基础上，主从之间存在一个级联复制的从服务器，当级联复制的主机复制了主服务器的数据，「级联复制主机充当为主服务器，从服务器复制级联复制主机的数据及二进制日志数据」。

「Tip」：中间级联复制的主机无法将二进制日志传递到其他从服务器上，因此需要加上log_slave_updates选项，「目的是为了将主服务器的二进制日志文件能写入到从服务器上」。

搭建一主一从主从复制环境

1. 主机规划

序号	主机名称	IP地址	虚拟机OS	MySQL
1	mysql-master	192.168.2.158	CnetOS 7.8	5.7
2	mysql-slave	192.168.2.159	CnetOS 7.8	5.7

2. 安装MySQL

还未安装的，直接参考：

如何在CentOS7下快速安装MySQL5.7

CV操作就行了。

3. 两台MySQL服务器创建数据库

两台主机均执行：

mysql> create database laogong;

该数据库用来指定同步的数据库名称。

4. 配置Master

4.1 配置主节点

在「mysql-master」节点下配置 「/etc/my.cnf」 ，在[mysqld]模块下添加如下内容：

log-bin=master-bin
binlog-format=ROW
server-id=1
binlog-do-db=laogong

「log-bin」：定义二进制文件名称。

「binlog-format」：二进制日志格式，有「row」、「statement」、「mixed」三种格式。

• 「row」：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
• 「statement」：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。「MySQL默认采用基于语句的复制，效率比较高」。
• 「mixed」：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

「server-id」：服务器的id，各节点的id必须不一样。

「binlog-do-db」：要同步的数据库名称。

4.2 授权从服务器登录主服务器

--授权操作
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by '123456';
--刷新权限
flush privileges;

5. 配置Slave

在「mysql-slave」节点配置 /etc/my.cnf ，在 [mysqld] 模块下添加：

log-bin=slave-bin
binlog-format=ROW
server-id=2

6. 重启Master，查看状态

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 | laogong      |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

7. 重启Slave并进行相关配置

mysql> change master to master_host='192.168.2.158',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G

查看slave状态时，可以看到有报错信息：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

这个报错是因为我偷懒，在master主机安装好MySQL以后，我直接克隆的一台slave主机，导致auto.cnf文件中保存的UUID是重复的。

解决方法很简单，停掉slave的mysql实例，删除其 datadir 文件夹下的的 auto.cnf 文件，再启动备库实例，此时备库就会产生一个新的 auto.cnf 文件，也就会生成和master的mysql服务不一样的UUID。

此时再查看slave状态：

show slave status\G

就OK了。

8. 验证

在主库创建一个table：

mysql> use laogong;
Database changed
mysql> create table xblzer(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

从库读取：

mysql> use laogong;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_laogong |
+-------------------+
| xblzer            |
+-------------------+
1 row in set (0.00 sec)

再在主库添加1条记录：

mysql> insert into xblzer values(1, 'laowang');

在从库查看：

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

这样，MySQL一主一从的主从复制架构就搭建起来了。

那么，为了减轻每台MySQL主机的访问压力，还可以对MySQL进行读写分离，实际上，主从复制和读写分离一般就是联合使用的。我们实际生产环境使用的是sharding-jdbc来实现的读写分离。

还有其他的像「mycat」，「amoeba」等中间件也可以做读写分离(善用中间件)

精心为你准备的最全的20道Mysql面试题。

2020-11-07

MySQL如何实时同步数据到ES？试试这款阿里开源的神器！

2020-11-10

MySQL中，21个写SQL的好习惯，值得培养

2020-11-05

妙用 Intellij IDEA 创建临时文件，Git 跟踪不到的那种

2020-11-03

多数人都曾遇到过的 limit 问题，深入浅出 MySQL 优先队列

2020-11-03