mysqldump全量備份+mysqlbinlog二進位日誌增量備份
1、mysqldump資料庫增量備份前提：
1>要開啟mysql log-bin日誌功能，若沒有開啟則，修改配置文件/etc/my.cnf，添加如下內容：
[mysqld]
datadir=/var/lib/mysql/data
log-bin=mysql-bin
server-id=1
重啟mysql
2>查看資料庫是否開啟了二進位日誌列印功能：
mysql> show variables like 'log_%'; +---------------------------------+---------------------+ | Variable_name | Value | +---------------------------------+---------------------+ | log_bin | ON | ......
log_bin為ON 則表示該功能已經開啟
3>存在一個完全備份，生產環境一般凌晨某個時刻進行全備
例如:mysqldump -uroot -p –default-character-set=gbk –single-transaction -F -B school |gzip > /backup/school_$(date +%F).sql.gz
InnoDB 表在備份時，通常啟用選項 –single-transaction 來保證備份的一致性
2、mysqldump全量備份+mysqlbinlog二進位日誌增量備份過程：
模擬00:00進行全量備份，誤刪資料庫，恢復
1>準備資料庫和表並向表中插入數據
mysql> create database school default character set utf8; //創建school資料庫
Query OK, 1 row affected (0.00 sec)
mysql> use school //切換至school資料庫
Database changed
mysql> create table student(
-> id int(10) not null comment '學號',
-> name varchar(16) not null comment '姓名',
-> sex varchar(16) not null comment '性別',
-> age tinyint(2) not null default '0' comment '學生年齡',
-> dept varchar(16) default 'null' comment '學生所在系別',
-> primary key (id))
-> ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
-> COLLATE=utf8_general_ci; //創建student表
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student values //向表中插入數據
-> (1,'張三','男',24,'電腦'),(2,'李四','女',27,'英語'),
-> (3,'王五','男',22,'電子商務'),(4,'趙六','女',20,'物流管理');
Query OK, 4 rows affected, 4 warnings (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 4
mysql> select * from student; //查看student表中的所有欄位的值
+----+--------+-----+-----+--------------+
| id | name | sex | age | dept |
+----+--------+-----+-----+--------------+
| 1 | 張三 | 男 | 24 | 電腦 |
| 2 | 李四 | 女 | 27 | 英語 |
| 3 | 王五 | 男 | 22 | 電子商務 |
| 4 | 趙六 | 女 | 20 | 物流管理 |
+----+--------+-----+-----+--------------+
4 rows in set (0.00 sec)
2>模擬凌晨00:00全備
[root@centos6 ~]# mkdir /backup //創建備份文件夾
[root@centos6 ~]# date -s "20200225" [root@centos6 ~]# mysqldump -uroot -p111111 --default-character-set=utf8 --single-transaction -F -B school -e | gzip > /backup/mysql_backup_`date +%F`.sql.gz
3>全備之後再向表中寫入數據
[root@centos6 ~]# mysql -uroot -p111111
......
mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into student values
-> (5,'tom','男',29,'化學'),(6,'jack','女',19,'法語'),
-> (7,'mimi','女',21,'化妝'),(8,'kaka','女',20,'酒店管理');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+-----+-----+--------------+
| id | name | sex | age | dept |
+----+--------+-----+-----+--------------+
| 1 | 張三 | 男 | 24 | 電腦 |
| 2 | 李四 | 女 | 27 | 英語 |
| 3 | 王五 | 男 | 22 | 電子商務 |
| 4 | 趙六 | 女 | 20 | 物流管理 |
| 5 | tom | 男 | 29 | 化學 |
| 6 | jack | 女 | 19 | 法語 |
| 7 | mimi | 女 | 21 | 化妝 |
| 8 | kaka | 女 | 20 | 酒店管理 |
+----+--------+-----+-----+--------------+
8 rows in set (0.00 sec)
4>模擬用戶破壞數據
mysql> drop database school; Query OK, 1 row affected (0.01 sec)
5>檢查全備後的所有binlog
[root@centos6 ~]# ll -nat /var/lib/mysql/data/ 總用量 21276 -rw-rw----. 1 27 27 5242880 2月 25 00:00 ib_logfile0 -rw-rw----. 1 27 27 10485760 2月 25 00:00 ibdata1 -rw-rw----. 1 27 27 416 2月 25 00:00 mysql-bin.000004 drwxr-xr-x. 4 27 27 4096 2月 25 00:00 . -rw-rw----. 1 27 27 76 2月 25 00:00 mysql-bin.index -rw-rw----. 1 27 27 1581 2月 25 00:00 mysql-bin.000003 drwx------. 2 27 27 4096 1月 12 18:34 school drwxr-xr-x. 3 27 27 4096 1月 12 18:31 .. -rw-rw----. 1 27 27 5242880 1月 12 18:31 ib_logfile1 -rw-rw----. 1 27 27 765307 1月 12 18:31 mysql-bin.000002 -rw-rw----. 1 27 27 19734 1月 12 18:31 mysql-bin.000001 drwx------. 2 27 27 4096 1月 12 18:31 mysql
6>立即刷新；再次查看binlog：根據時間點及前一個binlog可以知道發現問題時刻前binlog日誌
[root@centos6 ~]# mysqladmin -uroot -p111111 flush-logs [root@centos6 ~]# ll -nat /var/lib/mysql/data/ 總用量 21276 drwxr-xr-x. 3 27 27 4096 2月 25 00:05 . -rw-rw----. 1 27 27 95 2月 25 00:05 mysql-bin.index -rw-rw----. 1 27 27 106 2月 25 00:05 mysql-bin.000005 //鎖定該binlog -rw-rw----. 1 27 27 544 2月 25 00:05 mysql-bin.000004 -rw-rw----. 1 27 27 5242880 2月 25 00:03 ib_logfile0 -rw-rw----. 1 27 27 10485760 2月 25 00:03 ibdata1 -rw-rw----. 1 27 27 1581 2月 25 00:00 mysql-bin.000003 drwxr-xr-x. 3 27 27 4096 1月 12 18:31 .. -rw-rw----. 1 27 27 5242880 1月 12 18:31 ib_logfile1 -rw-rw----. 1 27 27 765307 1月 12 18:31 mysql-bin.000002 -rw-rw----. 1 27 27 19734 1月 12 18:31 mysql-bin.000001 drwx------. 2 27 27 4096 1月 12 18:31 mysql
7>備份出binlog至/backup目錄
[root@centos6 ~]# cp /var/lib/mysql/data/mysql-bin.* /backup/
8>恢復school資料庫：
(1)恢復全量備份
[root@centos6 data]# cd /backup/ [root@centos6 backup]# zcat mysql_backup_2020-02-25.sql.gz >mysql_backup_2020-02-25.sql [root@centos6 backup]# mysql -uroot -p111111 -e "create database school;" [root@centos6 backup]# mysql -uroot -p111111 school < /backup/mysql_backup_2020-02-25.sql [root@centos6 backup]# mysql -uroot -p111111 ...... mysql> use school Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from student; +----+--------+-----+-----+--------------+ | id | name | sex | age | dept | +----+--------+-----+-----+--------------+ | 1 | 張三 | 男 | 24 | 電腦 | | 2 | 李四 | 女 | 27 | 英語 | | 3 | 王五 | 男 | 22 | 電子商務 | | 4 | 趙六 | 女 | 20 | 物流管理 | +----+--------+-----+-----+--------------+ 4 rows in set (0.00 sec) //增量恢復成功
mysql> quit
(2)恢復增量備份
[root@centos6 backup]# cd /var/lib/mysql/data/ [root@centos6 data]# mysqlbinlog -d school mysql-bin.000004 > school-bin.sql [root@centos6 data]# mysqlbinlog -d school mysql-bin.000005 >> school-bin.sql [root@centos6 data]# vim school-bin.sql //打開school-bin.sql刪除drop sql語句 [root@centos6 data]# mysql -uroot -p111111 school < school-bin.sql ERROR 1007 (HY000) at line 65: Can't create database 'school'; database exists //因為school已經恢復至全量備份，所以此處提示school資料庫已經存在，不過不要緊，對存在的數據覆蓋 [root@centos6 data]# mysql -uroot -p111111 ...... mysql> use school Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from student; +----+--------+-----+-----+--------------+ | id | name | sex | age | dept | +----+--------+-----+-----+--------------+ | 1 | 張三 | 男 | 24 | 電腦 | | 2 | 李四 | 女 | 27 | 英語 | | 3 | 王五 | 男 | 22 | 電子商務 | | 4 | 趙六 | 女 | 20 | 物流管理 | | 5 | tom | 男 | 29 | 化學 | | 6 | jack | 女 | 19 | 法語 | | 7 | mimi | 女 | 21 | 化妝 | | 8 | kaka | 女 | 20 | 酒店管理 | +----+--------+-----+-----+--------------+ 8 rows in set (0.00 sec) //增量備份恢復 mysql> quit
9>總結
mysqlbinlog增量恢復方式
基於時間點恢復
(1)指定開始時間到結束時間 myslbinlog mysqlbin.000005 –start-datetime=’2020-02-25 01:10:46′ –stop datetime=’2020-02-25 03:10:46′ -r time.sql
(2)指定開始時間到文件結束 myslbinlog mysqlbin.000005 –start-datetime=’2020-02-25 01:10:46′ -d esen -r time.sql
(3)從文件開頭到指定結束時間 myslbinlog mysqlbin.000005 –stop-datetime=’2020-02-25 03:10:46′ -d esen -r time.sql
基於位置點的增量恢復
(1)指定開始位置到結束位置 myslbinlog mysqlbin.000005 –start-position=510 –stop-position=1312 -r pos.sql
(2)指定開始位置到文件結束 myslbinlog mysqlbin.000005 –start-position=510 -r pos.sql
(3)從文件開始位置到指定結束位置 myslbinlog mysqlbin.000005 –stop-position=1312 -r pos.sql
或者
在增量備份的時候，可以將二進位文件轉化成.sql語句，然後在刪除「drop」語句，修改.sql，再導入即可（上述恢復採用該方法）
3、實現自動化備份（資料庫小）
1>備份計劃：
(1)什麼時間：2:00
(2)對那些數據備份
(3)備份的文件放的位置
2>備份腳本：
[root@centos6 ~]# vim /mysql_back.sql
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=111111
if [ ! -d /backup ];then
mkdir -p /backup
fi
# 備份並截斷日誌
mysqldump -u${user} -p${pass} --lock-all-tables --routines --events --triggers -
-master-data=2 --flush-logs --all-databases > /$back_dir/$back_file
# 只保留最近一周的備份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;
3>測試：
[root@centos6 ~]# chmod +x /mysql_back.sql [root@centos6 ~]# /mysql_back.sql [root@centos6 ~]# ls /backup 2020-02-25_all.sql
4、複製數據文件
複製數據文件方式，可以使用cp或tar
(1)停止服務
[root@centos6 ~]# service mysqld stop
(2)備份數據文件
[root@centos6 ~]# mkdir /backup [root@centos6 ~]# tar cvf /backup/all.`date +%F`.tar.gz /var/lib/mysql/*
(3)將備份文件拷貝到目標伺服器
scp /backup/all.`date +%F`.tar.gz 192.168.129.142:/tmp/
(4)啟動服務，如有需要則恢復資料庫數據
