说明

  • 1个主库，可以设置 n 个从库，从库的数据能够自动和主库的数据保持一致
  • 主从复制的架构有两个好处：一个是进行数据自动的备份，数据在主库和从库上都有一份，如果主库挂了，从库还有一份完整的数据。另一个是可以实现数据库访问的读写分离，提高数据库的吞吐量。也就是对数据的更新操作是在主库中完成的，而读取操作在多个从库中完成的，从而实现了负荷分担的效果，提高数据库的访问效率
  • MySQL 的主从复制是通过他的归档日志（binlog） 来实现的。基本的过程就是从库在一个线程中和主库建立一个长连接，获取主库binlog 的位置，然后主库将这个位置之后的 binlog 日志内容发送给从库，从库获取到主库发送过来的 binlog 内容后先写入到本地的中转日志（relaylog）中，然后从库另一个进程在从中转日志中读取内容并解析成为 sql 语句在从库中执行，从而保证了主从的同步
  • 同步是基于binlog 的，所以默认同步具有 binlog 的所有库，但也可以在从库中指定需要同步的库，前提是该库具备 binlog

前提

  1. 主库和从库的数据库的版本保持一致

  2. 主从复制集群中每个数据库实例的 server-id 的值不能重复

  3. 要开启归档日志并且归档日志的格式选择为 row 方式。这两个在 MySQL 8 中默认就是这样，所以不需要设置，如果早期版本默认值不同需要在配置文件中人工设置

    1
    2
    3
    4
    # 是否开启 binlog
    show variables like 'log_bin';
    # binlog 格式
    show variables like 'binlog_format';

主库设置

配置 my.conf

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
#标识唯一id（必须），一般使用ip最后位
server-id=100

#不记录 binlog 的数据库，可设置多个
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
#指定需要 binlog 的数据库（和slave是相互匹配的），可以设置多个
binlog-do-db=jeecgboot_tongchuan_portal
binlog-do-db=jeecgboot_tongchuan_car

创建主从复制权限用户「或者使用root」

在 MySQL 中增加一个可以进行主从复制权限的用户。在 mysql 交互环境中执行如下命令

1
2
mysql>create user 'repl'@'10.110.2.72' identified by 'reple_password';
mysql>grant replication slave on *.* to 'repl'@'10.110.2.72';

查看日志位置

1
2
3
4
5
6
7
8
show master status;

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| WIN-PPBR15MIHTE-bin.000003 |     156  |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

获取到当前归档日志的名字和位置，后面从服务器设置主从复制的时候需要从这个位置开始

从库设置

配置 my.conf

1
2
3
4
5
6
servier-id = 11   ## id号一定不能与主服务器的id号相同
关掉二进制日志
#log-bin=mysql-bin
添加如下内容：   ##这些是开启中继日志的
relay-log=relay-bin
relay-log-index=relay-bin.index

重启服务

service mysqld restart

配置同步信息

1
2
3
4
5
6
7
8
9
mysql> change master to
    -> master_host='192.168.0.99',
    -> master_port=3306,
    -> master_user='root',
    -> master_password='123456',
    -> master_log_file='WIN-PPBR15MIHTE-bin.000003',
    -> master_log_pos=156;
mysql>start slave;
mysql>show slave status\G;

这个命令将当前数据库设置为 192.168.0.99 数据库的从库，并且从归档日志 WIN-PPBR15MIHTE-bin.000003 的位置 156 开始进行复制。

查看从库的状态

1
mysql> show slave status\G;

Slave_IO_State 的值为 Waiting for master to send event ，表示已经准备好接受主库发送过来的归档日志进行处理了

效果演示

在主库执行如下命令

1
2
3
4
5
mysql>create database test1;
mysql>use test1;
mysql>create table test1(id bigint);
mysql>insert into test1 values(1);
mysql>insert into test1 values(2);

在从库查看数据库状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test1;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test1           |
+-----------------+
1 row in set (0.01 sec)

mysql> select * from test1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql>

可以看到，主库中做的操作，都同步到从库中来了

指定同步的数据库

默认将主库中的所有数据库都同步到从库中来了，但可以通过如下2 种方式指定同步的数据库或者数据库的数据表

  • 方式一：在从库中执行命令，只同步数据库 test1。该方式重启数据库后失效
1
2
3
4
mysql> stop slave;
mysql> change replication filter replicate_do_db=(test1);
ysql> start slave;
mysql> show slave status\G; //查看状态
  • 方式二：在从库 my.cnf 文件中 mysqld 添加配置 「推荐」
1
2
3
4
[mysqld]
replicate_do_db=test1
replicate_do_db=test2
replicate_do_table=test.user

重新启动数据库