1 #-------------------------------------------------------------------------------
2 #3 # 企业案例恢复操作4 # 1.1 全备+恢复部分误删除的表(全备期间删除或者全备完成后删除)5 # Author：nod6 # Date:18-08-05
7 #-------------------------------------------------------------------------------
8
9
10
11
12 #-------------------------------------------------------------------------------
13 # 启动实例模拟备用数据库14 #-------------------------------------------------------------------------------
15 mysqld_safe --defaults-file=/data/3307/my.cnf &
16
17 [root@db01 tmp]# netstat -lnp | grep 330*
18 tcp 0 0 :::3306 :::* LISTEN 45894/mysqld19 tcp 0 0 :::3307 :::* LISTEN 46940/mysqld20 unix 2 [ACC] STREAM LISTENING 146100 46940/mysqld /data/3307/mysql.sock21
22
23 #-------------------------------------------------------------------------------
24 # 主库模拟数据25 #-------------------------------------------------------------------------------
26 [root@db01 tmp]# mysql -uroot -p123 -A27
28 mysql>flush logs;29
30 mysql>show master status;31 +------------------+----------+--------------+------------------+-------------------+
32 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
33 +------------------+----------+--------------+------------------+-------------------+
34 | mysql-bin.000012 | 120 | | | |
35 +------------------+----------+--------------+------------------+-------------------+
36 1 row in set (0.00sec)37
38
39 mysql> create database backup;40
41 mysql> create table backup.full select * fromworld.city;42
43 mysql> create table backup.full_1 select * from mysql.user;44
45 #-------------------------------------------------------------------------------
46 # 模拟主库每天定时进行全备47 #-------------------------------------------------------------------------------
48 [root@db01 backup]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
49 Warning: Using a password onthe command line interface can be insecure.50
51 [root@db01 backup]# ll52 total 328
53 -rw-r--r-- 1 root root 334770 Aug 5 22:19 full_2018-08-05.sql.gz
54
55
56
57 #-------------------------------------------------------------------------------
58 # 模拟主库数据发生变化59 # 故障前thur数据为239rows60 #-------------------------------------------------------------------------------
61 mysql> create table backup.thur select * fromworld.country;62 Query OK, 239 rows affected (0.11sec)63 Records: 239 Duplicates: 0 Warnings: 0
64
65 mysql> update backup.full set countrycode='CHN';66 Query OK, 3716 rows affected (0.04sec)67 Rows matched: 4079 Changed: 3716 Warnings: 0
68
69 mysql> delete from backup.full where id>200;70 Query OK, 3879 rows affected (0.05sec)71
72 #-------------------------------------------------------------------------------
73 # 模拟主库数据故障 删除thur表74 #-------------------------------------------------------------------------------
75 mysql> drop tablethur;76
77
78 #-------------------------------------------------------------------------------
79 # 开始恢复数据部分80 #-------------------------------------------------------------------------------
81
82
83 #-------------------------------------------------------------------------------
84 # 通过全备进行数据分析85 #-------------------------------------------------------------------------------
86 [root@db01 backup]# gzip -d full_2018-08-05.sql.gz87 [root@db01 backup]# ll88 total 1072
89 -rw-r--r-- 1 root root 1096129 Aug 5 22:19 full_2018-08-05.sql
90
91 #-------------------------------------------------------------------------------
92 # 2 分析full_2018-08-05.sql得出93 # CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=137208;94 # 说明是从mysql-bin.000012开始记录 start-position为13720895 # 3 通过show binlog events in 'mysql-bin.000012' 得出drop开始的位置为 536332
96 # 因而语句写为：97 # mysqlbinlog --start-position=137208 --stop-position=536332 /data/mysql/mysql-bin.000012 >/backup/inc.sql
98 #-------------------------------------------------------------------------------
99
100 [root@db01 backup]# mysqlbinlog --start-position=137208 --stop-position=536332 /data/mysql/mysql-bin.000012 >/backup/inc.sql
101 [root@db01 backup]# ll102 total 1608
103 -rw-r--r-- 1 root root 1096129 Aug 5 22:19 full_2018-08-05.sql
104 -rw-r--r-- 1 root root 546397 Aug 5 22:27 inc.sql
105
106
107
108 #-------------------------------------------------------------------------------
109 # 进入备用数据库 恢复数据110 #-------------------------------------------------------------------------------
111 [root@db01 tmp]# mysql -S /data/3307/mysql.sock112
113 set sql_log_bin=0;114 source /backup/full_2018-08-05.sql115 source /backup/inc.sql116
117 #-------------------------------------------------------------------------------
118 # 检查恢复后的数据119 #-------------------------------------------------------------------------------
120 mysql> select count(*) fromthur;121 +----------+
122 | count(*) |
123 +----------+
124 | 239 |
125 +----------+
126 1 row in set (0.00sec)127
128 #-------------------------------------------------------------------------------
129 # 将故障表导出130 #-------------------------------------------------------------------------------
131 [root@db01 backup]# mysqldump -S /data/3307/mysql.sock backup thur >/backup/thur.sql132
133 #-------------------------------------------------------------------------------
134 # 登录主库 导入数据thur.sql135 # 因为是导出的单表，一定要进入数据库后恢复 use backup
136 #-------------------------------------------------------------------------------
137 [root@db01 backup]# mysql -uroot -p123 -A138
139 mysql> use backup;140 Databasechanged141 mysql> source /backup/thur.sql;142 Query OK, 0 rows affected (0.00sec)143
144 Query OK, 0 rows affected (0.00sec)145
146 Query OK, 0 rows affected (0.00sec)147
148 Query OK, 0 rows affected (0.00sec)149
150 #-------------------------------------------------------------------------------
151 # 检查主库数据152 #-------------------------------------------------------------------------------
153 mysql> select count(*) fromthur;154 +----------+
155 | count(*) |
156 +----------+
157 | 239 |
158 +----------+
159 1 row in set (0.01 sec)
303
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
