一、数据的备份与恢复
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 日志名;