Backup and restoration of MySQL server in Linux

Physical backup and restoration of LVM-based mysql Databases: in this case, our msyql database must be installed on a logical volume. The following method can achieve almost hot backup (

Physical backup and restoration of LVM-based mysql Databases: in this case, our msyql database must be installed on a logical volume. The following method can achieve almost hot backup (

I. Physical backup and restoration of LVM-based mysql databases:

In this case, our msyql database must be installed on a logical volume. The following method can achieve almost hot backup (the reason is that when we create a snapshot, we cannot write data to the database), it should be achieved through the LVM snapshot.
1. Full backup
Enter the database to apply a read lock to all tables. In this case, the data in the database can only be read and cannot be written.
Mysql> flush tables with read lock;
We use the method of rolling logs (that is, creating a new binary log) to achieve instant point recovery:
Mysql> flush logs;
View the name and position of the current binary log for instant point recovery:
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000005 | 107 |
+ ------------------ + ---------- + -------------- + ------------------ +

Mysql> \ q
Our mysql data files are stored on the/dev/myvg/lv_mydata logical volume. We want to create a snapshot of this logical volume for backup. There is no write operation during this step, which is why we read the lock in the database:
# Lvcreate-L 100 M-n data-snap-s-p r/dev/myvg/lv_mydata
Access the mysql database to unlock the database, and our database can be accessed normally (and after that, all operations that affect the database will be recorded in the binary log, all: full backup + binary log = all our data ):
Mysql> unlock tables;
Follow these steps to back up the database:
# Mkdir/snap
# Mount/dev/myvg/data-snap/snap
# Tar jcf/tmp/all_data-'date ready using f-0000h-0000m-0000s'.tar.bz2/snap/
This generates our backup file: all_data-2012-03-18-09-39-06.tar.bz2
# Umount/snap
# Rm-rf/snap
# Lvremove/dev/myvg/data-snap
Okay, our database file is backed up successfully.

1. Backup and Restore
When the database fails, we can restore the backup as follows.
To simulate a fault, I deleted a database.
Mysql <drop database jiaowu;

We can use the above backup to restore:
To restore the full backup, you must stop the mysql server:
# Service mysqld stop
Copy the newly created binary log file during backup, and use it for Instant recovery:
# Cp/mydata/data/mysql-bin.000005/tmp
# Cd/tmp
# Tar xf all_data-2012-03-18-09-39-06.tar.bz2
Remove our original database file:
# Mv/mydata/data. bak
Put the database we backed up in the/mydata directory:
# Mv snap/data/mydata
If the owner of the group changes, the owner group is changed to mysql:
# Cd/mydata
# Chown mysql: mysql/mydata/data
# Chown mysql: mysql/mydata/data /*
Restart the service, and our full backup will be restored:
# Service mysqld start

Even if the point is restored:
# Mysqlbinlog/tmp/mysql-bin.000005
As shown in the following figure:
# At 97404
#120318 10:07:13 server id 1 end_log_pos 97489 Query thread_id = 454 exec_time = 0 error_code = 0
Set timestamp = 1332036433 /*! */;
Drop database jiaowu
/*! */;
# At 97489
#120318 10:07:36 server id 1 end_log_pos 97508 Stop
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;
After, I will delete the database jiaowu, which is a fault I have simulated and cannot be recovered. Therefore, we need to define the termination point 97404:

# Mysqlbinlog -- stop-position = 97404/tmp/mysql-bin.000005>/tmp/jiaowu. SQL
First, temporarily close the binary file to prevent the recovery instant point operation being recorded in the binary log:
Mysql> set SQL _log_bin = 0;
Load this file to restore to the immediate point:
Mysql> source/tmp/jiaowu. SQL



Ii. Use Xtrabackup for MySQL backup

I. Installation

1. Introduction
Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, Xtrabackup is the only open-source tool in the world that can perform hot backup for innodb and xtradb databases. Features:
(1) The backup process is fast and reliable;
(2) The backup process will not interrupt ongoing transactions;
(3) Saving disk space and traffic based on compression and other functions;
(4) automatic backup check;
(5) Fast Restoration;

2. Installation
The latest software is available. This article is based on the RHEL5.4 system. Therefore, you can directly download the rpm package of the corresponding version to install it. here we will not describe the process.