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

==========================================================

vim /etc/my.cnf

secure-file-priv=/tmp

/etc/init.d/mysqld restart

或者

systemctl restart mysqld

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

SELECT table_schema,table_name FROM information_schema.tables;

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

SELECT table_schema,table_name FROM information_schema.tables

WHERE table_schema='world'

UNION ALL

SELECT table_schema,table_name FROM information_schema.tables

WHERE table_schema='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_schema;

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.information_schema.tables+CONCAT(),拼接命令,使用方法举例

mysql> SELECT CONCAT(USER,"@","'",HOST,"'") FROM mysql.user;

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

mysqldump -uroot -p123456 world city >/tmp/world_city.sql

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

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

vim /etc/my.cnf

secure-file-priv=/tmp

/etc/init.d/mysqld restart

10.批量实现world下所有表的操作语句生成

alter table world.city discard tablespace;

select concat("alter table ",table_schema,".",table_name,"discard tablespace;")

from information_schema.tables

where table_schema='world'

into outfile '/tmp/discard.sql';

vim /etc/my.cnf

secure-file-priv=/tmp

/etc/init.d/mysqld restart

11.左外连接语句

select a.name,b.name ,b.surfacearea from city as a left join country as b on a.countrycode=b.code and a.population<100;

12.右外连接语句

select a.name,b.name ,b.surfacearea from city as a right join country as b on a.countrycode=b.code and a.population<100;

13.统计一下每个库下的表的个数

每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

SELECT

table_schema,

count( table_name ),

sum( AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH ) / 1024 / 1024 AS total

FROM

information_schema.TABLES

GROUP BY

table_schema;

14.统计一下每个库的真实数据量

SELECT table_schema,COUNT( table_name ),

SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS ToTAL_nB

FROM information_schema.TABLES

GROUP BY table_schema;