Mysql数据库相关一些知识整理

一、数据占用空间大小

Mysql数据库数据的存储信息由information_schema 系统配置数据库进行存储。

其中tables表存储表的相关信息，几个重要字段如下

TABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小

 

要知道一个表占用空间的大小，那就相当于是 数据大小 + 索引大小 即可

 

SQL：

<1>查询表的大小与记录条数

mysql> use information_schema

mysql>SELECTTABLE_SCHEMA,TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024,TABLE_ROWS FROM TABLESWHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'

 

<2>查询所有数据库占用空间大小(单位MB)

mysql> SELECTsum(DATA_LENGTH+INDEX_LENGTH)/1024/1024 FROM TABLES;

 

<3>查询某个数据库占用空间大小(单位MB)

mysql> SELECTsum(DATA_LENGTH+INDEX_LENGTH)/1024/1024 FROM TABLES WHERE TABLE_SCHEMA='数据库名'

 

<4>查询某个数据库某张表占用空间大小(单位MB)

mysql> SELECT (DATA_LENGTH+INDEX_LENGTH)/1024/1024FROM TABLES WHERE TABLE_SCHEMA='数据库名' and TABLE_NAME=’表名’

 

二、Mysql数据库的导出和导入

1.导出整个数据库

　　mysqldump [–h主机] -u用户名–p密码数据库名 > 导出的文件名

./mysqldump -uroot–p123456 test > /home/xing/test.sql

 

2.导出一个表

　　mysqldump [–h主机] -u用户名–p密码数据库名 表名> 导出的文件名

./mysqldump -uroot–p123456 test tb> /home/xing/testtb.sql

 

3.导出一个数据库结构

mysqldump [–h主机] -u用户名 –p密码 数据库名 -d--add-drop-table > 导出的文件名

./mysqldump [–h主机] -uroot –p123456test -d --add-drop-table > /home/xing/test.sql

-d 没有数据

--add-drop-table在每个create语句之前增加一个drop table,默认是打开的，如果想跳过采用--skip-add-drop-table

 

4.导入数据库

         方法一：使用source 命令

　　进入mysql数据库控制台，

　　使用数据库

mysql>use 数据库

　　source 脚本文件

mysql>source /home/xing/test.sql

 

方法二：mysql脚本

mysql -uroot –p密码 database <脚本文件

$ ./mysql –uroot–p123456 test /home/xing/test.sql

 

三、修改用户密码

mysql/bin目录下mysqladmin命令

 

修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码 

$ ./mysqladmin-uroot –p123456 password 123

 

为初始化用户设置密码

格式：mysqladmin -u用户名 password 新密码 

 

四、 增加新用户：

（注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符）

格式：mysql>grant select on 数据库.* to 用户名@登录主机 identified by "密码" 

例1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令： 

mysql> grantselect,insert,update,delete on *.* to test1@"%" Identified by"abc";

 

 但例1增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见例2。 

例2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。 

mysql>grantselect,insert,update,delete on mydb.* to test2@localhost identified by"abc"; 

如果你不想test2有密码，可以再打一个命令将密码消掉。 

mysql>grantselect,insert,update,delete on mydb.* to test2@localhost identified by""; 

 

 五、修改mysql数据库其他主机可以访问(%，不安全非常不建议使用)

1）mysql命令方式

mysql>GRANTALL PRIVILEGES ON *.* TO 'root'@'%' WITHGRANT OPTION 
//赋予任何主机访问数据的权限

 

mysql>FLUSHPRIVILEGES 

//修改生效

        

2）修改mysql表方式

改表法
　　将user表中的host改为'%'。

进入mysql数据库，然后

mysql>usemysql;

mysql>update userset host=’%’ where user=’root’ and host=’%’;

重启mysql服务即可访问