记一次mysql的问题处理@20181225

需求：由于某种原因，导致一次分库分表的环境中ddl添加字段和索引没有完全成功，比如100个分库，只有部分修改成功，需要将没有修改成功的库和表找出来，在手动去执行。

由于线上环境，这里模拟还原一下该问题的情景。就是有4个database,每个database有2张表，只有部分表上面ddl添加字段成功。

root@lxd-vm1/[(none)] 15:43:18>create database db1;
Query OK, 1 row affected (0.02 sec)

root@lxd-vm1/[(none)] 15:43:37>create database db2;
Query OK, 1 row affected (0.00 sec)

root@lxd-vm1/[(none)] 15:43:39>create database db3;
Query OK, 1 row affected (0.00 sec)

root@lxd-vm1/[(none)] 15:43:41>create database db4;
Query OK, 1 row affected (0.00 sec)

root@lxd-vm1/[(none)] 15:43:43>use db1;
Database changed
root@lxd-vm1/[db1] 15:44:05>create table t1(id int);
Query OK, 0 rows affected (0.03 sec)

root@lxd-vm1/[db1] 15:44:14>create table t2(id int);
Query OK, 0 rows affected (0.03 sec)

root@lxd-vm1/[db1] 15:44:18>use db2;
Database changed
root@lxd-vm1/[db2] 15:44:21>create table t3(id int);
Query OK, 0 rows affected (0.02 sec)

root@lxd-vm1/[db2] 15:44:25>create table t4(id int);
Query OK, 0 rows affected (0.03 sec)

root@lxd-vm1/[db2] 15:44:29>use db3;
Database changed
root@lxd-vm1/[db3] 15:44:34>create table t5(id int);
Query OK, 0 rows affected (0.03 sec)

root@lxd-vm1/[db3] 15:44:40>create table t6(id int);
Query OK, 0 rows affected (0.05 sec)

root@lxd-vm1/[db3] 15:44:43>use db4;
Database changed
root@lxd-vm1/[db4] 15:44:46>create table t7(id int);
Query OK, 0 rows affected (0.04 sec)

root@lxd-vm1/[db4] 15:44:52>create table t8(id int);
Query OK, 0 rows affected (0.02 sec)

root@lxd-vm1/[db4] 15:44:56>use db3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@lxd-vm1/[db3] 15:45:08>alter table t5 add column name varchar(20);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@lxd-vm1/[db3] 15:45:21>use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@lxd-vm1/[db1] 15:45:34>alter table t2 add column name varchar(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@lxd-vm1/[db1] 15:45:43>

信息确认，只有db1.t2和db3.t5的表上面ddl修改成功了

root@lxd-vm1/[(none)] 15:53:30>select distinct table_schema,table_name from information_schema.columns  where column_name='name' and table_schema like 'db%' and table_name like 't%';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| db1          | t2         |
| db3          | t5         |
+--------------+------------+
2 rows in set (0.00 sec)

问题处理脚本

/opt/mysql5720/bin/mysql -uroot -proot -h5.5.5.101 -P3306 2>/dev/null -Nse "select distinct concat(table_schema,'.',table_name)
from information_schema.columns 
where column_name='name'
and table_schema like 'db%'
and table_name like 't%' order by concat(table_schema,'.',table_name) ;" > have.txt

/opt/mysql5720/bin/mysql -uroot -proot -h5.5.5.101 -P3306 2>/dev/null -Nse "select distinct concat(table_schema,'.',table_name)
from information_schema.columns 
where table_schema like 'db%'
and table_name like 't%' order by concat(table_schema,'.',table_name);" > all.txt


cat all.txt | while read line
do
    echo $line | grep $line have.txt > /dev/null
    if [[ $? -ne 0 ]];then
        echo $line >> no.txt
    fi
done

cat no.txt | while read line
do
    
    /opt/mysql5720/bin/mysql -uroot -proot -h5.5.5.101 -P3306 2>/dev/null -Nse "alter table $line add column name varchar(20);"
    if [[ $? -eq 0 ]];then
        echo "$line alter successed"
    fi
done
rm -rf all.txt
rm -rf have.txt
rm -rf no.txt

执行上面的脚本

[mysql@lxd-vm1@/home/mysql]$ sh t1.sh
db1.t1 alter successed
db2.t3 alter successed
db2.t4 alter successed
db3.t6 alter successed
db4.t7 alter successed
db4.t8 alter successed

验证，所有的库对应的表上都有需求中的字段：

root@lxd-vm1/[(none)] 15:53:31>select distinct table_schema,table_name from information_schema.columns  where column_name='name' and table_schema like 'db%' and table_name like 't%';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| db1          | t1         |
| db1          | t2         |
| db2          | t3         |
| db2          | t4         |
| db3          | t5         |
| db3          | t6         |
| db4          | t7         |
| db4          | t8         |
+--------------+------------+
8 rows in set (0.00 sec)