备份所有数据库

[root@RJYF-U-314 ~]# /usr/local/mysql/bin/mysqldump -S /var/lib/mysql/mysql.sock -hlocalhost -uroot -p123456 --all-databases > all-db.sql

还原所有数据库

前提是备份时必须是 –all-databases .

[root@RJYF-U-314 ~]# /usr/local/mysql/bin/mysql -uroot -p123456 < all-db.sql

备份单个库

[root@RJYF-U-314 ~]# mysqldump -uroot -h172.20.60.18 -P 32000 -ptest --databases devops > devops.sql

还原单个库

[root@RJYF-U-314 ~]# mysql -uroot -h172.20.60.18 -P 32000 -ptest devops < devops.sql
[root@RJYF-U-314 ~]# mysqldump -uroot -p123456

backup && restore

备份单个testdb库,恢复库需要事先存在还原库
# mysqldump -u root -p qikang -h localhost testdb > /tmp/testdb.sql

还原
# mysql testdb < /tmp/testdb.sql

备份所有数据库
# mysqldump -u root -p qiknag -h localhost --all-databases > /tmp/all.sql

导出整个数据库结构和数据
# mysqldump -h localhost -uroot -p123456 database > dump.sql

导出单个数据表结构和数据
# mysqldump -h localhost -uroot -p123456 database table > dump.sql

导出整个数据库结构（不包含数据）
# mysqldump -h localhost -uroot -p123456 -d database > dump.sql

导出单个数据表结构（不包含数据）
# mysqldump -h localhost -uroot -p123456 -d database table > dump.sql

备份脚本

crontab中使用绝对路径

# mysqldump cReserveDB  | gzip > cReserveDB.tar.gz
# gunzip < cReserveDB.tar.gz | mysql cReserveDB

mysqldump是逻辑备份工具
mysqldumper是多线程的备份工具

备份前加锁
可以给单个库加锁
可以给单个表加锁
或全局锁

–all-databases 备份所有数据库
–database 指定数据库
–database testdb mysql #指定备份testdb,mysql两个库。使用这个选项还原库的时候，不用事先创建库
–lock-all-tables #备份时锁定所有表，建议使用。可读不可写。对MyISAM、Innodb、Aria做温备
–lock-tables #锁定单个表
–single-transaction 对Innodb存储引擎实现热备
–events 同时备份事件调度器代码
–routines 备份存储过程和存储函数
–triggers 备份触发器
–flush-logs 备份时滚动日志。方便二进制日志记录位置，实现即时点还原。
–master-data-n 主从时备份主，记录二进制日志的文件和事件
0不记录
1记录成CHANGE MASTER语句
2记录成注释的CHANGE MASTER语句
–opt 包含(–add-drop-table,–add-locks,–create-options,–disable-keys,–extended-insert,–lock-tables,–disables-keys,,,,)

启用二进制日志，上线服务

mysql> set session sql_log_bin=1;

关闭二进制日志，下线服务

mysql> set session sql_log_bin=0;

mysqldump一般备份步骤：

  • 1、先刷新表：mysql -uroot -pqikang -d “FLUSH TABLES WITH READ LOCK;” #把所有表缓冲区的数据写到磁盘并请求施加全局的读锁。如果MySQL量很大的时候会在此处阻塞。锁操作的shell不能断开否则锁失效。
  • 2、请求锁：–lock-all-tables或使用–singe-transaction进行innodb热备
  • 3、滚动日志： –flush-logs
  • 4、记录日志文件位置： SHOW MASTER STATUS;
  • 5、选定要备份的库： –databases
  • 6、指定二进制日志文件及位置：–master-data=2
  • 恢复

刷新日志和锁库

FLUSH TABLES WITH READ LOCK;

查看储存引擎

SHOW TABLE STATUS FROM testdb\G;

使用mysqldump+binlog做即时点备份和还原

  • 备份

    mysqldump --databases testdb --lock-all-tables --flush-logs --master-data=2 > /tmp/testdb4.sql 完全备份,并记录了日志文件位置和日志节点
mysql -uroot -pqikang -D testdb -e "create table newtb(Name CHAR(30));" 生成新数据
mysql -uroot -pqikang -D testdb -e "insert into newtb values ('Tom'),('Jerry');" 生成新数据

  • 删除testdb库，数据库故障

  • 导出bin log日志的备份 在备份的sql文件找到二进制文件开始位置，在已经存在的二进制文件中找到stop-position节点 mysqlbinlog后跟空格加多个文件

    此处为了做实验不导出删除testdb库的sql语句
# mysqlbinlog --start-position=367 --stop-position=669 /data/mysql/bin-log/master-bin.000005 > /tmp/testdb5.inc.sql 
实际生产应该使用的语句
# mysqlbinlog --start-position=367 /data/mysql/bin-log/master-bin.000005 > /tmp/testdbdb5.inc.sql

  • 还原

    • 让程序不能访问数据库
      • 关闭二进制日志，服务下线，不能写和读
      > set session sql_log_bin=0;
      • 还原前先滚动日志
      > flush logs;
      • 先导入完全备份
      # mysql < /tmp/testdb4.sql
    • 还原了库，但此时没有新数据
      • 导入用mysqlbinlog导出的新数据
      # mysql < /tmp/testdb5.inc.sql
      开启二进制日志，服务上线
      > set session sql_log_bin=0;