利用nfs实现数据库的异地备份与恢复

利用nfs实现数据库的异地备份与恢复

版权声明：转载请标明出处 https://blog.csdn.net/roo

mysql（mariadb）数据库备份的重要性：

数据是一个数据库最重要的文件之一，当我们的数据库损坏时我们的很多程序都会受到影响，以至于影响公司的正常运转。现在越来越多的企业开始越来越重视数据库了，所以就要求我们对数据库进行备份，甚至要实现数据库表要放在远程服务器中。
本次实验利用nfs的数据共享来实现对数据库表放在远程和备份到远程的操作

nfs服务端

[root@localhost ~]# yum install nfs
[root@localhost ~]# systemctl start nfs
[root@localhost ~]# vim /etc/exports
/hh/ *(rw,sync,no_all_squash,no_root_squash)    #这个no_all_squash,no_root_squash 条件一定要写上，否则会出现数据库启动不了
[root@localhost ~]# exportfs -r
[root@localhost ~]# exportfs -v
/hh           	<world>(rw,sync,wdelay,hide,no_subtree_check,sec=sys,secure,no_root_squash,no_all_squash)
[root@localhost ~]# mount /dev/vgname/lvname /hh   #将目录hh先挂载到lvm中

数据库端和nfs客户端：

[root@centos7test ~]# yum install nfs-utils -y

[root@centos7test ~]# showmount -e 172.18.251.133    #这个ip是nfs服务端的ip
Export list for 172.18.251.133:
/hh * 
[root@centos7test ~]# yum install mariadb-server -y   
[root@centos7test ~]# vim /etc/my.cnf
[mysqld]
datadir=/mysql/data     #数据库表文件存放位置
log_bin=/mysql/logbin/master-log   #数据库二进制文件存放位置
[root@centos7test /]# mkdir /mysql/data   
[root@centos7test /]# mkdir /mysql/logbin/
[root@centos7test ~]# mount 172.18.251.133:/hh /mysql/   #利用nfs挂载 
[root@centos7test /]# systemctl start mariadb.service 

数据库备份;

[root@centos7test /]# mysql     #启动数据库 这个数据库没有设置账号密码，详细设置

账号密码请访问(https://blog.csdn.net/root__oo7/article/details/82817501)

MariaDB [(none)]> show databases;   #展示现在的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)
  #数据库的逻辑备份
[root@centos7test /]# mysqldump -A -F --single-transaction --master-data=2 > /mysql/fullbak_$(date +%F_%T).sql 
[root@centos7test /]#  ls /mysql/fullbak_2018-10-12_00\:58\:31.sql 
/mysql/fullbak_2018-10-12_00:58:31.sql

部分数据表损坏恢复：

MariaDB [(none)]> create database zhangxinglei;   #创建一个库，以区分是否还原数据库，
Query OK, 1 row affected (0.05 sec)

MariaDB [(none)]> show databases;   #展示备份后的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
| zhangxinglei       |
+--------------------+
6 rows in set (0.01 sec)
MariaDB [(none)]> 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.01 sec)

MariaDB [hellodb]> drop table students;    #模仿数据库表的损坏，我们直接删除
Query OK, 0 rows affected (0.02 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| teachers          |
| toc               |
+-------------------+
6 rows in set (0.01 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Liu Bang      |  45 | M      |
|   2 | Ying Zheng    |  94 | M      |
|   3 | Wu Zetian     |  77 | F      |
|   4 | Cheng Jisihan |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> insert into teachers values (5,'ma lao shi',28,'M');   #这个操作是我们正确的操作，还原数据库以后要保留的
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Liu Bang      |  45 | M      |
|   2 | Ying Zheng    |  94 | M      |
|   3 | Wu Zetian     |  77 | F      |
|   4 | Cheng Jisihan |  93 | F      |
|   5 | ma lao shi    |  28 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

[root@centos7test /]# rm -fr /mysql/data/*    #删除数据库存放的表文件
[root@centos7test /]# mysql < /mysql/fullbak_2018-10-12_00\:58\:31.sql   #还原基本数据库，这个数据库是没有设置账号密码的，如果你设置了账号密码那么就需要：mysql  -U 账号 -p密码 < /mysql/fullbak_2018-10-12_00\:58\:31.sql 
MariaDB [(none)]> show databases; #没有库zhangxinglei
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.02 sec)

MariaDB [(none)]> use hellodb;  #students这个表已经被我们还原了
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.01 sec)

MariaDB [hellodb]> select * from teachers;  #插入操作还没有还原
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Liu Bang      |  45 | M      |
|   2 | Ying Zheng    |  94 | M      |
|   3 | Wu Zetian     |  77 | F      |
|   4 | Cheng Jisihan |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> exit   #退出数据库
#查看以哪个二进制文件还原数据库
[root@centos7test /]# vim /mysql/fullbak_2018-10-12_00\:58\:31.sql 
CHANGE MASTER TO MASTER_LOG_FILE='master-log.000004', MASTER_LOG_POS=245;
[root@centos7test /]# mysqlbinlog --start-position=245 /mysql/logbin/master-log.000004 > /root/bin.sql
[root@centos7test /]# vim /root/bin.sql 
#删除下面的一行
DROP TABLE `students` /* generated by server */

[root@centos7test /]# mysql     
#以下就是还原的数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
| zhangxinglei       |
+--------------------+
6 rows in set (0.02 sec)

MariaDB [(none)]> 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 |
+-------------------+
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Liu Bang      |  45 | M      |
|   2 | Ying Zheng    |  94 | M      |
|   3 | Wu Zetian     |  77 | F      |
|   4 | Cheng Jisihan |  93 | F      |
|   5 | ma lao shi    |  28 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

遇到的问题：

删除数据库表时：

[root@centos7test ~]# rm -fr /mysql/data/*
rm: cannot remove ‘/mysql/data/mysql’: Directory not empty

#这个问题是你权限的问题 ，你可以设置acl权限，当然了 你也可以去nfs服务端去删除

启动数据库时

[root@centos7test mysql]# systemctl start  mariadb.service
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.


#这个是你的selinux没关闭 或者是你设置nfs服务端时
/hh/ *(rw,sync,no_all_squash,no_root_squash)
这个条件no_all_squash,no_root_squash 遗漏了

 

补充：

1）提示：我们的数据库表存放的文件一定要放在lvm中
2）其实这样设置数据库的我们还是感觉不够安全，那么我么也可以将二进制文件也放在一个远程的服务器上。