Linux数据库介绍——MYSQL自带工具介绍

MySQL数据库不仅提供了数据库的服务器端应用程序，同时还提供了大量的客户端工具程序，如mysql，mysqladmin，mysqldump等等1、mysql命令Mysql命令是用的最多的一个命令工具了，为用户提供一个命令行接口来操作管理MySQL 服务器。语法格式：

# mysql -e "select user,host from user" mysql

大家只要运行一下“mysql --help”就会得到如下相应的基本使用帮助信息：

这里主要介绍一些在运维过程中会用到的相关选项：首先看看“-e, --execute=name”参数，这个参数是告诉mysql，我要执行“-e”后面的某个命令，而不是要通过mysql连接登录到MySQL Server 上面。此参数在我们写一些基本的MySQL 检查和监控的脚本中非常有用，运维mysql时经常在脚本中使用到它。

#mysql -hhostname -Pport -uusername -ppassword -e 相关mysql的sql语句

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

# mysql -uroot -p -e "show status like 'binlog_cache%'"

Enter password:

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 0

 Binlog_cache_use | 0

+-----------------------+-------+

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

# cat mysql1.sh #!/bin/bash HOSTNAME="192.168.31.150" PORT="3306" USERNAME="test" PASSWORD="123456"

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}"

创建授予test用户可以在指定的源登录

# mysql -uroot -p -e "grant all on test_db.* to test@'192.168.31.%' identified by '123456'"

Enter password:

测试test用户连接mysql服务器

# mysql -utest -p123456 -h 192.168.31.150

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 9

Server version: 5.7.22 Source distribution

Copyright (c) 2000, 2018, 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

授予脚本执行权限

#chmod +x /root/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.

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

query 之后以“\G”结尾一样。

# 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 27

Server version: 5.7.22 Source distribution

Copyright (c) 2000, 2018, 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: mytest

*************************** 4. row *************************** Database: mytest_db

*************************** 5. row *************************** Database: performance_schema

*************************** 6. row *************************** Database: sys

*************************** 7. row *************************** Database: test

*************************** 8. row *************************** Database: test1

*************************** 9. row *************************** Database: test2

*************************** 10. row *************************** Database: test_db

10 rows in set (0.00 sec) 

mysql>

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

#mysql -X -u root -p

“--prompt=name”参数对于做运维的人来说是一个非常重要的参数选项，其主要功能是定制自己的mysql提示符的显示内容。在默认情况下，我们通过mysql登入到数据库之后，mysql的提示符只是一个很简单的内

容”mysql>“，没有其他任何附加信息。非常幸运的是mysql通过“--prompt=name”参数给我们提供了自定义提示信息的办法，可以通过配置显示登入的主机地址，登录用户名，当前时间，当前数据库schema，MySQL

Server 的一些信息等等。我个人强烈建议将登录主机名，登录用户名和所在的schema 这三项加入提示内容，因为当大家手边管理的MySQL 越来越多，操作越来越频繁的时候，非常容易因为操作的时候没有太在意自己当前所处的环境而造成在错误的环境执行了错误的命令并造成严重后果的情况。如果我们在提示内容中加入了这几项之后，至少可以更方便的提醒自己当前所处环境，以尽量减少犯错误的概率。个人强烈建议提示符定义：

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

显示效果：

# mysql -uroot -p123 --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 28

Server version: 5.7.22 Source distribution

Copyright (c) 2000, 2018, 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) 05:20:38> 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

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

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

也可以在服务器上的/etc/my.cnf中的[client]加入tee =/tmp/client_mysql.log即可. 注：若没有[client]就添加即可或者在mysql>提示符下执行下面的命令

> tee /tmp/mysql.log

Logging to file '/tmp/mysql.log' root@localhost:(none) 05:28:30> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| mytest |

| mytest_db |

| performance_schema |

| sys |

| test |

| test1 |

| test2 |

| test_db |

+--------------------+

10 rows in set (0.00 sec)

mysql其他参数选项可以通过MySQL 官方参考手册查阅，也可以通过执行“mysql --help”或man mysql得到帮助信息之后通过自行实验来做进一步的深刻认识。2、mysqladmin Usage: mysqladmin [OPTIONS] command command ... mysqadmin，顾名思义，提供的功能都是与MySQL 管理相关的各种功能。如MySQL Server状态检查，各种统计信息的flush，创建/删除数据库，关闭MySQL Server 等等。mysqladmin所能做的事情，虽然大部分都可以通过mysql连接登录上MySQL Server 之后来完成，但是大部分通过mysqladmin来完成操作会更简单更方便。这里将介绍一下经常使用到的几个常用功能：ping 命令可以很容易检测MySQL Server 是否还能正常提供服务mysql本机上测试：

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

注1：地址192.168.56.11是mysql server的ip 注2：mysql server的防火墙要允许3306/tcp通信注3：在mysql

server上创建授权用户

status 命令可以获取当前MySQL Server 的几个基本的状态值：

mysqladmin status命令结果有下述列Uptime:是mysql服务器运行的秒数。Threads:活跃线程的数量即开启的会话数。Questions：服务器启动以来客户的问题(查询)数目（只要跟mysql作交互，不管查询表，还是查询服务器状态都记一次）。Slow queries：是慢查询的数量。Opens：mysql已经打开的数据库表的数量Flush tables: mysql已经执行的flush tables，refresh和reload命令的数量。注：flush tables //刷新表（清除缓存）

reload 重载授权表 refresh 洗掉所有表并关闭和打开日志文件 open：打开数据库的表的数量，以服务器启动开始。 Queries per second avg：select语句平均查询时间 Memory in use分配的内存(只有在MySQL用--with-

debug编译时可用) Max memory used分配的最大内存(只有在MySQL用--with-debug编译时可用)

processlist获取当前数据库的连接线程信息：监控mysql进程运行状态：

上面的这三个功能在一些简单监控脚本中经常使用到的。mysqladmin其他参数选项可以通过执行“mysqladmin

--help”或man mysqladmin得到帮助信息。编写一个简单的mysql监控脚本，内容如下：

#!/bin/bash

#监测服务是否正常

mysqladmin -uroot -p123 -h localhost ping

#获取mysql当前状态值

mysqladmin -uroot -p123 -h localhost status

#获取数据库当前连接信息

mysqladmin -uroot -p123 -h localhost processlist

#获取数据库当前的连接数

mysql -uroot -p123 -BNe "select host,count(host) from processlist group by host" information_schema

#显示mysql的启动时长

mysql -uroot -p123 -e "SHOW STATUS LIKE '%uptime%'" | awk '/ptime/{ calc = $NF

/3600;print $(NF-1), calc"Hour"}'

#查看数据库所有库大小

mysql -uroot -p123 -e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema'