Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief description of the main backup methods of mysql. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the main backup methods of mysql can bring you some practical help.

The main backup methods of Mysql

Online backup how to ensure the consistency of the backed up data, use the logical volume snapshot function to achieve, lock for a few seconds

Redirect the blog log to a file and import it into the database with source, because a new blog log is generated at each step of the operation

Physical backup:

Directly copy database files, suitable for large database environment, the general structure of lvm snapshot implementation, the disadvantage is that it can not be restored to heterogeneous systems such as Windows

Logical backup

The backup is the sql statements executed by table building, database building, insertion and other operations, which is suitable for small and medium-sized databases and is relatively inefficient.

Implementation of logical backup by Mysqldump

Grammar

# mysqldump-h CVM-u user name-p password database name > backup file. Sql

Options for database name

-- all-databases all libraries

School database name

Table stu _ info T1 of School stu_info T1 school database

-- databases bbs test mysql multiple databases

Example:

Single library backup:

# mysqldump-uroot-p123 bbs > bbs.sql

# mysqldump-uroot-p123 bbs table1 table2 > bbs table1 table2.sql

Multi-database backup

# mysqldump-uroot-p123-databases bbs test mysql > bbs_test_mysql,sql

Back up all libraries

# mysqldump-uroot-p123-- all-databases > all.sql

# mysqldump-uroot-p123-all-databses-- lock-all-tables > / tmp/all2.sql

Backup / restore case:

In case 1, if the database is damaged, the machine is broken.

Procedure:

Backup

1#mysqldump-uroot-p123-- all-databases-- lock-all-tables > / backup/ `date +% F` _ all.sql

2mysql-uroot-P123-e 'flush logs' / / truncate and generate a new binlog

3 insert data / / simulate the normal operation of the CVM

4mysql > set sql_log_bin=0

Mysql > drop database bbs / / simulate CVM corruption

Recovery: (clean environment, new machine)

1mysql > set sql_log_bin=0

Mysql > source / backup/2014-02-13 all.sql / / restore the last full backup

2mysqlbinlog Last binlog | mysql-uroot-p 123 / / restore the last binlog file

Case 2 if deleted by mistake

Backup

1#mysqldump-uroot-p123-- all-databases-- lock-all-tables > / backup/ `date +% F` _ all.sql

2mysql-uroot-P123-e 'flush logs' / / truncate and generate a new binlog

3 insert data / / simulate the normal operation of the CVM

4Mysql > drop table db1.t1 / / Simulation erroneous deletion

Recovery: (clean environment, new machine)

1mysql > set sql_log_bin=0

Mysql > source / backup/2014-02-13 all.sql / / restore the last full backup

2mysqlbinlog's last binlog-- stop-position=260 | mysql-uroot-p 123

Mysqlbinlog's last binlog-- start-position=500 | mysql-uroot-p 123. In this case, you need to use blog character interception to recover.

Backup / restore considerations:

1) fully back up to a clean environment (such as a new database or delete an existing database)

2) all sql statements during recovery should not be recorded in binlog

3) you need to create a database manually before restoring a single database.

Realize automatic backup

1) write a script

Vim / mysql_back.sql

#! / bin/Bash

# The mysql_back

# Author: luliechu (E-mail: 983765387@qq.com)

Back_dir=/backup

Back_file= `date +% F` _ all.sql

User=root

Pass=123

If [!-d / backup]

Then

Mkdir-p / backup

Fi

# backup and cut off the log

Mysqldump-u$ {user}-p$ {pass}-- events-- all-databases-- lock-all-tables > ${back_dir} / ${back_file}

Mysql-u$ {user}-p$ {pass}-e 'flush' logs

# keep only the most recent week's backup

Cd $back_dir

Find-mtime + 7-exec rm-rf {}\

2) Test

Chmod aquix / mysql_back.sql / / give permission to script execution

Chattr + I / mysql_back.sql / / lock the script file for security and forbid anyone to make changes

/ mysql_back.sql / / execute the script

3) configure scheduled tasks to the production environment

Crontab-l

0 2 * / mysql_back.sql / / execute the script at 2 am every day

Export tabl

Import tables into a text file

Export and import of tables

Select.... into outfile export text file

Example:

Mysql > select * from school.student1

Into outfile 'student1.txt'

Fields terminated by `,` / / define field separator

Optinon

Ally enclosed by ```defines what symbols are used to enclose a string

Lines terminated by'\ n' / / define newline characters

Database migration

Be careful to migrate between the same versions as much as possible

Mysqldump-h Migration Source ip-uroot-P123-- databases bbs | mysql-h destination ip-uroot

-p456

/ / the migration source is local, no need to apply

Summary: mysql supports a variety of different backup methods, including physical backup, logical backup, etc., which is generally realized by the combination of physical backup and logical backup in production environment.

What are the main backup methods of mysql to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.