深圳市龙华区网站建设,企业对公账户查询系统,软件工程学费,网站制作 西安目录 #x1f341;mysql #x1f341;mysqladmin #x1f990;博客主页#xff1a;大虾好吃吗的博客 #x1f990;MySQL专栏#xff1a;MySQL专栏地址 MySQL数据库不仅提供了数据库的服务器端应用程序#xff0c;同时还提供了大量的客户端工具程序#xff0c;如mysqlmysql mysqladmin 博客主页大虾好吃吗的博客 MySQL专栏MySQL专栏地址 MySQL数据库不仅提供了数据库的服务器端应用程序同时还提供了大量的客户端工具程序如mysqlmysqladminmysqldump等等 Mysql命令是用的最多的一个命令工具了为用户提供一个命令行接口来操作管理MySQL 服务器。 运行‘mysql --help’ 就可以获取mysql帮助信息这里主要介绍运维过程中用到的选项-e 这个参数可以在linux终端就可以对mysql数据库操作这种方法多用于脚本的使用格式如下
[rootlocalhost ~]# mysql -uroot -p123 -e show databases #在终端查看有哪些库
mysql 例1 通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够
[rootlocalhost ~]# 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实验中不理会就可以了。
[rootlocalhost ~]# cat mysql1.sh
#!/bin/bash
HOSTNAME192.168.8.2
PORT3306
USERNAMEz3
PASSWORD123
DBNAMEtest_db
TABLENAMEtb1
#create database
create_db_sqlcreate database if not exists ${DBNAME}
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e ${create_db_sql}
#create table
create_table_sqlcreate 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_sqlinsert into ${TABLENAME} values (tom,1)
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e ${insert_sql}
#select data
select_sqlselect * from ${TABLENAME}
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e ${select_sql}
#update data
update_sqlupdate ${TABLENAME} set id3
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_sqldelete 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用户可以在指定的源登录
[rootmysql1 ~]# mysql -uroot -p123 -e grant all on test_db.* to z3192.168.8.% identified by 123
测试z3用户连接mysql服务器
[rootmysql ~]# mysql -uz3 -p123 -h 192.168.8.2
#省略部分内容
授予脚本执行权限
[rootmysql1 ~]# chmod x /root/mysql1.sh #给脚本执行权限
[rootmysql1 ~]# ./mysql1.sh #执行 如果在连接时候使用了“-E, --vertical”参数登入之后的所有查询结果都将以纵列显示效果和我们在一条查询之后以“\G”结尾一样。
[rootmysql1 ~]# 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 文件格式导出某些报表文件的时候是非常方便的。
[rootmysql1 ~]# mysql -X -uroot -p123 -e show databases
mysql: [Warning] Using a password on the command line interface can be insecure.
?xml version1.0?
resultset statementshow databasesxmlns:xsihttp://www.w3.org/2001/XMLSchema-instancerowfield nameDatabaseinformation_schema/field/row
rowfield nameDatabasemysql/field/row
rowfield nameDatabaseperformance_schema/field/row
rowfield nameDatabasesys/field/row
/resultset “--promptname”参数对于做运维的人来说是一个非常重要的参数选项其主要功能是定制自己的mysql提示符的显示内容。在默认情况下我们通过mysql登入到数据库之后mysql的提示符只是一个很简单的内容”mysql“没有其他任何附加信息。非常幸运的是mysql通过“--promptname”参数给我们提供了自定义提示信息的办法可以通过配置显示登入的主机地址登录用户名当前时间当前数据库schemaMySQLServer 的一些信息等等。我个人强烈建议将登录主机名登录用户名和所在的schema 这三项加入提示内容因为当大家手边管理的MySQL 越来越多操作越来越频繁的时候非常容易因为操作的时候没有太在意自己当前所处的环境而造成在错误的环境执行了错误的命令并造成严重后果的情况。如果我们在提示内容中加入了这几项之后至少可以更方便的提醒自己当前所处环境以尽量减少犯错误的概率。
个人建议提示符定义 \u\h : \d \r:\m:\s 显示效果
[rootmysql1 ~]# mysql -uroot -p123 --prompt\\u\\h : \\d \\r:\\m:\\s
#省略部分内容
rootlocalhost : (none) 10:52:19 提示符解释 \u 表示用户名, \h 表示主机名 \d 表示当前数据库\r小时12小时制\m分种\s秒\R The current time, in 24-hour military time (0–23) “--teename”参数也是对运维人员非常有用的参数选项用来告诉mysql将所有输入和输出内容都记录进文件。在我们一些较大维护变更的时候为了方便被查最好是将整个操作过程的所有输入和输出内容都保存下来。 假如mysql命令行状态下要进行大量的交互操作其实可以把这些操作记录在log中进行审计很简单
[rootmysql1 ~]# 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文件内。
[rootmysql1 ~]# 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
USERroot
PWD123
HOST192.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),ahour}
#查看数据库所有库大小
mysql -u${USER} -p${PWD} -h${HOST} information_schema -e select TABLE_SCHEMA as db,concat(round((sum(DATA_LENGTHINDEX_LENGTH))/1024/1024,2),M) as total from tables group by TABLE_SCHEMA 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的相关数据让我们更加容易的检索元数据。 mysqldump 这个工具其功能就是将MySQL Server中的数据以SQL 语句的形式从数据库中dump 成文本文件。mysqldump是做为MySQL 的一种逻辑备份工具 mysqlbinlog mysqlbinlog程序的主要功能就是分析MySQL Server 所产生的二进制日志也就是binlog。通过mysqlbinlog我们可以解析出binlog中指定时间段或者指定日志起始和结束位置的内容解析成SQL 语句。 Mysqlslap性能测试MySQL二种存储引擎 mysqlslap是mysql自带的基准测试工具 优点:查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较。mysqlslap为mysql性能优化前后提供了直观的验证依据,建议系统运维和DBA人员应该掌握一些常见的压力测试工具,才能准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题。 现在看一下这个压力测试工具mysqlslap关于他的选项手册上以及--help介绍的很详细。 这里解释一下一些常用的选项。 --concurrency代表并发数量多个可以用逗号隔开。例如concurrency50,100,200 --engines代表要测试的引擎可以有多个用分隔符隔开。
--iterations代表要运行这些测试多少次即运行多少次后得到结果。
--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的read,write,update,mixed
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。比如倒数第二个结果2200/100。
--debug-info 代表要额外输出CPU以及内存的相关信息注只有在MySQL用--with-debug编译时可。
--number-int-cols 代表测试表中的INTEGER类型的属性有几个。
--number-char-cols代表测试表的char类型字段的数量。
--create-schema 代表自己定义的模式在MySQL中也就是库即创建测试的数据库。
--query 代表自己的SQL脚本。
--only-print如果只想打印看看SQL语句是什么可以用这个选项。
--csvname 生产CSV格式数据文件 查看Mysql数据库默认最大连接数。 可以看到mysql5.7.13默认是151注不同版本默认最大连接数不差别。一般生产环境是不够的在my.cnf[mysqld]下添加 max_connections1024 增加到1024重启Mysql。 修改my.cnf文件并重启mysqld服务。
查看修改后的最大连接数
查看Mysql默认使用存储引擎如下查看 mysql show engines;
现在我们来看一下具体测试的例子 用自带的SQL脚本来测试:
[rootlocalhost ~]# mysqlslap --defaults-file/etc/my.cnf --concurrency100,200 --iterations1 --number-int-cols20 --number-char-cols30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-typemixed --enginemyisam,innodb --number-of-queries2000 -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格式数据文件并转换成图表形式
[rootlocalhost ~]# mysqlslap --defaults-file/etc/my.cnf --concurrency100,200 --iterations1 --number-int-cols20 --number-char-cols30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-typemixed --enginemyisam,innodb --number-of-queries2000 -uroot -p123 --csv/root/a.csv
执行结果
将a.csv拷贝到windows主机上打开并生成图表。 用我们自己定义的SQL 脚本或语句来测试 首先准备好要测试的数据库表这里我们编写一个生成表的脚本去完成 脚本内容如下
[rootmysql1 ~]# cat /root/mysql2.sh
#!/bin/bash
HOSTNAMElocalhost
PORT3306
USERNAMEroot
PASSWORD123
DBNAMEtest1
TABLENAMEtb1
#create database
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e drop database if exists ${DBNAME}
create_db_sqlcreate database if not exists ${DBNAME}
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e ${create_db_sql}
#create table
create_table_sqlcreate 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
i1
while [ $i -le 20000 ]
do
insert_sqlinsert 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_sqlselect count(*) from ${TABLENAME}
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e ${select_sql}
授权脚本x执行权限
[rootmysql1 ~]# chmod x /root/mysql2.sh
执行脚本mysql3.sh生成mysqlslap工具需要的测试表
[rootmysql1 ~]# /root/mysql2.sh
执行mysqlslap工具进行测试
[rootmysql1 ~]# mysqlslap --defaults-file/etc/my.cnf --concurrency10,20 --iterations1 --create-schematest1 --queryselect * from test1.tb1 --enginemyisam,innodb --number-of-queries2000 -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的负载。
