mysql常用命令（持续更新）

查看进程

show processlist;

杀死进程

kill 111111(id);

查看详细配置信息

show variables;

查看全局状态

show global status;

查看基本信息

\s

查看master信息

show master status\G

查看slave信息

show slave status\G

启动/停止slave

slave start;
slave stop;

退出mysql

\q
quit;

将InnoDB的表格式转换为MyISAM格式

ALTER TABLE `tablename` ENGINE = MYISAM

查看binlog信息

show binlog events;

用binlog恢复数据库

mysqlbinlog --start-position=134 --stop-position=330 mysql-binlog.000001 | mysql -uroot -p
或者
mysqlbinlog --start-position=134 --stop-position=330 mysql-binlog.000001 > /root/test.txt
进入MYSQL导入
mysql> source /root/test.txt

binlog恢复部分数据库

mysqlbinlog -d test --start-position=134 --stop-position=330 mysql-binlog.000001 | mysql -uroot -p test
或者
mysqlbinlog -d test mysql-binlog.000001 > /root/test.txt
进入MYSQL导入
mysql> source /root/test.txt

查看binlog文件

show binary logs;

结果：
Log_name File_size
mysql-bin.000097 188508046
mysql-bin.000098 21117
mysql-bin.000099 1041262
mysql-bin.000100 1073840875
mysql-bin.000101 1073746745
mysql-bin.000102 505666779
mysql-bin.000103 1073742195
mysql-bin.000104 27878070
mysql-bin.000105 866926714
mysql-bin.000106 1073860943
mysql-bin.000107 246717786
mysql-bin.000108 117
mysql-bin.000109 4863847

删除bin-log
(删除mysql-bin.000100之前的所有二进制日志文件，不包括mysql-bin.000100)

purge binary logs to 'mysql-bin.000100';

show binary logs;结果：
Log_name File_size
mysql-bin.000100 1073840875
mysql-bin.000101 1073746745
mysql-bin.000102 505666779
mysql-bin.000103 1073742195
mysql-bin.000104 27878070
mysql-bin.000105 866926714
mysql-bin.000106 1073860943
mysql-bin.000107 246717786
mysql-bin.000108 117
mysql-bin.000109 4863847

修改运行中mysql的参数
(不加 GLOBAL 只对当前会话有效)

SET GLOBAL wait_timeout = 600;

myisam引擎查看post表信息

myisamchk -dv post

修改密码

mysql -u root
use mysql
UPDATE user SET password=PASSWORD("new password") WHERE user='name';
FLUSH PRIVILEGES;

查询结果输出到文件

pager cat > /tmp/temp.txt
select * from table1 limit 100;