文章目錄
- 1. mysql備份方案
- 2. 備份工具mysqldump
- 3. 差異備份與恢複
1. mysql備份方案
資料庫備份方案：
- 全量備份：
- 全量備份是指将某一時間點的所有資料進行一次完全拷貝
- 特點：資料恢複快，備份時間長，占用空間大
- 增量備份：
- 增量備份是指在上一次全量備份或增量備份的基礎上，以後每次的備份隻需要備份與前一次相比增加或修改的檔案
- 特點：沒有重複的資料，備份時間短，恢複資料時必須按一定的順序進行
- 差異備份
- 差異備份是指在一次全量備份後，以後每一次備份都會與這一次全量備份對比，對增加或修改的檔案的備份。
- 在恢複資料時，我們隻需要恢複全量備份和最後一次差異備份，就能夠恢複全部資料
- 特點：恢複資料友善，相比全量備份占用空間較小
資料較少時建議使用全量備份
資料較多時建議将全量備份和差異備份組合使用
2. 備份工具mysqldump
文法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
常用的OPTIONS：
-uUSERNAME //指定資料庫使用者名
-hHOST //指定伺服器主機，請使用ip位址
-pPASSWORD //指定資料庫使用者的密碼
-P# //指定資料庫監聽的端口，這裡的#需用實際的端口号代替，如-P3307
備份與恢複示例：
因為root目錄中有.my.cnf配置檔案，所有後面的操作可以直接使用mysql進入資料庫，不用跟使用者和密碼
[[email protected] ~]# cat .my.cnf
[client]
user=root
password=123
[mysqldump]
user=root
password=123
- 備份整個資料庫
檢視資料：
[[email protected] ~]# mysql
mysql> use school;
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | jerry | 20 |
| 3 | zhangshan | 30 |
+----+-----------+------+
3 rows in set (0.00 sec)
mysql> exit
Bye
進行全量備份：
[[email protected] ~]# mysqldump --all-databases > all-202006121010.sql
[[email protected] ~]# ls
all-202006121010.sql
删除資料庫：
[[email protected] ~]# mysql
mysql> drop database school;
Query OK, 1 row affected (0.01 sec)
恢複全量備份：
[[email protected] ~]# mysql < all-202006121010.sql
檢視資料：
mysql> use school;
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | jerry | 20 |
| 3 | zhangshan | 30 |
+----+-----------+------+
3 rows in set (0.00 sec)
- 備份school資料庫中的student表
[[email protected] ~]# mysqldump school student > table-202006121010.sql
[[email protected] ~]# ls
table-202006121010.sql
恢複school資料庫中的student表
mysql> use school; //如果school庫不存在，則需要建立school庫才能恢複備份的表
mysql> source table-202006121010.sql
- 備份school庫
[[email protected] ~]# mysqldump --databases school > yu-202006121010.sql
[[email protected] ~]# ls
yu-202006121010.sql
恢複school庫
[[email protected] ~]# mysql < yu-202006121010.sql
3. 差異備份與恢複
- 開啟二進制日志功能
[[email protected] ~]# vim /etc/my.cnf
添加以下兩行内容
server-id = 1
log-bin = mysql.bin
重新開機服務：
[[email protected] ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
- 對資料庫進行全量備份
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | jerry | 20 |
| 3 | zhangshan | 30 |
+----+-----------+------+
3 rows in set (0.00 sec)
全量備份：
[[email protected] ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202006121010.sql
[[email protected] ~]# ls
all-202006121010.sql
- 在資料庫中新增和修改資料
mysql> insert into student(name,age) value('lisi',25);
Query OK, 1 row affected (0.00 sec)
mysql> update student set age = 30 where name = 'lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | jerry | 20 |
| 3 | zhangshan | 30 |
| 4 | lisi | 30 |
+----+-----------+------+
4 rows in set (0.00 sec)
- 模拟誤删庫
mysql> drop database school;
Query OK, 1 row affected (0.01 sec)
- 重新整理建立新的二進制日志
[[email protected] ~]# ls /opt/data |grep mysql.00*
mysql.000002
[[email protected] ~]# mysqladmin flush-logs
[[email protected] ~]# ls /opt/data |grep mysql.00*
mysql.000002
mysql.000003
- 恢複全量備份
[[email protected] ~]# mysql < all-202006121010.sql
mysql> use school;
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | jerry | 20 |
| 3 | zhangshan | 30 |
+----+-----------+------+
3 rows in set (0.00 sec)
- 檢查誤删部分id
mysql> show binlog events in 'mysql.000002';
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mysql.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql.000002 | 219 | Query | 1 | 293 | BEGIN |
| mysql.000002 | 293 | Table_map | 1 | 349 | table_id: 140 (school.student) |
| mysql.000002 | 349 | Write_rows | 1 | 395 | table_id: 140 flags: STMT_END_F |
| mysql.000002 | 395 | Xid | 1 | 426 | COMMIT /* xid=463 */ |
| mysql.000002 | 426 | Anonymous_Gtid | 1 | 491 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql.000002 | 491 | Query | 1 | 565 | BEGIN |
| mysql.000002 | 565 | Table_map | 1 | 621 | table_id: 140 (school.student) |
| mysql.000002 | 621 | Update_rows | 1 | 679 | table_id: 140 flags: STMT_END_F |
| mysql.000002 | 679 | Xid | 1 | 710 | COMMIT /* xid=464 */ |
| mysql.000002 | 710 | Anonymous_Gtid | 1 | 775 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql.000002 | 775 | Query | 1 | 873 | drop database school |
| mysql.000002 | 873 | Rotate | 1 | 916 | mysql.000003;pos=4 |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
//從上面的表中可以看到 drop database school 這條指令開始id為775
- 恢複差異備份
- 驗證資料是否恢複
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 15 |
| 2 | jerry | 20 |
| 3 | zhangshan | 30 |
| 4 | lisi | 30 |
+----+-----------+------+
4 rows in set (0.00 sec)
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)