Mysql 管理

# 创建一个名为`test`密码为`1234`的用户：
create user test identified by '1234';

# 查询用户以及host
select user,host from mysql.user;

# 修改用户允许登录的主机
# % 代表所有主机
# localhost 代表只能在本机登录
# 192.168.1.1 代表只能在192.168.1.1这个ip登录
update mysql.user set host = 'localhost' where user = 'test';

# 修改用户密码
alter user 'test'@'localhost' identified by 'pswd';

# 重命名用户
rename user test to test1;

# 删除用户test
drop user 'test'@'localhost';

# 查询当前用户权限
show grants;

# 查询其他用户权限
show grants for test;
select * from mysql.user where user='test';

# 授予用户权限。
grant privilegesCode on dbName.tableName to username@host;

# 撤销用户权限。
revoke privilegesCode on dbName.tableName from username@host;

grant select on testdb.* to dba@localhost with grant option;

# ANALYZE TABLE用来检查表键是否正确
mysql> analyze table users;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| test.users            | analyze | status   | OK       |
+-----------------------+---------+----------+----------+

# CHECK TABLE 用来针对许多问题对标进行检查
#   CHANGED 检查自最后一次检查以来改动过的表
#   EXTENDED 检查所有被删除的连接并进行键检验
#   QUICK 只进行快速扫描
mysql> check table users,orders extended;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| test.users              | check | status   | OK       |
| test.orders             | check | status   | OK       |
+-------------------------+-------+----------+----------+

mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项]  > 脚本名

# 系统行命令
mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /backup/mysqldump/db_name.db

注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。
# soure 方法
use db_name
source /backup/mysqldump/db_name.db

# 查看所有数据库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

# 查看所有数据库各表容量大小
select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

# 查看指定数据库容量大小
select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

# 查看当前设置
mysql>SHOW VARIABLES;
mysql>SHOW STATUS;

# 查看活动进程,可以使用KILL终结某个进程
mysql>SHOW PROCESSLIST;

# 使用EXPLAIN让MySQL解释它如何执行一条SQL语句
mysql> explain select * from test.users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  145 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+