MySQL迁移后续的校验
MySQL迁移后续的校验
以前搞的Oracle迁移后续步骤有个校验过程。
其中，由于权限信息过多，所以比对是在目标端创建一个可以连接到源端库的dblink，通过minus来比对是否有异常权限。
比如：
select * from dba_tab_privs@dzka where grantee in ('业务用户列表') minus select * from dba_tab_privs where grantee in ('业务用户列表'); select * from dba_tab_privs where grantee in ('业务用户列表') minus select * from dba_tab_privs@dzka where grantee in ('业务用户列表');
如果是低版本迁移到高版本可能存在dba_*视图定义有所不同，前期测试清楚后根据情况对SQL语句作更改即可。
最近搞MySQL迁移测试，也想像这搞一个。
需要解决两个问题：
1.如何实现MySQL下的DB Link达到连通两个库的目的
2.MySQL没有minus，如何实现结果比对
关于第一个问题，这里不做阐述，参考http://blog.itpub.net/31401187/viewspace-2649520/
第二点，可以通过left join实现同样的效果，我直接把结果放这里了。
创建server，
create server link_11_1 foreign data wrapper mysql options (user 'root',password 'root',host '172.30.11.1',database 'mysql'); (root@localhost 10:07:55) [(none)]> select * from mysql.servers; +-------------+-------------+-------+----------+----------+------+--------+---------+-------+ | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner | +-------------+-------------+-------+----------+----------+------+--------+---------+-------+ | link_11_1 | 172.30.11.1 | mysql | root | root | 0 | | mysql | | +-------------+-------------+-------+----------+----------+------+--------+---------+-------+ 1 row in set (0.00 sec) --后续删除：drop server link_11_1;
创建临时库放federated的远程表，并创建federated远程表，
下边的(...)是字段信息，可通过show create table mysql.user代入关键信息。
create database zkm; create table zkm.user(...) ENGINE=FEDERATED CONNECTION='link_11_1/user'; create table zkm.db(...) ENGINE=FEDERATED CONNECTION='link_11_1/db'; create table zkm.tables_priv(...) ENGINE=FEDERATED CONNECTION='link_11_1/tables_priv'; create table zkm.columns_priv(...) ENGINE=FEDERATED CONNECTION='link_11_1/columns_priv'; --后续删除drop database zkm;
权限信息比对，分为4块：
其中，用户'nacos','bak'是不需要迁移的业务用户，所以排掉。
全局权限：
select * from mysql.user a left join zkm.user b on (a.host=b.host and a.user=b.user) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null; select * from zkm.user a left join mysql.user b on (a.host=b.host and a.user=b.user) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null;
db权限：
select a.* from mysql.db a left join zkm.db b on (a.host=b.host and a.user=b.user and a.db=b.db) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null; select a.* from zkm.db a left join mysql.db b on (a.host=b.host and a.user=b.user and a.db=b.db) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null;
表权限（注意字段Timestamp）：
select a.* from mysql.tables_priv a left join zkm.tables_priv b on (a.host=b.host and a.user=b.user and a.db=b.db and a.Table_name=b.Table_name) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null; select a.* from zkm.tables_priv a left join mysql.tables_priv b on (a.host=b.host and a.user=b.user and a.db=b.db and a.Table_name=b.Table_name) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null;
列权限（注意字段Timestamp）：
select a.* from mysql.columns_priv a left join zkm.columns_priv b on (a.host=b.host and a.user=b.user and a.db=b.db and a.Table_name=b.Table_name and a.Column_name=b.Column_name) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null; select a.* from zkm.columns_priv a left join mysql.columns_priv b on (a.host=b.host and a.user=b.user and a.db=b.db and a.Table_name=b.Table_name and a.Column_name=b.Column_name) where a.user not in ('root','mysql.infoschema','mysql.session','mysql.sys','nacos','bak') and b.host is null;
对象比对的话比较麻烦，没有Oracle的一个视图dba_objects方便。
--存储过程和函数 select routine_schema,routine_type,count(*) from information_schema.routines WHERE routine_schema != 'sys' group by routine_schema,routine_type order by routine_schema,routine_type; --表和视图 select TABLE_SCHEMA,table_type,count(*) from information_schema.tables where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys','nacos') group by TABLE_SCHEMA,table_type; --更加详细的视图信息 select * from information_schema.VIEWS where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys','nacos'); --索引 SELECT TABLE_SCHEMA,count(*) FROM INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys','nacos') group by TABLE_SCHEMA order by 1; --触发器 select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.`TRIGGERS` where TRIGGER_SCHEMA not in ('information_schema','performance_schema','mysql','sys','nacos'); --event定时任务 select EVENT_SCHEMA,count(*) from information_schema.events where EVENT_SCHEMA not in ('information_schema','performance_schema','mysql','sys','nacos') group by EVENT_SCHEMA;
--约束 select table_schema,count(*) from information_schema.table_constraints where table_schema not in ('information_schema','performance_schema','mysql','sys','nacos') group by table_schema; --外键 select constraint_schema,count(*) from information_schema.referential_constraints where constraint_schema not in ('information_schema','performance_schema','mysql','sys','nacos') group by constraint_schema;
以后遇到更复杂的情况在更新。
至此。
