7. mysql Backup and restore

1. Logical backup

A logical backup in MySQL is to back up the data in the database as a text file, and this text file can be viewed and edited

mysqldump

[[Email protected]~]# mysqldump

usage:mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS]--databases [OPTIONS]DB1 [DB2 DB3 ...]

OR mysqldump [OPTIONS]--all-databases[options]

mysqldump-uroot-paixocm ds > Ds.sql ---Backing up the DS database

mysqldump DS Stu Dept > ds.sql  --- Backup DS under the library stu,dept Table

mysqldump--databases ds ds2 > Ds.sql--- backing up multiple libraries (Note the difference on the back of the restore)

Write lock : This session can be added or deleted, the other session is waiting

read lock : All session can only select, no additions and deletions

flush tables with read lock;              ---- Lock All Tables

Locktables Stu Read or write;

Unlocktables;

MYSQLDUMP-UROOT-PAIXOCM-LF ds > Ds.sql

-L (lowercase) adds read locks to all tables and is read-only and cannot be written back

-F (Flush logs) generates a new binary log file

Restores

mysql-uroot-paixocm DS < Ds.sql --input redirection, cat ds.sql | MySQL pipe can also be

or use ds after source/tmp/ds.sql

Note the above situation is not added --databases Backup, Need First New good an empty database, and add the words, you can not build a new library, restore automatically built.

Not fully recovered

Point-in-time recovery based on location recovery (more precisely because of concurrency at one point in time)

mysqlbinlog mysql-bin.000001--stop-datetime= "2012-1-1 12:1:2" | Mysql-uroot-paix ds         // can refer to bin Log

2. Physical backup

CP Database folder such as DS

Cold backup: Post-service CP, non-stop service copy data inconsistent

Hot backup: CP (read lock) for service uptime

Example of a hot backup (MyISAM table only):

Method 1:

/usr/local/mysql56/bin/mysqlhotcopy-u root-p aixocm-s ... ds/tmp    --- it's locked automatically.

Method 2 (Manual read lock):

flush tables with read lock;--Lock all tables, lock tables T1 read; lock only T1

CP folder

Unlocktables;

InnoDB tables , not supported, can be used with third-party tools such as: Ibbackup tools, charges.

Restores:

cp-pr/tmp/ds/database/

From for notes (Wiz)

7. mysql Backup and restore