MySQL自带工具使用介绍
MySQL数据库不仅提供了数据库的服务器端应用程序，同时还提供了大量的客户端工具程序，如mysql、mysqladmin、mysqldump等。
一、mysql命令
为用户提供一个命令行接口来操作管理MySQL服务器。
1.语法格式
Usage: mysql [OPTIONS] [database]
2.参数选项
-e：这个参数后面可以跟SQL语句，不用通过mysql连接登录再执行。
示例
[root@mysql ~]# mysql -u root -pasd123 -e "select user,host from mysql.user"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
此参数在写一些基本的MySQL检查和监控的脚本中非常有用，运维mysql时经常在脚本中使用。
示例
通过binlog_cache_use以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够。
[root@mysql ~]# mysql -uroot -pasd123 -e "show status like 'binlog_cache%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
+-----------------------+-------+
示例
通过脚本创建数据库、表及对表进行增、改、删、查操作。
[root@mysql ~]# vim mysql.sh
#!/bin/bash
HOSTNAME="192.168.229.220"
PORT="3306"
USERNAME="test"
PASSWORD="asd123"
DBNAME="test_db"
TABLENAME="tb1"
#create database
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME} (name varchar(20),id int default 0)"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
insert_sql="insert into ${TABLENAME} values ('tom',1)"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
#select data
select_sql="select * from ${TABLENAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
#update data
update_sql="update ${TABLENAME} set id=3"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${update_sql}"
#select data
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
#delete data
delete_sql="delete from ${TABLENAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${delete_sql}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
创建授予test用户可以在指定的源登录
[root@mysql ~]# mysql -u root -pasd123 -e "grant all on *.* to test@'192.168.229.%' identified by 'asd123'"
mysql: [Warning] Using a password on the command line interface can be insecure.
测试test用户连接mysql服务器
[root@mysql ~]# mysql -utest -pasd123 -h 192.168.229.220
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.32 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit
Bye
授予脚本执行权限
[root@mysql ~]# chmod +x mysql.sh
执行脚本
[root@mysql ~]# . mysql.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| name | id |
+------+------+
| tom | 1 |
+------+------+
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| name | id |
+------+------+
| tom | 3 |
+------+------+
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
3.参数选项
-E：登入之后的所有查询结果都将以纵列显示，效果和在一条query之后以“\G”结尾一样。
示例
[root@mysql ~]# mysql -u root -pasd123 -E
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.32 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
*************************** 4. row ***************************
Database: sys
*************************** 5. row ***************************
Database: test_db
5 rows in set (0.00 sec)
4.参数选项
-H与-X：在启用这两个参数之后，select出来的所有结果都会按照“html”与“xml”格式来输出。
在有些场合下，比如希望xml或者html文件格式导出某些报表文件的时候，是非常方便的。
示例
[root@mysql ~]# mysql -u root -X -pasd123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.7.32 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host from mysql.user;
<?xml version="1.0"?>
<resultset statement="select user,host from mysql.user;" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="user">test</field>
<field name="host">192.168.229.%</field>
</row>
<row>
<field name="user">mysql.session</field>
<field name="host">localhost</field>
</row>
<row>
<field name="user">mysql.sys</field>
<field name="host">localhost</field>
</row>
<row>
<field name="user">root</field>
<field name="host">localhost</field>
</row>
</resultset>
4 rows in set (0.00 sec)
5.参数选项
--prompt=name：是一个非常重要的参数选项，主要功能是定制自己的mysql提示符的显示内容。
在默认情况下，通过mysql登入到数据库之后，mysql的提示符只是一个很简单的内容”mysql>“，没有其他任何附加信息。
此参数提供了自定义提示信息的办法，可以通过配置显示登入的主机地址，登录用户名，当前时间，当前数据库schema，MySQL Server的一些信息等。
建议将登录主机名，登录用户名和所在的schema这三项加入提示内容，因为管理的MySQL越来越多，操作越来越频繁的时候，非常容易因为操作的时候没有太在意自己当前所处的环境而造成在错误的环境执行了错误的命令并造成严重后果的情况。
建议提示符定义如下：
"\\u@\\h : \\d \\r:\\m:\\s> "
显示效果
[root@mysql ~]# mysql -u root -pasd123 --prompt="\\u@\\h : \\d \\r:\\m:\\s> "
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.32 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost : (none) 08:29:44> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@localhost : mysql 08:29:58>
提示符解释
\u:表示用户名
\h:表示主机名
\d:表示当前数据库
\r:小时(12小时制)
\m:分种
\s:秒
\R:The current time,in 24-hour military time (0–23)
注意如果不想每次输入都加上这么一长串，可以把这条写到配置文件中。但是需要写到配置文件中的client组中。
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/data/mysqld.err
socket=/tmp/mysql.sock
[client]
prompt="\\u@\\h : \\d \\r:\\m:\\s> "
[root@mysql ~]# service mysqld restart
[root@mysql ~]# mysql -u root -pasd123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost : (none) 11:20:50>
6.参数选项
--tee=name：此参数用来告诉mysql，将所有输入和输出内容都记录进文件。
在一些较大维护变更时，为了方便被查，最好是将整个操作过程的所有输入和输出内容都保存下来。
假如mysql命令行状态下，要进行大量的交互操作，其实可以把这些操作记录在log中进行审计，很简单。
mysql -u root -p --tee=/path/xxxx.log
也可以在服务器上的/etc/my.cnf中的[client]加入tee=/tmp/client_mysql.log即可。
注意：若没有[client]，添加即可或者在mysql>提示符下执行下面的命令。
root@localhost : (none) 08:37:31> tee /tmp/mysql.log;
Logging to file '/tmp/mysql.log'
root@localhost : (none) 08:37:34> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
5 rows in set (0.00 sec)
[root@mysql ~]# ls /tmp
mysql.log mysql.sock mysql.sock.lock
mysql其他参数选项可以通过MySQL官方参考手册查阅，也可以通过执行“mysql --help”或man mysql得到帮助信息，之后通过自行实验来做进一步的深刻认识。
二、mysqladmin
1.语法格式
Usage: mysqladmin [OPTIONS] command command....
提供的功能都是与MySQL管理相关的各种功能。如MySQL Server状态检查，各种统计信息的flush，创建/删除数据库，关闭MySQL Server等。
mysqladmin所能做的事情，虽然大部分都可以通过mysql连接登录上MySQL Server之后来完成，但是大部分通过mysqladmin来完成操作会更简单更方便。
2.几个常用功能
注意：如果主配文件中有client组内容，则不能使用mysqladmin命令。
[root@mysql ~]# mysqladmin ping -u root -pasd123
mysqladmin: [ERROR] unknown variable 'prompt=\u@\h : \d \r:\m:\s> '
(1)ping命令
可以很容易检测MySQL Server是否还能正常提供服务。
mysql本机上测试：
[root@mysql ~]# mysqladmin ping -u root -pasd123
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive
在其他主机上测试mysql server是否正常提供服务。
注1：地址192.168.229.220是mysql server的ip。
注2：mysql server的防火墙要允许3306/tcp通信。
注3：在mysql server上创建授权用户。
(2)status命令
可以获取当前MySQL Server的几个基本的状态值。
[root@mysql ~]# mysqladmin status -u root -pasd123
mysql: [Warning] Using a password on the command line interface can be insecure.
Uptime: 4008045 Threads: 1 Questions: 125 Slow queries: 0 Opens: 158 Flush tables: 1 Open tables: 151 Queries per second avg: 0.000
返回值介绍
Uptime：是mysql服务器运行的秒数。
Threads：活跃线程的数量即开启的会话数。
Questions：服务器启动以来客户的问题(查询)数目（只要跟mysql作交互，不管查询表，还是查询服务器状态都记一次）。
Slow queries：是慢查询的数量。
Opens：mysql已经打开的数据库表的数量
Flush tables: mysql已经执行的flush tables，refresh和reload命令的数量。
flush tables #刷新表（清除缓存）
reload #重载授权表
refresh #洗掉所有表并关闭和打开日志文件
open tables：打开数据库的表的数量，以服务器启动开始。
Queries per second avg：select语句平均查询时间Memory in use分配的内存(只有在MySQL用--with-debug编译时可用)，Max memory used分配的最大内存(只有在MySQL用--with-debug编译时可用)
(3)processlist
获取当前数据库的连接线程信息，监控mysql进程运行状态。
[root@mysql ~]# mysqladmin processlist -u root -pasd123
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----------+----+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+----------+------------------+
| 35 | root | localhost | | Query | 0 | starting | show processlist |
+----+------+-----------+----+---------+------+----------+------------------+
上面的这三个功能在一些简单监控脚本中经常使用到的。
mysqladmin其他参数选项可以通过执行“mysqladmin --help”或man mysqladmin得到帮助信息。
3.编写一个简单的mysql监控脚本
[root@mysql ~]# vim mysql_jk.sh
#!/bin/bash
#监测服务是否正常
mysqladmin -uroot -pasd123 -h localhost ping
#获取mysql当前状态值
mysqladmin -uroot -pasd123 -h localhost status
#获取数据库当前连接信息
mysqladmin -uroot -pasd123 -h localhost processlist
#获取数据库当前的连接数
mysql -uroot -pasd123 -BNe "select host,count(host) from processlist group by host" information_schema
#B：不使用历史文件，禁用交互行为。
#N：结果中不写列名。
#显示mysql的启动时长
mysql -uroot -pasd123 -e "SHOW STATUS LIKE '%uptime%'" | awk '/ptime/{ calc = $NF /3600;print $(NF-1), calc"Hour"}'
#查看数据库所有库大小
mysql -uroot -pasd123 -e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema'
三、Mysql的系统数据库
1.INFORMATION_SCHEMA数据字典
数据库的元数据存放在该库中，元数据是关于数据的数据，如database name或table name，列的数据类型，访问权限等。
包含了所有的数据库、表、索引，每个会话信息也在该库中记录。通过这个库可以进行一些数据资产统计，比如有多少个库、多少表、占用了多大的硬盘空间等。
主要系统表
(1)TABLES表
保存了所有表的数据字典信息，比如表名、表引擎、表大小、表行数等。
提供了关于数据库中的表和视图的信息(Table_schema字段代表数据表所属的数据库名)。
select * from information_schema.tables where table_schema='数据库名'
mysql> select * from information_schema.tables where table_schema='test'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: fruits
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 16
AVG_ROW_LENGTH: 1024
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2021-01-13 10:09:19
UPDATE_TIME: 2021-01-13 10:09:25
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.01 sec)
(2)COLUMNS表
提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。
select * from information_schema.columns where table_schema='数据库名' and table_name='表名';
mysql> select * from information_schema.columns where table_schema='test' and table_name='fruits'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: fruits
COLUMN_NAME: f_id
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 10
CHARACTER_OCTET_LENGTH: 30
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_general_ci
COLUMN_TYPE: char(10)
COLUMN_KEY: PRI
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: fruits
COLUMN_NAME: s_id
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 10
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: int(11)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: fruits
COLUMN_NAME: f_name
ORDINAL_POSITION: 3
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 255
CHARACTER_OCTET_LENGTH: 765
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_general_ci
COLUMN_TYPE: char(255)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: fruits
COLUMN_NAME: f_price
ORDINAL_POSITION: 4
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: decimal
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 8
NUMERIC_SCALE: 2
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: decimal(8,2)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
4 rows in set (0.00 sec)
(3)TABLE_CONSTRAINTS表
存储主键约束、外键约束、唯一约束、check约束各字段的说明信息。
select * from information_schema.table_constraints where table_schema='数据库名' and table_name='表名';
mysql> select * from information_schema.table_constraints where table_schema='test' and table_name='fruits'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: PRIMARY
TABLE_SCHEMA: test
TABLE_NAME: fruits
CONSTRAINT_TYPE: PRIMARY KEY
1 row in set (0.00 sec)
(4)STATISTICS表
提供了关于表索引的信息。
select * from information_schema.statistics where table_schema='数据库名' and table_name='表名';
mysql> select * from information_schema.statistics where table_schema='test' and table_name='f
ruits'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: fruits
NON_UNIQUE: 0
INDEX_SCHEMA: test
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
COLUMN_NAME: f_id
COLLATION: A
CARDINALITY: 16
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
1 row in set (0.01 sec)
(5)PROCESSLIST表
记录了会话详细信息，执行show processlist命令其实就是在查询该表。
mysql> select * from information_schema.processlist\G
*************************** 1. row ***************************
ID: 4
USER: root
HOST: localhost
DB: performance_schema
COMMAND: Query
TIME: 0
STATE: executing
INFO: select * from information_schema.processlist
1 row in set (0.00 sec)
(6)INNODB_TRX表
记录了所有事务，包括事务是否被锁。
mysql> select * from information_schema.innodb_trx\G
(7)INNODB_LOCK_WAITS
记录了事务正在等待的锁的信息。
mysql> select * from information_schema.innodb_lock_waits\G
2.performance_schema性能字典
用于收集数据库服务器性能数据，以便分析问题。比如哪个SQL执行次数最多、耗时最长、哪个SQL被锁等有用的信息。
3.MYSQL数据库
MySQL服务的核心数据库，主要存储了数据库用户、权限等信息，如果将慢日志或者通用日志调整成table形式也存在这个库里。
(1)user表
用户全局权限表，记录了每个用户权限。
mysql> select * from mysql.user where user='root'\G
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *204C8A3B79DC1533866049F6819865291C5E6AD5
password_expired: N
password_last_changed: 2021-01-07 09:37:33
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
(2)db表
库级别权限表。
mysql> select * from mysql.db\G
*************************** 1. row ***************************
Host: localhost
Db: performance_schema
User: mysql.session
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 2. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: Y
2 rows in set (0.00 sec)
(3)tables_priv
表级权限。
mysql> select * from mysql.tables_priv\G
*************************** 1. row ***************************
Host: localhost
Db: mysql
User: mysql.session
Table_name: user
Grantor: boot@connecting host
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv:
*************************** 2. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Table_name: sys_config
Grantor: root@localhost
Timestamp: 2021-01-07 09:37:30
Table_priv: Select
Column_priv:
2 rows in set (0.00 sec)
(4)columns_priv
列级别全新表
mysql> select * from mysql.columns_priv\G
(5)procs_priv
存储过程与函数权限
mysql> select * from mysql.procs_priv\G
(6)proxies_priv
代理用户的权限
mysql> select * from mysql.proxies_priv\G
*************************** 1. row ***************************
Host: localhost
User: root
Proxied_host:
Proxied_user:
With_grant: 1
Grantor: boot@connecting host
Timestamp: 0000-00-00 00:00:00
1 row in set (0.00 sec)
(7)event
事件与任务调度表
mysql> select * from mysql.event\G
(8)gtid_executed
与GTID主从复制有关的表
mysql> select * from mysql.gtid_executed\G
(9)innodb_index_stats
innodb索引统计信息
mysql> select * from mysql.innodb_index_stats\G
*************************** 1. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-01-07 09:37:30
stat_name: n_diff_pfx01
stat_value: 0
sample_size: 1
stat_description: source_uuid
*************************** 2. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-01-07 09:37:30
stat_name: n_diff_pfx02
stat_value: 0
sample_size: 1
stat_description: source_uuid,interval_start
*************************** 3. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-01-07 09:37:30
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 4. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-01-07 09:37:30
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
*************************** 5. row ***************************
database_name: sys
table_name: sys_config
index_name: PRIMARY
last_update: 2021-01-07 09:37:30
stat_name: n_diff_pfx01
stat_value: 6
sample_size: 1
stat_description: variable
*************************** 6. row ***************************
database_name: sys
table_name: sys_config
index_name: PRIMARY
last_update: 2021-01-07 09:37:30
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 7. row ***************************
database_name: sys
table_name: sys_config
index_name: PRIMARY
last_update: 2021-01-07 09:37:30
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
7 rows in set (0.00 sec)
(10)innodb_table_stats
innodb表统计信息
mysql> select * from mysql.innodb_table_stats\G
*************************** 1. row ***************************
database_name: mysql
table_name: gtid_executed
last_update: 2021-01-07 09:37:30
n_rows: 0
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 2. row ***************************
database_name: sys
table_name: sys_config
last_update: 2021-01-07 09:37:30
n_rows: 6
clustered_index_size: 1
sum_of_other_index_sizes: 0
2 rows in set (0.00 sec)
(11)plugin
插件表
mysql> select * from mysql.plugin\G
4.MySQL5.7提供了sys系统数据库
sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速了解系统的元数据信息。该库所有数据来自performance_schema，这些视图简化了对performance_schema的查询，便于更快速了解数据库运行情况。
sys系统数据库结合了information_schema和performance_schema的相关数据，让我们更加容易的检索元数据。
5.MySQL默认库应用
(1)统计MySQL每个库中表的个数。
mysql> select table_schema,count(table_name) from information_schema.tables group by table_schema;
+--------------------+-------------------+
| table_schema | count(table_name) |
+--------------------+-------------------+
| information_schema | 61 |
| mysql | 31 |
| performance_schema | 87 |
| sys | 101 |
+--------------------+-------------------+
4 rows in set (0.00 sec)
(2)统计MySQL每个库占用空间大小。
mysql> select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;
+--------------------+--------------------------------------------------+
| table_schema | round(sum(data_length+index_length)/1024/1024,4) |
+--------------------+--------------------------------------------------+
| information_schema | 0.1563 |
| mysql | 2.5207 |
| performance_schema | 0.0000 |
| sys | 0.0156 |
+--------------------+--------------------------------------------------+
4 rows in set (0.02 sec)
(3)找出MySQL中不是innodb的表。
mysql> select table_schema,table_name from information_schema.tables where engine !='innodb' and table_schema not in ('sys','performance_schema','information_schema','mysql');
Empty set (0.00 sec)
(4)统计MySQL中所有用户。
mysql> select concat(user,'@',host) from mysql.user;
+-------------------------+
| concat(user,'@',host) |
+-------------------------+
| mysql.session@localhost |
| mysql.sys@localhost |
| root@localhost |
+-------------------------+
3 rows in set (0.00 sec)
(5)查询当前MySQL中会话信息。
mysql> select * from information_schema.processlist;
+----+------+-----------+--------------------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+--------------------+---------+------+-----------+----------------------------------------------+
| 4 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.processlist |
+----+------+-----------+--------------------+---------+------+-----------+----------------------------------------------+
1 row in set (0.00 sec)
(6)批量杀死某个用户的进程。
mysql> select concat('KILL',id,';') from information_schema.processlist where user='test';
四、mysqldump
将MySQL Server中的数据以SQL语句的形式从数据库中dump成文本文件。mysqldump是做为MySQL的一种逻辑备份工具。
五、mysqlbinlog
主要功能就是分析MySQL Server所产生的二进制日志。
通过mysqlbinlog，可以解析出binlog中指定时间段或者指定日志起始和结束位置的内容解析成SQL语句。
六、mysqlslap性能测试MySQL两种存储引擎
mysqlslap是mysql自带的基准测试工具。
优点：查询数据，语法简单，灵活容易使用。
该工具可以模拟多个客户端同时并发的向服务器发出查询更新，给出了性能测试数据而且提供了多种引擎的性能比较。
mysqlslap为mysql性能优化前后提供了直观的验证依据，建议系统运维和DBA人员应该掌握一些常见的压力测试工具，才能准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题。
1.常用的选项
--concurrency：代表并发数量，多个可以用逗号隔开。
例如：concurrency=50,100,200
--engines：代表要测试的引擎，可以有多个，用分隔符隔开。
--iterations：代表要运行这些测试多少次，即运行多少次后得到结果。
--auto-generate-sql：代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type：代表要测试的是读还是写还是两者混合的。(read,write,update,mixed)。
--number-of-queries：代表总共要运行多少次查询。
每个客户运行的查询数量可以用查询总数/并发数来计算。
--debug-info：代表要额外输出CPU以及内存的相关信息
注：只有在MySQL用--with-debug编译时可。
--number-int-cols：代表测试表中的INTEGER类型的属性有几个。
--number-char-cols：代表测试表的char，varchar类型字段的数量。
--create-schema：代表自己定义的模式(在MySQL中也就是库即创建测试的数据库)。
--query：代表自己的SQL脚本。
--only-print：如果只想打印看看SQL语句是什么，可以用这个选项。
--csv=name：生产CSV格式数据文件。
2.查看Mysql数据库默认最大连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
可以看到mysql5.7.23默认是151，一般生产环境是不够的。
注意：不同版本默认最大连接数不差别。
在my.cnf中的[mysqld]下添加max_connections=1024增加到1024，重启Mysql。
3.修改my.cnf文件并重启mysqld服务
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/data/mysqld.err
socket=/tmp/mysql.sock
max_connections=1024
[root@localhost ~]# service mysqld restart
4.查看修改后的最大连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1024 |
+-----------------+-------+
1 row in set (0.00 sec)
5.查看Mysql默认使用存储引擎
mysql> show storage engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
6.具体测试示例，用mysql自带的SQL脚本来测试
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -pasd123 --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.352 seconds
Minimum number of seconds to run all queries: 0.352 seconds
Maximum number of seconds to run all queries: 0.352 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.378 seconds
Minimum number of seconds to run all queries: 0.378 seconds
Maximum number of seconds to run all queries: 0.378 seconds
Number of clients running queries: 200
Average number of queries per client: 10
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.840 seconds
Minimum number of seconds to run all queries: 0.840 seconds
Maximum number of seconds to run all queries: 0.840 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.854 seconds
Minimum number of seconds to run all queries: 0.854 seconds
Maximum number of seconds to run all queries: 0.854 seconds
Number of clients running queries: 200
Average number of queries per client: 10
(1)测试说明
模拟测试两次读写并发，第一次100，第二次200，自动生成SQL脚本，测试表包含20个int字段，30个char字段，每次执行2000查询请求。测试引擎分别是myisam，innodb。
(2)测试结果说明
myisam第一次100客户端同时发起增查用0.352/s，第二次200客户端同时发起增查用0.378/s。
innodb第一次100客户端同时发起增查用0.840/s，第二次200客户端同时发起增查用0.854/s。
(3)结论
MyISAM存储引擎处理性能是最好的，也是最常用的，但不支持事务。
InonDB存储引擎提供了事务型数据引擎(ACID)，在事务型引擎里使用最多的。具有事务回滚、系统修复等特点。
(4)mysqlslap测试工具生产CSV格式数据文件并转换成图表形式
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -pasd123 --csv=/root/a.csv
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
a.csv anaconda-ks.cfg
将a.csv拷贝到电脑主机上，打开并生成图表。
scp -rp root@192.168.229.220:/root/a.csv Desktop/
root@192.168.229.220's password:
a.csv 100% 152 297.5KB/s 00:00
用我们自己定义的SQL脚本或语句来测试。
7.自定义数据库测试
(1)首先准备好要测试的数据库表，然后编写一个生成表的脚本。
[root@localhost ~]# vim mysql.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="asd123"
DBNAME="test1"
TABLENAME="tb1"
#create database
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1) not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100) default null)"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
i=1
while [ $i -le 20000 ]
do
insert_sql="insert into ${TABLENAME} values
($i,'zhangsan','1','1234567890123456','1999-10-10','2016-9-3','zhongguo beijingshi changpinqu')"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
(2)授权脚本执行权限
[root@localhost ~]# chmod +x /root/mysql.sh
(3)执行脚本mysql.sh生成测试表
[root@localhost ~]# ./mysql.sh
...
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
| 20000 |
+----------+
(4)执行mysqlslap工具进行测试
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=10,20 --iterations=1 --create-schema='test1' --query='select * from test1.tb1' --engine=myisam,innodb --number-of-queries=2000 -uroot -pasd123 –verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 35.632 seconds
Minimum number of seconds to run all queries: 35.632 seconds
Maximum number of seconds to run all queries: 35.632 seconds
Number of clients running queries: 10
Average number of queries per client: 200
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 34.079 seconds
Minimum number of seconds to run all queries: 34.079 seconds
Maximum number of seconds to run all queries: 34.079 seconds
Number of clients running queries: 20
Average number of queries per client: 100
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 34.537 seconds
Minimum number of seconds to run all queries: 34.537 seconds
Maximum number of seconds to run all queries: 34.537 seconds
Number of clients running queries: 10
Average number of queries per client: 200
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 33.599 seconds
Minimum number of seconds to run all queries: 33.599 seconds
Maximum number of seconds to run all queries: 33.599 seconds
Number of clients running queries: 20
Average number of queries per client: 100
注意：通过mysqlslap工具对mysql server进行压力测试，可以通过–concurrency、–number-of-queries等选项的值查看每次测试的结果，通过反复测试、优化得出mysql server的最大并发数。如果mysqlslap工具输出结果为Segmentation fault(core dumped)基本表示走超出mysql server的负载。
扫一扫
3899
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
