mysql -- 自带命令及工具
MySQL数据库不仅提供了数据库的服务器端应用程序，同时还提供了大量的客户端工具程序，如mysql、mysqladmin、mysqldump等等
1. mysql命令
mysql命令是使用最多的一个命令工具了，为用户提供一个命令行接口来操作管理MySQL服务器。
| mysql命令选项 | 作用 | 说明 |
|---|---|---|
| -u | 指定连接数据库时使用的用户 | |
| -p | 指定用户的密码 | 可以-p后面直接写密码，也可以不写，进行交互式输入密码，推荐后者 |
| -h | 指定要登录的主机 | 可选，如果为空，则登录本机 |
| -P | 指定要连接的端口 | 可选，默认是3306 |
| -e | 可以通过-e命令直接执行SQL语句，而不用进入数据库 | 免交互登录数据库执行SQL语句，通常在脚本中使用 |
| -D | 指定要登录到哪个库 | 默认不会登录到库，可以省略此选项，直接写库名 |
| -E | 查询到的结果以行来显示 | 类似于每条SQL语句后面加“\G” |
| -f | 即使出现SQL错误，也强制继续 | 比如在不登陆数据库执行删除库的操作会有一个交互式的确认操作，可以使用此选项来避免交互式 |
| -X | 将查询到的数据导出位xml文件 | 导出的文件在windows系统中可以使用excel表格打开 |
| -H | 将查询到的数据导出位html文件 | 导出的文件在windows系统中可以使用浏览器打开 |
| --prompt | 定制自己的MySQL提示符显示的内容 | 默认登登录到MySQL后的提示符是“mysql >”，可以使用该选项定制提示符 |
| --tee | 将操作数据库所有输入和输出的内容都记录进文件中 | 在一些较大维护变更的时候，为了方便被查，可以将整个操作过程中的输出信息保存到某个文件中 |
语法格式：
$ mysql [options] db_name
例如：
$ mysql -e 'select user,host from user' mysql -uroot -pCom.123456
可以执行mysql --help来获取基本帮助信息
下面主要介绍一些在运维过程中会用到的相关选项：
-e参数：告诉MySQL执行-e后面的命令，而不是通过mysql连接登录到MySQL服务器。此参数在写一些基本的MySQL检查和监控脚本中非常有用
例1：
过binlog_cache_use以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够
$ mysql -uroot -p -e 'show status like "%binlog_cache%"'
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 349 |
| Binlog_cache_use | 406 |
+-----------------------+-------+
例2：
通过脚本创建数据库、表及对表进行增、删、改、查操作。
$ vim mysql1.sh
#!/bin/bash
HOSTNAME='127.0.0.1'
PORT='3306'
USERNAME='root'
PASSWORD='123456Abc!'
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}"
# 赋予脚本可行执行权限
$ chmod +x /root/mysql/script/mysql1.sh
# 执行脚本
$ ./mysql1.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.
创建用户lisi， 并授权
$ mysql -uroot -pCom.123456 -e "create user if not exists 'lisi'@'192.168.154.137' identified by 'Com.123456'"
mysql: [Warning] Using a password on the command line interface can be insecure.
$ mysql -uroot -pCom.123456 -e "grant all on test_db.* to 'lisi'@'192.168.154.137'"
mysql: [Warning] Using a password on the command line interface can be insecure.
$ mysql -uroot -pCom.123456 -e "show grants for'lisi'@'192.168.154.137'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------------------------------------------+
| \| Grants for lisi@192.168.154.137 | \| |
| ------------------------------------------------------------ | ---- |
| +-----------------------------------------------------------------+ | |
| \| GRANT USAGE ON *.* TO `lisi`@`192.168.154.137` | \| |
| GRANT ALL PRIVILEGES ON `test_db`.* TO `lisi`@`192.168.154.137` |
+-----------------------------------------------------------------+
# 测试lisi用户连接数据库
$ mysql -ulisi -pCom.123456 -h 192.168.154.137
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 20
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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>
-E,--vertical （[ˈvɜːrtɪkl] 垂直的）参数：使用此参数，登入后所有查询结果都将以纵列显示，效果和在sql语句后以‘\G’结尾一样
$ mysql -uroot -pCom.123456 -E -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Database: bank
*************************** 2. row ***************************
Database: employees
*************************** 3. row ***************************
Database: information_schema
*************************** 4. row ***************************
Database: mysql
*************************** 5. row ***************************
Database: performance_schema
*************************** 6. row ***************************
Database: sys
*************************** 7. row ***************************
Database: test_db
*************************** 8. row ***************************
Database: testdb
-H,--html参数：以HTML格式显示
-X,--xml参数：以xml格式显示
--prompt=name参数：（prompt [prɑːmpt] 促使）对运维人员来说是一个非常重要的参数选项。主要功能是定制自己的mysql提示符的显示内容，可以通过配置显示登入的主机地址、登录用户名、当前时间、当前数据库schema、MySQL Server的一些信息等。这样就可以时刻看到自己的所处环境，减少出错的几率，建议登录主机
名、登录用户名和所在的schema这3项必须加入提示信息
$ mysql -h 192.168.154.137 -ulisi -pCom.123456 --
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 40
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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.
lisi@192.168.154.137:(none)17:46:51>use test_db;
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
lisi@192.168.154.137:test_db17:47:05>
- \u 表示用户名
- \h 表示主机名
- \d 表示当前数据库
- \R 小时（24小时制）
- \r 小时（12小时制）
- \m 分钟
- \s 秒
--tee=name参数：也是对运维人员非常有用的参数选项，可以将所有操作记录到文件中，方便查询。
$ mysql -h 192.168.154.137 -ulisi -pCom.123456 --
prompt="\\u@\\h:\\d\\R:\\m:\\s>" --tee=/tmp/mysql.log
mysql: [Warning] Using a password on the command line interface can be insecure.
Logging to file '/tmp/mysql.log'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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.
lisi@192.168.154.137:(none)17:51:59>show databases;
+--------------------+
| \| Database | \| |
| ----------------------------------------------- | ---- |
| +--------------------+ \| information_schema \| | |
| \| test_db | \| |
+--------------------+
2 rows in set (0.00 sec)
lisi@192.168.154.137:(none)17:52:02>use test_db;
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
lisi@192.168.154.137:test_db17:52:07>quit
Bye
# 查询记录文件
$ cat /tmp/mysql.log
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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.
lisi@192.168.154.137:(none)17:51:59>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test_db |
+--------------------+
2 rows in set (0.00 sec)
lisi@192.168.154.137:(none)17:52:02>use test_db;
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
lisi@192.168.154.137:test_db17:52:07>quit
可以将其加入到配置文件中
$ vim /etc/my.cnf
[client]
tee=/tmp/mysql_client.log
也可以在mysql>提示符下输入
> tee /mysql_client.log
Logging to file '/mysql_client.log '
其它选项可以参照MySQL官方手册进行查询：https://dev.mysql.com/doc/refman/8.0/en/programsclient.html
2. mysqladmin
语法：
$ mysqladmin [options] command [command-options] [command [command-options]]
顾名思义，提供的功能都是与MySQL管理相关的各种功能。如MySQL Server状态检查，各种统计信息的flush，创建/删除数据库，关闭MySQL Server等等。mysqladmin所能做的事情，虽然大部分可以通过mysql连接登录上服务器后来完成，但是大部分通过mysqladmin来完成操作会更简单更方便。下面介绍一下经常使用到的几个功能：
2.1 ping命令
可以很容易检测MySQL Server是否还能正常提供服务。 （ 检查mysqld是否活着 ）
mysql本机上测试 ：
$ mysqladmin -u root -p -h localhost ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive
在其他主机上测试MySQL Server是否正常提供服务
$ mysqladmin -u lisi -pCom.123456 -h 192.168.154.137 ping
mysqladmin: [Warning] Using a password on the command line interface can be
insecure.
mysqld is alive
- 注1：地址192.168.154.137是MySQL Server的IP
- 注2：MySQL Server的防火墙要允许3306/tcp通信
- 注3：在MySQL Server上要创建授权用户
2.2 status
此命令可以获取当前MySQL Server的几个基本的状态值 。
$ mysqladmin -u lisi -pCom.123456 -h 192.168.154.137 status
mysqladmin: [Warning] Using a password on the command line interface can be
insecure.
Uptime: 2248 Threads: 2 Questions: 11 Slow queries: 0 Opens: 132 Flush
tables: 3 Open tables: 36 Queries per second avg: 0.004
- Uptime：是MySQL服务器运行的时间（秒）
- Threads：（[θredz]）活跃线程的数量，即开启的会话数
- Questions：服务器启动以来客户的问题（查询）数目，只要跟mysql做交互，不管查询表，还是查询服务器状态都记一次。
- Slow queries （[ˈkwɪriz]）：慢查询的数量
- Opens：MySQL已经打开的数据库表的数量
- Flush tables：MySQL已经执行的flush tables（刷新表，清除缓存）,refresh（ [rɪˈfreʃ] 刷新）（清洗所有表并关闭和打开日志文件）和reload（重载授权表）命令的数量
- Open tables：打开数据库的表的数量，以服务器启动开始
- Queries per second avg：select语句平均查询时间
2.3 processist
获取当前数据库的连接线程信息
监控MySQL进程运行状态
$ mysqladmin -u root -pCom.123456 processlist status
mysqladmin: [Warning] Using a password on the command line interface can be
insecure.
+----+-----------------+-----------------------+----+---------+------+----------
-------------------+------------------+
| \| Id \| User | \| Host \| Info | \| db \| Command \| Time \| State | \| |
| ------------- | ---------------- | --------------------------------- | ---- |
| | | | |
+----+-----------------+-----------------------+----+---------+------+----------
-------------------+------------------+
| 4 | event_scheduler | localhost | | Daemon | 3309 | Waiting
for next activation | |
| 17 | root | localhost | | Sleep | 80 |
| |
| 23 | lisi | 192.168.154.137:53832 | | Sleep | 6 |
| |
| 24 | root | localhost | | Query | 0 | starting
| show processlist |
+----+-----------------+-----------------------+----+---------+------+----------
-------------------+----------------
Uptime: 3312 Threads: 4 Questions: 30 Slow queries: 0 Opens: 132
tables: 3 Open tables: 36 Queries per second avg: 0.009
上面的这三个功能在一些简单监控脚本中经常使用到。
mysqladmin其他参数选项可以通过执行mysqladmin --help或man mysqladmin得到帮助信息。
编写一个简单的MySQL监控脚本，内容如下 ：
$ vim check_mysql.sh
#/bin/bash
USERNAME='root'
PASSWORD='Com.123456'
HOST='localhost'
#检测mysql server是否正常提供服务
mysqladmin -u${USERNAME} -p${PASSWORD} -h${HOST} ping
#获取mysql当前的几个状态值
mysqladmin -u${USERNAME} -p${PASSWORD} -h${HOST} status
#获取数据库当前的连接信息
mysqladmin -u${USERNAME} -p${PASSWORD} -h${HOST} processlist
#获取当前数据库的连接数
mysql -u${USERNAME} -p${PASSWORD} -h${HOST} -BNe "select host,count(host) from processlist group by host;" information_schema
#显示mysql的uptime
mysql -u${USERNAME} -p${PASSWORD} -h${HOST} -e "show status like '%uptime%'" | awk '/ptime/ {calc = $NF / 3600;print $(NF-1),calc"Hour"}'
# 查看数据库的大小
mysql -u${USERNAME} -p${PASSWORD} -h${HOST} -e "select
table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;"
3. mysqldump
这个工具功能就是将MySQL Server中的数据以SQL语句的形式从数据库中dump成文本文件。是做为MySQL的一种逻辑备份工具 。
4. mysqlbinlog
mysqlbinlog程序的主要功能就是分析MySQL Server所产生的二进制日志（也就是binlog）。通过mysqlbinlog，我们可以将binlog中指定时间段或者指定日志起始和结束位置内容解析成SQL语句