记一次mysql的问题处理@20181225
2024-10-08 09:25:39
需求：由于某种原因，导致一次分库分表的环境中ddl添加字段和索引没有完全成功，比如100个分库，只有部分修改成功，需要将没有修改成功的库和表找出来，在手动去执行。
由于线上环境，这里模拟还原一下该问题的情景。就是有4个database,每个database有2张表，只有部分表上面ddl添加字段成功。
root@lxd-vm1/[(none)] ::>create database db1;
Query OK, row affected (0.02 sec) root@lxd-vm1/[(none)] ::>create database db2;
Query OK, row affected (0.00 sec) root@lxd-vm1/[(none)] ::>create database db3;
Query OK, row affected (0.00 sec) root@lxd-vm1/[(none)] ::>create database db4;
Query OK, row affected (0.00 sec) root@lxd-vm1/[(none)] ::>use db1;
Database changed
root@lxd-vm1/[db1] ::>create table t1(id int);
Query OK, rows affected (0.03 sec) root@lxd-vm1/[db1] ::>create table t2(id int);
Query OK, rows affected (0.03 sec) root@lxd-vm1/[db1] ::>use db2;
Database changed
root@lxd-vm1/[db2] ::>create table t3(id int);
Query OK, rows affected (0.02 sec) root@lxd-vm1/[db2] ::>create table t4(id int);
Query OK, rows affected (0.03 sec) root@lxd-vm1/[db2] ::>use db3;
Database changed
root@lxd-vm1/[db3] ::>create table t5(id int);
Query OK, rows affected (0.03 sec) root@lxd-vm1/[db3] ::>create table t6(id int);
Query OK, rows affected (0.05 sec) root@lxd-vm1/[db3] ::>use db4;
Database changed
root@lxd-vm1/[db4] ::>create table t7(id int);
Query OK, rows affected (0.04 sec) root@lxd-vm1/[db4] ::>create table t8(id int);
Query OK, rows affected (0.02 sec) root@lxd-vm1/[db4] ::>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] ::>alter table t5 add column name varchar();
Query OK, rows affected (0.06 sec)
Records: Duplicates: Warnings: root@lxd-vm1/[db3] ::>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] ::>alter table t2 add column name varchar();
Query OK, rows affected (0.04 sec)
Records: Duplicates: Warnings: root@lxd-vm1/[db1] ::>
信息确认，只有db1.t2和db3.t5的表上面ddl修改成功了
root@lxd-vm1/[(none)] ::>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 |
+--------------+------------+
rows in set (0.00 sec)
问题处理脚本
/opt/mysql5720/bin/mysql -uroot -proot -h5.5.5. -P3306 >/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. -P3306 >/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 ]];then
echo $line >> no.txt
fi
done cat no.txt | while read line
do /opt/mysql5720/bin/mysql -uroot -proot -h5.5.5. -P3306 >/dev/null -Nse "alter table $line add column name varchar(20);"
if [[ $? -eq ]];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)] ::>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 |
+--------------+------------+
rows in set (0.00 sec)
最新文章
- 手机浏览器浏览WebApp弹出的键盘遮盖住文本框的解决办法
- WebRTC通信流程
- 轻松自动化---selenium-webdriver(python) (八)
- Linux upstart启动方式详解
- hibernate学习笔记--可选的配置属性
- 不是 EPUB ，而是 ePub
- linux自动交互工具expect，tcl安装和安装包，以及自动互信脚本
- Timus 1777. Anindilyakwa 奇怪的问题计数
- PyQt界面编程应用与实践
- Mac 下office 2013制作组合表
- SQL Server创建Job， 实现执行相同脚本而产生不同作业计划的探究
- [Swift]LeetCode671. 二叉树中第二小的节点 | Second Minimum Node In a Binary Tree
- mybatis10--自连接多对一查询
- centos7安装mysql客户端
- Handler使用中可能引发的内存泄漏
- sublime text3的快捷键
- 浅谈QT打印功能实现
- 自己写的一个小的剪刀——石头——布游戏的GUI程序
- vs的 Avalon 自动补全
- JavaScript的六种数据类型