Docker环境模拟测试脚本动态创建MySQL innodb slave从服务器并立即开始同步，升降配前准备

た入场券 2023-01-17 07:37 8阅读 0赞

主服务器

mkdir /tmp/mysql1
    docker run --name mysql1 -v /tmp/mysql1:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=iaThohY9hei.V_ec -d mysql:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --log_bin=binlog --server_id=1

登陆master执行

CREATE USER 'replicate'@'%' IDENTIFIED WITH mysql_native_password BY 'iaThohY9hei.V_ec';
    GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%'
    flush privileges;

创建测试库、表

create database dumptest;
    
    use dumptest;
    
     
    
    create table t1(id int) engine=innodb;
    
    create table t2(id int) engine=innodb;

编写Python写入脚本

#!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import time
    
    import MySQLdb
    
    conn=MySQLdb.connect(host='172.17.0.2', db='dumptest', user="root", password="iaThohY9hei.V_ec")
    
    cursor=conn.cursor()
    
    for i in range(10000000):
    
        cursor.execute("insert into t1(id) values(%d)" % i)
    
        cursor.execute("insert into t2(id) values(%d)" % i)
    
        conn.commit()
    
        time.sleep(0.1)

需要安装python3 的 mysqlclient

./venv/bin/pip3 install mysqlclient

启动程序，开始写入

创建从数据库

mkdir /tmp/mysql2
    docker run --name mysql2 -v /tmp/mysql2:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=iaThohY9hei.V_ec -d mysql:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --log_bin=binlog --server_id=2

修改dump.sql，补全CHANGE MASTER TO 指令

原

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=156;

改为

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=156, MASTER_HOST='172.17.0.2', MASTER_USER='replicate', MASTER_PASSWORD='iaThohY9hei.V_ec';

导入数据到slave

mysql -h172.17.0.3 -uroot -p < dump.sql

登陆slave执行

start slave;

查看slave状态

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.0.2
                      Master_User: replicate
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000004
              Read_Master_Log_Pos: 5668960
                   Relay_Log_File: 37ebe3d38718-relay-bin.000002
                    Relay_Log_Pos: 5669125
            Relay_Master_Log_File: binlog.000004
                 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: 5668960
                  Relay_Log_Space: 5669341
                  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: 11db6bc6-a725-11eb-932c-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
                Network_Namespace: 
    1 row in set, 1 warning (0.01 sec)

由于模拟环境数据量较小，slave很快就跟master保持了数据同步，新增的数据也很快同步过来了。