Mysql backup and recovery

Mysql backup classification:
Mysql Data Backup:
Logical backup and physical backup
Mysql is classified by Dataset:
Full backup
Incremental Backup
Differential backup
These types are generally used in combination. For example, full backup can be performed once a week, Incremental Backup
Backup and differential backup can be performed once a day, but this depends on the business logic of the enterprise.
A proper choice will minimize the loss during data recovery.
Mysql is classified by server downtime:
Cold backup: the service is completely stopped.
Warm backup: the service is not closed, but during backup, other transactions only have query operations.
Hot Backup: the service is online and does not affect read/write (but it is difficult for non-transaction storage engines to implement hot backup,
Yes. LVM can be used for almost hot backup)
Backup:
1. Data Files
2. log files (transaction logs, binary logs)
3. Stored Procedures, stored functions, and triggers
4. Configuration File
Log category:
1. Transaction log:
Random IO conversion to sequential IO
Event replay (revocation) provides the foundation
2. Event transactions:
History time
MySQL Log File
1. the error log
Error message: the information generated when the server process starts or stops.
Will also record
Main configuration file startup error log,
Log_error =/var/log/mysqld. err (ensure that you have write permission for mysqld)
2. the binary log
Provide Incremental Backup
Time Point-based recovery
Replication architecture provides the foundation
Log_bin =/data/logs/binary/changelog (data file by default)
Log_bin_index =/data/logs/relay/binarylog defines the binary file index
Location
3. the general query log is usually not recommended.
General_log = ON | OFF
General_log_file =/PATH
4. the slow query log
Slow_query_log = ON
Slow_query_log_file =/PATH
Long_query_time = NUM defines the timeout time
5. relay log
By default, Mysql does not enable any logs, but the error log (
Error log)

Common mysql backup tools:
Mysqldump
SELECT INTO OUTFILE
Mk-parallel-backup
Ibbackup
Mysqlhostcopy (cold backup of MyISAM storage engine, which looks like hot backup)
 
Next, I will use my simple database to demonstrate the data backup and restoration process:
1. Use mysqldump for backup
Mysqldump backup is a logical backup;
The backup format is as follows;
# Mysqldump db_name [table_name]>/PATH
Of course, there are many options for mysqldump backup:
-- All-databases backup all databases
-- Events backup event
-- Routines backup storage function
-- Triggers backup trigger
-- Extended-insert extended insert backup
-- Skip-extended-insert: Disable extended backup insert
-- Lock-all-tables: the lock is required.
-- Flush-logs is required to refresh the log file to the disk.
-- Master-data = n {0 | 1 | 2} used for replication (2)
-- No-data: only back up the table structure in the database
-- Where "where clause" backs up specified data
Mysql> create database luowei;
Mysql> show databases; // view all DATABASES
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Luowei |
| Mysql |
| Ndodb |
| Test |
+ -------------------- +
Mysql> USE luowei;
Mysql> create table st (
-> Id int unique AUTO_INCREMENT,
-> Name VARCHAR (30 ));
Mysql> show tables; // view the table
+ ------------------ +
| Tables_in_luowei |
+ ------------------ +
| St |
+ ------------------ +
Mysql> insert into st (Name) VALUE ('A'), ('B'), ('C ');
Mysql> SELECT * FROM st; // displays the data in the table.
+ ---- + ------ +
| ID | Name |
+ ---- + ------ +
| 1 | a |
| 2 | B |
| 3 | c |
+ ---- + ------ +
This is the original information in my database, because the database and table will be deleted later, so let's take a look at it first.
The data in it will be verified for convenience;
[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. Because mysqldump uses logical backup
Some create and insert statements
Let's 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 is the entire statement that I just created to create a database, create a table, and insert data.
;
Next, we will perform database recovery:
[Root @ nfs ~] # Mysql-uroot-e "drop database luowei;"-p // Delete
Database
Enter password:
[Root @ nfs ~] # Mysql-uroot-e "show databases;"-p // view after deletion
Database
Enter password:
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Ndodb |
| Test |
+ -------------------- +
I deleted the luowei database and will restore it now.
[Root @ nfs ~] # Mysql-uroot-p </root/luoweidb
Enter password:
[Root @ nfs ~] # Mysql-e "show databases ;"
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Hellodb |
| Luowei |
| Mysql |
| Ndodb |
| Test |
+ -------------------- +
OK. The database is recovered. 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 |
+ ---- + ------ +
Okay. Well, well, of course, other parameters are not described in detail. If you need to use them,
You can use the HELP command for HELP.
The following describes how to enter a Mysql password for the local machine:
# Vim/root/. my. cnf
[Client]
User = root
Password = redhat
Save it. The mysql Command is directly used for the next database operation. You do not need to enter the password.
Now;
In the next experiment, I will not enter the password;

2. Back up data using SELECT INTO OUTFILE
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 data with IDs less than or equal to 2, you can back up data using this method.
Mysql> SELECT * into outfile '/tmp/st. bak' FROM st where id <= 2;
[Root @ nfs ~] # Cat/tmp/st. bak
1
2 B
We can see a text file, similar to a table, but this method only backs up the data itself, not
Back up the table structure. Therefore, you need to create a new table according to the structure and then
Restore
Mysql> create table mt LIKE st; // here I CREATE a new st-based TABLE
Table mt, and then restore the data that has just been backed up to the new table mt. This also applies to restoring to the original table.
Sorry, I'm a little lazy !!!
Mysql> load data infile '/tmp/st. bak' into table mt;
Mysql> SELECT * FROM mt;
+ ---- + ------ +
| ID | Name |
+ ---- + ------ +
| 1 | a |
| 2 | B |
+ ---- + ------ +
Okay. Let's summarize the two methods. First, use the data backed up by mysqldump
Logical backup is used to back up statements for database operations, which is easy to restore.
Select into outfile can be used to back up specified data and selectively back up data,
However, you need to create a table when restoring it.

3. LVM-based Snapshot Backup:
Procedure:
A. Ensure that the data file is on the LV
B. Apply a read lock to a table in the database.
C. take a snapshot of the LV of the data.
D. Release the read lock of the database.
E. Mount snapshot volumes to back up data files (for the InnoDB Storage engine, at the same time back up transaction logs
File)
F. delete a snapshot.
When using LVM for snapshot backup, the following conditions are met:
> Show variables like 'Log _ binlog'; ON
> Change show variables like 'sync _ binlog' to 1.
> Show variables like 'innodb _ support_xa 'is in the ON state.
LV Snapshot Backup
# Mysql
> Flush tables with read lock; // Add a READ LOCK
# Lvcreate-L 50 M-n mysnap-s/dev/myvg/mydata // create a snapshot
> Unlock tables; // UNLOCK
# Mount/dev/myvg/mysnap/mnt // mount the snapshot volume
# Cd/mnt/data
# Tar-jcf/root/mysql.tar.bz2 luowei/ibdata1 ib_logfile * mysql-
Bin. * // back up and compress data
# Umount/mnt // detach a snapshot
# Lvremove -- force/dev/myvg/mysnap // remove a 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 mysql-related processes
# Cd/mydata/data
# Tar xf/root/mysql.tar.bz2-C. // restore the backup to the mysql data file.
After logging on to Mysql, we will find that the original data still exists.

4. Use binary log files to restore time points
Display the currently used transaction file
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; // scroll the log file
View the transaction file in use and record the Position.
Because the binary log file only records data modification information
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 already performed a full backup, we can now back up all the data starting from 330
Data
# Mysqlbinlog -- start-position 330 mysql-bin.000007
>/Root/mysql. incr. SQL // backup the added binary file
Then we log on 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 on to the database,
You will find that data recovery is normal.

Author: "IT dream-qi-sharing"