MySQL Backup instance scenario

How the online backup guarantees the consistency of the backed-up data, using the logical Volume Snapshot feature, first lock for a few seconds

Redirect Blog log to a file, and then import into the database with source , because every step of the operation will generate a new blog log

Physical Backup:

Direct copy of database files, suitable for large database environment, general structure LVM Snapshot Implementation, the disadvantage is that can not be restored to heterogeneous systems such as Windows

Logical Backup

The backup is the SQL statement executed by the operation of building table, building the database, inserting and so on, which is very inefficient for small and medium sized databases .

Mysqldump Implementing logical Backups

Grammar

#mysqldump-H Server- u user name - p password database name > backup File . sql

Options for database names

--all-databases All Libraries

School Database name

School stu_info T1 School database table stu_info T1

--databases BBS test MySQL multiple databases

Instance:

Library Backup:

#mysqldump-uroot-p123 BBS >bbs.sql

#mysqldump-uroot-p123 BBS table1 table 2 > BBS table1 table2.sql

Docu 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:

situation 1 The database is damaged, it's the machine that's broken.

Operation Steps:

Backup

1#mysqldump-uroot-p123--all-databases--lock-all-tables>/backup/' date+%f ' _all.sql

2mysql-uroot-p123-e ' Flush Logs ' // truncate and generate new binlog

3 inserting data // emulation server is running properly

4mysql>set sql_log_bin=0;

Mysql>drop database BBS// Analog server corruption

Recovery: (Clean environment, new machine)

1mysql>set sql_log_bin=0;

MYSQL>SOURCE/BACKUP/2014-02-13 all.sql// restore last full backup

2mysqlbinlog last binlog |mysql-uroot-p 123// recover last binlog file

Scenario 2 If you delete the error

Backup

1#mysqldump-uroot-p123--all-databases--lock-all-tables>/backup/' date+%f ' _all.sql

2mysql-uroot-p123-e ' Flush Logs ' // truncate and generate new binlog

3 inserting data // emulation server is running properly

4mysql>drop table DB1.T1// analog accidental deletion

Recovery: (Clean environment, new machine)

1mysql>set sql_log_bin=0;

MYSQL>SOURCE/BACKUP/2014-02-13 all.sql// restore last full backup

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

Mysqlbinlog last binlog--start-position=500|mysql-uroot-p 123 In this case you need to use the blog c8> character interception to recover

backup / Restore considerations:

1) Full backup to a clean environment (such as a new database or deleting an existing database)

2) all SQL statements should not be logged to binlog during recovery

3) You need to manually create a database before restoring a library

Enable automated backup

1) Writing Scripts

Vim/mysql_back.sql

#!/bin/bash

#The Mysql_back

# Author:luliechu (E-mail: [email protected])

Back_dir=/backup

Back_file= ' date+%f ' _all.sql

User=root

Pass=123

if[!-d/backup];

Then

Mkdir-p/backup

Fi

# back up and cut logs

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

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

# Keep backups for the last week only

Cd $back _dir

Find-mtime +7-exec RM-RF {}\;

2) Test

Chmod a+x/mysql_back.sql//Give script Execute permission

Chattr +i/mysql_back.sql// in order to secure this script file lock, prohibit anyone to make changes

/mysql_back.sql// Execute script

3) Configuring Scheduled tasks into a production environment

Crontab-l

0 2 * * */mysql_back.sql// 2 hours a day to execute the script.

Exporting Tables

Import a table into a text file

Export and import of tables

Select .... into outfile export text file

Instance:

Mysql>select * from School.student1

Into outfile ' Student1.txt '

Fields terminated by ', '// define field separators

Optinon

Ally enclosed by ' "' ' define what symbols are used to enclose the string.

Lines terminated by ' \ n ' // define line break

Database Migration

Note to try to migrate between the same versions

Mysqldump-h Migration Source ip-uroot-p123--databases bbs |mysql-h target ip-uroot

-p456

migration source is native without application

Summary:MySQL supports a variety of different backup methods, including physical backup, logical backup, etc., by combining physical and logical backups in a production environment

This article is from "Happy Learning" blog, please be sure to keep this source http://983865387.blog.51cto.com/9838888/1875829

MySQL Backup instance scenario