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 * 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 * 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;
表权限：
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;
列权限：
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');
以后遇到更复杂的情况在更新。
至此。
标签：MySQL,db,校验,host,user,mysql,迁移,where,select From： https://www.cnblogs.com/PiscesCanon/p/17151801.html