mysql会话命令_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_nameFile_size

mysql-bin.000097188508046

mysql-bin.00009821117

mysql-bin.0000991041262

mysql-bin.0001001073840875

mysql-bin.0001011073746745

mysql-bin.000102505666779

mysql-bin.0001031073742195

mysql-bin.00010427878070

mysql-bin.000105866926714

mysql-bin.0001061073860943

mysql-bin.000107246717786

mysql-bin.000108117

mysql-bin.0001094863847

删除bin-log

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

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

show binary logs;结果：

Log_nameFile_size

mysql-bin.0001001073840875

mysql-bin.0001011073746745

mysql-bin.000102505666779

mysql-bin.0001031073742195

mysql-bin.00010427878070

mysql-bin.000105866926714

mysql-bin.0001061073860943

mysql-bin.000107246717786

mysql-bin.000108117

mysql-bin.0001094863847

修改运行中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;

分享到：

2009-10-06 01:53

浏览 1528

论坛回复 / 浏览 (1 / 4216)

分类:数据库

评论

1 楼

wangpeihu

2010-09-05

文章不错，如果加上适当的说明，就更好了。学习中...