##数据库备份和恢复

数据备份

  1. 备份整个数据库
     $ mysqldump -u [uname] -p[pass] db_name > db_backup.sql
  1. 备份指定表
    $ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql
  1. 备份压缩
   $ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

###数据恢复

  1. 恢复数据库
   $ mysql -p -u[user] [database] < db_backup.dump
  1. 从恢复表
   $ mysql -uroot -p DatabaseName < path\TableName.sql
  1. 从整个数据库备份中恢复某个表
   $ grep -n "Table structure" mydump.sql
   # identify the first and last line numbers (n1 and n2) of desired table
   $ sed -n n1,n2p mydump.sql > mytable.sql # (e.g. sed -n 48,112p)
   $ mysql -uroot -p DatabaseName <mytable.sql

###dump数据库表创建sql

mysqldump -d --compact --compatible=mysql323 ${dbname}|egrep -v "(^SET|^/\*\!)"

查看数据库表大小

从大到小列出所有数据库所有表

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

查看某个表大小

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";