Mysql-12-mysql backup and recovery, mysql-12-mysql backup

Mysql-12-mysql backup and recovery, mysql-12-mysql backup

1. Database Backup

Backup = copy and management to prevent data loss and record user operation records. The most effective backup is to back up the IT architecture.

Principles:

(1) databases should be backed up regularly. The backup cycle should be based on the recovery time that the application data system can afford, and the regular backup time should be carried out at the lowest system load. For important data, it is necessary to ensure that all losses in extreme circumstances can be recovered normally.

(2) After regular backup, you also need to perform regular Recovery tests to understand the correctness and reliability of the backup and ensure that the backup is meaningful and recoverable.

(3) determine whether to adopt Incremental Backup Based on System Requirements. Incremental Backup only needs to back up incremental data every day, which takes less time and reduces the system load. The disadvantage is that all the previously backed up data needs to be loaded during restoration. Recovery takes a long time.

(4) Make sure that the log-bin option is enabled for mysql. mysql requires binlog for full recovery or restoration based on time points.

(5) Remote Backup may be considered.

 

2. Logical backup and recovery

(1) logical backup: it can also be a file-level backup, which backs up data in the database as a text file, and the backup size depends on the file size. In addition, the text file can be transplanted to other machines, or even machines with different hardware structures.

• Use mysqldump command to generate insert statement backup

Syntax: mysqldump [arguments]> file_name. SQL

The help is as follows:

[Root @ cai ~] # MysqldumpUsage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..] OR mysqldump [OPTIONS] -- all-databases [OPTIONS] For more options, use mysqldump-help example: Back up all databases. The statement is as follows: [root @ cai dumpback] # mysqldump-uroot-p123456 -- all-databases>/tmp/dumpback/alldb. the syntax for SQL backup of some databases is as follows: [root @ cai dumpback] # mysqldump-uroot-p123456 -- databases aa>/tmp/dumpback/data_aa. SQL backs up tables in a database. The statement is as follows: [root @ cai dumpback] # mysqldump-uroot-p123456 aa cairui>/tmp/dumpback/aa_cairui. SQL: [root @ cai dumpback] # more aa_cairui. SQL

How can we ensure data backup consistency? There are two methods:

① Retrieve all data at the same time

For storage engines supported by transactions, such as innodb or bdb, you can use the-single-transaction test> test_backup. SQL option to control the entire backup process in the same transaction.

Example: [root @ cai dumpback] # mysqldump-uroot-p123456 -- single-transaction aa>/tmp/dumpback/aa_backup. SQL

② The data in the database is in the static state

The lock table parameter is not completed.

LOCK-TABLES: locks a database table each time. The default value of this parameter is true.

LOCK-ALL-TABLES: locks all tables at a time. It is applicable when dump TABLES are in different databases.

 

L generate plain text file backup in a specific format

① Select... To outfile from... Command

The parameters are as follows:

Fields escaped by ['name']: characters to be escaped in SQL statements

Fields terminated by: sets the delimiter between each two fields

Fields [optionally] enclosed by 'name': packed. The optionally numeric type is not packaged. Otherwise, it is fully packaged.

Lines terminated by 'name': Line separator, that is, the characters added at the end of each record.

For example, mysql> select * into outfile '/tmp/tb1.txt'-> fields terminated ', '-> optionally enclosed by' "'-> lines terminated by' \ N'-> from cai limit 50;

② Use mysqldump to export text.

This method can generate a text data and a corresponding database structure creation script. The main parameters are as follows:

-T, -- tab = name

Example: export the tb1 table of the sqoop Library (http://blog.itpub.net/31386161/viewspace-2130313/error handling method)

[root@cai ~]# mysqldump -uroot -p123456 -T /tmp sqoop tb1 --fields-enclosed-by=\" --fields-terminated-by=,

(2) logical Backup Recovery

[root@cai dumpback]# mysql -uroot -p123456 -D cai </tmp/dumpback/aa_cairui.sql

Restore the cairui table under aa to the cai database

② Restore plain text files

Use the mysqllimport tool for restoration. This tool can be used to restore the generation of txt and SQL files. Therefore, ensure that the table in the database corresponding to the txt file exists.

First, restore the table structure. The statement is as follows: [root @ cai tmp] # mysql-uroot-p123456-D aa </tmp/tb1. SQL to restore data. The statement is as follows: [root @ cai tmp] # mysqlimport-uroot-p123456 aa -- fields-enclosed-by = \ "-- fields-terminated-by =,/tmp/tb1.txt

 

3. Physical backup and recovery

Physical backup is faster than logical backup, which can be divided into the following two types:

A. Cold backup: This method is the most direct backup method. It is to stop the database service first, then cp data files, stop mysql during recovery, and restore files at the operating system level first, restart the mysql service and use mysqlbinlog to restore all binlogs since the backup. Although this method is simple and supports all engines, a major drawback is that you need to disable the database service. In most current information systems, long-term downtime is not allowed.

B. Hot Backup: Different Storage engine methods are also different

 

4. Specific implementation of various backup and recovery methods

(1) data backup and restoration using select into outfile

① Back up the data to be backed up

Mysql> select * from students; + ------ + | id | name | age | + ------ + | 1 | li | 36 | 2 | wang | 26 | 3 | cai | 12 | mysql> select * from students where age> 30 into outfile '/tmp/students.txt '; // back up the information of students older than 30

Students found. Txt is a text file and cannot be directly imported into the database. You need to use load data infile to restore the file to the mysql server, delete users older than 30, and the simulation data is damaged.

mysql> delete from students where age>30;mysql> load data infile '/tmp/students.txt' into table students;

(2) backup policy: Full backup + Incremental backup + binary log

① Back up the database completely first

Http://www.cnblogs.com/kerrycode/p/4565669.html (error solution)

[root@cai tmp]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --databases hellodb >/backup/hellodb_'data+%F'.sql

② Return to the mysql server to update data

mysql> use hellodb;Database changedmysql> create table tb1(id int);Query OK, 0 rows affected (0.02 sec)mysql> insert into tb1 values(1),(2),(3);Query OK, 3 rows affected (0.02 sec)Records: 3  Duplicates: 0  Warnings: 0

③ Check the location recorded in the full backup file first

[Root @ cai backup] # cat hellodb_data + % F. SQL | less -- CHANGE MASTER TO MASTER_LOG_FILE = 'cai-bin.000001 ', MASTER_LOG_POS = 107; (records the location of binary logs)

④ Return to the server

Mysql> show master status; displays the location of the binary log at this time. The position recorded in the backup file is the incremental part. + Bytes + ---------- + ------------ + bytes + | File | Position | percent | + ------------------ + ---------- + -------------- + percent + | cai-bin.000001 | 394 | + ---------------- + ---------- + -------------- + ------------------ + 1 row in set (0.00 sec)

⑤ Perform Incremental Backup

[root@cai backup]# mysqlbinlog --start-position=107 --stop-position=394 /application/mysql/data/cai-bin.000001 >/backup/hellodb_'data+$F_%H'.sql

6. Return to the server.

mysql> insert into tb1 values(4),(5);mysql> drop database hellodb;

7. Export the binary log

[Root @ cai backup] # mysqlbinlog -- start-position = 394/application/mysql/data/cai-bin.000001 to view the location of the binary log during the delete operation [root @ cai backup] # mysqlbinlog -- start -position = 394 -- stop-position = 587/application/mysql/data/cai-bin.000001>/tmp/hellodb. SQL (export binary logs)

Offline let mysql go offline

Mysql> set SQL _log_bin = 0; Disable binary log mysql> flush logs; scroll down logs

Crash simulation database destruction

mysql>drop database hellodb;

Restore starts to restore Data

# Mysql </backup/hellodb_2013-09-08. SQL import full backup file # mysql </backup/hellodb_2013-09-08_05. SQL import Incremental backup file # mysql 
(1. In a real production environment, data on the entire mysql server should be exported, rather than a single database. Therefore, use-all-databases.
(2) When exporting binary logs, you can directly copy the file, but note that the log is rolled down before backup.
(3) Use LVM snapshots to back up and restore Data in hot standby mode.
 
Important: Use xtrabackup for Backup Recovery
1. Advantages
(1) fast and reliable full backup
(2) transactions are not affected during the backup process.
(3) supports data stream, network transmission, and compression, so it can effectively save disk resources and network bandwidth.
(4) Data availability can be verified by automatic backup.
A. Install rabackup
[root@cai tools]# tar zxf percona-xtrabackup-2.4.2-Linux-x86_64.tar.gz [root@cai tools]# mv percona-xtrabackup-2.4.2-Linux-x86_64 /application/percona-xtrabackup2.4.2[root@cai tools]# ln -s /application/percona-xtrabackup2.4.2/ /application/xtrabackup[root@cai tools]#  echo "export PATH=\$PATH:/application/xtrabackup/bin" >> /etc/profile[root@cai tools]# . /etc/profile
B. Full backup
Mysql> create user 'backup '@' % 'identified by '000000'; create backup user mysql> grant all on *. * to 'backup '@' % '; Authorize mysql> flush privileges; Enable mysql> select * from cairui; + ------ + | id | name | + ------ + | 1 | li | 2 | wang | the test table is cairui under cai.

[Root @ cai backup] # innobackupex -- user = root -- password = 123456/data/backup/(backup to backup) 170511 16:46:14 completed OK! Success
 

Xtrabackup_checkpoints: Backup Type, backup status, And LSN (log serial number) range information.
Xtrabackup_binlog_info: the binary file currently in use on the mysql server and the location of the binary log events that have been backed up so far.
Xtrabackup_logfile: non-text files, xtrabackup your own log files.
Backup-my.cnf: mysql configuration in the data file during Backup.
Mysql> delete from bb where age> 30; the Incremental Backup code for xtrabackup is as follows: [root @ cai backup] # innobackupex -- user = root -- password = 123456 -- incremental/data/backup/-- incremental-basedir =/data/backup/2017-05-11_16-57-24