2

使用docker实现mysql主从同步

 1 year ago
source link: https://blog.51cto.com/u_15011559/5986141
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

       本文是以最少步骤的安装,仅实现了在docker下mysql的主从同步

1. 创建Docker镜像

       创建两个MySQL版本:5.7的镜像,一个容器名称为master,另一个为slaver。

docker run -d --name master -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:5.7
docker run -d --link master:master  --name slaver -e MYSQL_ROOT_PASSWORD=root -p 3307:3306 mysql:5.7

       通过docker inspect命令查看容器的ip+port
       master主数据库为<master-mysql-ip> <master-mysql-port>。
       slaver从数据库<slave-mysql-ip> <slave-mysql-port> 对宿主机是3306,对于容器来说依旧是3306。

2. 主数据库配置

       修改配置主数据库master的配置文件可以通过exec进入命令,但是需要更新apt-get,然后再安装vim编辑工具,所以我才用的是docker cp命令进行修改的。

docker cp master:/etc/mysql/mysql.cnf  <host-machine-pwd>

       配置文件显示为如下内容。

[mysqld]
server-id=1
log-bin=log
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#binlog-ignore-db设置不需要同步的库

       然后再复制回去。

docker cp <host-machine-pwd>/mysql.cnf master:/etc/mysql/

       然后连接数据库执行一下命令。

create user 'slaver'@'%' identified by 'slaver'; 
grant FILe on *.* to 'slaver'@'<slave-mysql-ip>' identified by 'slaver';  
grant replication slave on *.* to 'slaver'@'<slave-mysql-ip>' identified by 'slaver';
flush privileges;

       此命令表示创建一个名为slaver密码也为slaver的账户,然后授予主从复制权限给这个用户,其中<slave-mysql-ip>为slaver的host。
执行完毕后重启数据库(重启镜像)，而后连接主数据库master,执行show master status。

mysql> show master status;
+------------+----------+--------------+-------------------------------------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------+----------+--------------+-------------------------------------------------+-------------------+
| log.000002 |      414 |              | mysql,information_schema,performance_schema,sys |                   |
+------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

3. 从数据库配置

       复制文件至宿主机

docker cp slaver:/etc/mysql/mysql.cnf  <host-machine-pwd>/

       修改服务名称，注意，server-id不能和主节点重复。

[mysqld]
server-id=2
log-bin=mysql-bin
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
#binlog-ignore-db设置不需要同步的库

       然后再复制回去

docker cp <host-machine-pwd>/mysql.cnf slaver:/etc/mysql/

       然后重启数据库(重启镜像)，连接从数据库执行 stop slave。

stop slave

change master to 
MASTER_HOST ='10.20.66.150', 
MASTER_USER ='slaver',
MASTER_PASSWORD ='slaver',
MASTER_PORT  = 3306,
MASTER_LOG_FILE ='log.000002',
MASTER_LOG_POS =414;

start slave;

       看到别人的博客在配置这里的地方都有错误,在这里我贴出官网文档,  https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

       到目前为止主从同步已经搭建完成，在从库中使用show slave status \G;查看同步状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slaver
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log.000002
          Read_Master_Log_Pos: 414
               Relay_Log_File: 3a03e76858dd-relay-bin.000003
                Relay_Log_Pos: 615
        Relay_Master_Log_File: log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
           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: 414
              Relay_Log_Space: 1661
              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: 1
                  Master_UUID: beecd22a-ac7b-11ea-b4ba-0242ac110002
             Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
       上方两条结果表示运行正常,如果失败,请看
Last_IO_Errno:
Last_IO_Error:
       搭建过程中遇到了一个问题就是数据库密码错误导致无法连接主库,所以还是要仔细小心
       具体的学习主从复制的配置,可以翻阅官方文档  https://dev.mysql.com/doc/refman/5.7/en/replication.html


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK