mysqldump:仅适用于数据集较小
使用格式:
mysqldump [OPTIONS] database [tables] 还原是数据库必须存在,备份单个库
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]还原数据库可以不存在,备份指定的多个库
mysqldump [OPTIONS] --all-databases [OPTIONS] 还原数据库可以不存在,备份所有数据库
OPTIONS:
-u uername 指定连接数据库的用户名
-h 主机名指定连接数据库的主机名
-p 密码指定连接数据库的密码
备份单个库(恢复时如果目标不存在,应先创建数据库,如果不想创建,这使用--databases)
mysqldump -uroot -hlocalhost -p --databases hellodb > /mydata/backups/hdb.sql
mysql -uroot -p < /mydata/backups/hdb.sql
备份指定的多个库
mysqldump -uroot -hlocalhost -p --databases hellodb mydb > /mydata/backups/mdb.sql
mysql -uroot -p < /mydata/backups/mdb.sql
备份所有的库
mysqldump --all-databases > /mydata/backups/all.sql
mysql -uroot -p < /mydata/backups/all.sql
注意:备份前要加锁
--lock-all-tables请求锁定所有表之后再备份,对MyISAM,InnoDB,Aria做温备
-single-transaction能够对InnoDB存储引擎实现热备
备份代码
-events备份事件调度器代码
-routines备份存储过程和存储函数
-triggers备份触发器
备份时滚动日志:
--flush-logs 备份前、请求到锁之后滚动日志
复制时的同步位置标记
--master-data={0|1|2}
0表示不记录,
1表示记录为change master语句
2表示记录为注释的change master语句
使用mysqldump备份步骤
请求锁:--lock-all-tables 或使用--single-transaction进行innodb热备
滚动日志:--flush-logs
选定要备份的库 --databases
记录二进制文件及位置 --master-date
恢复建议
关闭二进制日志,关闭其他用户的写操作
应该用souce 恢复
即时点还原场景:
hellodb使用的InnoDB存储引擎,在做过一次完备后,之后又有过数据的变动,变动后的数据还未来得及做备份,服务器硬件故障崩溃。
处理过程:
新机器上重新安装系统,部署mysql数据库。先用之前的完备做恢复,再根据二进制日志进行即时点恢复,恢复系统崩溃时的数据库状态。
准备过程
service mysqld stop
vim /etc/my.cnf
log-bin=/mydata/binlog/log-bin
chown -R mysql.mysql /mydata/binlog/
service mysqld restart
sed -i 's/MyISAM/InnoDB/' hellodb.sql
mysql -uroot -p < hellodb.sql
mysql
SHOW DATABASES;
SHOW TABLE STATUS FROM hellodb\G;
完全备份
mysqldump -uroot -p --databases hellodb --lock-all-tables --flush-logs --master-data=2 > /mydata/backups/hellodb.sql
grep "CHANGE MASTER" /mydata/backups/hellodb.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000003', MASTER_LOG_POS=107;
完全备份后新增一些数据
mysql
USE hellodb;
CREATE TABLE newtb(ID INT);
INSERT INTO newtb VALUES (1),(2),(3),(4),(5);
SELECT * FROM newtb;
导出二进制日志文件(完备后新增数据产生的日志)
mysqlbinlog --start-position=107 /mydata/binlog/log-bin.000003 > /mydata/backups/binlog.sql
scp /mydata/backups/*.sql root@192.168.130.64:/mydata/backups
在一台新的机器上恢复
service mysqld stop
vim /etc/my.cnf
log-bin=/mydata/binlog/log-bin
chown -R mysql.mysql /mydata/binlog/
service mysqld restart
mysql
SET SESSION sql_log_bin=0; (关闭二进制日志)
SOURCE /mydata/backups/hellodb.sql
SOURCE /mydata/backups/binlog.sql;
SET SESSION sql_log_bin=1;