MySQL backup restore, master-slave replication, primary master replication.

Database as an integral part of our daily work, the importance of its internal data is obvious, so data security is critical. To ensure data security, we must do a good job of data backup. Say a few simple MySQL backup tools and how to use them to make backups of your data.

A: MySQL comes with the tool---mysqldump, I believe you should also have an image: # Ls/usr/local/mysql/bi We will see this command.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/23/FE/wKioL1NKHLWj_YptAAJsHemp4qU290.jpg "title=" 1.jpg " alt= "Wkiol1nkhlwj_yptaajshemp4qu290.jpg"/>

Usage: mysqldump [options] [db_name [Tbl_name ...]

Common options:

--databases--Backing up the database, followed by the name of a database

--all-databases---Back up all databases

--lock-all-tables---> Request lock all tables and back up, write operations will be blocked

--lock-tables---> Lock a table

--single-transaction---> The ability to implement hot spares for InnoDB storage engines can be done without--lock-all-tables. MyISAM, Aria do Win Bei

--events---> Backup Event Scheduler Code

--routines---> Backup stored procedures, storage functions

--triggers---> Backup triggers

--flush-logs---> Scroll logs before and after a lock is requested

--MASTER-DATA=[0|1|2] records the binary log file name and the location where the event occurred. 0: Indicates no record, 1: Record as change master statement, 2: Record as comment change master statement

2: Use Mysqldum to implement backup mechanism:

First: Request lock the part to be backed up, if use--single-transaction can not request lock

Second: Scrolling binary log

Then: Select the library you want to back up

Log the contents of the binary log file, record the file name of the binary log that started the backup, and the end of its backup to that location.

3: Specific implementation steps: Do a full backup first.

First look at the original database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/23/FE/wKiom1NKHQXA568AAAEAfGqn67g688.jpg "title=" 2.jpg " alt= "Wkiom1nkhqxa568aaaeafgqn67g688.jpg"/>

Backup: # mysqldump--all-databases--lock-all-tables--flush-logs--master-data=2 >/tmp/mysql.sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/23/FE/wKioL1NKHO3yHbh3AACTc5hNkbs608.jpg "title=" 4.jpg " alt= "Wkiol1nkho3yhbh3aactc5hnkbs608.jpg"/>

To delete a database:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/23/FE/wKiom1NKHS-Rl_ztAAD_qeqsWTU462.jpg "title=" 3.jpg " alt= "Wkiom1nkhs-rl_ztaad_qeqswtu462.jpg"/>

Recovering a Database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/23/FE/wKiom1NKIELjp7pqAANGYPPoGNU108.jpg "title=" 5.jpg " alt= "Wkiom1nkieljp7pqaangyppognu108.jpg"/>

In fact, the backup of some databases is just a minor change in command.

--all-databases for--databases, follow the name of the database you want to back up, and space between the names of multiple databases. Example: # mysqldump--databases--lock-all-tables mydb test--flush-logs--master-data=2 >/tmp/mysql.sql

If our database uses the storage engine is InnoDB then we can not use--lock-all-tables this option, lock the table, just in order to not let us in backup when others are modifying the data data. Cause our backups to change. Instead, use--single-transaction. This way we can do the writing while we're backing up, and it doesn't affect our data backup.

# mysqldump--databases--single-transaction mydb test--flush-logs--master-data=2 >/tmp/mysql.sql

--fulsh-logs: The purpose of the scrolling day is to record the operation of data changes between backups and the next backup so that we can revert to the time of the last data change at a time when the database is damaged and restored.

We can demonstrate the mysqldump+ binary log file to do instant point restore data.

Database restore We've done this, based on the instant point restore data based on the binary log file.

First: View the file name and location of the current binary log file:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/23/FF/wKiom1NKOGjhWn8JAAF_jVbJ978654.jpg "title=" 2.jpg " alt= "Wkiom1nkogjhwn8jaaf_jvbj978654.jpg"/>

Second: We do some work to modify the data

MariaDB [hellodb]> CREATE DATABASE Linux;

MariaDB [hellodb]> use Linux;

MariaDB [linux]> CREATE TABLE student (Name char (), age int);

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/23/FF/wKioL1NKOFDC_ZjzAAHN9InDtM4236.jpg "title=" 1.jpg " alt= "Wkiol1nkofdc_zjzaahn9indtm4236.jpg"/>

Next: We manually scroll down the binary log

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/23/FF/wKiom1NKOIbCSVxOAAHUtlNpdSs517.jpg "title=" 3.jpg " alt= "Wkiom1nkoibcsvxoaahutlnpdss517.jpg"/>

Impersonation: Database corruption, we delete the database file just created

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/23/FF/wKioL1NKOHWCCc6oAAGjdJ2vagA791.jpg "title=" 4.jpg " alt= "Wkiol1nkohwccc6oaagjdj2vaga791.jpg"/>

Export, just before we do the data modification to delete the database before the binary log file content, because we have scrolled the binary log before the deletion of the database, the owner of the Mysqlbinlog command when the user does not need to specify the end location, only need to specify the start location

# Mysqlbinlog--start-position=525815/mydata/master/master-bin.000004 >/tmp/000004bin.sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/23/FF/wKiom1NKOLHSgdUbAAFvHk-wCPY508.jpg "title=" 5.jpg " alt= "Wkiom1nkolhsgdubaafvhk-wcpy508.jpg"/>

Finally: We begin to restore the data:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/23/FF/wKioL1NKOJySbpllAAOMt1VlYIY258.jpg "style=" float: none; "title=" 6.jpg "alt=" Wkiol1nkojysbpllaaomt1vlyiy258.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/23/FF/wKiom1NKOMaA5w0iAAKAZHkLMNI119.jpg "style=" float: none; "title=" 7.jpg "alt=" Wkiom1nkomaa5w0iaakazhklmni119.jpg "/>

We also generate binary log files when recovering data, but these are not required to be recorded in the binary log file, so we should restore the data at the time it is best to manually shut down the binary log file, and then restore the backup, the restoration is completed before the binary log.

Set session sql_log_bin=0;

Set session sql_log_bin=1;

Note: You can not use the--database option when backing up a single database with mysqldump, and if you do not use it, you will need to manually create this database file if the database file you restored does not exist when you restore it.

Two: lvm-snapshot: LVM Snapshot-based backup, but there is a premise that the transaction log must be on the same volume as the data file. This allows us to take a snapshot of the data file with a snapshot of the log file based on the same moment.

A snapshot-based approach to backup and restore:

First of all: we also need to do the request global lock, prohibit the user's write operation, prevent the data change.

Second: After locking the table, we want to scroll the binary log in order to facilitate instant point restore

Then: Make the name and location tag of the binary log file

Next: Take a snapshot

Finally: releasing the global lock

With these ideas, let's take a practical Walkthrough:

1: Request Global lock MariaDB [(None)]> FLUSH TABLES with READ LOCK;

2: Manual scrolling binary log file MariaDB [(none)]> flush logs;

3: Record the binary log file name and where the current event is located

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/24/00/wKiom1NKWrexD-7OAAMSjw8s6TU100.jpg "title=" 1.jpg " alt= "Wkiom1nkwrexd-7oaamsjw8s6tu100.jpg"/>

# mysql-e ' Show master status; ' >/tmp/000004.sql

4: Create snapshot lvcreate-l 100m-s-n mydata-snap-p r/dev/mydb/mydata

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/24/00/wKioL1NKWlfS9YEVAAF_A1beWsM864.jpg "title=" 2.jpg " alt= "Wkiol1nkwlfs9yevaaf_a1bewsm864.jpg"/>

5: Release Global lock MariaDB [(None)]> UNLOCK TABLES;

6: Mount the snapshot and back up the data

Mount/dev/myvg/mydata-snap/mnt-o ro

cp/mnt/*/tmp/mydb.back-a

7: Unmount The mount and delete the snapshot.

8: Stop service and simulate database corruption

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/24/00/wKioL1NKWpuwpOIGAAIC3nbl_qg894.jpg "title=" 3.jpg " alt= "Wkiol1nkwpuwpoigaaic3nbl_qg894.jpg"/>

9: Restore data and start the MySQL service

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/24/00/wKioL1NKXZXTkcR7AAcQYDTeIoQ675.jpg "title=" 4.jpg " alt= "Wkiol1nkxzxtkcr7aacqydteioq675.jpg"/>

10: The instant point restore based on the binary log file is consistent with mysqldump. We'll stop doing the demo.

Three: MySQL backup based on Xtrabackup

Yum installs Xtrabackup software because it has dependencies.

There are two points to note when using Innobackupex Backup:

1: To use an advanced backup mechanism, you must use a separate table space structure, so you need to define the startup stand-alone tablespace in the configuration file.

# VIM/ETC/MY.CNF

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/24/02/wKiom1NKtPOiuzPXAADS1Uukjpo387.jpg "title=" 1.jpg " alt= "Wkiom1nktpoiuzpxaads1uukjpo387.jpg"/>

2:innobackupex only support InnoDB, MyISAM just copy

Practical Walkthrough:

First: Create a backup file to store the directory

# Mkdir/mybackup

Second: Create an account with backup permissions for security reasons

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/24/02/wKioL1NKtNqh0t-_AADaBfw5rjo204.jpg "style=" float: none; "title=" 2.jpg "alt=" Wkiol1nktnqh0t-_aadabfw5rjo204.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/24/02/wKioL1NKtj6Ab-wNAAJtBcF2xrQ414.jpg "title=" 4.jpg " alt= "Wkiol1nktj6ab-wnaajtbcf2xrq414.jpg"/>

Then: Start Backup

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/24/02/wKioL1NKtsKT2wBLAASnP_lBmoY732.jpg "style=" float: none; "title=" 6.jpg "alt=" Wkiol1nktskt2wblaasnp_lbmoy732.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/24/02/wKiom1NKtu3QqvkqAAH7UNIeMCI016.jpg "style=" float: none; "title=" 7.jpg "alt=" Wkiom1nktu3qqvkqaah7uniemci016.jpg "/>

Last: Restore

This article is from "Welcome everyone to Guest" blog, please make sure to keep this source http://aperson.blog.51cto.com/3480661/1433018