MYSQL主从复制的坑

海鲨数据库架构师 2022-12-05

142

上周开发同学发布个DDL操作,没有USE! 导致从库没有同步操作,结果新插入的数据库无法在从库里执行,报字节错误主库是256字节,从库是120字节.

主要是

ALTER TABLE LABS.OPERTAR change column login_name LOGIN_NAME VARCHAR(64);
复制

发现参数没有起作用

为此周末验证下

mysql> use sharkdb
Database changed
mysql> CREATE TABLE `big_table` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` char(11) DEFAULT NULL,
  `passwd` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` tinyint DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `updated_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;








mysql> exit


复制

主库修改字段大小

[root@centos7-mysql8-master ~]# sh login_mysql.sh 
mysql> alter table sharkdb.big_table change column name  name varchar(30) DEFAULT null ;
Query OK, 0 rows affected (5.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc sharkdb.big_table;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| uid          | int         | NO   | PRI | NULL    | auto_increment |
| mobile       | char(11)    | YES  |     | NULL    |                |
| passwd       | varchar(50) | YES  |     | NULL    |                |
| name         | varchar(30) | YES  |     | NULL    |                |
| sex          | tinyint     | YES  |     | NULL    |                |
| birthday     | datetime    | YES  |     | NULL    |                |
| updated_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
 
复制

从库查看是否被更改了?

#从库查看 
mysql> show tables;
+-------------------+
| Tables_in_sharkdb |
+-------------------+
| big_table         |
+-------------------+
1 row in set (0.01 sec)


mysql> desc big_table;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| uid          | int         | NO   | PRI | NULL    | auto_increment |
| mobile       | char(11)    | YES  |     | NULL    |                |
| passwd       | varchar(50) | YES  |     | NULL    |                |
| name         | varchar(50) | YES  |     | NULL    |                |
| sex          | tinyint     | YES  |     | NULL    |                |
| birthday     | datetime    | YES  |     | NULL    |                |
| updated_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)


复制

显然还是50!

从库添加参数:

replicate_wild_do_table = sharkdb.%



[root@CENOT7-MYSQL8-SALVE ~]# service mysqld restart
Redirecting to bin/systemctl restart  mysqld.service
[root@CENOT7-MYSQL8-SALVE ~]# sh login_mysql 
mysql> use sharkdb
Database changed
mysql> desc big_table;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| uid          | int         | NO   | PRI | NULL    | auto_increment |
| mobile       | char(11)    | YES  |     | NULL    |                |
| passwd       | varchar(50) | YES  |     | NULL    |                |
| name         | varchar(50) | YES  |     | NULL    |                |
| sex          | tinyint     | YES  |     | NULL    |                |
| birthday     | datetime    | YES  |     | NULL    |                |
| updated_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (0.02 sec)


mysql> start slave;
Query OK, 0 rows affected, 1 warning (2.73 sec)


mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.2.31
                  Master_User: Repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master_bin.000010
          Read_Master_Log_Pos: 1411
               Relay_Log_File: RelayLog.000004
                Relay_Log_Pos: 423
        Relay_Master_Log_File: Master_bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sharkdb
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: sharkdb.%


复制

#主库继续DDL操作

mysql> alter table sharkdb.big_table change column name  name varchar(30) DEFAULT null ;
Query OK, 0 rows affected (5.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制

#从库查看没有效果

mysql> use sharkdb
Database changed
mysql> desc big_table;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| uid          | int         | NO   | PRI | NULL    | auto_increment |
| mobile       | char(11)    | YES  |     | NULL    |                |
| passwd       | varchar(50) | YES  |     | NULL    |                |
| name         | varchar(50) | YES  |     | NULL    |                |
| sex          | tinyint     | YES  |     | NULL    |                |
| birthday     | datetime    | YES  |     | NULL    |                |
| updated_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
复制

#屏蔽从库参数

#replicate_do_db=sharkdb

replicate_wild_do_table = sharkdb.%

#主库继续DDL操作

mysql> alter table sharkdb.big_table change column name  name varchar(30) DEFAULT null ;
Query OK, 0 rows affected (5.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制

#从库有效果了

mysql> desc big_table;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| uid          | int         | NO   | PRI | NULL    | auto_increment |
| mobile       | char(11)    | YES  |     | NULL    |                |
| passwd       | varchar(50) | YES  |     | NULL    |                |
| name         | varchar(30) | YES  |     | NULL    |                |
| sex          | tinyint     | YES  |     | NULL    |                |
| birthday     | datetime    | YES  |     | NULL    |                |
| updated_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (10.54 sec)


复制

说明这两个参数只能二选一, 两个参数都设置的话
replicate_do_db=sharkdb 生效

replicate_wild_do_table = sharkdb.% 无效

如果遇到这情况咋办?
其实这个没有USE到目标库执行的情况,蛮多的!
没啥就是直接在从库再执行下DDL操作

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.01 sec)
复制