Backup database using cp command

Keywords: MySQL MariaDB Database Oracle

cp command backup is a warm, full backup, the backup process needs to maintain a global read lock, this lock is more lethal to the database, it is likely to make the database hang in production environment, so it is not recommended to use in production environment.

1. Backup

1. Add a global read lock to the backup library: flush tables with read lock;

MariaDB [hse_whj]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

2. Create backup path: mkdir backup_whj;

[root@centos007 /]# mkdir backup_whj;

3. Copy the data file, pay attention to keep the source file attributes when copying: CP-A. /var/lib/mysql/hse_whj/*. /backup_whj/

Check the backup path for existing data files after copying: ls. /backup_whj

[root@centos007 /]# cp -a ./var/lib/mysql/hse_whj/* ./backup_whj/
[root@centos007 /]# ls ./backup_whj
db.opt  whj_a.frm  whj_a.ibd

4. Release the global lock on the backup library: unlock tables;

MariaDB [hse_whj]> unlock tables;
Query OK, 0 rows affected (0.10 sec)

2. Use backup to restore database

1. Delete data files to simulate data file corruption:

[root@centos007 /]# cd /var/lib/mysql/hse_whj
[root@centos007 hse_whj]# ls
db.opt  whj_a.frm  whj_a.ibd
[root@centos007 hse_whj]# rm -f *
[root@centos007 hse_whj]# ls
[root@centos007 hse_whj]# 

2. Copy backup files directly to the database directory:

[root@centos007 /]# cp ./backup_whj/* ./var/lib/mysql/hse_whj/
[root@centos007 /]# cd /var/lib/mysql/hse_whj
[root@centos007 hse_whj]# ls
db.opt  whj_a.frm  whj_a.ibd
[root@centos007 hse_whj]# 

3. Change the owner of the data folder and restart the database service:

[root@centos007 mysql]# chown -R mysql:mysql hse_whj
[root@centos007 mysql]# systemctl restart mysql

4. Re-login to the database to verify the data:

[root@centos007 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.14-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use hse_whj
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 [hse_whj]> select count(1) from WHJ_A;
+----------+
| count(1) |
+----------+
|        7 |
+----------+
1 row in set (0.06 sec)

MariaDB [hse_whj]> 

 

Posted by djopie on Thu, 16 Jan 2020 09:39:26 -0800