MySQL-自带工具介绍

目录

🍁mysql

🍁mysqladmin

---

  🦐博客主页：大虾好吃吗的博客

  🦐MySQL专栏：MySQL专栏地址

        MySQL数据库不仅提供了数据库的服务器端应用程序，同时还提供了大量的客户端工具程序，如mysql，mysqladmin，mysqldump等等

        Mysql命令是用的最多的一个命令工具了，为用户提供一个命令行接口来操作管理MySQL 服务器。

        运行‘mysql --help’ 就可以获取mysql帮助信息，这里主要介绍运维过程中用到的选项，-e 这个参数可以在linux终端就可以对mysql数据库操作，这种方法多用于脚本的使用，格式如下：

[root@localhost ~]# mysql -uroot -p123 -e "show databases"             #在终端查看有哪些库

mysql

        例1： 通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够

[root@localhost ~]# mysql -uroot -p123 -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     |
+-----------------------+-------+

例2：通过脚本创建数据库、表及对表进行增、改、删、查操作。 脚本内容如下：

mysql1:192.168.8.1

mysql2:192.168.8.2

        打开两台mysql服务器，IP分别为8.1和8.2，执行脚本使mysql1主机远程访问mysql2，对其进行增、删、改、查操作。需要注意的是，执行脚本前mysql2需要提前创建一个远程用户‘z3’、密码‘123’，因为密码在终端显示，所以会有Warning，实验中不理会就可以了。

[root@localhost ~]# cat mysql1.sh
#!/bin/bash
HOSTNAME="192.168.8.2"
PORT="3306"
USERNAME="z3"
PASSWORD="123"
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}"
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}"

使用mysql1主机创建授予z3用户可以在指定的源登录

[root@mysql1 ~]# mysql -uroot -p123 -e "grant all on test_db.* to z3@'192.168.8.%' identified by '123'"

测试z3用户连接mysql服务器

[root@mysql ~]# mysql -uz3 -p123 -h 192.168.8.2
#省略部分内容

授予脚本执行权限

[root@mysql1 ~]# chmod +x /root/mysql1.sh               #给脚本执行权限
[root@mysql1 ~]# ./mysql1.sh                            #执行

        如果在连接时候使用了“-E, --vertical”参数，登入之后的所有查询结果都将以纵列显示，效果和我们在一条查询之后以“\G”结尾一样。

[root@mysql1 ~]# mysql -uroot -p123 -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 3
Server version: 5.7.40 Source distribution
​
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
​
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
4 rows in set (0.01 sec)

        “-H, --html”与“-X, --xml”，在启用这两个参数之后，select出来的所有结果都会按照“Html”与“Xml”格式来输出，在有些场合之下，比如希望Xml或者Html 文件格式导出某些报表文件的时候，是非常方便的。

[root@mysql1 ~]# mysql -X -uroot -p123 -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
<?xml version="1.0"?>
​
<resultset statement="show databases
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><row><field name="Database">information_schema</field></row>
​<row><field name="Database">mysql</field></row>
​<row><field name="Database">performance_schema</field></row>
​<row><field name="Database">sys</field></row>
</resultset>

        “--prompt=name”参数对于做运维的人来说是一个非常重要的参数选项，其主要功能是定制自己的mysql提示符的显示内容。在默认情况下，我们通过mysql登入到数据库之后，mysql的提示符只是一个很简单的内容”mysql>“，没有其他任何附加信息。非常幸运的是mysql通过“--prompt=name”参数给我们提供了自定义提示信息的办法，可以通过配置显示登入的主机地址，登录用户名，当前时间，当前数据库schema，MySQLServer 的一些信息等等。我个人强烈建议将登录主机名，登录用户名和所在的schema 这三项加入提示内容，因为当大家手边管理的MySQL 越来越多，操作越来越频繁的时候，非常容易因为操作的时候没有太在意自己当前所处的环境而造成在错误的环境执行了错误的命令并造成严重后果的情况。如果我们在提示内容中加入了这几项之后，至少可以更方便的提醒自己当前所处环境，以尽量减少犯错误的概率。

个人建议提示符定义：

"\u@\h : \d \r:\m:\s> "

显示效果：

[root@mysql1 ~]# mysql -uroot -p123 --prompt="\\u@\\h : \\d \\r:\\m:\\s> "
#省略部分内容
​
root@localhost : (none) 10:52:19> 

        提示符解释： \u 表示用户名, \h 表示主机名， \d 表示当前数据库，\r小时（12小时制），\m分种，\s秒，\R The current time, in 24-hour military time (0–23)

        “--tee=name”参数也是对运维人员非常有用的参数选项，用来告诉mysql，将所有输入和输出内容都记录进文件。在我们一些较大维护变更的时候，为了方便被查，最好是将整个操作过程的所有输入和输出内容都保存下来。 假如mysql命令行状态下，要进行大量的交互操作，其实可以把这些操作记录在log中进行审计，很简单

[root@mysql1 ~]# mysql -uroot -p123 --tee=/opt/my.log

        也可以在服务器上的/etc/my.cnf中的[client]加入 tee =/tmp/client_mysql.log即可. 注：若没有[client]就添加即可 或者在mysql>提示符下执行下面的命令> tee /opt/my.log，当登录mysql执行操作时，都将把数据保存到my.log文件内。

[root@mysql1 ~]# cat /opt/my.log 
#省略部分内容
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
​
mysql> exit

mysqladmin

        mysqladmin Usage: mysqladmin [OPTIONS] commandcommand ... mysqadmin，顾名思义，提供的功能都是与MySQL 管理相关的各种功能。如MySQL Server状态检查，各种统计信息的flush，创建/删除数据库，关闭MySQL Server 等等。mysqladmin所能做的事情，虽然大部分都可以通过mysql连接登录上MySQL Server 之后来完成，但是大部分通过mysqladmin来完成操作会更简单更方便。这里将介绍一下经常使用到的几个常用功能： ping 命令可以很容易检测MySQL Server 是否还能正常提供服务。

mysql本机上测试：

在其他主机上测试mysql server是否正常提供服务

注1：地址192.168.8.1是mysql1的ip

注2：mysql1的防火墙要允许3306/tcp通信

注3：在mysql1上创建授权用户

        mysqladmin其他参数选项可以通过执行“mysqladmin --help”或man mysqladmin得到帮助信息。 编写一个简单的mysql监控脚本，内容如下：

#!/bin/bash
USER=root
PWD=123
HOST=192.168.8.1
​
#监测服务是否正常
mysqladmin -u${USER} -p${PWD} -h${HOST} ping 2>/dev/null
if [  $? -ne 0 ];thenecho "db down"
fi
​
#获取mysql当前状态值
mysqladmin -u${USER} -p${PWD} -h${HOST} status 2>/dev/null
​
#获取数据库当前连接信息
mysqladmin -u${USER} -p${PWD} -h${HOST} processlist 2>/dev/null
​
#获取数据库当前的连接数
mysql -u${USER} -p${PWD} -h${HOST} information_schema -BNe "select user,host,count(*) from processlist group by host,user"
​
#显示mysql的启动时长
mysql -u${USER} -p${PWD} -h${HOST} -NBe "show status like '%Uptime'" 2>/dev/null |awk '/^Uptime/{a=$NF/3600;print $(NF-1),a"hour"}'
​
#查看数据库所有库大小
mysql -u${USER} -p${PWD} -h${HOST} information_schema -e  "select TABLE_SCHEMA as db,concat(round((sum(DATA_LENGTH+INDEX_LENGTH))/1024/1024,2),'M') as total from tables group by TABLE_SCHEMA"

1. Mysql的系统数据库

1）INFORMATION_SCHEMA数据字典：此数据库存贮了其他所有数据库的信息（元数据）。元数据是关于数据的数据，如database name或table name，列的数据类型，或访问权限等。

        INFORMATION_SCHEMA库的主要系统表 TABLES表：提供了关于数据库中的表和视图的信息。（Table_schema字段代表 数据表所属的数据库名）

        SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库名'; COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。

        SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='数据库名‘' AND TABLE_NAME='表名' TABLE_CONSTRAINTS表：存储主键约束、外键约束、唯一约束、check约束。各字段的说明信息SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名' STATISTICS表：提供了关于表索引的信息。

        SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='数据库名’ AND TABLE_NAME=’表名’ 。

2）performance_schema性能字典，此数据库为数据库性能优化提供重要的参考信息。

3）MYSQL数据库: 该数据库也是个核心数据库，存储用户的权限信息与帮助信息。

4）MySQL5.7 提供了 sys系统数据库。 sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。sys系统数据库结合了information_schema和performance_schema的相关数据，让我们更加容易的检索元数据。

1. mysqldump： 这个工具其功能就是将MySQL Server中的数据以SQL 语句的形式从数据库中dump 成文本文件。mysqldump是做为MySQL 的一种逻辑备份工具

2. mysqlbinlog mysqlbinlog程序的主要功能就是分析MySQL Server 所产生的二进制日志（也就是binlog）。通过mysqlbinlog，我们可以解析出binlog中指定时间段或者指定日志起始和结束位置的内容解析成SQL 语句。

3. Mysqlslap性能测试MySQL二种存储引擎 mysqlslap是mysql自带的基准测试工具

        优点:查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较。mysqlslap为mysql性能优化前后提供了直观的验证依据,建议系统运维和DBA人员应该掌握一些常见的压力测试工具,才能准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题。

        现在看一下这个压力测试工具mysqlslap，关于他的选项手册上以及--help介绍的很详细。 这里解释一下一些常用的选项。

--concurrency代表并发数量，多个可以用逗号隔开。例如：concurrency=50,100,200 --engines代表要测试的引擎，可以有多个，用分隔符隔开。 
--iterations代表要运行这些测试多少次，即运行多少次后，得到结果。 
--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。 
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed） 
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。比如倒数第二个结果2=200/100。 
--debug-info 代表要额外输出CPU以及内存的相关信息（注：只有在MySQL用--with-debug编译时可）。 
--number-int-cols 代表测试表中的INTEGER类型的属性有几个。 
--number-char-cols代表测试表的char类型字段的数量。 
--create-schema 代表自己定义的模式（在MySQL中也就是库即创建测试的数据库）。 
--query 代表自己的SQL脚本。 
--only-print如果只想打印看看SQL语句是什么，可以用这个选项。 
--csv=name 生产CSV格式数据文件 查看Mysql数据库默认最大连接数。

        可以看到mysql5.7.13默认是151，注：不同版本默认最大连接数不差别。一般生产环境是不够的，在my.cnf[mysqld]下添加 max_connections=1024 增加到1024，重启Mysql。 修改my.cnf文件并重启mysqld服务。

查看修改后的最大连接数

查看Mysql默认使用存储引擎，如下查看： mysql> show engines;

现在我们来看一下具体测试的例子 用自带的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 -p123 --verbose

显示结果：

        测试说明：模拟测试两次读写并发，第一次100，第二次200，自动生成SQL脚本，测试表包含20个init字段，30个char字段，每次执行2000查询请求。测试引擎分别是myisam，innodb。

        测试结果说明： Myisam第一次100客户端同时发起增查用0.413/s,第二次200客户端同时发起增查用0.509/s Innodb第一次100客户端同时发起增查用0.692/s,第二次200客户端同时发起增查用0.617/s 由此可见MyISAM存储引擎处理性能是最好的，也是最常用的，但不支持事务。InonDB存储引擎提供了事务型数据引擎（ACID），在事务型引擎里使用最多的。具有事务回滚，系统修复等特点。

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 -p123 --csv=/root/a.csv

执行结果：

将a.csv拷贝到windows主机上，打开并生成图表。

        用我们自己定义的SQL 脚本或语句来测试 首先准备好要测试的数据库表，这里我们编写一个生成表的脚本去完成 脚本内容如下：

[root@mysql1 ~]# cat /root/mysql2.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123"
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}"

授权脚本x执行权限

[root@mysql1 ~]# chmod +x /root/mysql2.sh

执行脚本mysql3.sh生成mysqlslap工具需要的测试表

[root@mysql1 ~]# /root/mysql2.sh

执行mysqlslap工具进行测试

[root@mysql1 ~]# 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 -p123 –verbose

显示结果：

mysqlslap: [Warning] Using a password on the command line interface can be insecure.
BenchmarkRunning for engine myisamAverage number of seconds to run all queries: 1.947 secondsMinimum number of seconds to run all queries: 1.947 secondsMaximum number of seconds to run all queries: 1.947 secondsNumber of clients running queries: 10Average number of queries per client: 200
​
BenchmarkRunning for engine myisamAverage number of seconds to run all queries: 2.171 secondsMinimum number of seconds to run all queries: 2.171 secondsMaximum number of seconds to run all queries: 2.171 secondsNumber of clients running queries: 20Average number of queries per client: 100
​
BenchmarkRunning for engine innodbAverage number of seconds to run all queries: 1.777 secondsMinimum number of seconds to run all queries: 1.777 secondsMaximum number of seconds to run all queries: 1.777 secondsNumber of clients running queries: 10Average number of queries per client: 200
​
BenchmarkRunning for engine innodbAverage number of seconds to run all queries: 2.042 secondsMinimum number of seconds to run all queries: 2.042 secondsMaximum number of seconds to run all queries: 2.042 secondsNumber of clients running queries: 20Average number of queries per client: 100

        注：通过mysqlslap工具对mysql server进行压力测试，可以通过--concurrency、--number-of-queries等选项的值查看每次测试的结果，通过反复测试、优化得出mysql server的最大并发数。

        如果mysqlslap工具输出结果为Segmentation fault (core dumped)基本表示走超出mysql server的负载。