Mysql数据库的一些运维命令

常用操作

查询所有工作进程

show processlist;
或
show full processlist;

杀死某个工作进程

kill [process id];
如
kill 56;

查询锁表情况

• 通过执行show engine innodb status命令来查看是否存在锁表情况
• MySQL将事务和锁信息记录在了information_schema数据库中，我们只需要查询即可。涉及三张表 INNODB_LOCK_WAITS、INNODB_LOCKS、INNODB_TRX。

查询/设置锁等待时间

查询

show variables like '%innodb_lock_wait_timeout%';

设置

set global innodb_lock_wait_timeout = 60;

查询会话状态

show status;

#全局状态
show  global status;

查询全局增删改查数据统计 

variables_name=Com_开头的。分析到底是哪种事件执行的多, 如 可以通过该命令看出读多写少，可以做读写分离优化。

show global  status like 'Com_%';

查询缓冲池（Buffer Pool）大小

show variables like '%innodb_buffer_pool_size%';

设置缓冲池（Buffer Pool）大小

# 单位字节，类型number
set global innodb_buffer_pool_size=[size]

设置查询缓存

设置查询缓存空间

set global query_cache_size = 1048576;

# 查询缓存空间
show variables like '%query_cache_size%'

 

开启/关闭查询缓存

只能修改my.cnf并重启

[mysqld]

#大部分情况下关闭下面两项
query_cache_size = 0
# 0关闭 1和2开启
query_cache_type = 0

查询InnoDB引擎状态

show engine innodb status;

设置MySQL数据包长度最大值

# 查询数据包大小
show variables like '%max_allowed_packet%'

#设置数据包大小
set global max_allowed_packet = 4194304;

 

数据备份/恢复

mysqldump 备份数据

mysqldump命令是MySQL自带的备份工具

命令格式

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

参数名	含义	缩写
–host	服务器IP地址	-h
–port	服务器端口号	-P
–user	MySQL 用户名	-u
–pasword	MySQL 密码	-p
–databases	指定要备份的数据库
–all-databases	备份mysql服务器上的所有数据库
–compact	压缩模式，产生更少的输出
–comments	添加注释信息
–complete-insert	输出完成的插入语句
–lock-tables	备份前，锁定所有数据库表
–no-create-db/–no-create-info	禁止生成创建数据库语句
–force	当出现错误时仍然继续备份操作
–default-character-set	指定默认字符集
–add-locks	备份数据库表时锁定数据库表

 

执行命令

mysqldump -uroot -p [password] test test > '/data/test.dump'

mysql 数据恢复

mysql命令是操作mysql的命令行工具客户端，也可以用它来恢复数据

执行如下命令，就可以将mysqldump的文件数据恢复

mysql -u root -p[password] test < /data/person.dump

mysqladmin 非交互式命令

与mysql命令不同的是，不会以交互式的方式连接到命令窗口

命令格式

mysqladmin [OPTIONS] command command....

#其操作请运行以下命令查看

mysqladmin --help

运行命令创建test数据库

mysqladmin -uroot -p [password] create test

 

服务基本信息相关

查找my.cnf配置文件所在目录

可以执行下面命令，可以得到mysql的配置文件的默认加载顺序

mysql --help | grep 'Default options' -A 1

• grep ... -A 1 表示查询匹配行以及下方一行 （A = after）

查看mysql版本

show variables  like '%version%';

查看支持的所有存储引擎

show engines;

设置mysql后台线程数

这个修改也只能在my.cnf文件中配置，重启生效

[mysqld]
innodb_read_io_threads=8
innodb_write_io_threads=8