MySQL常见操作

修复表

mysql> REPAIR TABLE table_name;

查看正在执行的sql

mysql> show processlist;

+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info             |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 38316 | Waiting on empty queue | NULL             |
| 26 | root            | localhost | UNPD | Query   |     0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
2 rows in set (0.00 sec)

如果要干掉某个执行的SQL

mysql> kill 26;

清理mysql日志，保留binlog.000057往后的日志

PURGE BINARY LOGS TO 'binlog.000057';

导出数据（不包含创建表语句的方式）

mysqldump -uroot -p密码 -h 192.168.2.88 --databases DB_Name --tables 表名 -t -w 'id > 0 and id <= 10000000' --column-statistics=0 > /home/mysql/file.sql

-t 代表不包含建表语句

导入数据

mysql -u root -h 192.168.2.88 -p密码 DB_Name < /home/mysql/file.sql

DB_name是数据库名