MySQL系统管理

一、数据的备份与恢复

1.1数据库备份

WINDOWS系统首先进入到放置mysqldump.exe的路径下

1.备份一个数据库
mysqldump -u username -p 数据库名称 数据库1 数据库2 ...>保存路径
2.备份多个数据库
mysqldump -u username -p --databases 数据库1 数据库2 ...>保存路径
3.mysqldump -u username -p --all -database >保存路径
4.直接备份文件夹(需要先查找到mysql自己的备份路径，不推荐，仅了解)
show variables like '%datadir%';

1.2数据恢复

mysql -uroot -p[数据库名称] <文件路径
或
直接复制到数据库目录(仅适用于MyISAM)

1.3数据迁移

mysqldump -h host1 -u root -password=password1 -all-database 
| 
mysql -h host2 -u root -password=password2

1.4数据导出

1.select ..into outfile导出文本文件
select [列名] from table [where语句] into outfile '目标文件[OPTION]';

 OPTION常用参数说明：

• FIELDS TERMINATED BY '字符串'：设置字符串为字段的分隔符，默认值为'\t'
• FIELDS ENCLOSED BY '字符'：设置字符来括上字段的值。默认不使用任何符号
• FIELDS OPTIONALLY ENCLOSED BY '字符'：设置字符来括上CHAR、VARCHAR和TEXT等字符型字段。默认情况不适用任何符号。
• FIELSC ESCAPED BY '字符'：设置转义字符，默认值为‘\’。
• LINES STARTING BY '字符串'：设置每行开头的字符，默认情况下无任何字符。
• LINES TERMINATED BY ‘字符串’：设置每行的结束符，默认值是‘\n’。

1.用mysqldump命令导出文本文件
mysqldump -u root -pPassword -T 目标目录 dbname table [option];
2.用mysqldump命令导出xml格式文件
mysqldump -u root -pPassword --xml|-X dbname table >路径
3.用mysql命令导出文本文件
mysql -u root -pPassword -e "SELECT 语句" dbname >路径
4.用mysql命令导出XML文件
mysql -u root -pPassword --xml|-X -e "SELECT 语句" dbname >路径
5.用mysql命令导出HTML文件
mysql -u root -pPassword --hxml|-H -e "SELECT 语句" dbname >路径

option常用参数说明：

• --fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值为'\t'
• --fields-enclosed-by=字符：设置字符来括上字段的值。默认不使用任何符号
• --fields-optionally-enclose-by=字符：设置字符来括上CHAR、VARCHAR和TEXT等字符型字段。默认情况不适用任何符号。
• --fields-escaped-by=字符：设置转义字符，默认值为‘\’。
• --lines-terminated-by=字符串：设置每行的结束符，默认值是‘\n’。

1.5数据导入

1.将文本文件导入到数据库
LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE file_name(路径) INTO TABLE table_name [OPTION];
2.用mysqlimport导入到数据库
mysqlimport -u root -p database file_name(路径) [option];

二、性能优化

1.1查看MySQL数据库的性能

show status like 'value';

value常用参数：

• Connections：链接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。
• Com_delete：删除操作的次数。

1.2优化查询

1.2.1分析查询语句

explain select语句;
或
describe(desc) select语句;

各字段代表的意义：

• id列：指出在整个查询中SELECT的位置。
• table列：存放所查询的表名。
• type列：链接类型，该列中存储很多值，范围从const到ALL。
• possible_keys列：指出为了提高查找速度，在MySQL中可以使用的索引。
• key列：指出实际使用的键。
• rows列：指出MySQL需要在相应表中返回查询结果所检验的行数，为了得到该总行数，MySQL必须扫描处理整个查询，再乘以每个表的行值。
• Extra列：包含一些其他信息，设计MySQL如何处理查询。

1.2.2索引对查询速度的影响

--单列索引
create index 索引名 on 表名(字段名称);
或
--多列索引
create index index_name on 表名(字段名1,字段名2.....);

1.2.3优化数据库结构

1.2.3(1)将字段很多的表分解成多个表

1.2.3(2)增加中间表

1.2.3(3)优化插入记录的速度

1.2.3(3).1禁用索引

--禁用索引
alter table 表名 disable keys;
--重新开启索引
alter table 表名 enable keys;

1.2.3(3).2禁用唯一性检查

--禁用唯一性检查
set unique_checks=0;
--重启唯一性检查
set unique_checks=1;

1.2.3(3).3优化INSERT语句

当插入大量数据时，建议使用一个INSERT语句插入多条记录的方式。

1.2.4分析表、检查表和优化表

1.2.4(1)分析表

analyze table 表名1,表名2,....

参数说明：

• Table：显示表的名称。
• Op：表示执行的操作。analyze表示进行分析操作。check表示进行检查查询。optimize表示进行优化操作。
• Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息四者之一。
• Msg_text：显示信息。

1.2.4(2)检查表

check table 表名1,表名2,...[option];

option有五个参数：QUICK、FAST、CHANGED、MEDIUM、EXTENDED。 

1.2.4(3)优化表

optimize table 表名1,表名2,....

三、安全管理

3.1用户操作

--创建用户
create user 用户名 IDENTIFIED by '用户密码';
--删除用户
drop user 用户名;
--重命名用户
rename user 旧用户名 to 新用户名;
--查看用户权限
show grants for 用户名@主机名;
--GRANT和REVOKE命令
GRANT 权限类型 on 数据库名.表名 to 用户名 IDENTIFIED by '用户密码';
REVOKE 权限类型 from 数据库名.表名 to 用户名 IDENTIFIED by '用户密码';
--权限更改何时生效
flush privileges;
或
mysqladmin flush-privileges 
或
mysqladmin reload
--设置账户密码
mysqladmin -u 用户名 -h 链接地址 password "新密码";
或
set password for '用户名'@'表' ='新密码';

3.2日志文件

--查看错误日志路径
show variables like '%log_error%';
--开启慢查询日志
set global slow_query_log=on;
--查看慢查询时间
show variables like '%long_query_time%';
--设置时间
set global long_query_time=1;
--查看慢查询日志路径
show variables like '%slow_query_log%';
--开启查询日志
set global general_log=on;
--关闭查询日志
set global general_log=off;
--设置查询日志输出格式
set global log_output='FILE';
--查看日志路径
show variables like '%general%';
--查看二进制文件开启
show variables like 'log_bin';
--查看二进制文件名
show binary logs;
--对事件进行回放
show binlog events in 日志名;