MySQL主从同步模拟

MySQL主从同步模拟

2019年05月20日 15:23:46 郭 璞 阅读数：1049

版权声明：本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。转载时请标注http://guoruibiao.blog.csdn.net https://blog.csdn.net/Marksinoberg/article/details/90375711

如题，今天来模拟下简单的MySQL主从同步模型的搭建。正式开始之前，先确保已经安装了docker。

准备素材

拉镜像

docker pull mysql

• 起一个容器，待会要从里面拿到原始的配置文件

docker run -d --name mysql_origin mysql

拿到配置文件

docker ps -a  # 拿到对应的container-id
docker inspect container-id | grep IP # 拿到此容器的IP地址，一般第一个都是172.17.0.2
docker cp container-id:/etc/mysql ./ # 把容器中的mysql配置文件 取出来 待会改改就可以用了

主从配置

经过上一步， 已经把测试需要的内容拿到了，接下来就分别对master和slave做下配置。
我这里的目录结构如下：

➜  mysql tree .
.
├── master     # master 配置，从mysql_origin内容修改而来
│   ├── conf.d
│   │   ├── docker.cnf
│   │   └── mysql.cnf
│   └── my.cnf
├── mysql          # 刚才从mysql_origin容器中拷到的
│   ├── conf.d
│   │   ├── docker.cnf
│   │   └── mysql.cnf
│   ├── my.cnf
│   └── my.cnf.fallback
└── slave     # slave 配置，从mysql_origin内容修改而来
    ├── conf.d
    │   ├── docker.cnf
    │   └── mysql.cnf
    └── my.cnf

6 directories, 10 files

master配置

修改配置文件

➜  mysql cat master/my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id       = 100
log-bin         = mysql-bin

然后把容器跑起来：

docker run -d -p 6666:3306 -e MYSQL_ROOT_PASSWORD=123456 -v $PWD/master:/etc/mysql --name mysql_master mysql

• 1

创建一个用于同步的账号，否则slave没办法从master这里通过权限校验，也就拿不到数据了。这里创建一个slave账号。

CREATE USER 'slave'@'172.17.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'172.17.0.%';FLUSH PRIVILEGES;

• 1
• 2

slave配置

修改下slave的配置，对比master的内容，变化就再最后面几行。

➜  mysql cat slave/my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id       = 101
log-bin         = mysql-slave-bin
relay_log       = edu-mysql-relay-bin # 可选项，这个想加就加，不想加就算了。

然后把slave也跑起来：

docker run -d -p 7777:3306 -e MYSQL_ROOT_PASSWORD=123456 -v $PWD/slave:/etc/mysql --name mysql_slave mysql

• 1

设置slave选项，大致的结构如下：

change master to
master_host='172.17.0.2',
master_user='slave',
master_password='123456',
master_port=3306,
master_log_file='mysql-bin.000003',
master_log_pos=1753,
master_connect_retry=30;

需要注意的是这个配置中的两个部分：

master_log_file
master_log_pos

这俩是主从同步的关键，具体的内容来自于在master中使用

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1753 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

同步操作

接下来正式操作了，在slave命令行中输入：

start slave;

•  

然后观察下slave的status，如果是下面的：

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1753
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000003
             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: 1753
              Relay_Log_Space: 534
              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: 100
                  Master_UUID: 2022420e-7ac6-11e9-a12e-0242ac110002
             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)

如果Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 说明基本上成功了，可以在master上创建库，创建表，然后再slave上去查看是否同步过来了。
如果不是这样，可能就会存在问题。具体的调试可以从Last_IO_Error 入手，可以从具体的文案进行处理。

问题汇总

在模拟的过程中，遇到了一些问题，最多的就是

Last_IO_Error: error connecting to master 'slave@172.17.0.2:3306' - retry-time: 30  retries: 10

• 1

参考链接中的文章也都描述过此类问题，基本上就是：

• 网络不通：可以ping或者在slave上用mysql-client 去连master等来测试
• pos或者log_file写错了： 先在master上show master status; 再去slave上填写对应的change master …
• slave账号的密码不对: 一般是在master上创建slave账户的时候密码出问题了，强制使用native格式的密码可以解决这个问题，如我开头写的。最后记得flush privileges;刷新下grant了的权限信息。

总结

模拟的过程本身就是一个学习的过程，从中可以了解到一种模式下的思想。上面列举的知识点也只是我个人遇到的一些小问题的总结，谈不上什么高深的内容，网上也都搜的到，我只是个知识的搬运工，能让后来人少踩点坑就够了。

anyway，enjoy it.

---

参考链接：
https://www.cnblogs.com/songwenjie/p/9371422.html

https://qq994300880.github.io/2019/03/27/MySQL主从复制配置/