mysql单表备份语句 +多表

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 '"'; -- 以逗号分割，引号包裹

多表备份
mysqldump -rp2p_sit -p --database p2p_sit --tables tablename1 tablename2 > ~/p2p_init.sql

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';