mysql routines是什么_5.7 mysqldump routines

5.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的方式没有这个问题。

转载请注明源出处

QQ 273002188 欢迎一起学习

QQ 群 236941212

oracle,mysql,PG 相互交流