本篇内容主要讲解“怎么解决MySQL5.7的mysqldump routines问题”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“怎么解决MySQL5.7的mysqldump routines问题”吧!
 
场景:
1 新建一个5.7.12的实例
2 导出全库
mysqldump --all-databases -uroot -ptest --set-gtid-purged=off --single-transaction --quick -R --events > /tmp/a.sql
3 导出前检查:
mysql> select count(*) from mysql.proc;
+----------+
| count(*) |
+----------+
|       47 |
+----------+
1 row in set (0.00 sec)
 
mysql> select * from  host_summary;
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost |       1823 | 1.14 s            | 623.98 us             |         468 |     7000 | 714.18 ms       |                   1 |                 7 |            1 | 0 bytes        | 0 bytes                |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
1 row in set (0.04 sec)
 
4 全库导入:
 mysql -uroot -ptest < /tmp/a.sql
 
 5 mysql> select count(*) from mysql.proc;
+----------+
| count(*) |
+----------+
|       0 |
+----------+
1 row in set (0.00 sec)
 
mysql> select * from sys.host_summary;
ERROR 1356 (HY000): View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
 
5.5 5.6中是没有这个问题的
因为他们系统自带的没有proc.
 
两个方式避免:
1 不导出mysql.proc表
mysqldump --all-databases -uroot -ptest --set-gtid-purged=off --single-transaction --quick -R --events --ignore-table=mysql.proc > /tmp/a.sql
2 导入完成后upgrade
 
所以,无论是哪种备份方式,还原好之后最好都要执行下upgrade。
 
另:
使用innobackup的方式没有这个问题。
 
到此,相信大家对“怎么解决MySQL5.7的mysqldump routines问题”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

dawei

【声明】:乐山站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。