• docker 安装 mysql 5.7 主从实战 (1主1从)

     1. 创建 mysql-master 配置文件
            mkdir -p /root/docker/mysql-master/conf
            vim /root/docker/mysql-master/conf/my.cnf

    1. [client]
    2. # 客户端默认字符集
    3. default_character_set=utf8mb4
    4. [mysql]
    5. # 默认字符集
    6. default_character_set=utf8mb4
    7. [mysqld]
    8. # 设置唯一id
    9. server_id=1001
    10. # pid文件
    11. pid-file=/var/run/mysqld/mysqld.pid
    12. # sock文件
    13. socket=/var/run/mysqld/mysqld.sock
    14. # 数据目录
    15. datadir=/var/lib/mysql
    16. # 不区分大小 0区分 1不区分
    17. lower_case_table_names=1 
    18. # 服务器时区
    19. default-time_zone='+8:00'
    20. # 服务端字符集
    21. character_set_server=utf8mb4
    22. # 字符排序规则
    23. collation_server=utf8mb4_general_ci
    24. # 默认存储引擎
    25. default-storage-engine=InnoDB 
    26. # 指定不需要同步的数据库名称
    27. binlog-ignore-db=mysql
    28. # 开启binlog功能
    29. log-bin=prod-mysql-bin
    30. # 设置binlog事务内存使用大小
    31. binlog_cache_size=1m
    32. # 设置binlog格式 (mixed,statement,row)
    33. binlog_format=mixed
    34. # 设置binlog过期清理时间
    35. expire_logs_days=7
    36. # 跳过主从复制中的错误 1062主键重复
    37. slave_skip_errors=1062

    2. 创建 mysql-slave1 配置文件 
            mkdir -p /root/docker/mysql-slave1/conf
            vim /root/docker/mysql-slave1/conf/my.cnf

    1. [client]
    2. # 客户端默认字符集
    3. default_character_set=utf8mb4
    4. [mysql]
    5. # 默认字符集
    6. default_character_set=utf8mb4
    7. [mysqld]
    8. # 设置唯一id
    9. server_id=1002
    10. # pid文件
    11. pid-file=/var/run/mysqld/mysqld.pid
    12. # sock文件
    13. socket=/var/run/mysqld/mysqld.sock
    14. # 数据目录
    15. datadir=/var/lib/mysql
    16. # 不区分大小 0区分 1不区分
    17. lower_case_table_names=1 
    18. # 服务器时区
    19. default-time_zone='+8:00'
    20. # 服务端字符集
    21. character_set_server=utf8mb4
    22. # 字符排序规则
    23. collation_server=utf8mb4_general_ci
    24. # 默认存储引擎
    25. default-storage-engine=InnoDB 
    26. # 指定不需要同步的数据库名称
    27. binlog-ignore-db=mysql
    28. # 开启binlog功能
    29. log-bin=prod-slave1-mysql-bin
    30. # 设置binlog事务内存使用大小
    31. binlog_cache_size=1m
    32. # 设置binlog格式 (mixed,statement,row)
    33. binlog_format=mixed
    34. # 设置binlog过期清理时间
    35. expire_logs_days=7
    36. # 跳过主从复制中的错误 1062主键重复
    37. slave_skip_errors=1062
    38. # 配置中继日志
    39. relay_log=prod-mysql-relay-bin
    40. # 设置slave更新自己的binlog
    41. log_slave_updates=1
    42. # slave只读
    43. read_only=1

    3. 启动容器 master
        docker run -d -p 13306:3306 \
        --restart always \
        --privileged \
        --name mysql-master \
        -e MYSQL_USER=admin \
        -e MYSQL_PASSWORD=admin123 \
        -e MYSQL_ROOT_PASSWORD=admin123 \
        -v /root/docker/mysql-master/logs:/var/log/mysql \
        -v /root/docker/mysql-master/data:/var/lib/mysql \
        -v /root/docker/mysql-master/conf:/etc/mysql/conf.d \
        mysql:5.7

    4. 启动容器 slave1
        docker run -d -p 13307:3306 \
        --restart always \
        --privileged \
        --name mysql-slave1 \
        -e MYSQL_USER=admin \
        -e MYSQL_PASSWORD=admin123 \
        -e MYSQL_ROOT_PASSWORD=admin123 \
        -v /root/docker/mysql-slave1/logs:/var/log/mysql \
        -v /root/docker/mysql-slave1/data:/var/lib/mysql \
        -v /root/docker/mysql-slave1/conf:/etc/mysql/conf.d \
        mysql:5.7

    5. 进入 master 容器执行操作
        docker exec -it mysql-master /bin/bash
        mysql -uroot -padmin123
        # 创建同步用户
        CREATE USER 'slave'@'%' IDENTIFIED BY 'slave-sync'; 
        # 授权同步用户
        GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
        # 查看 master 状态, 记录 File 和 Position
        show master status;

    1. root@a5c0e0b51647:/# mysql -uroot -padmin123
    2. mysql: [Warning] Using a password on the command line interface can be insecure.
    3. Welcome to the MySQL monitor.  Commands end with ; or \g.
    4. Your MySQL connection id is 2
    5. Server version: 5.7.36-log MySQL Community Server (GPL)
    6.  
    7. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
    8.  
    9. Oracle is a registered trademark of Oracle Corporation and/or its
    10. affiliates. Other names may be trademarks of their respective
    11. owners.
    12.  
    13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    14.  
    15. mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'slave-sync';
    16. Query OK, 0 rows affected (0.01 sec)
    17.  
    18. mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
    19. Query OK, 0 rows affected (0.00 sec)
    20.  
    21. mysql> show master status;
    22. +-----------------------+----------+--------------+------------------+-------------------+
    23. | File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    24. +-----------------------+----------+--------------+------------------+-------------------+
    25. | prod-mysql-bin.000003 |      617 |              | mysql            |                   |
    26. +-----------------------+----------+--------------+------------------+-------------------+

     6. 进入 slave1 容器执行操作
        docker exec -it mysql-slave /bin/bash
        mysql -uroot -padmin123
        # 切换master
        change master to master_host='宿主机ip', master_user='同步用户用户名', master_password='同步用户密码', master_port=master端口, master_log_file='刚刚记录的File', master_log_pos=刚刚记录的position, master_connect_retry=同步重试隔秒;
        # 启动从slave
        start slave;
        # 观察同步状态  Slave_IO_Running 和 Slave_SQL_Running 是否都为yes
        show slave status \G;

    1. mysql> change master to master_host='172.19.6.128', master_user='slave', master_password='slave-sync', master_port=13306, master_log_file='prod-mysql-bin.000003', master_log_pos=617, master_connect_retry=30;
    2. Query OK, 0 rows affected, 2 warnings (0.02 sec)
    3.  
    4. mysql> show slave status \G;
    5. *************************** 1. row ***************************
    6.                Slave_IO_State: 
    7.                   Master_Host: 172.19.6.128
    8.                   Master_User: slave
    9.                   Master_Port: 13306
    10.                 Connect_Retry: 30
    11.               Master_Log_File: prod-mysql-bin.000003
    12.           Read_Master_Log_Pos: 617
    13.                Relay_Log_File: prod-mysql-relay-bin.000001
    14.                 Relay_Log_Pos: 4
    15.         Relay_Master_Log_File: prod-mysql-bin.000003
    16.              Slave_IO_Running: No
    17.             Slave_SQL_Running: No
    18.               Replicate_Do_DB: 
    19.           Replicate_Ignore_DB: 
    20.            Replicate_Do_Table: 
    21.        Replicate_Ignore_Table: 
    22.       Replicate_Wild_Do_Table: 
    23.   Replicate_Wild_Ignore_Table: 
    24.                    Last_Errno: 0
    25.                    Last_Error: 
    26.                  Skip_Counter: 0
    27.           Exec_Master_Log_Pos: 617
    28.               Relay_Log_Space: 154
    29.               Until_Condition: None
    30.                Until_Log_File: 
    31.                 Until_Log_Pos: 0
    32.            Master_SSL_Allowed: No
    33.            Master_SSL_CA_File: 
    34.            Master_SSL_CA_Path: 
    35.               Master_SSL_Cert: 
    36.             Master_SSL_Cipher: 
    37.                Master_SSL_Key: 
    38.         Seconds_Behind_Master: NULL
    39. Master_SSL_Verify_Server_Cert: No
    40.                 Last_IO_Errno: 0
    41.                 Last_IO_Error: 
    42.                Last_SQL_Errno: 0
    43.                Last_SQL_Error: 
    44.   Replicate_Ignore_Server_Ids: 
    45.              Master_Server_Id: 0
    46.                   Master_UUID: 
    47.              Master_Info_File: /var/lib/mysql/master.info
    48.                     SQL_Delay: 0
    49.           SQL_Remaining_Delay: NULL
    50.       Slave_SQL_Running_State: 
    51.            Master_Retry_Count: 86400
    52.                   Master_Bind: 
    53.       Last_IO_Error_Timestamp: 
    54.      Last_SQL_Error_Timestamp: 
    55.                Master_SSL_Crl: 
    56.            Master_SSL_Crlpath: 
    57.            Retrieved_Gtid_Set: 
    58.             Executed_Gtid_Set: 
    59.                 Auto_Position: 0
    60.          Replicate_Rewrite_DB: 
    61.                  Channel_Name: 
    62.            Master_TLS_Version: 
    63. 1 row in set (0.00 sec)
    64.  
    65. ERROR: 
    66. No query specified
    67.  
    68. mysql> start slave;
    69. Query OK, 0 rows affected (0.01 sec)
    70.  
    71. mysql> show slave status \G;
    72. *************************** 1. row ***************************
    73.                Slave_IO_State: Waiting for master to send event
    74.                   Master_Host: 172.19.6.128
    75.                   Master_User: slave
    76.                   Master_Port: 13306
    77.                 Connect_Retry: 30
    78.               Master_Log_File: prod-mysql-bin.000003
    79.           Read_Master_Log_Pos: 617
    80.                Relay_Log_File: prod-mysql-relay-bin.000002
    81.                 Relay_Log_Pos: 325
    82.         Relay_Master_Log_File: prod-mysql-bin.000003
    83.              Slave_IO_Running: Yes
    84.             Slave_SQL_Running: Yes
    85.               Replicate_Do_DB: 
    86.           Replicate_Ignore_DB: 
    87.            Replicate_Do_Table: 
    88.        Replicate_Ignore_Table: 
    89.       Replicate_Wild_Do_Table: 
    90.   Replicate_Wild_Ignore_Table: 
    91.                    Last_Errno: 0
    92.                    Last_Error: 
    93.                  Skip_Counter: 0
    94.           Exec_Master_Log_Pos: 617
    95.               Relay_Log_Space: 537
    96.               Until_Condition: None
    97.                Until_Log_File: 
    98.                 Until_Log_Pos: 0
    99.            Master_SSL_Allowed: No
    100.            Master_SSL_CA_File: 
    101.            Master_SSL_CA_Path: 
    102.               Master_SSL_Cert: 
    103.             Master_SSL_Cipher: 
    104.                Master_SSL_Key: 
    105.         Seconds_Behind_Master: 0
    106. Master_SSL_Verify_Server_Cert: No
    107.                 Last_IO_Errno: 0
    108.                 Last_IO_Error: 
    109.                Last_SQL_Errno: 0
    110.                Last_SQL_Error: 
    111.   Replicate_Ignore_Server_Ids: 
    112.              Master_Server_Id: 1001
    113.                   Master_UUID: a1bfecf9-72fd-11ed-b605-0242ac110002
    114.              Master_Info_File: /var/lib/mysql/master.info
    115.                     SQL_Delay: 0
    116.           SQL_Remaining_Delay: NULL
    117.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    118.            Master_Retry_Count: 86400
    119.                   Master_Bind: 
    120.       Last_IO_Error_Timestamp: 
    121.      Last_SQL_Error_Timestamp: 
    122.                Master_SSL_Crl: 
    123.            Master_SSL_Crlpath: 
    124.            Retrieved_Gtid_Set: 
    125.             Executed_Gtid_Set: 
    126.                 Auto_Position: 0
    127.          Replicate_Rewrite_DB: 
    128.                  Channel_Name: 
    129.            Master_TLS_Version: 
    130. 1 row in set (0.00 sec)
    131.

    7. 测试

            记得开放端口 13306, 13307

  • 相关阅读:
    可编程USB转 UART/I2C /SMBusS/SPI/CAN/1 -Wire适配器USB2S结构尺寸及电压设置
    服务端 TCP 连接的 TIME_WAIT 过多问题的分析与解决
    快速计算发票金额
    Linux 5种网络模型
    ComponentAce FlexCompress强大功能
    数据结构和算法——用C语言实现所有排序算法
    应用程序通过 Envoy 代理和 Jaeger 进行分布式追踪（一）
    游戏盾的防御原理及产品特性
    阿里的这份支持千万级并发Nginx Web服务器详解PDF也有人说不行？
    审稿人：拜托，请把模型时间序列去趋势！！
  • 原文地址：https://blog.csdn.net/qq_41011894/article/details/128165368