docker下的mysql主从同步

mysql- @ imooto- 1年前

docker下的mysql主从同步

先介绍一下我搭建使用的环境：centos7
坑1：mysqld: Can't read dir of '/etc/mysql/conf.d/'，这是因为Centos7安全Selinux禁止了一些安全权限，导致mysql和mariadb在进行挂载/var/lib/mysql的时候会提示如下信息

logs命令查看，发现提示信息是：chown: changing ownership of ‘/var/lib/mysql/....‘: Permission denied

于是，解决方法有这么两个：

  1. 在docker run中加入 --privileged=true  给容器加上特定权限
  2. 关闭selinux
    本人采用的是第一种方式
    坑2：CHANGE MASTER TO MASTER_HOST='192.168.150.129',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=761;  我在linux上配置了主机名与ip的映射，因此我再master_host填写的是我的主机名，但是一直连不上，后来改为ip地址即可

主从配置文件

master配置文件/home/mysql/etc/master/master.cnf

  1. [mysqld]
  2. server_id = 1
  3.  
  4. character-set-server=utf8mb4
  5. collation-server=utf8mb4_unicode_ci
  6. default-storage-engine=INNODB
  7.  
  8. #Optimize omit
  9.  
  10. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  11.  
  12. log-bin     = /var/lib/mysql/binlog
  13. log_bin_trust_function_creators=1
  14. binlog_format = ROW
  15. expire_logs_days = 99
  16. sync_binlog = 0
  17.  
  18. slow-query-log=1
  19. slow-query-log-file=/var/log/mysql/slow-queries.log
  20. long_query_time = 3
  21. log-queries-not-using-indexes

slave配置文件/home/mysql/etc/slave/slave.cnf

  1. [mysqld]
  2. server_id = 2
  3.  
  4. character-set-server=utf8mb4
  5. collation-server=utf8mb4_unicode_ci
  6. default-storage-engine=INNODB
  7.  
  8. #Optimize omit
  9.  
  10. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  11.  
  12. log-bin     = /var/lib/mysql/binlog
  13. log_bin_trust_function_creators=1
  14. binlog_format = ROW
  15. expire_logs_days = 99
  16. sync_binlog = 0
  17.  
  18. relay_log=slave-relay-bin
  19. log-slave-updates=1
  20. slave-skip-errors=all
  21.  
  22. slow-query-log=1
  23. slow-query-log-file=/var/log/mysql/slow-queries.log
  24. long_query_time = 3

log-queries-not-using-indexes

启动容器,配置主从复制

创建数据目录：

  1. mkdir -p /home/mysql/data/master
  2. mkdir -p /home/mysql/data/slave
  3.  
  4. MASTER_DIR=/home/mysql/data/master
  5. SLAVE_DIR=/home/mysql/data/slave

启动MySQL容器：

  1. docker run --name mysql-master \
  2. -v /home/mysql/etc/master:/etc/mysql/conf.d \
  3. -v /home/mysql/data/master:/var/lib/mysql \
  4. -e MYSQL_ROOT_PASSWORD=root \
  5. -d \
  6. mysql:5.7
  7.  
  8.  
  9. docker run --name mysql-slave \
  10. -v /home/mysql/etc/slave:/etc/mysql/conf.d \
  11. -v /home/mysql/data/slave:/var/lib/mysql \
  12. -e MYSQL_ROOT_PASSWORD=root \
  13. --link mysql-master:master \
  14. -d mysql:5.7

Master上创建复制用户：

  1. docker exec -it mysql-master mysql -p
  2.  
  3. CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
  4. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

查看Master节点 Binlog状态：

  1. show master status\G
  2. *************************** 1. row ***************************
  3.              File: binlog.000008
  4.          Position: 595
  5.      Binlog_Do_DB:
  6.  Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. 1 row in set (0.00 sec)

在Slave节点上配置复制：

  1. docker exec -it mysql-slave mysql -p
  2.  
  3. CHANGE MASTER TO \
  4. MASTER_HOST='master',\
  5. MASTER_PORT=3306,\
  6. MASTER_USER='repl',\
  7. MASTER_PASSWORD='repl',\
  8. MASTER_LOG_FILE='binlog.000008',\
  9. MASTER_LOG_POS=595;
  10.  
  11. start slave;
  12.

查看Salve状态：

  1. show slave status\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: master
  5.                   Master_User: repl
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: binlog.000008
  9.           Read_Master_Log_Pos: 595
  10.                Relay_Log_File: slave-relay-bin.000002
  11.                 Relay_Log_Pos: 317
  12.         Relay_Master_Log_File: binlog.000008
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB:
  16.           Replicate_Ignore_DB:
  17.            Replicate_Do_Table:
  18.        Replicate_Ignore_Table:
  19.       Replicate_Wild_Do_Table:
  20.   Replicate_Wild_Ignore_Table:
  21.                    Last_Errno: 0
  22.                    Last_Error:
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 595
  25.               Relay_Log_Space: 524
  26.               Until_Condition: None
  27.                Until_Log_File:
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File:
  31.            Master_SSL_CA_Path:
  32.               Master_SSL_Cert:
  33.             Master_SSL_Cipher:
  34.                Master_SSL_Key:
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error:
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error:
  41.   Replicate_Ignore_Server_Ids:
  42.              Master_Server_Id: 1
  43.                   Master_UUID: 7a5a452f-fca9-11e6-be18-0242ac110002
  44.              Master_Info_File: /var/lib/mysql/master.info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind:
  50.       Last_IO_Error_Timestamp:
  51.      Last_SQL_Error_Timestamp:
  52.                Master_SSL_Crl:
  53.            Master_SSL_Crlpath:
  54.            Retrieved_Gtid_Set:
  55.             Executed_Gtid_Set:
  56.                 Auto_Position: 0
  57.          Replicate_Rewrite_DB:
  58.                  Channel_Name:
  59.            Master_TLS_Version:
  60. 1 row in set (0.00 sec)

  laravel , 闲得慌 , laravel , 闲得慌 ,

作者  :  imooto

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.

评论

还没有评论,快来抢沙发吧