MySQL备份与恢复

MySQL数据库自带了一个很方便备份的命令: mysqldump，其备份数据就是把数据从数据库里以逻辑的sql语句的形式直接输出或者生成备份文件的过程，属于逻辑备份，而对文件系统的数据进行打包属于物理备份，基本格式:

mysqldump -u {user_name} -p {user_passwd} {database_name} > /{path}/{file_name}.sql

恢复：

mysql -u {user_name} -p {user_passwd} {database_name} < /{path}/{file_name}.sql

上面的备份没有创建数据库，可以加-B参数来备份创建数据库的语句，这种情况可以备份多个数据库，因为会加上建库语句(备份多个数据库，只需要以空格隔开多个数据库名即可):

mysqldump -u {user_name} -p {user_passwd} -B {database_name} > /{path}/{file_name}.sql

这种情况下，恢复可以不需要指定库：

mysql -u {user_name} -p {user_passwd} < /{path}/{file_name}.sql

备份时，在数据库名后可以设置压缩:

mysqldump -u {user_name} -p {user_passwd} -B {database_name}|gzip > /{path}/{file_name}.sql.gz

如果数据类型有blob类型，需要加参数--hex-blob，综合示例:

date_now=$(date +%Y%m%d%H%M)db_name=`mysql -u${db_user} -P${db_port} -p123456789 -e "show databases;" | grep 'linjk'`mysqldump -u${db_user} -P${db_port} -p123456789 --hex-blob --single-transaction -B ${db_name}|gzip > ./${db_name}_${date_now}.sql.gz

分库备份

可以多次执行一个备份库的语句，但是多个库时就麻烦了，可以执行如下命令批量备份：

mysql -uroot -p'123456' -e "show databases;"| grep -Evi "database|info|perfor"|sed -r ' s#^([a-z].*$)#mysqldump -uroot -p'123456' --events -B \1|gzip > /opt/\1.sql.gz#g'| bash

这里指定备份时不备份数据库名包含database或info或perfor的数据库，命令执行后如下：

使用分库备份，可以区分每个库，在一个数据库出问题时，容易排查，因此建议分库备份而不使用一个文件。

备份单个表

mysqldump -u {user_name} -p {user_passwd} {database_name} {table_name} > /{path}/{file_name}.sql   (注意：这时候不需要-B参数)

加-t参数可以只备份这个表的数据

备份多个表

和备份多个库类型原理，但是有个缺点就是文件比较多，一般按需求处理。

备份数据库表结构

备份加参数-d即可:

mysqldump -u {user_name} -p {user_passwd} -d {database_name} > /{path}/{file_name}.sql

全备份

mysqldump -u {user_name} -p {user_passwd} -A -B --events|gzip > /{path}/{file_name}.sql.gz

可以加-F参数刷新、切割binlog

增量备份时，需要找到备份点，可以加参数--master-data=2，会输出备份文件和备份的位置，然后从那个位置往后备份即可。

另外，还有-x参数、-l参数，用于锁表，具体可以查看mysqldump的帮助: mysqldump --help，对于InnoDB事务数据库的备份，通常还加--single-transaction参数来保证本次备份操作不会看到其他会话已经提交了的数据，从而保证数据一致性，而对于MyISam数据库引擎来说，通常加-x参数锁表来保证数据一致。但是，如果一个数据库使用了两种存储引擎，备份时要以myisam为主，即加-x参数，但现在一般建议使用InnoDB了。

----------------------------- 华丽丽的分界线(以上为备份，以下为恢复) -----------------------------

登录数据库后，可以使用source命令来恢复数据库: source /opt/haha.sql

（在登录数据库后，可使用system {command}来执行linux的常用命令）

----------------------------- 华丽丽的分界线 -----------------------------

扩展：

1. 查看数据库连接情况： show processlist; 或: show full processlist;

2. 查看当前连接(会话)的状态： show status;

3. 查看全局状态： show global status;

4. 登录后更改数据库参数，且要求重启后参数还能生效：修改my.cnf即可，数据库会自己读取