Three methods of MySQL backup

Source: Internet
Author: User
Tags mysql backup

Three methods of MySQL backup

The main function of MySQL backup is to quickly, efficiently and securely solve problems in the case of hardware faults, software faults, natural disasters, hacker attacks, and human error, so as to avoid unnecessary losses.
What do I need to back up during MySQL backup?
1. Data to be used
2. Binary logs and InnoDB Transaction logs
3. Store code, such as stored procedures, stored functions, triggers, and time schedulers.
4. Server Configuration File

Note the following points during MySQL backup:
1. maximum data loss allowed
2. How long does it take to restore Data?
3. Data to be restored
4. After the backup is completed, you must perform a restoration test to test the backup availability.

Considerations for MySQL backup
1. How long does it take to hold the lock?
2. Backup process duration
3. Will the system service load be increased during backup?
4. Duration of the recovery process


MySQL Backup Type;
From the dataset perspective:

Full backup: Back up the entire dataset
Partial backup: only backup data subsets
Split by timeline:
Full backup: Back up the entire dataset
Incremental Backup: backs up the data changed after the last full backup or Incremental Backup
Differential backup: backs up the changed data after the last full backup
Note: Although Incremental Backup saves more disk space than differential backup, it is more difficult to restore incremental data than differential backup.

Based on whether the server business system can be online:
Hot Backup: read/write operations can be performed
Warm backup: Read operations are executable, but write operations are not executable
Cold backup: neither read/write operations can be performed.
Note: the InnoDB Storage engine supports hot backup and warm backup, while the MyISAM storage engine only supports warm backup and does not support hot backup.
Physical backup: directly copies data files for backup
Logical backup: data is exported from the database for backup. Logical backup has nothing to do with the storage engine. If the InnoDB Storage engine is used for backup, it can also be used on the MyISAM storage engine.

MySQL backup method:
1. Implement mysqldump + copy binlog: Use mysqldump to completely back up data, and copy events within a specified time range in binlog to implement Incremental backup.
2. Copy binlog with lvm2 +: Use lvm2 snapshots and command tools such as cp to achieve physical backup and full backup. Copy the event within the event scope in binlog to implement Incremental backup.
3. xtrabackup: A backup tool provided by Percona. InnoDB supports hot backup, full backup, and Incremental backup. MySIAM supports warm backup and full backup.

I. Back up data based on mysqldump + copy binlog
Prepare the environment:

Node 1 IP: 172.18.42.100
Node 2 IP: 172.18.42.111

1. Deploy Node 1
(1) install the mysql service, edit its binary file, and perform full backup
[Root @ localhost ~] # Yum install mysql-server-y # install the Mysql Service
[Root @ localhost ~] # Vim/etc/my. cnf # edit its configuration file
[Mysqld]
Innodb_file_per_table = ON
Log_bin = mysql-bin # enable binary files
[Root @ localhost ~] # Mysqldump-uroot -- lock-tables -- master-data = 2 -- databases Mydata> db1. SQL # Make a full backup before modifying the database
# -- Lock-tables: lock the specified database. We recommend that you use
# -- Lock-all-tables: locks all databases. We recommend that you use this function when backing up multiple databases.
# -- Master-data = 1 | 2
#1: The record is a change master to statement, which will not be commented out
#2: The record is a change master to statement, which will be commented out
# -- Databases: Specifies the database to be backed up. Multiple databases can be separated by spaces.
[Root @ localhost ~] # Less db1. SQL
-- Change master to MASTER_LOG_FILE = 'mysql-bin.000003 ', MASTER_LOG_POS = 1106; # record the binary log of the current backup and the location of the event in the binary log

(2) modify the data in the database and use "mysqlbinlog" to generate an incremental backup.
Mysql> select * from db1; # View data once before modifying data
+ ---- + ------------ + ------ +
| ID | Name Age
+ ---- + ------------ + ------ +
| 1 | BaiGu jing 100
| 2 | SunDa shen 200
| 3 | ZhuBa jie150
+ ---- + ------------ + ------ +
Mysql> insert into db1 (ID, Name, Age) values (4, 'rule', 999 );
Mysql> insert into db1 (ID, Name, Age) values (5, 'yudi ', 888 );
Mysql> select * from db1; # View data after modification
+ ---- + ------------ + ------ +
| ID | Name Age
+ ---- + ------------ + ------ +
| 1 | BaiGu jing 100
| 2 | SunDa shen 200
| 3 | ZhuBa jie150
| 4 | rulaai 999
| 5 | YuDi 888
+ ---- + ------------ + ------ +
[Root @ localhost ~] # Mysqlbinlog-uroot -- start-position = 1106/var/lib/mysql/mysql-bin.000003> binary_log # generate Incremental Backup
# -- Start-position: Specifies the pos of the event
[Root @ localhost ~] # Less binary_log # view Incremental Backup Files
Insert into db1 (ID, Name, Age) values (4, 'rule', 999) # records table modification operations
/*! */;
# At 1223
#160612 21:44:35 server id 1 end_log_pos 1339 Query thread_id = 9 exec_time = 0 error_code = 0
Set timestamp = 1465739075 /*! */;
Insert into db1 (ID, Name, Age) values (5, 'yudi ', 888)
/*! */;
# At 1339
#160612 21:45:41 server id 1 end_log_pos 1441 Query thread_id = 9 exec_time = 0 error_code = 0
Set timestamp = 1465739141 /*! */;

2. Deploy Node 2
(1) provide the mysql service and send the full backup file "db1. SQL" and Incremental backup file "binary_log" of Node 1 to node 2.
[Root @ localhost ~] # Yum install mysql-server-y
[Root @ localhost ~] # Scp db1. SQL binary_log root@172.18.42.111:/root

(2) node 2 Import "db1. SQL" and view its data
[Root @ localhost ~] # Mysql <db1. SQL
Mysql> select * from db1;
+ ---- + ------------ + ------ +
| ID | Name Age
+ ---- + ------------ + ------ +
| 1 | BaiGu jing 100
| 2 | SunDa shen 200
| 3 | ZhuBa jie150
+ ---- + ------------ + ------ +
# The data is not the data we modified

(3) If node 1 fails, you need to restore Node 2 to the data when Node 1 is currently modifying the database. In this case, you only need Node 2 to import the Incremental backup file "binary_log ".
[Root @ localhost ~] # Mysql <binary_log
Mysql> select * from db1; # After the Incremental backup is imported, the data of Node 2 is the same as that of Node 1 after database modification.
+ ---- + ------------ + ------ +
| ID | Name Age
+ ---- + ------------ + ------ +
| 1 | BaiGu jing 100
| 2 | SunDa shen 200
| 3 | ZhuBa jie150
| 4 | rulaai 999
| 5 | YuDi 888
+ ---- + ------------ + ------ +

For more details, please continue to read the highlights on the next page:

  • 1
  • 2
  • 3
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.