Mysql backup and recovery

Mysql backup classification:
Mysql data backup:
Logical backup, physical backup
Mysql is classified according to data set:
Full backup
Incremental backup
Differential backup
Generally these are used in combination, such as full backup It can be done once a week, and incremental backup and differential backup can be done once a day, but this depends on the business logic of the enterprise, so the appropriate choice will minimize the loss during data recovery.
mysql is classified according to whether the server is down:
Cold backup: the service is completely stopped
Warm backup: the service is not shut down, but during backup, other transactions are only query operations
Hot backup: the service is online, does not affect reading and writing (but not It is difficult to implement hot standby for transactional storage engines, but
you can use LVM to achieve almost hot standby function)
Things that need to be backed up:
1. Data files
2. Log files (transaction logs, binary logs)
3. Storage Procedure, stored function, trigger
4. Configuration file
Log classification:
1. Transaction log:
Convert random IO into sequential IO
Provide the basis for event replay (undo)
2. Event transaction:
Record historical time
MySQL Log files
1.The error log Error log
Error information, information generated when the server process starts or stops, when the master adds replication from it, it will also be recorded
The main configuration file startup error log,
log_error=/var/ log/mysqld.err (make sure you have write permission to mysqld)
2. The binary log binary log
Provides incremental backup
Point-in-time recovery
The replication architecture provides the basis
log_bin=/data/logs/binary/changelog ( Default is in the data file)
Log_bin_index=/data/logs/relay/binarylog Defines the index
location of the binary file
3.the general query log It is usually recommended not to open
general_log=ON|OFF
general_log_file=/PATH
4.the slow query log slow query log
slow_query_log=ON
slow_query_log_file=/PATH
long_query_time=NUM defines the timeout
5.relay log relay log
By default, Mysql does not enable any logs, but the error log is enabled in the mysqld script (the
error log)

Commonly used backup tools for mysql:
mysqldump
SELECT INTO OUTFILE
mk-parallel-backup
ibbackup
mysqlhostcopy (almost cold backup of MyISAM storage engine, the name looks like a hot backup)

Next I will use one of my A simple database demonstrates the process of data backup and restoration:
1. Use mysqldump to back up
The backup of mysqldump is a logical backup;
The backup format is as follows;
#mysqldump db_name [table_name] > /PATH
Of course mysqldump There are many options for backup:
--all-databases Backup all databases
--events backup events
--routines backup storage function
--triggers backup trigger
--extended-insert Backup extended insertion
--skip -extended-insert Turn off the insertion of backup extensions
--lock-all-tables Lock, this is necessary
--flush-logs Refresh the log file to the disk, it is also necessary
--master-data=n{ 0|1|2} Use (2) when doing replication
--no-data only backs up the table structure in the database
--where "WHERE CLAUSE" backs up the specified data
mysql> CREATE DATABASE luowei;
mysql> SHOW DATABASES;//View all databases
+--------------------+
| Database |
+--------------------- --------+
| information_schema |
| luowei                                                                                                 -+
mysql> USE luowei;
mysql> CREATE TABLE st (
-> ID INT UNIQUE AUTO_INCREMENT,
-> Name VARCHAR(30) );
mysql> SHOW TABLES; //View table
+--- ---------------+
| Tables_in_luowei |
+------------------+
| st |
+--- ---------------+
mysql> INSERT INTO st (Name) VALUE ('a'),('b'),('c');
mysql> SELECT * FROM st; //Display the data in the table
+----+------+
| ID | Name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
This is the original information in my database, because I will delete the database and tables later, so take a look at it first
The data inside will be convenient for verification later;
[root@nfs ~]# mysqldump -uroot -p luowei st > /root/st.sql //Start
backup table st, no parameters are used here
Enter password :
OK, the backup is complete at this time. Since mysqldump uses logical backup, what is backed up
are some create and insert statements
We can open it and take a look
[root@nfs ~]# cat /root/luoweidb | grep - v "^/" | grep -v "^$"
-- MySQL dump 10.11
--
-- Host: localhost Database: luowei
------------------ ----------------------------------------
-- Server version 5.0.77
--
-- Current Database: `luowei`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `luowei` /*!40100 DEFAULT
CHARACTER SET latin1 */;
USE `luowei`;
--
-- Table structure for table `st`
--
DROP TABLE IF EXISTS `st`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `st` (
`ID` int(11) NOT NULL auto_increment,
`Name` varchar(30) default NULL,
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
- - Dumping data for table `st`
--
LOCK TABLES `st` WRITE;
INSERT INTO `st` VALUES (1,'a'),(2,'b'),(3,'c');
UNLOCK TABLES;
-- Dump completed on 2011-09-05 8:00:08
See, here are the entire statements that I just did to create a database, create a table, and insert data
;
Next, Let's do database recovery:
[root@nfs ~]# mysql -uroot -e "DROP DATABASE luowei;" -p //Delete
database
Enter password:
[root@nfs ~]# mysql -uroot -e " SHOW DATABASES;" -p //View the deleted database
Enter password:
+--------------------------+
| Database      |
+--- ------------------+
| information_schema |
| mysql |
| ndodb |
test mysql -uroot -p < /root/luoweidb
Enter password:
[root@nfs ~]# mysql -e "SHOW DATABASES;"
+---------------- ---+
| Database |
+--------------------------+
| information_schema |
| hellodb |
| luowei |
| mysql |
| ndodb |
| test |
+--------------------------+
OK, the database has been restored, the next step is to check whether the information in the table is complete
[ root@nfs ~]# mysql -e "SELECT * FROM luowei.st" -uroot -p
Enter password:
+----+------+
| ID | Name |
+--- -+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
Ok, stay well together, haha, Of course, other parameters will not be introduced in detail. If you need to use them, you can use the HELP command to ask for help.
The following introduces a method that does not require entering the Mysql password on this machine:
#vim /root/.my.cnf
[client]
user = root
password = redhat
Save it, and use the mysql command directly next time you operate the database No need to enter the password anymore
;
I will not enter the password for the next experiment;

2. Use the SELECT INTO OUTFILE method for backup
or use the above table,
[root@nfs ~]# mysql -e "SELECT * FROM luowei.st"

+----+------+

| ID | Name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
If you want to back up those with ID less than or equal to 2, you can use this method to back up
mysql> SELECT * INTO OUTFILE '/tmp/st.bak' FROM st WHERE ID <=2;
[root@nfs ~]# cat /tmp/st.bak
1 a
2 b
We can see a text file, Similar to a table, but this method only backs up the data itself, not the table structure, so when restoring, you need to re-create a new table according to the structure, and then restore it after
mysql> CREATE TABLE mt LIKE st; //I Here we create a new
table mt based on the st table format, and then restore the newly backed up data to the new table mt. This is similar to restoring to the original table. Haha, let’s be lazy! ! !
mysql> LOAD DATA INFILE '/tmp/st.bak' INTO TABLE mt;
mysql> SELECT * FROM mt;
+----+-------+
| ID | Name |
+-- --+------+
| 1 | a |
| 2 | b |
+----+------+
ok, let’s summarize these two methods now, First, use mysqldump to back up the data using logical backup. What is backed up are some statements that operate on the database, and recovery is simple. Using SELECT INTO OUTFILE, you can back up specified data and selectively back up data.
But it’s a little more troublesome when restoring, and you have to create tables.

3. LVM-based snapshot backup:
Operation steps:
a. Make sure the data file is on the LV
b. Apply a read lock to the table in the database
c. Take a snapshot of the LV where the data is located
d. Release the database Read lock
e. Mount the snapshot volume and back up the data files (for the InnoDB storage engine, also back up the transaction log
file)

f. Delete the snapshot

The conditions for using LVM for snapshot backup are:
>SHOW VARIABLES LIKE 'log_binlog' ; is ON
>SHOW VARIABLES LIKE 'sync_binlog' changed to 1
>SHOW VARIABLES LIKE 'innodb_support_xa' is ON
LV snapshot backup
#mysql
>FLUSH TABLES WITH READ LOCK; //Add read lock
# lvcreate -L 50M -n mysnap -s /dev/myvg/mydata //Create snapshot
>UNLOCK TABLES; //Unlock
#mount /dev/myvg/mysnap /mnt //Mount snapshot volume
#cd /mnt/data
#tar -jcf /root/mysql.tar.bz2 luowei/ ibdata1 ib_logfile* mysql-
bin.* //Back up data and compress it
#umount /mnt //Uninstall snapshot
#lvremove --force / dev/myvg/mysnap //Remove snapshot

LV recovery
cd /usr/local/mysql
#chown -R mysql:mysql . //Modify permissions
#scripts/mysql_install_db --user=mysql --datadir=/ mydata/data/ //
Regenerate
#chown -R root .
#killall mysqld //Kill all processes related to mysql
#cd /mydata/data
#tar xf /root/mysql.tar.bz2 -C ./ //Restore the backup to the mysql data file
Then when we log in to Mysql, we will find that the original data still exists

4. Use binary log files to achieve point-in-time recovery
Display the transaction files currently in use
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------ ------+----------+--------------+------------------ -+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+------ --------+------------------+
| mysql-bin.000006 | 330 |
+-------- ----------+----------+-------------+------------- -----+
mysql> FLUSH LOGS; //Roll the log file
Check the transaction file being used, and then record the Position
Since the binary log file only records data modification information, every modification The corresponding
Position will change
Now I insert a row of data into the table
mysql> INSERT INTO st (Name) VALUE ('d');
mysql> INSERT INTO st (Name) VALUE ('E');
mysql> SHOW MASTER STATUS;
+------------------+----------+-------------- +------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------ +----------+--------------+------------------+
| mysql- bin.000007 | 553 | ----+------------------+
Since we have done a full backup before, we can now back up the
data starting from 330
# mysqlbinlog --start-position 330 mysql-bin.000007
>/root/mysql.incr.sql //Back up the added binary file
Then we log in to mysql at this time
mysql>DROP DATABASE luowei;
Then restore the database
# service mysqld stop
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
# cd /mydata/data/
# tar xf /root/mysql.incr.sql -C .
# mysql < ; /root/mysql.incr.sql
# service mysqld start
Log in to the database,
you will find that the data has returned to normal