测试目的

  1. 同步一个数据库 canal_example
  2. 主服务器binlog格式由mix 同步到 row 是否影响从库的同步

主库

主库IP : 192.168.1.101
账号 : docker-canal
密码 : CVsVQWF!^e@06A0e&y

从库:
虚拟机IP : 192.168.39.5
需要同步的表 : canal_example

步骤

  1. 确定是 mix 模式
  1. # open bin log
  2. log_bin=mysql-bin
  3. binlog_format=MIXED
  5. # server id
  6. server_id=1
$ systemctl restart mysqld
  1. 导入数据
# 数据库锁
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

给从库设置外部访问密码

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'CVsVQWF!^e@06A0e&y';
mysql> FLUSH PRIVILEGES;

# 导出数据到从库, 这里如果输入密码需要用引号引起来, 否则可能会出现特殊字符
$ mysqldump canal_example -uroot --add-drop-table | mysql -h 192.168.39.5 canal_example -uroot -p

# 取消锁
mysql> UNLOCK TABLES;
  1. 进行同步
mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.1.101',
    MASTER_USER='docker-canal',
    MASTER_PASSWORD='CVsVQWF!^e@06A0e&y',
    MASTER_AUTO_POSITION = 1;

mix 模式执行生成的 SQL
image.png

  1. 更改 row 模式
#查看指定binlog文件的内容
mysql> show binlog events in 'mysql-bin.000013';

image.png

  1. 查看结果
# 主库
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 |     1166 |              |                  | 670bc9f4-684d-11ea-881e-740cde387cc4:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)


# salve 从库
mysql > show slave status

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: docker-canal
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 1166
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 1379
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: canal_example.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1166
              Relay_Log_Space: 4489
              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: 670bc9f4-684d-11ea-881e-740cde387cc4
             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: 670bc9f4-684d-11ea-881e-740cde387cc4:1-8
            Executed_Gtid_Set: 670bc9f4-684d-11ea-881e-740cde387cc4:1-8
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: