1、mysql命令
Mysql命令是用的最多的一个命令工具了,为用户提供一个命令行接口来操作管理MySQL 服务器,可以通过mysql --help
来查看其详细使用方法。
选项 | 作用 | 说明 |
---|---|---|
-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 | 将操作数据库所有输入和输出的内容都记录进文件中 | 在一些较大维护变更的时候,为了方便被查,可以将整个操作过程中的输出信息保存到某个文件中 |
这里主要介绍一些在运维过程中会用到的相关选项。
(1)-e、-u、-p、-h、-P等选项的使用语法
首先看看“-e, --execute=name”参数,这个参数是告诉mysql,我要执行“-e”后面的某个命令,而不是要通过mysql连接登录到MySQL Server 上面。此参数在我们写一些基本的MySQL 检查和监控的脚本中非常有用,运维mysql时经常在脚本中使用到它。
语法格式:
mysql -hhostname -Pport -uusername -ppassword -e 相关mysql的sql语句
示例1:
免登录执行sql语句
mysql -hlocalhost -P3306 -uroot -p mysql -e "select user,host from user;"
Enter password: ****
+---------------+-----------+
| user | host |
+---------------+-----------+
| bankMaster | % |
| bankMaster | 127.0.0.1 |
| epetadmin | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
示例2:
通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够
[root@192 ~]# mysql -uroot -p -e "show status like 'binlog_cache%'"
Enter password:
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
+-----------------------+-------+
示例3:
通过脚本创建数据库、表及对表进行增、改、删、查操作。 脚本内容如下:
# cat mysql1.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="1234"
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用户可以在指定的源登录
[root@192 ~]# mysql -uroot -p -e "grant all on test_db.* to test@'192.168.189.%' identified by '123456'"
Enter password:
[root@192 ~]# mysql -hlocalhost -P3306 -uroot -p mysql -e "select host,user from user"
Enter password:
+---------------+---------------+
| host | user |
+---------------+---------------+
| 192.168.189.% | test |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+---------------+---------------+
测试test用户连接mysql服务器
[root@192 ~]# mysql -utest -p -h192.168.189.129
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.7.30 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.
mysql>
(2)-E
如果在连接时候使用了“-E, --vertical”参数,登入之后的所有查询结果都将以纵列显示,效果和我们在一条 query 之后以“\G”结尾一样。
[root@192 ~]# mysql -utest -p -h192.168.189.129 -E
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.7.30 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.
mysql> show databases;
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: test_db
2 rows in set (0.00 sec)
[root@192 ~]# mysql -utest -p -X
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.30 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.
mysql> 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
mysql> select * from tb1;
<?xml version="1.0"?>
<resultset statement="select * from tb1;" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="name">tom</field>
<field name="id">1</field>
</row>
<row>
<field name="name">tom</field>
<field name="id">2</field>
</row>
<row>
<field name="name">tom</field>
<field name="id">3</field>
</row>
</resultset>
3 rows in set (0.01 sec)
(3)--prompt使用方法
“--prompt=name”参数对于做运维的人来说是一个非常重要的参数选项,其主要功能是定制自己的mysql提示 符的显示内容。
在默认情况下,我们通过mysql登入到数据库之后,mysql的提示符只是一个很简单的内容”mysql>“,没有其他任何附加信息。非常幸运的是mysql通过“--prompt=name”参数给我们提供了自定义提示信息的办法,可以通过配置显示登入的主机地址、登录用户名、当前时间、当前数据库schema,MySQL Server 的一些信息等等。
个人强烈建议将登录主机名、登录用户名和所在的schema 这三项加入提示内容,因为当大家手边管理的MySQL 越来越多,操作越来越频繁的时候,非常容易因为操作的时候没有太在意自己当前所处的环境而造成在错误的环境执行了错误的命令并造成严重后果的情况。如果我们在提示内容中加入了这几项之后,至少可以更方便的提醒自己当前所处环境,以尽量减少犯错误的概率。
个人强烈建议提示符定义:
"\\u@\\h : \\d \\r:\\m:\\s> "
提示符解释:
\u 表示用户名
\h 表示主机名,
\d 表示当前数据库(none表示没有在任何库中)
\r小时(12小时制)
\R小时(24小时制)
\m分种
\s秒
显示效果如下:
[root@192 ~]# mysql -utest -p --prompt="\\u@\\h: \\d \\r:\\m:\\s> "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.30 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.
test@localhost: (none) 04:52:32>
test@localhost: (none) 04:52:55> 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
test@localhost: test_db 04:54:38>
上述方式每次连接都要写那些字符进行定制,非常麻烦,可以将其写入配置文件中的clinet字段下,之后再登录就可以省略了。如下所示:
[root@192 opt]# vim /etc/my.cnf
[client]
prompt="\\u@\\h: \\d \\R:\\m:\\s> "
[root@192 opt]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.7.30 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) 17:07:27>
(4)--tee的使用方法
“--tee=name”参数也是对运维人员非常有用的参数选项,用来告诉mysql,将所有输入和输出内容都记录进文 件。在我们一些较大维护变更的时候,为了方便被查,最好是将整个操作过程的所有输入和输出内容都保存下 来。
假如mysql命令行状态下,要进行大量的交互操作,其实可以把这些操作记录在log中进行审计,很简单 mysql -u root -p --tee=/path/xxxx.log
也可以在服务器上的/etc/my.cnf中的[client]加入 tee =/tmp/client_mysql.log即可.。
注:若没有[client]就添加即可,或者在mysql>提示符下执行下面的命令
test@localhost: test_db 04:54:38> tee /opt/tmp.log
Logging to file '/opt/tmp.log'
test@localhost: test_db 04:59:27> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test_db |
+--------------------+
2 rows in set (0.01 sec)
test@localhost: test_db 04:59:54>
查看日志,内容如下:
test@localhost: test_db 04:54:38> tee /opt/tmp.log
Logging to file '/opt/tmp.log'
test@localhost: test_db 04:59:27> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test_db |
+--------------------+
2 rows in set (0.01 sec)
test@localhost: test_db 04:59:54>
2、mysqladmin命令
mysqladmin,顾名思义,提供的功能都是与MySQL 管理相关的各种功能。如MySQL Server状态检查,各种统计信息的flush,创建/删除数据库,关闭MySQL Server 等等。mysqladmin所能做的事情,虽然大部分都可以通过mysql连接登录上MySQL Server 之后来完成,但是大部分通过mysqladmin来完成操作会更简单更方便。
mysqladmin后面可以接选项,也可以接命令,这里就不说选项了,主要说一下命令。
(1)ping
监测服务是否正常
[root@192 ~]# mysqladmin -utest -p ping
Enter password:
mysqld is alive
[root@192 ~]#
(2)status
获取mysql当前状态值
[root@192 ~]# mysqladmin -utest -p status
Enter password:
Uptime: 173569 Threads: 3 Questions: 156 Slow queries: 0 Opens: 128 Flush tables: 1 Open tables: 121 Queries per second avg: 0.000
(3)processlist
获取数据库当前连接信息
[root@192 ~]# mysqladmin -utest -p processlist Enter password: +----+------+-----------+----+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+----------+------------------+ | 48 | test | localhost | | Query | 0 | starting | show processlist | +----+------+-----------+----+---------+------+----------+------------------+
(4)获取数据库当前的连接数
3、mysqldump
这个工具其功能就是将MySQL Server中的数据以SQL 语句的形式从数据库中dump 成文本文件。mysqldump是做为MySQL 的一种逻辑备份工具。
4、mysqlbinlog
mysqlbinlog程序的主要功能就是分析MySQL Server 所产生的二进制日志(也就是binlog)。 通过mysqlbinlog,我们可以解析出binlog中指定时间段或者指定日志起始和结束位置的内容解析成SQL 语句。
5、Mysqlslap性能测试
mysqlslap是mysql自带的基准测试工具,优点:查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较.mysqlslap为mysql性能优化前后提供了直观的验证依据,建议系统运维和DBA人员应该掌握一些常见的压力测试工具,才能准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题。
这里解释一下一些常用的选项。
--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格式数据文件
(1)查看Mysql数据库默认最大连接数
可以看到mysql5.7.13默认是151。注:不同版本默认最大连接数不差别。一般生产环境是不够的。
mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec)
(2)修改MySQL数据库默认最大连接数
方式一:
mysql> set GLOBAL max_connections = 1024; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1024 | +-----------------+-------+ 1 row in set (0.01 sec)
方式二:
在my.cnf[mysqld]下添加:
max_connections=1024
重启Mysql,查看修改后的最大连接数。
(3)查看Mysql默认使用的存储引擎
mysql> show 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)
(4)测试
现在我们来看一下具体测试的例子。
1)用自带的SQL脚本来测试
[root@192 opt]# 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 -p1234 --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.459 seconds Minimum number of seconds to run all queries: 1.459 seconds Maximum number of seconds to run all queries: 1.459 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: 1.420 seconds Minimum number of seconds to run all queries: 1.420 seconds Maximum number of seconds to run all queries: 1.420 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: 1.352 seconds Minimum number of seconds to run all queries: 1.352 seconds Maximum number of seconds to run all queries: 1.352 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: 2.330 seconds Minimum number of seconds to run all queries: 2.330 seconds Maximum number of seconds to run all queries: 2.330 seconds Number of clients running queries: 200 Average number of queries per client: 10
测试说明
模拟测试两次读写并发,第一次100,第二次200,自动生成SQL脚本,测试表包含20个init字段,30个char字段,每次执行2000查询请求。测试引擎分别是myisam,innodb。
测试结果说明
Myisam第一次100客户端同时发起增查用1.459/s,第二次200客户端同时发起增查用1.420/s Innodb第一次100客户端同时发起增查用1.352/s,第二次200客户端同时发起增查用2.330/s
测试结论
由此可见MyISAM存储引擎处理性能是最好的,也是最常用的,但不支持事务。 InonDB存储引擎提供了事务型数据引擎(ACID),在事务型引擎里使用最多的。具有事务回滚,系统修复等特点。
2)测试结果保存为csv文件
Mysqlslap测试工具生产CSV格式数据文件并转换成图表形式:
[root@192 opt]# 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 -p1234 --csv=/opt/mysql.csv mysqlslap: [Warning] Using a password on the command line interface can be insecure. [root@192 opt]# ll 总用量 8 -rw-r-----. 1 root root 152 6月 18 17:54 mysql.csv drwxr-xr-x. 2 root root 6 10月 31 2018 rh -rw-r--r--. 1 root root 508 6月 18 17:14 tmp.log
执行结果:
[root@192 opt]# cat mysql.csv myisam,mixed,0.952,0.952,0.952,100,20 myisam,mixed,0.745,0.745,0.745,200,10 innodb,mixed,0.953,0.953,0.953,100,20 innodb,mixed,1.945,1.945,1.945,200,10
3)使用自定义sql脚本测试
用我们自己定义的SQL 脚本或语句来测试 首先准备好要测试的数据库表,这里我们编写一个生成表的脚本去完 成脚本内容如下:
[root@192 opt]# vim mysql_test.sh #!/bin/bash HOSTNAME="localhost" PORT="3306" USERNAME="root" PASSWORD="1234" 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 2000 ] 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@192 opt]# chmod +x mysql_test.sh
执行脚本mysq_test.sh生成mysqlslap工具需要的测试表。
[root@192 opt]# mysql_test.sh 执行mysqlslap工具进行测试 [root@192 opt]# 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 -p1234 –verbose
显示结果:
[root@192 opt]# 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 -p1234 –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: 3.261 seconds Minimum number of seconds to run all queries: 3.261 seconds Maximum number of seconds to run all queries: 3.261 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: 3.010 seconds Minimum number of seconds to run all queries: 3.010 seconds Maximum number of seconds to run all queries: 3.010 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: 3.421 seconds Minimum number of seconds to run all queries: 3.421 seconds Maximum number of seconds to run all queries: 3.421 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: 3.252 seconds Minimum number of seconds to run all queries: 3.252 seconds Maximum number of seconds to run all queries: 3.252 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的负载。