MySql 常用命令

发表于 更新于 分类于

Mysql 常用命令整理

备份数据

1、备份所有数据库，并记录binlog文件明及其位置(增加从库时经常使用)

1
mysqldump -u root -p --all-databases --master-data=2 > all.sql

2、备份某一个数据库

1
mysqldump -u root -p test > test.sql

–routines：导出存储过程和函数
–single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。
–master-data：默认等于1，将dump起始（change master to）binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。

恢复数据

1、直接恢复

1
mysql -u root -p123456 test < test.sql

2、连接Mysql后通过source命令恢复

1
source /data0/back/all.sql

直接通过命令行执行sql，使用-e参数

1
mysql -uroot -p -e 'show databases'

该方式可以用于通过shell脚本监控数据库

设置数据库从库只读

1
set global read_only=1;    #1是只读，0是读写

该参数在从库时非常有用，从库必须设置为只读状态，防止程序误写数据导致主从同步失败。注意即使设置了该参数，root用户仍然可写

开启并查看gen_log

1
2
SHOW VARIABLES LIKE "general_log%"
SET GLOBAL general_log = 'ON';

创建数据库

1
create database xinhua default character set utf8mb4 collate utf8mb4_unicode_ci;

新增用户并授权

1
2
grant all privileges on dennis.* to 'dennis' identified by 'dennis';
flush privileges;

数据库字段内容替换

1
UPDATE xh_article SET cover=REPLACE(cover, 'oriString','desString') where id =1681;

查看mysql运行线程

1
2
show processlist
show full processlist

id： 一个标识
user： 显示当前用户，如果不是root，这 个命令就只显示你权限范围内的sql语句。
host： 显示这个语句是从哪个ip的哪个端口上发出的
db： 显示 这个进程目前连接的数据库。
command：显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接 （connect）。
time： 此这个状态持续的时间，单位是秒。
state： 显示使用当前连接的sql语句的状态，只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成
info： 显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

查看主库、从库同步状态

1
2
show master status
show slave status

查看binlog日志信息

1
2
SHOW BINLOG EVENTS in 'mysql-bin.000050' limit 50;
mysqlbinlog mysql-bin.000050 -vv | more

设置mysql密码强度

1
2
set global validate_password_policy=0
show VARIABLES like "%password%"

0:弱，1：中等，2:强

快速复制一个表

1
2
CREATE TABLE newadmin LIKE admin;
NSERT INTO newadmin SELECT * FROM admin;

查询系统表中的锁信息

1
2
3
select * from information_schema.INNODB_LOCK_WAITS;
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_TRX;

发生死锁时查询死锁发生的SQL和时间

1
show engine innodb status;

查询innodb的锁等待超时配置

1
show VARIABLES like '%innodb_lock_wait_timeout%';

杂七杂八

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT * FROM information_schema.INNODB_TRX;
show OPEN TABLES where In_use > 0; 
show full processlist;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
local table td_push read;
show status like 'innodb_row_lock%';
select * from td_push_dic where id=5 lock in share mode;
select * from td_push_dic where id=5 for update;
Show engine innodb status\G;

show global variables like "innodb_locks%"; 
show global variables like "autocommit";
show global variables like "%tx%";
set session transaction isolation level 'repeatable read';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

show variables like '%log_bin%';
show binary logs;
show variables like 'binlog_format';
show master status;

SHOW BINLOG EVENTS in 'mysql-bin.000050' limit 50;
mysqlbinlog mysql-bin.000050 -vv | more