查看数据库占用磁盘大小
语句如下
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;
执行结果如下
mysql> select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;
+--------------------+------------+------------+
| TABLE_SCHEMA | data_size | index_size |
+--------------------+------------+------------+
| shdx | 1042.38 MB | 258.97MB |
| mysql | 0.52 MB | 0.08MB |
| information_schema | 0.00 MB | 0.00MB |
+--------------------+------------+------------+
3 rows in set
mysql>
查看某库所有表占用磁盘大小
执行语句如下
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'TestDB'
group by TABLE_NAME
order by data_length desc;
执行结果如下，截取部分
mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'shdx'
group by TABLE_NAME
order by data_length desc;
+--------------------------------+-----------+------------+
| TABLE_NAME | data_size | index_size |
+--------------------------------+-----------+------------+
| tab_automatch_log | 499.08 MB | 95.03 MB |
| tab_fundlog201505 | 101.96 MB | 19.13 MB |
| tab_online | 63.49 MB | 14.32 MB |
| tab_game_record201505 | 56.97 MB | 30.93 MB |
| tab_user | 52.17 MB | 12.31 MB |
| dc_user_action_active | 39.94 MB | 0.00 MB |
| tab_gametime_day | 37.22 MB | 21.50 MB |
| tab_fundlog | 35.12 MB | 6.45 MB |
| tab_game_record | 19.85 MB | 10.56 MB |
| tab_feeaccount | 19.17 MB | 10.75 MB |
| tab_userinfo | 18.78 MB | 6.33 MB |
设置Mysql允许远程访问
导出数据库
-
导出数据库中的表结构和数据
格式：mysqldump -u root -p 数据库名字 > /要生成sql文件的路径/文件名字.sql
mysqldump -u root -p syn > /home/syn.sql -
导出数据库中的某个表
mysqldump -u root -p bbb Person > /home/Person.sql
(注意：首先数据库要有bbb这个数据库才能使用这个命令)
导入数据库
(1)首先建空数据库
mysql>create database aaa;
(2) 选择数据库
mysql>use aaa;
(3)导入数据（注意sql文件的路径）
mysql>source /home/syn.sql;