Mysql 常用命令

备份与还原

备份数据库

  • 首先dump一下: mysqldump --add-drop-table -h localhost -u wordpressuser -p wordpress > wordpress.sql
    关于–add-drop-table :It isn’t necessary. It is just there so that if you import the created dump file into a database that already has a table with the same name, it will drop that table and then add the new table in its place. Otherwise you will get an error, and the dump file won’t be imported.

  • 然后压缩一下: bzip2 wordpress.sql

  • 或者直接一行搞掂: mysqldump --add-drop-table -h localhost -u wordpressuser -p wordpress | bzip2 -c > wordpress.sql.bz2

还原数据库

  • 首先解压一下: bzip2 -d wordpress.sql.bz2
  • 然后导入搞掂: mysql -h localhost -u wordpressuser -p wordpress < wordpress.sql

日志

慢查询日志

  • 开启日志:
    set global slow_query_log=1;
  • 设置查询时间阈值:
    set session long_query_time=0.1;
    set global long_query_time=0.1;
  • 显示状态:
    show variables like 'long%';
    show global variables like "%slow%";
  • 查询日志:
    system more your_slow_query_log_file_dir

先去上课ORZ。。。


Leave a Reply