MySQL Data backup and restore

Different classifications for backups:

Hot backup, warm backup and cold backup

Hot backup: Read, write not affected;

Warm backup: Only read operation can be performed;

Cold backup: Offline backup, read and write operations are aborted;

Physical and logical backups

Physical Backup: Copy the data file, what is the original data on the operating system, and what is the backup, applicable

For large, important, and fast-recovering scenarios where failures occur

Logical backup: Backup SQL statement, restore the time to perform a backup of the SQL statement, more flexible, but the recovery speed is more

Slow, suitable for small, easy-to-restore scenarios on other servers

Full backup, incremental backup, and differential backup;

Full backup: Back up all data;

Incremental backup: Backs up only data that has changed since the last full or incremental backup;

Differential backup: Backs up only data that has changed since the last full backup;

Backup tool:

1. Create a backup by copying the object-related files

Generally applies to the MyISAM engine table, as long as the. frm,. MYD,. Myi These files are copied and saved is equivalent to a backup

Steps:

Mysql>flush TABLES tb_name with READ LOCK #备份前对表进行只读锁定 [[email protected]/]cp/the/path/to/data/the/path/to/bac Kup Mysql>unlock TABLES #释放锁

This backup method does not apply to the InnoDB table, and when Innodb_file_per_table is set to 1 o'clock, the associated file for the table object is copied. IBD cannot achieve the purpose of backup because information such as the table structure, the owning database, and so on is still present in the system tablespace of InnoDB.

2. Using SQL statements for backup

Mysql>select * into OUTFILE '/path/to/somefile.txt ' from tb_name [WHERE clause];

3. Use mysqldump for logical backup

Backing up a single database/table

Mysqldump-uusername-p PASSWORD db_name [tb_name]>/path/to/backup/db_name.sql

Backing up multiple databases

Mysqldump-uusername-p PASSWORD--databases db1 DB2 >/path/to/backup/db1_db2.sql

Use--tab output txt files and SQL files to separate data from the build table statements

Mysqldump-uusername-p PASSWORD--tab=/path/to/backup db_name [Tb_name] #将数据库的全部对象或特定的表导出到backup目录下

Example 1: Backing Up the Tutors table under database Jiaowu

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/86/BD/wKiom1fJA7LyU_inAAARD41U0nk403.png "title=" Beifen5.png "alt=" Wkiom1fja7lyu_inaaard41u0nk403.png "/>

View/tmp/test2 Directory

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/BD/wKioL1fJA-7RV0BpAAAO0JkSAkA914.png "title=" Beifen.png "alt=" Wkiol1fja-7rv0bpaaao0jksaka914.png "/>

Example 2: Backing up the entire database

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/86/BB/wKioL1fI74-T-TF-AAAPbIOlE0g940.png "title=" Beifen1.png "alt=" Wkiol1fi74-t-tf-aaapbiole0g940.png "/>

Then view the/tmp/test directory

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/BC/wKiom1fI8m2zvQDyAAAKQs06oso347.png "title=" Beifen2.png "alt=" Wkiom1fi8m2zvqdyaaakqs06oso347.png "/>

It can be found that each table in the database generates two files of the same name,. txt stores the actual data in the table. SQL stored SQL statement (build table statement)

Take a look at the Tutors.txt file.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/86/BC/wKiom1fI8waxgoHsAAA0jxQRuhg530.png "title=" Beifen3.png "alt=" Wkiom1fi8waxgohsaaa0jxqruhg530.png "/>

As you can see, each record is printed on one line, the column values are separated by tab characters, and if you want to customize the format of the output, you can use the following options

--fields-terminated-by= ', '	Separating column values by commas
--fields-enclosed-by= ' "'	column values are enclosed by double quotation marks
--lines-terminated-by= ' \ '	Specify \ For line terminator, default to line feed

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/BC/wKiom1fJABXB-vjyAAATPV39slg540.png "title=" Beifen3.png "alt=" Wkiom1fjabxb-vjyaaatpv39slg540.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/86/BD/wKiom1fJAGnjlSWfAAA5IuZv7jA054.png "title=" Beifen4.png "alt=" Wkiom1fjagnjlswfaaa5iuzv7ja054.png "/>

As you can see, the exported Tutors.txt column values are separated by commas and enclosed in double quotation marks, which makes it easy to import data back into the database later

Note The main points:

When mysqldump does not use--tab, its exported SQL file has both an action statement and a table statement; When you use--tab, where txt files hold data, the SQL file has only the build table statement.

Restoring a Database

1. mysqldump Not using--tab option

Mysql>use Db_namemysql>source/path/to/backup/tb.sql #这样即可导入

2. mysqldump Use--tab option

1. Create a table with a. sql file

Mysql-uusername-p PASSWORD Db_name </path/to/backup/tb.sql

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/86/BD/wKiom1fJEAqDAxDvAAAQjb3NCu4425.png "title=" 2016-09-02 13-36-54 screen. png "alt=" Wkiom1fjeaqdaxdvaaaqjb3ncu4425.png "/>

2. Import the. txt file into the table by using the Load data infile statement in the database

Mysql>load DATA INFILE '/path/to/tb.txt ' into TABLE tb_name [parameters]

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/BD/wKioL1fJE6uQUjiMAAAUaOzW5bs198.png "title=" 2016-09-02 13-41-29 screen. png "alt=" Wkiol1fje6uqujimaaauaozw5bs198.png "/>

Create a Cold backup

1. View binary logs in use

Mysql>show Master status;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/86/BE/wKiom1fJF6GRExYRAAAclYHZ4gs434.png "title=" 1.png " alt= "Wkiom1fjf6grexyraaaclyhz4gs434.png"/>

2, the binary log scrolling

Mysql>flush logs

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/86/BE/wKiom1fJF8riqIghAAA1WM1twUI715.png "title=" 2.png " alt= "Wkiom1fjf8riqighaaa1wm1twui715.png"/>

3, close MySQL, backup data directory, open MySQL can

Create an incremental backup

1, the Binary log scrolling

2. Using Mysqlbinlog for backup

mysqlbinlog/path/to/binlog/mysql-bin.0000xx >/path/to/backup/0000xx.sql

(You can restore it directly from the source or MySQL command (mentioned above when restoring))

To put it bluntly, an incremental backup is to use Mysqlbinlog to back up the newly generated binary log to the specified directory after the last backup

This article is from the "a" blog, please make sure to keep this source http://lzs66.blog.51cto.com/9607068/1845574

MySQL Data backup and restore