MySQL常用命令与方法

分享到：
作者: 日期: 2010-11-15

一、查看命令

  1. 查看建表语句 
    show create table 表名;
  2. 查看当前运行的语句 
    show processlist;

    或

    show full processlist;
  3. 查看当前运行状态

1) status;

参数说明

  • mysql.sock 该值是用于当mysql的客户机和服务端都在一台服务器时通信所用，开发机默认都装有客户机。线上服务器依情况而定
  • Questions: 统计的是客户端发出请求的数量(一般不用关心)
  • Slow queries: 慢查询的执行次数，这个慢查询的规定需要在启动时指定时限，比如2秒
  • Queries per second avg: 每秒执行的查询次数。如果值太大，则需要考虑减轻数据库的压力了

2) show status;
会有很多的值，我们经常关注以下值就可以

  • Max_used_connections: 最大值连接数值，当达到或接近max_user_connections时，则需要考虑增大max_user_connections值
  • show status like ‘Created_tmp%’: 根据该值可以看到查询时用到临时表的状态，如果过多，需要优化sql。
  • show status like ‘%_lock%’; 查看当前锁表的情况，用MyISAM关注table_lock

3) show variables;

  • max_user_connections 最大的连接值，一般1000就是极限，因为查询速度跟不上，连接上也是没有用的。
  • net_buffer_length TCP/IP和套接字通信缓冲区大小。
  • max_allowed_packet 客户端/服务器之间通信的缓存区的最大大小;
  • date_format 和 datetime_format 这两个值会决定日期的存储格式，一般是按照正常的格式。
  • time_zone 时区，一般是跟着操作系统走，如果发现格式不对或者时区不对需要相关的设置。因为我们的时区是东八区

二、修改MySQL用户(一般开发人员不会用到的)

  1. 增加或修改用户权限 
    GRANT ALL PRIVILEGES ON 库名.* TO 用户名@'ip值' IDENTIFIED BY '密码' WITH GRANT OPTION;

    ALL PRIVILEGES表示所有权限，select ,delete,update,create table等
    当库名为*时，则所有库均可访问
    用户名一般为root，可自定义
    ip值，可指定具体某个ip或域名可访问该数据库，当不指定时，用百分号%

  1. 删除用户 
    DELETE FROM user WHERE User='user_name' and Host='host_name';
FLUSH PRIVILEGES;

三、备份和还原mysql数据

1)直接用系统命令cp(适用于MyISAM且备份机与源机的MySQL版本一致)
2)Mysqldump
3)导出sql语句查询结果

  • 系统命令行下
    shell > mysql -uroot -pxxx -e “select * from mysql.user” > /tmp/result.log
  • 在Mysql命令行中
    mysql > select * into outfile ‘/tmp/result.log’ fields terminated by ‘,’ from mysql.user;
    4) 还原sql文件
    shell > mysql -uroot -pxxx < /tmp/test.sql

    三、常见问题及解决办法

    1)表错误修复(会锁表，谨慎操作，不能支持Innodb)

  • myisamchk用于myisam引擎
  • mysql进程中执行 mysql>repair table 表名;
    2) 忘记mysql用户密码(适用于自己玩)

    service mysql stop //停止当前mysql，不行就kill
mysqld_safe --skip-grant-table&//启动mysql
mysql//进入mysql控制台
use mysql;//选择库
update user set password = password('test') where user = 'root';//修改root密码
flush privileges;//更新权限

service mysql stop//停止
service mysql start//重启，现在可以用新密码正常登陆

    3) Too many connections错误
    与max_user_connections值息息相关，确实需要调时则相应增加
    4) Packet too large错误
    与max_allowed_packet相关，一般不需要调大，需要确认你的返回结果集和执行的sql是否确认过大。可修改my.cnf或在进程中执行
    5) The table is full错误
    需要调大tmp_table_size值，mysql在查询时有时会用到临时创建一个表来作子查询或排序
    6) 表损坏
    当错误信息包含以下提示时，表示表可能被破坏

  • Can’t open file: ‘xxx.MYI’ (errno: 145)
  • Incorrect key file for table: ‘…’. Try to repair it
  • Error: Table ‘xxx’ is marked as crashed and should be repaired
    需要用repair等命令修复（innodb需要通过回滚备份的方式来实现）
    7) 待续
    推荐阅读 《High.Performance.MySQL_Second.Edition》
数据库

评论关闭。