openshell 的个人博客

一天很长,但十年很短。

Open Source, Open Mind,
Open Sight, Open Future!
  menu
110 文章
5051 浏览
0 当前访客
ღゝ◡╹)ノ❤️

【MySQL】主从复制

Mysql单库缺乏可用性和并发性,当数据库挂掉时整个系统就无法工作了,同时无法承受过高并发的请求。

读写分离

准备环境

环境说明:

  1. 准备三个mysql

    • mysql3301
    docker run --restart=always \
    --name=mysql3301  \
    --privileged=true \
    -e MYSQL_ROOT_PASSWORD=123456 \
    -p 3301:3306 \
    -v /dockerData/mysql/3301/:/etc/mysql/ \
    -v /dockerData/mysql/3301/logs:/logs \
    -v /dockerData/mysql/3301/mysql-files:/var/lib/mysql-files/ \
    -d mysql:8.0.13
    

    --restart=always :容器重启时始终重启该应用
    --name=mysql3301 :容器名称
    --privileged=true:使容器内部的root具有真正的root权限

    • mysql3302
    docker run --restart=always \
    --name=mysql3302  \
    --privileged=true \
    -e MYSQL_ROOT_PASSWORD=123456 \
    -p 3302:3306 \
    -v /dockerData/mysql/3302/conf/conf.d/:/etc/mysql/conf.d/ \
    -v /dockerData/mysql/3302/logs:/logs \
    -v /dockerData/mysql/3302/mysql-files:/var/lib/mysql-files/ \
    -d mysql:8.0.13
    
    • 创建my.cnf文
    docker exec  mysql3301 touch /etc/mysql/my.cnf
    docker exec  mysql3302 touch /etc/mysql/my.cnf
    docker exec  mysql3303 touch /etc/mysql/my.cnf
    
    • 时区同步
    docker cp /etc/localtime mysql3301:/etc/localtime
    docker cp /etc/localtime mysql3302:/etc/localtime
    docker cp /etc/localtime mysql3303:/etc/localtime
    
  2. 环境查看

    • 各容器的ip
    docker inspect mysql3301 |grep IPAddress 
    docker inspect mysql3302 |grep IPAddress
    docker inspect mysql3303 |grep IPAddress
    

    mysql3301: 172.18.0.3
    mysql3302: 172.18.0.2
    mysql3303: 172.18.0.4

配置

  1. Master(mysql3301)配置

    • 创建测试库
    docker exec -it mysql3301 bash
    mysql -uroot -p123456
    create database test_db;
    exit
    exit
    
    • 配置my.cnf
    #my.cnf 配置文件
    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    # 开启binlog
    log-bin=mysql-bin
    server-id=100
    # 需要同步的数据库,如果不配置则同步全部数据库
    binlog-do-db=test_db
    # binlog日志保留的天数,清除超过10天的日志
    # # 防止日志文件过大,导致磁盘空间不足
    expire-logs-days=10
    # Disabling symbolic-links is recommended to prevent assorted security risks
    #symbolic-links=0
    
    # Custom config should go here
    #!includedir /etc/mysql/conf.d/
    
    • 配置一个slave权限
    CREATE USER 'rep2'@'172.18.0.2' IDENTIFIED WITH mysql_native_password BY '123456';
    GRANT REPLICATION SLAVE ON *.* TO 'rep2'@'172.18.0.2';
    update user set host='172.18.%.%' where user='rep1';
    

重启Master节点

docker restart mysql3301

进入节点查看当前binlog日志信息

docker exec -it mysql3301 bash
mysql -uroot -p123456
show master status\G
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 155
     Binlog_Do_DB: test_db
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
  1. Slave配置

    • my.cnf 配置
    #my.cnf 配置文件
    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    secure-file-priv= NULL
    server-id=200
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    # Custom config should go here
    #!includedir /etc/mysql/conf.d/
    
    • 指定Matser
    docker exec -it mysql3302 bash
    
    mysql -uroot -p123456
    
    mysql>CHANGE MASTER TO 
    MASTER_HOST='172.18.0.3',
    MASTER_USER='rep1',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=155,
    master_port=3306;
    
    • 启动
    mysql> start slave;
    
    • 查看状态
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.18.0.3
                      Master_User: rep1
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000002
              Read_Master_Log_Pos: 155
                   Relay_Log_File: 232b0e17507e-relay-bin.000004
                    Relay_Log_Pos: 369
            Relay_Master_Log_File: mysql-bin.000002
                 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: 155
                  Relay_Log_Space: 751
                  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: b8cf8930-1df9-11ec-b54f-0242ac120003
                 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.04 sec)
    

    部分释意:
    Relay_Master_Log_File: mysql-bin.000001 //binlog日志文件名称
    Slave_IO_Running: Yes //Slave_IO线程、SQL线程都在运行
    Master_Server_Id: 100 //master主机的服务id

    mysql3303类似不再赘述。

测试

踩坑

  • 初始化数据不一致
    由于上面我的疏忽给各位留了一个小彩蛋,上面在配置好Master和Slave之前就创建了test_db库,所以在mysql3302启动的时候就会报错。
    错误信息: Last_Error: Error executing row event: 'Unknown database 'test_db''
    解决办法:将主数据库中的拷贝至从库即可。
    a. 锁定主库

    mysql> flush tables with read lock;
    

    b. 备份主库数据

    docker exec mysql3301 mysqldump \
    -u root --password=123456 \
    --databases test_db > backup.sql
    

    c.恢复数据至从库

    docker cp ./backup.sql mysql3303:/tmp
    docker exec  -it mysql3301 bash
    mysql -u root -p123456 < /tmp/backup.sql
    

    d.检查开启主从复制
    在mysql3303中执行start slave后有可能仍然不能正常同步,此时执行上面的“指定Matser”即可。

  • server_id重复
    需要确保mysql的my.cnf中server_id不一样

  • server_uuid重复
    错误信息:Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
    解决办法:需要确保auto.cnf中的server_uuid不一样,该文件通常位于/var/lib/mysql下,删除auto.cnf后重启mysql即可。

参考:
https://www.yinxiang.com/everhub/note/8be28c22-f814-40e2-9632-560da6acb3f4


标题:【MySQL】主从复制
作者:openshell
地址:http://solo.caiqz.cn/articles/2021/10/12/1634051433344.html