目录：
0.故障描述
1.二进制日志
2.全局备份
3.删除数据库
4.还原
0.故障描述
数据库物理损坏.jpg
描述：数据库数据量不大，每天凌晨两点做完全备份。现在，周二下午18:00发生磁盘损坏，数据库文件丢失。以下为还原测试
1.开启二进制日志
[root@node08 ~]# yum install mariadb-server mariadb -y
[root@node08 ~]# cat /etc/my.cnf
[mysqld]
log-bin=/data/logbin/mysql-bin
[root@node08 ~]# mkdir /data/logbin
[root@node08 ~]# chown mysql.mysql /data/logbin/
[root@node08 ~]# systemctl start mariadb
[root@node08 ~]# cd /data/logbin
[root@node08 logbin]# ll
total 1056
-rw-rw---- 1 mysql mysql 30334 Jul 24 00:35 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1038814 Jul 24 00:35 mysql-bin.000002
-rw-rw---- 1 mysql mysql 245 Jul 24 00:35 mysql-bin.000003
-rw-rw---- 1 mysql mysql 90 Jul 24 00:35 mysql-bin.index
二进制日志一定要和数据库文件分开存放
2.完全备份
[root@node08 ~]# mysqldump -A --master-data=2 > /data/all.sql
[root@node08 ~]# vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=7655; 这个位置很重要
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
......
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
添加表记录：
MariaDB [hellodb]> insert students (name,age)values('a',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30334 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 7887 |
+------------------+-----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> insert students (name,age)values('b',30);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30334 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 8119 |
+------------------+-----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
......
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | a | 20 | F | NULL | NULL |
| 27 | b | 30 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
3.数据库文件全部丢失
3.1故障发生
[root@node08 ~]# ls /var/lib/mysql/
aria_log.00000001 hellodb ib_logfile0 mysql performance_schema
aria_log_control ibdata1 ib_logfile1 mysql.sock test
[root@node08 ~]# rm -rf /var/lib/mysql/*
[root@node08 ~]# ls /var/lib/mysql/
检查：二进制日志文件仍在
[root@node08 logbin]# ll
total 1060
-rw-rw---- 1 mysql mysql 30334 Jul 24 00:35 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1038814 Jul 24 00:35 mysql-bin.000002
-rw-rw---- 1 mysql mysql 8119 Jul 24 00:54 mysql-bin.000003
-rw-rw---- 1 mysql mysql 90 Jul 24 00:35 mysql-bin.index
3.2重启数据库
[root@node08 ~]# systemctl restart mariadb
[root@node08 ~]# ls /var/lib/mysql/
aria_log.00000001 ibdata1 ib_logfile1 mysql.sock test
aria_log_control ib_logfile0 mysql performance_schema
[root@node08 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
4.开始恢复，首先确保别人无法访问
4.1禁止他人访问
可配置skip_networking或配置iptabales规则
4.2关闭二进制日志服务
MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30334 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 8138 |
| mysql-bin.000004 | 30334 |
| mysql-bin.000005 | 1038814 |
| mysql-bin.000006 | 245 |
+------------------+-----------+
6 rows in set (0.00 sec)
4.3导出二进制日志文件
[root@node08 logbin]# pwd
/data/logbin
[root@node08 logbin]# mysqlbinlog --start-position=7655 mysql-bin.000003 > /data/inc.sql
[root@node08 logbin]# mysqlbinlog mysql-bin.000004 >> /data/inc.sql
[root@node08 logbin]# mysqlbinlog mysql-bin.000005 >> /data/inc.sql
4.4开始还原
MariaDB [hellodb]> source /data/all.sql
Query OK, 1 row affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> source /data/inc.sql
Query OK, 1 row affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
4.5检查还原结果
MariaDB [mysql]> use hellodb
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
......
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | a | 20 | F | NULL | NULL |
| 27 | b | 30 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
4.6开启二进制日志
MariaDB [hellodb]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec
4.7开启用户访问
取消参数skip_networking和配置的iptabales规则
网友评论