高效导入多个.sql文件方法详解

常用的命令有两个：mysql和source

此外还有sqlimport和LOAD DATA INFILE等导入方法，不过它们主要用于导入.csv或.xml文件数据，不是.sql文件

假设脚本user1.sql，user2.sql，导入到的数据库为mydb

• mysql 命令导入（适合多个脚本执行）

  for SQL in /home/work/*.sql; do mysql -uroot -p***** mydb < $SQL;done
  

• source 命令导入（适合少量脚本执行）

  # 首先需要进入mysql命令行中
  mysql -uroot -p*****;
  use mydb;
  source user1.sql;
  source user2.sql;
  

mysql单表备份语句

1. mysql单表备份

   SELECT CONCAT("mysqldump -uroot -p123456  ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")  
   FROM information_schema.tables 
   WHERE table_schema NOT IN('sys','performance','information_schema')
   INTO OUTFILE '/tmp/bak.sh';
   

   INTO COUTFILE ‘/tmp.bak.sh’; –将查询结果输出保存到一个文件中

   FIELDS TERMINATED BY “,” ENCLOSED BY ‘”’; – 以逗号分割，引号包裹

2. 查询整个数据库中所有的库对应的表明

   select table_schema, table_name from information_schema.tables;
   

3. 查询world和school库下所有的表明

   select table_schema, table_name from information_schema.tables
   where table_name = 'world'
   union all
   select table_schema, table_name from information_schema.tables
   where table_name = 'school';
   

4. 查询整个数据库中所有的库对应的表明，每个库显示一行

   select table_schema, group_concat(table_name) from information_schema.tables
   group by table_schema;
   

5. 统计每个库下的表的个数

   select table_schema, count(table_name) from information_schema.tables group by table_name;
   

6. 统计每个库的真实数据量 （感觉有问题）

   # 每个表的数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
   SELECT sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 as total_nb from 
   information_schema.tables;
   

7. Concat拼接命令

   select concat(user,"@","'",host,"'") from mysql.user;
   

8. 对数据库下的单张表进行单独备份

   # world库下的city表
   mysqldump -uroot -p****** world city > /tmp/world_city.sql
   

9. 对整个数据库下的1000张表进行单独备份，排除sys，performance，information_schema。

   select concat("mysqldump -uroot -p******",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
   from information_schema.tables
   where table_schema not in ("sys","performance","information_schema")
   into outfile '/tmp/bak.sh';