mysql dump 查看_mysql安装 mysql帮助手册 mysqldump用法 查看数据库占用空间

一、mysql安装

mariadb是mysql的一个分支。

centos6 yum install mysql-server

centos7 yum install mariadb-server

二、mysql帮助手册

执行sql文件：source /tmp/tmp.sql，cat /tmp/tmp.sql |mysql -h127.0.0.1 -uuser -p dbname；

执行linux系统命令：system pwd，system ls -al；

三、mysqldump用法

备份表：mysqldump [options] db_name [tbl_name ...]

备份库：mysqldump [options] --databases db_name ...

备份所有库：mysqldump [options] --all-databases

指定utf8编码备份数据库：mysqldump -uuser -p --default-character-set=utf8 dbname >dbname.sql

备份数据表：mysqldump -uuser -p dbname tablename >tablename.sql

-w或--where备份部分数据：mysqldump -uuser -p dbname tablename -w "id=8" >tablename.sql

into outfile，备份到mysql服务器上面，默认在/var/lib/mysql下面：

select width,height from eps_file into outfile '/tmp/eps_file_bak.sql';

into dumpfile，备份到mysql服务器上面，默认在/var/lib/mysql下面，适合备份一行数据，因为数据行列无分隔符：

select width,height from eps_file limit 1 into dumpfile '/tmp/eps_file_bak.sql';

四、查看数据库占用空间

1、数据库所占空间

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,

CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,

CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,

CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size

FROM information_schema.tables WHERE TABLE_SCHEMA = '数据库名';

2、表所占空间

SELECT TABLE_NAME AS table_name, CONCAT(TRUNCATE(data_length/1024/1024,2),'MB') AS data_size,

CONCAT(TRUNCATE(max_data_length/1024/1024,2),'MB') AS max_data_size,

CONCAT(TRUNCATE(data_free/1024/1024,2),'MB') AS data_free,

CONCAT(TRUNCATE(index_length/1024/1024,2),'MB') AS index_size

FROM information_schema.tables WHERE TABLE_SCHEMA = '数据库名' order by data_size desc;