查看进程
- show processlist;
show processlist;
杀死进程
- kill 111111(id);
kill 111111(id);
查看详细配置信息
- show variables;
show variables;
查看全局状态
- show global status;
show global status;
查看基本信息
- /s
/s
查看master信息
- show master status/G
show master status/G
查看slave信息
- show slave status/G
show slave status/G
启动/停止slave
- slave start;
- slave stop;
slave start;
slave stop;
退出mysql
- /q
- quit;
/q
quit;
将InnoDB的表格式转换为MyISAM格式
- ALTER TABLE `tablename` ENGINE = MYISAM
ALTER TABLE `tablename` ENGINE = MYISAM
查看binlog信息
- show binlog events;
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
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
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;
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';
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;
SET GLOBAL wait_timeout = 600;
myisam引擎查看post表信息
- myisamchk -dv post
myisamchk -dv post
修改密码
- mysql -u root
- use mysql
- UPDATE user SET password=PASSWORD("new password") WHERE user='name';
- FLUSH PRIVILEGES;