Mysql常用命令

• 查询为空的表：

SELECT table_schema,table_name,table_rows FROM information_schema.TABLES WHERE table_rows = 0 AND table_schema not in ('performance_schema','mysql','information_schema')\G;

• 查询不为空的表：

SELECT table_schema,table_name,table_rows FROM information_schema.TABLES WHERE table_rows != 0 AND table_schema not in ('performance_schema','mysql','information_schema')\G;

• 把sql执行结果导出到文件：

mysql -hxx.xx.xx.xx -P3306 -uxxx -pxxx -e "SELECT table_schema,table_name,table_rows FROM information_schema.TABLES WHERE table_rows = 0 AND table_schema not in ('performance_schema','mysql','information_schema')\G;" > /xxx/test.txt

• 导出数据库/表(数据+结构)：

mysqldump -hxx.xx.xx.xx -P3306 -uxxx -pxxx db_name table_name > /xxx/test.sql

• 导出数据库/表(数据)：

mysqldump -hxx.xx.xx.xx -P3306 -uxxx -pxxx -t db_name table_name > /xxx/test.sql

• 导出数据库/表(结构)：

mysqldump -hxx.xx.xx.xx -P3306 -uxxx -pxxx -d db_name table_name > /xxx/test.sql