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 #启动数据库 这个数据库没有设置账号密码，详细设置
账号密码请访问()
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）其实这样设置数据库的我们还是感觉不够安全，那么我么也可以将二进制文件也放在一个远程的服务器上。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
