The importance of mysql (mariadb) database backup:
Data is one of the most important files in a database. When our database is damaged, many of our programs will be affected, which will affect the normal operation of the company. Now more and more enterprises begin to pay more and more attention to the database, so we are required to back up the database, and even to realize that the database table should be placed in the remote server.
This experiment uses the data sharing of nfs to realize the operation of putting database tables in remote and backup to remote
nfs server:
[root@localhost ~]# yum install nfs [root@localhost ~]# systemctl start nfs [root@localhost ~]# vim /etc/exports /hh/ *(rw,sync,no_all_squash,no_root_squash) #If you do not want to start the database, you must write down the no ﹣ all ﹣ squash, no ﹣ root ﹣ squash condition [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 #Mount the directory hh to lvm first
Database side and nfs client:
[root@centos7test ~]# yum install nfs-utils -y [root@centos7test ~]# showmount -e 172.18.251.133 #This ip is the ip of the nfs server 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 #Database table file storage location log_bin=/mysql/logbin/master-log #Database binary storage location [root@centos7test /]# mkdir /mysql/data [root@centos7test /]# mkdir /mysql/logbin/ [root@centos7test ~]# mount 172.18.251.133:/hh /mysql/ #Mount with nfs [root@centos7test /]# systemctl start mariadb.service
Database backup;
[root@centos7test /]# mysql #The account and password are not set for starting the database. Please set them in detail //For account password, please visit (https://blog.csdn.net/root/oo7/article/details/82817501) MariaDB [(none)]> show databases; #Show the current database +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec) #Logical backup of database [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
Partial data table corruption recovery:
MariaDB [(none)]> create database zhangxinglei; #Create a library to distinguish whether to restore the database, Query OK, 1 row affected (0.05 sec) MariaDB [(none)]> show databases; #Show the database after backup +--------------------+ | 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; #Imitating the damage of database table, we directly delete it 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'); #This operation is our correct operation. We need to keep it after restoring the database 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/* #Delete table files stored in database [root@centos7test /]# mysql < /mysql/fullbak_2018-10-12_00\:58\:31.sql #Restore the basic database. This database has no account password. If you have set the account password, you need: MySQL - U account - p password < / MySQL / fullbak ߢ 10-12_ \: 58 \: 31.sql MariaDB [(none)]> show databases; #No library +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.02 sec) MariaDB [(none)]> use hellodb; #students This table has been restored by us 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; #Insert operation has not been restored +-----+---------------+-----+--------+ | 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 #Exit database #See which binary to restore the database with [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 #Delete the line below DROP TABLE `students` /* generated by server */ [root@centos7test /]# mysql #Here is the restored database 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)
Problems encountered:
When deleting a database table:
[root@centos7test ~]# rm -fr /mysql/data/* rm: cannot remove '/mysql/data/mysql': Directory not empty #This problem is about your permission. You can set acl permission. Of course, you can also go to the nfs server to delete it
When starting the database:
[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. #This is when your selinux is not turned off or when you set the nfs server /hh/ *(rw,sync,no_all_squash,no_root_squash) //This condition is missing
Add:
1) prompt: the files stored in our database table must be placed in lvm
2) in fact, we don't think it's safe to set the database like this, so we can also put the binary files on a remote server.