您好,登录后才能下订单哦!
Mysql备份的主要作用是当出现硬件故障、软件故障、自然灾害、******、人为误操作时能快速、高效、安全的解决问题,以免造成不必要的损失
Mysql备份时需要备份什么?
1、需要用到的数据
2、二进制日志、InnoDB事务日志
3、存储代码;例如:存储过程、存储函数、触发器、时间调度器
4、服务器的配置文件
Mysql备份时需要注意的要点:
1、能容忍最多丢失多少数据
2、恢复数据需要在多长时间内完成
3、需要恢复那些数据
4、做好备份之后,必须要做还原测试,用于测试备份的可用性
Mysql备份时需要考虑的因素
1、持锁需要多长时间
2、备份的过程时长
3、在备份时,是否会增加系统业务的负载
4、恢复过程的时长
Mysql备份的类型;
以数据集角度来划分:
完全备份:备份整个数据集
部分备份:只备份数据子集
以时间轴角度来划分:
完全备份:备份整个数据集
增量备份:备份最近一次完全备份或增量备份之后变化的数据
差异备份:备份最近一次完全备份之后变化的数据
注意:虽然增量备份比差异备份更节约磁盘空间,但是还原数据起来增量备份却比差异备份更麻烦
根据服务器业务系统能否在线来划分:
热备份:读写操作均可执行
温备份:读操作可执行,但写操作不可执行
冷备份:读写操作均不可执行
注意:InnoDB存储引擎即支持热备份,也支持温备份;而MyISAM存储引擎只支持温备份,不支持热备份
物理备份:直接复制数据文件进行备份
逻辑备份:从数据库中导出数据从而进行备份;逻辑备份与存储引擎无关,如果备份的是InnoDB存储引擎,用在MyISAM存储引擎上也是可以的
Mysql备份的方式:
1、mysqldump+复制binlog来实现:使用mysqldump来实现数据的完全备份,复制binlog中指定时间范围内的event来实现增量备份
2、lvm2+复制binlog:利用lvm2快照以及使用cp等命令工具来实现物理备份、完全备份,复制binlog中指点事件范围内的event来实现增量备份
3、xtrabackup:由Percona提供的备份工具;对InnoDB支持热备、完全备份和增量备份,MySIAM支持温备、完全备份
一、基于mysqldump+复制binlog来实现备份
准备环境:
节点1 | IP:172.18.42.100 |
节点2 | IP:172.18.42.111 |
1、部署节点1
(1)安装mysql服务,编辑其二进制文件并进行完全备份
[root@localhost ~]# yum install mysql-server -y ##安装Mysql服务 [root@localhost ~]# vim /etc/my.cnf ##编辑其配置文件 [mysqld] innodb_file_per_table = ON log_bin=mysql-bin ##启用二进制文件 [root@localhost ~]# mysqldump -uroot --lock-tables --master-data=2 --databases Mydata > db1.sql ##在未修改数据库之前做一次完全备份 ## --lock-tables:锁定指定的数据库,建议在备份单个数据库是使用 ## --lock-all-tables:锁定所有的数据库,建议在备份多个数据库时使用 ## --master-data=1 | 2 #1:记录为change master to语句,此语句不会被注释 #2:记录为change master to语句,此语句会被注释 ## --databases:指定需要备份的数据库,当有多个数据库时中间可用空格隔开 [root@localhost ~]# less db1.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1106; ##记录当前备份时的二进制日志,以及事件在二进制日志中所在位置
(2)修改数据库中的数据,并使用“mysqlbinlog”生成增量备份
mysql> select * from db1; ##未修改数据之前查看一次数据 +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 +----+------------+------+ mysql> insert into db1 (ID,Name,Age) values (4,'RuLai',999); mysql> insert into db1 (ID,Name,Age) values (5,'YuDi',888); mysql> select * from db1; ##修改数据之后查看一次 +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 | 4 | RuLai 999 | 5 | YuDi 888 +----+------------+------+ [root@localhost ~]# mysqlbinlog -uroot --start-position=1106 /var/lib/mysql/mysql-bin.000003 > binary_log ##生成增量备份 ##--start-position:指明事件的pos [root@localhost ~]# less binary_log ##查看增量备份文件 insert into db1 (ID,Name,Age) values (4,'RuLai',999) ##记录了修改表的相关操作 /*!*/; # at 1223 #160612 21:44:35 server id 1 end_log_pos 1339 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1465739075/*!*/; insert into db1 (ID,Name,Age) values (5,'YuDi',888) /*!*/; # at 1339 #160612 21:45:41 server id 1 end_log_pos 1441 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1465739141/*!*/;
2、部署节点2
(1)提供mysql服务,并将节点1的完全备份文件“db1.sql”和增量备份文件“binary_log”发送给节点2
[root@localhost ~]# yum install mysql-server -y [root@localhost ~]# scp db1.sql binary_log root@172.18.42.111:/root
(2)节点2导入“db1.sql",并查看其数据
[root@localhost ~]# mysql < db1.sql mysql> select * from db1; +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 +----+------------+------+ ##数据并不是我们修改之后的数据
(3)假设节点1挂掉了,需要让节点2恢复到节点1当前修改数据库时的数据,此时我们只需要节点2导入“binary_log”增量备份文件即可
[root@localhost ~]# mysql < binary_log mysql> select * from db1; ##导入增量备份之后,节点2的数据和节点1修改数据库之后的数据一样 +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 | 4 | RuLai 999 | 5 | YuDi 888 +----+------------+------+
二:基于lvm2+复制binlog来实现备份
准备环境:
节点1 | IP:172.18.42.100 |
节点2 | IP:172.18.42.111 |
1、部署节点1
(1)安装mysql服务,滚动日志并记录
[root@localhost ~]# yum install mysql-server -y [root@localhost ~]# service mysqld start [root@localhost ~]# mysql -e "show master status" > binary_file ##对于如何开启二进制日志小白我就不再说了 [root@localhost ~]# cat binary_file File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000004 208 ##记录了当前使用的二进制日志以及事件所在二进制日志中的pos [root@localhost ~]# service mysqld stop ##记录完日志之后把mysql服务关闭
(2)创建lvm快照,并挂载
[root@localhost ~]# pvcreate /dev/sda5 ##创建物理卷 Physical volume "/dev/sda5" successfully created [root@localhost ~]# vgcreate wxpp /dev/sda5 ##创建卷组,名为“wxpp” Volume group "wxpp" successfully created [root@localhost ~]# lvcreate -L +5G -n wxxp1 wxpp ##创建逻辑卷,大小为5G,名为“wxpp1” Logical volume "wxxp1" created. [root@localhost ~]# mke2fs -t ext4 /dev/wxpp/wxxp1 ##将逻辑卷格式化为“ext4”的文件系统 [root@localhost ~]# mount /dev/wxpp/wxxp1 /data/ ##将逻辑卷挂载至“/data”目录下 [root@localhost ~]# mkdir /data/mysql ##创建mysql数据库存储数据的目录,注意:此路径一定要和配置文件里的“datadir”指向的路径相同 [root@localhost ~]# chown mysql.mysql /data/mysql ##让mysql用户具有存储数据的权限 [root@localhost ~]# ll /data/ drwxr-xr-x 2 mysql mysql 4096 Jun 12 22:20 /data/mysql drwx------ 2 root root 16384 Jun 12 22:18 lost+found [root@localhost ~]# service mysqld start ##启动mysql服务 [root@localhost ~]# ll /data/mysql/ ##生成了大量的日志文件 total 21272 -rw-rw---- 1 mysql mysql 10485760 Jun 12 22:28 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:28 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:24 ib_logfile1 drwx------ 2 mysql mysql 4096 Jun 12 22:25 Mydata drwx------ 2 mysql mysql 4096 Jun 12 22:24 mysql -rw-rw---- 1 mysql mysql 19758 Jun 12 22:24 mysql-bin.000001 -rw-rw---- 1 mysql mysql 765307 Jun 12 22:24 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1190 Jun 12 22:28 mysql-bin.000003 -rw-rw---- 1 mysql mysql 106 Jun 12 22:28 mysql-bin.000004 -rw-rw---- 1 mysql mysql 76 Jun 12 22:28 mysql-bin.index drwx------ 2 mysql mysql 4096 Jun 12 22:24 test mysql> flush tables with read lock; ##给数据施加读锁,防止数据改变 [root@localhost ~]# lvcreate -L +1G -s -n binary_log /dev/wxpp/wxxp1 ##创建lvm快照,大小为1G,名为“binary_log” [root@localhost ~]# mount /dev/wxpp/binary_log /mnt/ ##将快照挂载至“/mnt”目录下 [root@localhost ~]# ll /mnt/mysql/ total 21272 -rw-rw---- 1 mysql mysql 10485760 Jun 12 22:28 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:28 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:24 ib_logfile1 drwx------ 2 mysql mysql 4096 Jun 12 22:25 Mydata drwx------ 2 mysql mysql 4096 Jun 12 22:24 mysql -rw-rw---- 1 mysql mysql 19758 Jun 12 22:24 mysql-bin.000001 -rw-rw---- 1 mysql mysql 765307 Jun 12 22:24 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1190 Jun 12 22:28 mysql-bin.000003 -rw-rw---- 1 mysql mysql 106 Jun 12 22:28 mysql-bin.000004 -rw-rw---- 1 mysql mysql 76 Jun 12 22:28 mysql-bin.index drwx------ 2 mysql mysql 4096 Jun 12 22:24 test mysql> unlock tables; ##释放写锁
(3)生成增量备份
mysql> select * from db1; ##未修改前查看一次数据 +----+-------+------+ | ID | Name Age +----+-------+------+ | 1 | MaGe 100 | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+ mysql> delete from db1 where ID=1; mysql> select * from db1; +----+-------+------+ | ID | Name Age +----+-------+------+ | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+ [root@localhost ~]# mysqlbinlog --start-position=208 /data/mysql/mysql-bin.000004 > backup.sql ##生成增量备份文件
2、部署节点2
(1)将节点1快照下“/mnt/mysql“目录下的文件以及增量备份文件发送给节点2;
[root@localhost ~]# scp -r /mnt/mysql/* backup.sql root@172.18.42.111:/data/mysql/ [root@localhost ~]# chown -R mysql.mysql /data/mysql/ ##将权限更改为“mysql” [root@localhost ~]# ll /data/mysql/ total 21272 -rw-r----- 1 mysql mysql 10485760 May 9 22:33 ibdata1 -rw-r----- 1 mysql mysql 5242880 May 9 22:33 ib_logfile0 -rw-r----- 1 mysql mysql 5242880 May 9 22:33 ib_logfile1 drwx------ 2 mysql mysql 4096 May 9 22:33 Mydata drwx------ 2 mysql mysql 4096 May 9 22:33 mysql -rw-r----- 1 mysql mysql 19758 May 9 22:33 mysql-bin.000001 -rw-r----- 1 mysql mysql 765307 May 9 22:33 mysql-bin.000002 -rw-r----- 1 mysql mysql 1190 May 9 22:33 mysql-bin.000003 -rw-r----- 1 mysql mysql 208 May 9 22:33 mysql-bin.000004 -rw-r----- 1 mysql mysql 76 May 9 22:33 mysql-bin.index drwx------ 2 mysql mysql 4096 May 9 22:33 test
(2)启动mysql服务,并查看其数据库
[root@localhost ~]# service mysqld start mysql> select * from db1; ##并不是修改之后的数据 +----+-------+------+ | ID | Name Age +----+-------+------+ | 1 | MaGe 100 | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+
(3)假设节点1因为人为误操作导致数据库崩了,此时让节点二导入增量备份“backup.sql”即可
[root@localhost ~]# mysql < backup.sql mysql> select * from db1; +----+-------+------+ | ID | Name Age +----+-------+------+ | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+
(4)移除lvm快照
[root@localhost ~]# umount /mnt [root@localhost ~]# lvremove /dev/wxpp/binary_log Logical volume "binary_log" successfully removed
当有大量数据库需要复制时,此时使用快照是最快、最有效的方式,防止对数据库施加过长时间的读锁,以免造成不必要的损失
三、基于xtrabackup命令工具来实现备份恢复
准备环境
节点1 | IP:172.18.42.100 |
节点2 | IP:172.18.42.111 |
1、部署节点1
(1)安装“xtrabackup”工具
[root@node0 ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y ##下载rpm包之后安装 [root@node0 ~]# rpm -ql percona-xtrabackup ##查看percona-xtrabackup生成的文件 /usr/bin/innobackupex ##我们主要使用这个命令 /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/share/doc/percona-xtrabackup-2.3.2 /usr/share/doc/percona-xtrabackup-2.3.2/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz
(2)首先确保存储引擎为innodb,且“innodb_file_per_table”为ON状态
MariaDB [Mydata]> show table status\G; Name: db1 Engine: InnoDB MariaDB [Mydata]> show global variables like "%innodb%"; innodb_file_per_table ON ##确保每个表使用单独的表空间,如果不指明,则所有数据库中的所有表将放在同一空间下
(3)实现整个数据库备份
[root@node0 ~]# innobackupex --user=root /backup/ ##对整个数据库做一次备份,放在“/backup”目录下;注意:如果登录mysql需要密码,则需要指明密码 [root@node0 ~]# ll /backup/2016-04-20_10-34-41/ total 18460 -rw-r----- 1 root root 385 Apr 20 10:34 backup-my.cnf -rw-r----- 1 root root 18874368 Apr 20 10:34 ibdata1 drwx------ 2 root root 47 Apr 20 10:34 Mydata drwx------ 2 root root 4096 Apr 20 10:34 mysql drwx------ 2 root root 4096 Apr 20 10:34 performance_schema drwx------ 2 root root 19 Apr 20 10:34 test -rw-r----- 1 root root 21 Apr 20 10:34 xtrabackup_binlog_info -rw-r----- 1 root root 113 Apr 20 10:34 xtrabackup_checkpoints -rw-r----- 1 root root 457 Apr 20 10:34 xtrabackup_info -rw-r----- 1 root root 2560 Apr 20 10:34 xtrabackup_logfile
(4)修改数据,生成增量备份
MariaDB [Mydata]> select * from db1; ##未修改数据之前查看一次 +------+ | id +------+ | 1 | 2 | 3 +------+ MariaDB [Mydata]> insert into db1 values (100),(200); ##插入两个“ID” MariaDB [Mydata]> select * from db1; +------+ | id +------+ | 1 | 2 | 3 | 100 | 200 +------+ [root@node0 ~]# innobackupex --incremental /backup --incremental-basedir=/backup/2016-04-20_10-34-41/ ##生成增量备份 ##--incremental:把增量备份存储在哪个路径下 ##--incremental-basedir:基于谁来做增量备份 [root@node0 ~]# ll /backup/ drwx------ 6 root root 4096 Apr 20 10:34 2016-04-20_10-34-41 ##完全备份文件 drwx------ 6 root root 4096 Apr 20 10:41 2016-04-20_10-41-42 ##增量备份文件
(5)对完全备份数据做出整理,并把增量备份导入到完全备份中
[root@node0 ~]# innobackupex --apply-log --redo-only /backup/2016-04-20_10-34-41/ ##对完全备份做出整理 [root@node0 ~]# innobackupex --apply-log --redo-only /backup/2016-04-20_10-34-41/ --increment-dir=/backup/2016-04-20_10-41-42 ##将增量备份导入到完全备份中
2、部署节点2
(1)安装xtrabackup工具
[root@node0 ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y
(2)将节点1上已经导入增量备份的完全备份目录发送给节点2,如果节点1挂了,我们可以基于导入增量备份的完全备份来恢复
[root@node0 ~]# scp -r /backup/2016-04-20_10-34-41/ root@172.18.42.201:/root [root@node1 ~]# innobackupex --copy-back 2016-04-20_10-34-41/ ##导出数据 [root@node1 ~]# chown -R mysql.mysql /data/ ##更改权限为mysql [root@node1 ~]# ll /data/mysql/ total 18448 drwx------ 2 root root 6 Apr 20 11:02 2016-04-20_10-34-41 -rw-r----- 1 root root 18874368 Apr 20 11:02 ibdata1 drwx------ 2 root root 47 Apr 20 11:02 Mydata drwx------ 2 root root 4096 Apr 20 11:02 mysql drwx------ 2 root root 4096 Apr 20 11:02 performance_schema drwx------ 2 root root 19 Apr 20 11:02 test -rw-r----- 1 root root 23 Apr 20 11:02 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 457 Apr 20 11:02 xtrabackup_info [root@node1 ~]# systemctl start mariadb.service MariaDB [Mydata]> select * from db1; +------+ | id +------+ | 1 | 2 | 3 | 100 | 200 +------+
问题小结:
1、当使用mysqldump+复制binlog方法时,最好不要将数据文件和二进制文件放在同一磁盘上,且需要对二进制文件作出备份
2、使用lvm2+复制binlog方法时,在创建快照的同时,最好对数据库施加读锁,以免数据发生改变,同时要确保文件的属主、属组为mysql
3、在使用xtrabackup方法时,整个过程中mysql存储数据的目录必须要和配置文件里面的“datadir”“相同,同时要确保文件的属主、属组为mysql
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。