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