mysql备份

msyqldump备份命令

# egrep -v "#|\*|--|^$"  /opt/mysql_bak.sql  # 查看备份的内容

# mysqldump -u 用户名 -p  数据库名 > 备份的文件名  # 备份命令

# mysqldump -u 用户名 -p -B 数据库名 > 备份的文件名  # 备份命令，-B会把创建切换库的语句也备份

# mysqldump -u 用户名 -p -B 数据库名 | gzip>备份的文件名  # 备份命令，-B会把创建切换库的语句也备份,gzip压缩

# mysqldump -u 用户名 -p -B 数据库名1 数据库名1 | gzip>备份的文件名  # 备份多个库命令，-B表示接多个库，并且增加use db和create db的信息,gzip压缩

# mysqldump -u 用户名 -p 数据库名1 表名  # 备份单个表命令

# mysqldump -u 用户名 -p -d 数据库名  表名  # 备份单个表结构的命令

# mysqldump -u 用户名 -p -A -B --events | gzip>备份的文件名  #备份所有数据库命令

# mysqldump -u 用户名 -p -A -B -F --events | gzip>备份的文件名  #备份整个数据库命令，刷新binlog日志（F参数，切割binlog）

分库备份

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

mysqldump关键参数：

1. -B 制定备份多个库，增加建库语句和use切换语句

2. --compact 去掉注释，适合调试，生产环境不用

3. -A 备份时，制定所有库 等同于--all-databases

4. -F 刷新binlog日志或者用下边的--master-data  等同于--flush-logs

5. --master-data 增加binlog日志名及对应的位置点 1，2的区别 1 不是注释，2是注释

6. -x，--lock-all-tables  锁表

7. -l,--lock-tables 只读锁表

8. -d 只备份表结构

9. -t 只备份数据

10. --single-transaction 适合innodb事务数据库的备份，保证备份的一致性

11. --master-data作用

--master-data=1  无注释  --为注释

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=1191;

--master-data=2  注释

--CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=1191;

mysql备份常用的命令行

myisam备份命令

# mysqldump -uroot -p'pass' -A -B -F --master-data=2 -x --events | gzip >/opt/data.sql.zip  常规备份（触发器和存储过程需要加其他的参数）

# mysqldump --user=root --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs \

--triggers --routines --events --hex-blob >$BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

innodb备份命令

不包含触发器和存储过程

# mysql -uroot -p'pass' -A -B -F --master-data=2 --events --single-transaction | gzip >/opt/data.sql.zip #只是锁表的参数有区别

参数比较全

# mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs \

--triggers --routines --events --hex-blob >$ BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

大B参数是否需要？

# mysqldump --user=root -A -B -F --master-data=1 --events  --single-transaction --flush-privileges    \

--triggers --routines --hex-blob >$ BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

如果既有myisam和innodb，以myisam的备份命令为主

--master-data  2的时候是注释的，1不是注释的从库常用的

mysql数据库的恢复

source命令恢复

在备份时未制定-B参数，必须在恢复的时候给定库名

mysql -uroot -p -e '' 参数

mysql常用的命令解释

show status  #查看当前的会话的数据库信息

show global status #查看整个数据库运行状态信息，很重要，要分析做好监控

show processlist  #查看正在执行的sql语句，看不全

show full processlist  #查看正在执行的sql语句全

set global key_buffer_size=32777218 #不重启数据库修改参数

show variables; #查看数据库的参数信息

mysqlbinlog 解析mysq的binlog日志文件

-d 截取指定库的binlog

mysql的binlog日志 用来记录mysql的增删改查贵mysql数据库有更新的内容的记录（不记录select）

mysqlbinlog -d oldboy mysql-bin.000020>oldboy.sql  # 恢复的只有oldboy库的语句

指定位置和时间点恢复

按照位置截取：

msqlbinlog mysqlbin.000020 --start-position=365 --stop-position=456 -r pos.sql

按照时间点截取：

mysqlbinlog mysqlbin.0000020 --start-datetime='2014-10-16 17:14:15' --stop-datetime='2014-10-16 17:15:15' -r time.sql