为MySQL5.6加上5.7才有的sys库

MySQL5.7.7引入的sys库可以看作是由information_schema引出的一套视图，更便于获取一些性能数据。

下面我会记录如何为MySQL5.6加上sys库。

首先从https://github.com/mysql/mysql-sys上克隆项目到本地 假设存放在/tmp/mysql-sys下（注意文件权限为root，执行的可以是拥有invoke权限的用户）

[root@237_21 tmp]# git clone https://github.com/mysql/mysql-sys.git
Initialized empty Git repository in /tmp/mysql-sys/.git/
remote: Counting objects: 3009, done.
remote: Total 3009 (delta 0), reused 0 (delta 0), pack-reused 3009
Receiving objects: 100% (3009/3009), 1.17 MiB | 205 KiB/s, done.
Resolving deltas: 100% (1770/1770), done.
[root@237_21 tmp]# ll mysql-sys/
total 380
-rw-r--r-- 1 root root    782 Oct 30 11:12 after_setup.sql
-rw-r--r-- 1 root root    891 Oct 30 11:12 before_setup.sql
-rw-r--r-- 1 root root  17987 Oct 30 11:12 COPYING
drwxr-xr-x 2 root root   4096 Oct 30 11:12 functions
-rwxr-xr-x 1 root root   7842 Oct 30 11:12 generate_sql_file.sh
-rw-r--r-- 1 root root    707 Oct 30 11:12 LICENSE
drwxr-xr-x 3 root root   4096 Oct 30 11:12 mysql-test
-rw-r--r-- 1 root root  20596 Oct 30 11:12 NEWS.md
drwxr-xr-x 2 root root   4096 Oct 30 11:12 procedures
-rw-r--r-- 1 root root 270716 Oct 30 11:12 README.md
-rw-r--r-- 1 root root   7613 Oct 30 11:12 sys_56.sql
-rw-r--r-- 1 root root   8366 Oct 30 11:12 sys_57.sql
drwxr-xr-x 2 root root   4096 Oct 30 11:12 tables
drwxr-xr-x 2 root root   4096 Oct 30 11:12 templates
drwxr-xr-x 2 root root   4096 Oct 30 11:12 triggers
drwxr-xr-x 4 root root   4096 Oct 30 11:12 views

导入出错

[root@237_21 tmp]# mysql -uroot -p -S /tmp/mysql3306.sock < ./mysql-sys/sys_56.sql 
Enter password: 
ERROR at line 16: Failed to open file "./before_setup.sql", error: 2

cd /tmp/mysql-sys/
mysql -u root -p < ./sys_56.sql
Or if you would like to log in to the client, and install the 5.7 version: cd /tmp/mysql-sys/
mysql -u root -p
SOURCE ./sys_57.sql
Alternatively, you could just choose to load individual files based on your needs, but beware, certain objects have dependencies on other objects. You will need to ensure that these are also loaded.

Generating a single SQL file There is bash script within the root of the branch directory, called generate_sql_file.sh, that allows you to create a single SQL file from the branch. This includes substitution parameters for the MySQL user to use, and whether to include or exclude SET sql_log_bin commands from the scripts. This is particularly useful for installations such as Amazon RDS, which do not have the root@localhost user, or disallow setting sql_log_bin. When run, this outputs a file named such as sys_<sys_version>_<mysql_version_identifier>_inline.sql, i.e. sys_1.2.0_56_inline.sql is sys version 1.2.0, built for MySQL 5.6. Options v: The version of MySQL to build the sys schema for, either "56" or "57"
b: Whether to omit any lines that deal with sql_log_bin (useful for RDS)
u: The user to set as the owner of the objects (useful for RDS)
m: Whether to generate a mysql_install_db / mysqld --initialize formatted file
Examples Generate a MySQL 5.7 SQL file that uses the "mark"@"localhost" user: ./generate_sql_file.sh -v 57 -u ""mark"@"localhost""
Generate a MySQL 5.6 SQL file for RDS: ./generate_sql_file.sh -v 56 -b -u CURRENT_USER
Generate a MySQL 5.7 bootstrap file: ./generate_sql_file.sh -v 57 -m

参考文档：

https://github.com/mysql/mysql-sys

https://yq.aliyun.com/articles/36106

http://blog.csdn.net/zdy0_2004/article/details/50583875

阅读更多