2023年7月30日发(作者：)

mysql单表备份语句mysql单表备份语句1. mysql单表备份SELECT CONCAT("mysqldump -uroot -p123456 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")

FROM information_

WHERE table_schema NOT IN('sys','performance','information_schema')INTO OUTFILE '/tmp/';INTO COUTFILE '/'; --将查询结果输出保存到⼀个⽂件中FIELDS TERMINATED BY "," ENCLOSED BY '"'; -- 以逗号分割，引号包裹2. 查询整个数据库中所有的库对应的表明select table_schema, table_name from information_;3. 查询world和school库下所有的表明select table_schema, table_name from information_where table_name = 'world'union allselect table_schema, table_name from information_where table_name = 'school';4. 查询整个数据库中所有的库对应的表明，每个库显⽰⼀⾏select table_schema, group_concat(table_name) from information_group by table_schema;5. 统计每个库下的表的个数select table_schema, count(table_name) from information_ group by table_name;6. 统计每个库的真实数据量 （感觉有问题）# 每个表的数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTHSELECT sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 as total_nb from

information_;7. Concat拼接命令select concat(user,"@","'",host,"'") from ;8. 对数据库下的单张表进⾏单独备份# world库下的city表mysqldump -uroot -p****** world city > /tmp/world_9. 对整个数据库下的1000张表进⾏单独备份，排除sys，performance，information_schema。select concat("mysqldump -uroot -p******",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")from information_where table_schema not in ("sys","performance","information_schema")into outfile '/tmp/';