MySQL 8 主从复制设置

前言

MySQL 有一个比较重要的功能是主从复制，也就是一个主库，可以设置 n 个从库，从库的数据能够自动和主库的数据保持一致。主从复制的架构有两个好处，一个是进行数据自动的备份，数据在主库和从库上都有一份，如果主库挂了，从库还有一份完整的数据。另一个是可以实现数据库访问的读写分离，提高数据库的吞吐量。也就是对数据的更新操作是在主库中完成的，而读取操作在多个从库中完成的，从而实现了负荷分担的效果，提高数据库的访问效率。

MySQL 的主从复制是通过他的归档日志（binlog） 来实现的。基本的过程就是从库在一个线程中和主库建立一个长连接，告诉主库从主库同步的 binlog 的位置，然后主库将这个位置之后的 binlog 日志内容发送给从库，从库获取到主库发送过来的 binlog 内容后先写入到本地的中转日志（relaylog）中，然后从库另一个进程在从中转日志中读取内容并解析成为 sql 语句在从库中执行，从而保证了主从的同步。具体的架构如下图所示。


MySQL 主从复制架构图

要设置主从复制，有几个前提：

  1. 首先，主库和从库的数据库的版本保持一致。
  2. 第二，主从复制集群中每个数据库实例的 server-id 的值不能重复。
  3. 第三，要开启归档日志并且归档日志的格式选择为 row 方式。这两个在 MySQL 8 中默认就是这样，所以不需要设置，如果早期版本默认值不同需要在配置文件中人工设置。

全新的主库设置从库

这里，我们以一个全新安装的主库为例来说明如何设置一个主从复制的MySQL 集群。以我们在前一篇文章 《MySQL 8 二进制方式安装到 CentOS 7 中》 安装的数据库作为主库，在服务器 10.110.2.72 中安装一个版本一样的 MySQL 库作为从库。特别注意，在这里的 server-id 值要设置为和主库设置的 71 不一样，这里我们把他设置为

主库设置

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

mysql>create user 'repl'@'10.110.2.72' identified by 'reple_password';
mysql>grant replication slave on *.* to 'repl'@'10.110.2.72';
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     888  |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

从库设置

得到这些信息后，我们就可以登录到 10.10.2.72 服务器上的数据库，然后通过下面的命令将这个数据库设置为 10.110.2.71 数据库的从服务器。

mysql> change master to
    -> master_host='10.110.2.71',
    -> master_port=38965,
    -> master_user='repl',
    -> master_password='replpassword',
    -> master_log_file='binlog.000001',
    -> master_log_pos=888;
mysql>start slave;
mysql>show slave slave\G;

这个命令将当前数据库设置为 10.110.2.71 数据库的从库，并且从归档日志 binlog.000001 的位置 888 开始进行复制。

查看从库的状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.110.2.71
                  Master_User: repl
                  Master_Port: 38965
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 888
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 319
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4035
              Relay_Log_Space: 1431
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 71
                  Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

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

效果演示

登录主库执行如下命令

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);

在从库查看数据库状态

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>

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

同步指定的数据库

上面的设置中，我们是将主库中的所有数据库都同步到从库中来了。实际上，我们还可以通过命令的方式同步指定的数据库或者数据库的数据表。在从库中执行下面的命令将会指定只同步数据库 test1

mysql> stop slave;
Query OK, 0 rows affected (0.16 sec)
mysql> change replication filter replicate_do_db=(test1);
Query OK, 0 rows affected (0.00 sec)
ysql> start slave;
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.110.2.71
                  Master_User: repl
                  Master_Port: 38965
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 4035
               Relay_Log_File: localhost-relay-bin.000010
                Relay_Log_Pos: 319
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test1
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4035
              Relay_Log_Space: 1431
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 71
                  Master_UUID: 2980729e-23c6-11e9-aec6-005056881ba9
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

这样，只有主库中 test1 数据库中的变化会同步到从库中来，其余的数据库的变化则不会同步。

这个命令产生的效果没有保存下来，如果数据库重启了，那么就会失效。如果要生效就需要将配置写在 my.cnf 文件中。在从库中，将 my.cnf 文件内容更改为如下即可。

[mysqld]
server-id=72
port=38965
character-set-server=utf8mb4
default-time_zone='+8:00'
lower_case_table_names=1

#max_connections=3000
#max_connect_errors=100000

default_authentication_plugin=mysql_native_password
datadir=/data/mysql/data
socket=/data/mysql/mysql.sock
log-error=/data/mysql/logs/mysqld.log
pid-file=/data/mysql/mysqld.pid

long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/mysql/logs/slow.log
replicate_do_db=test1

[client]
port=38965
default-character-set=utf8mb4
socket=/data/mysql/mysql.sock

[mysql]
no-auto-rehash
default-character-set=utf8mb4

加入了新行 replicate_do_db=test1。然后重新启动数据库即可。

非空主库主从设置

很多时候，设置主从复制的时候，主库已经运行了一段时间，有了一些业务数据，那么这个时候我们首先将主库设置为只读状态，不允许新数据写入，然后查看当前的归档日志状态，记录下来后将数据库备份出来，恢复从库的可写状态，最后把备份出来的数据库恢复到从库中，再设置为从刚才查询出来的归档日志和归档日志的位置开始进行同步即可。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,340评论 5赞 467
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,762评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,329评论 0赞 329
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,678评论 1赞 270
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,583评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 47,995评论 1赞 275
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,493评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,145评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,293评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,250评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,267评论 1赞 328
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,973评论 3赞 316
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,556评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,648评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,873评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,257评论 2赞 345
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,809评论 2赞 339

推荐阅读更多精彩内容