一、数据库配置文件
1.数据库配置方式
1）预编译
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20
-DMYSQL_DATADIR=/application/mysql-5.7.20/data
-DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_EXTRA_CHARSETS=all
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITH_ZLIB=bundled
-DWITH_SSL=bundled
-DENABLED_LOCAL_INFILE=1
-DWITH_EMBEDDED_SERVER=1
-DENABLE_DOWNLOADS=1
-DWITH_DEBUG=0
2）配置文件
vim /etc/my.cnf
basedir=
datadir=
socket=
server_id=
log_err=
log_bin=
3）命令行
--skip-grant-tables
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my.cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err
2.数据库配置文件
1）配置文件
/etc/my.cnf
/etc/mysql/my.cnf
$basedir/my.cnf
~/.my.cnf
#defaults-extra-file （类似include）
2）配置文件读取顺序
1./etc/my.cnf 4
2./etc/mysql/my.cnf 3
3.$basedir/my.cnf 2
4.~/.my.cnf 1
3）配置文件优先级
1.~/my.cnf
2.$basedir/my.cnf
3./etc/mysql/my.cnf
4./etc/my.cnf
4）配置文件生效顺序测试
#配置~/.my.cnf
[root@db03 ~]# vim ~/.my.cnf
[mysqld]
server_id=1
#配置$basedir/my.cnf
[root@db03 ~]# vim /service/mysql/my.cnf
[mysqld]
server_id=2
#配置/etc/mysql/my.cnf
[root@db03 ~]# vim /etc/mysql/my.cnf
[mysqld]
server_id=3
#配置/etc/my.cnf
[root@db03 ~]# vim /etc/my.cnf
server_id=4
#重启数据库
[root@db03 ~]# /etc/init.d/mysqld start
#查看id测试
[root@db03 ~]# mysql -uroot -p123 -e 'show variables like "server_id"'
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 4 |
+---------------+-------+
3.配置生效顺序
#1.预编译制定socket文件
cmake . -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.6.46/tmp/mysql.sock
#2.配置文件制定
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
socket=/tmp/mysql.sock
#3.配置a.txt
[root@db02 ~]# vim a.txt
[mysqld]
socket=/opt/mysql.sock
#4.命令行
--socket=/tmp/commond.sock
#5.分别使用以下命令测试生效的配置
[root@db02 ~]# mysqld_safe --defaults-file=a.txt --socket=/tmp/commond.sock &
[root@db02 ~]# mysqld_safe --defaults-file=a.txt
[root@db02 ~]# mysqld_safe
#6.查看socket文件位置判断优先级
4.mysql配置优先级总结
#优先级从高到低
命令行 > --defaults-file > ~/my.cnf > $basedir/my.cnf > /etc/mysql/my.cnf > /etc/my.cnf > defaults-extra-file > 初始化 > 预编译
5.配置文件的作用
1）作用
1.影响客户端的连接
2.影响服务端启动
2）影响客户端的连接
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
user=root
password=123
#不需要重启，直接mysql就可以连接数据库
3）影响服务端启动
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
socket=/tmp/mysql.sock
server_id=1
#配置server_id，必须要重启，查看才生效
4）总结
1.客户端也受配置文件影响 [mysql] [client]
2.客户端配置以后可以不重启
3.服务端配置以后必须重启 [mysqld] [server]
4.企业一般配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
socket=/tmp/mysql.sock
server_id=1
[mysql]
socket=/tmp/mysql.sock
二、MySQL的命令
1.mysql连接后快捷键
? #帮助
c #终止当前命令
#重连数据库
d #修改命令结束符
e #写入文件，同时执行多条命令
G #垂直显示结果
q #退出数据库
g #命令结束
h #帮助
#取消写入文件
T #将执行过程与结果写入一个文件
p #打印一次命令
R #修改命令提示符
source (.) #读取sql文件
status (s) #查看数据库状态
system (!) #在数据库里执行命令行命令
use (u) #切换数据库
prompt (R) Change your mysql prompt.
nopager (
) Disable pager, print to stdout.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (W) Show warnings after every statement.
nowarning (w) Don't show warnings after every statement.
2.help命令
mysql> help
mysql> help contents
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show
3.客户端mysqladmin命令
1）修改密码
mysqladmin -uroot -p123 password 123456
2）关闭数据库
#一般多实例使用
mysqladmin -uroot -p123 -S /tmp/mysql.sock shutdown
3）建库
[root@db03 ~]# mysqladmin -uroot -p create zengdao
Enter password:
4）删库
[root@db03 ~]# mysqladmin -uroot -p drop zengdao
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'zengdao' database [y/N] y
Database "zengdao" dropped
5）查看数据库配置
[root@db03 ~]# mysqladmin -uroot -p variables | grep server_id
Enter password:
| server_id | 4 |
| server_id_bits | 32 |
6）确认数据库是否启动
[root@db03 ~]# mysqladmin -uroot -p123 ping
Warning: Using a password on the command line interface can be insecure.
mysqld is alive
7）查看数据库信息
[root@db03 ~]# mysqladmin -uroot -p123 status
Warning: Using a password on the command line interface can be insecure.
Uptime: 143 Threads: 1 Questions: 22 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.153
8）刷新授权表
#相当于在数据库中执行 flush privileges
[root@db03 ~]# mysqladmin -uroot -p123 reload
9）刷新binlog
#如果有主从，不要刷新binlog，如果没有主从，可以先刷新binlog再做主从
[root@db03 ~]# mysqladmin -uroot -p123 flush-log
SQL语句
一、sql语句的语义种类
DDL: 数据定义语言 Data Definition Language
DCL: 数据控制语言 Data Control Language
DML: 数据操作语言 Data Manipulate Language
DQL: 数据查询语言 Data Query Language
二、DDL: 数据定义语言 （create、drop）
1.create针对库的操作
1）语法
mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
2）创建库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create SCHEMA db2;
Query OK, 1 row affected (0.00 sec)
3）建库时提示已存在
mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
4）查看建库语句
mysql> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
5）创建数据库并指定字符集
mysql> create database db3 charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db3;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
#不指定校验规则默认就是 utf8_general_ci
mysql> create database db4 charset utf8;
Query OK, 1 row affected (0.00 sec)
6）删库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
7）修改库
mysql> show create database db2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database db2 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db2;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
2.create针对表的操作
1）语法
mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
2）建表
#1.进入一个库
mysql> use db2
Database changed
#2.查看当前所在库
mysql> select database();
+------------+
| database() |
+------------+
| db2 |
+------------+
1 row in set (0.00 sec)
#3.建表，建表最少有一列
mysql> create table tb1;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)
#4.查看表
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
3）数据类型
int 整数 -2^31 - 2^31-1 (-2147483648 - 2147483647)
tinyint 最小整数 -128 - 127 #年龄 0 - 255
varchar 字符类型（变长） #身份证
char 字符类型（定长）
enum 枚举类型 #给它固定选项，只能选则选择项中的值 性别
datetime 时间类型 年月日时分秒
4）数据类型测试
#int类型
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert tb1 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert tb1 values(11111111111);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values(2147483647);
Query OK, 1 row affected (0.00 sec)
mysql> insert tb1 values(2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values(-2147483648);
Query OK, 1 row affected (0.00 sec)
mysql> insert tb1 values(-2147483649);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
#enum类型
mysql> create table qiudao(id int,sex enum('nan','nv'));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into qiudao values(1,'nan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into qiudao values(1,'qiudao');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
5）建表测试
表名：student
id
name
age
gender
cometime
#1.建表
mysql> create table student(
-> id int,
-> name varchar(12),
-> age tinyint,
-> gender enum('M','F'),
-> cometime datetime);
Query OK, 0 rows affected (0.01 sec)
#2.插入数据
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.01 sec)
#3.查看数据
mysql> select * from student;
+------+--------+------+--------+---------------------+
| id | name | age | gender | cometime |
+------+--------+------+--------+---------------------+
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:04 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:08 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:09 |
| 1 | 邱导 | -18 | M | 2020-07-14 19:34:10 |
+------+--------+------+--------+---------------------+
4 rows in set (0.00 sec)
6）建表数据属性
not null： #非空
primary key： #主键（唯一且非空的）
auto_increment： #自增（此列必须是：primary key或者unique key）
unique key： #唯一键，单独的唯一的
default： #默认值
unsigned： #非负数
comment： #注释
7）加上属性建表
#1.建表语句
create table students(
id int primary key auto_increment comment "学生id",
name varchar(12) not null comment "学生姓名",
age tinyint unsigned not null comment "学生年龄",
gender enum('M','F') default 'M' comment "学生性别",
cometime datetime default now() comment "入学时间");
#2.查看建表语句
mysql> show create table students;
| students | CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(12) NOT NULL COMMENT '学生姓名',
`age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
`gender` enum('M','F') DEFAULT 'M' COMMENT '学生性别',
`cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
#3.插入数据
mysql> insert into students values(1,'qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#因为主键相同无法插入
mysql> insert into students values(1,'qiudao',18,'M',now());
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#应该
mysql> insert into students values('2','qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#主键已经设置自增没必要自己插入
#正规插入数据的写法
mysql> insert students(name,age) values('lhd',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert students(name,age) values('lhd',12);
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+----+--------+-----+--------+---------------------+
| id | name | age | gender | cometime |
+----+--------+-----+--------+---------------------+
| 1 | qiudao | 18 | M | 2020-07-14 19:51:44 |
| 2 | qiudao | 18 | M | 2020-07-14 19:52:19 |
| 3 | lhd | 18 | M | 2020-07-14 19:53:50 |
| 4 | lhd | 12 | M | 2020-07-14 19:53:58 |
+----+--------+-----+--------+---------------------+
4 rows in set (0.00 sec)
3.删除表
drop table student;
4.修改表
#1.新建表
mysql> create table linux(daijiadong tinyint);
Query OK, 0 rows affected (0.04 sec
#2.修改表名
mysql> alter table linux rename linux9;
Query OK, 0 rows affected (0.01 sec)
#3.插入新字段
mysql> alter table linux9 add rengyufeng int;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#4.插入多个新字段
mysql> alter table linux9 add liukong int,add wangzhangxing int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#5.插入字段到最前面
mysql> alter table linux9 add kangpeiwen varchar(100) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| daijiadong | tinyint(4) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
#6.插入字段到指定字段后面
mysql> alter table linux9 add chenjianqing varchar(100) after daijiadong;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| daijiadong | tinyint(4) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
#7.删除指定列
mysql> alter table linux9 drop daijiadong;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| rengyufeng | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#8.修改字段
mysql> alter table linux9 change rengyufeng congtianqi int;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| congtianqi | int(11) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#9.修改字段属性
mysql> alter table linux9 modify congtianqi tinyint;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen | varchar(100) | YES | | NULL | |
| chenjianqing | varchar(100) | YES | | NULL | |
| congtianqi | tinyint(4) | YES | | NULL | |
| liukong | int(11) | YES | | NULL | |
| wangzhangxing | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
三、DCL: 数据控制语言 （grant、revoke）
1.grant授权
#1.授权语句
grant all on *.* to root@'172.16.1.%' identified by '123';
#2.查看用户权限
mysql> show grants for root@'localhost';
| Grants for root@localhost |
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
#3.扩展参数
max_queries_per_hour：一个用户每小时可发出的查询数量
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_queries_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_updates_per_hour：一个用户每小时可发出的更新数量
mysql> grant all on *.* to root@'172.16.1.%' identified by '123' with max_updates_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
mysql> grant all on *.* to lhd@'172.16.1.%' identified by '123' with max_connections_per_hour 2;
Query OK, 0 rows affected (0.00 sec)
max_user_connetions：允许同时连接数量
mysql> grant all on *.* to lhd@'172.16.1.%' identified by '123' with max_user_connections 1;
Query OK, 0 rows affected (0.00 sec)
2.revoke回收权限
mysql> revoke drop on *.* from lhd@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for lhd@'172.16.1.%';
| Grants for lhd@172.16.1.%
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'lhd'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH MAX_CONNECTIONS_PER_HOUR 2 MAX_USER_CONNECTIONS 1
#所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DROP, GRANT
3.授权超级管理员
grant all on *.* to root@'172.16.1.%' identified by '123' with grant option;