MySQL-自带工具介绍

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

目录

mysql

mysqladmin

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

  MySQL专栏MySQL专栏地址

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

        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”参数给我们提供了自定义提示信息的办法可以通过配置显示登入的主机地址登录用户名当前时间当前数据库schemaMySQLServer 的一些信息等等。我个人强烈建议将登录主机名登录用户名和所在的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

注2mysql1的防火墙要允许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 ];then
        echo "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的系统数据库

1INFORMATION_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=’表名’ 。

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

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

4MySQL5.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查询请求。测试引擎分别是myisaminnodb。

        测试结果说明 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.
Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 1.947 seconds
    Minimum number of seconds to run all queries: 1.947 seconds
    Maximum number of seconds to run all queries: 1.947 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: 2.171 seconds
    Minimum number of seconds to run all queries: 2.171 seconds
    Maximum number of seconds to run all queries: 2.171 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: 1.777 seconds
    Minimum number of seconds to run all queries: 1.777 seconds
    Maximum number of seconds to run all queries: 1.777 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: 2.042 seconds
    Minimum number of seconds to run all queries: 2.042 seconds
    Maximum number of seconds to run all queries: 2.042 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的负载。

 

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysql
返回列表

上一篇：内存泄露检测工具VLD(Visual Leak Detector)使用说明

下一篇：【Vue2+Element ui通用后台】面包屑和tag功能

“MySQL-自带工具介绍” 的相关文章

MySQL笔记01: MySQL入门_1.2 MySQL下载安装与配置1年前 (2023-02-02)
MySQL数据库技术知识点：数据的高效检索1年前 (2023-02-02)
MySQL基础——多表查询1年前 (2023-02-02)
修改mysql时区1年前 (2023-02-02)
MySQL性能优化浅析及线上案例1年前 (2023-02-02)
修改Mysql用户密码_mysql修改用户密码1年前 (2023-02-02)
MySQL学习之一条SQL更新语句的执行1年前 (2023-02-02)
【MySQL】为什么使用B+树做索引1年前 (2023-02-02)
vue+node+mysql全栈项目完整记录1年前 (2023-02-02)
【5】KubeSphere部署应用 | MySQL1年前 (2023-02-02)

阿里云国际版