MySQL Backup and recovery

1. Backing up a single database

MySQL database comes with a good backup command, that is mysqldump, his basic use is as follows:
Syntax: mysqldump-u user name-p database name > backup file name

Backup 11, Backup: Mysqldump-uroot-p ' * * * * * Test >/opt/test_bak.sql2, view the contents of the backup data    egrep-v "#|\*|--|^$"/opt/test_bak.sql3 , in order to facilitate the test can consider to delete the original table: Mysql-uroot-p ' * * * * * * * * "use Test;dorp table test;" 4. Recovery: Mysql-uroot-p ' * * * * * Test </OPT/TEST_BAK.SQL5, view recovered Data mysql-uroot-p ' * * * * * * * * * from Test; Backup 21, Backup: Mysqldump-uroot-p ' * * * * * Test >/opt/test_bak.sql    added B after the role, increased the creation of the database and connected to the database command, added better,-B can specify multiple library backup 2, Recovery: mysql-uroot-p ' * * * * * * </opt/test_bak.sql backup 31, Backup: Mysqldump-uroot-p ' * * * * * Test|gzip >/opt/test_ BAK.SQL.GZ  Compressed Backup backup 41, Backup: Mysqldump-uroot-p ' * * * * * * test1 test2 |gzip >/opt/test_bak.sql    back up multiple libraries

Sub-Library Backup:

Sometimes a company's database will have multiple libraries, but the problem is likely to be a certain library, if the backup of all libraries are backed up as a data file, the recovery of a library of data is more troublesome, all B is backed up into a data file inside.

2. Backup table

Syntax: mysqldump-u user name-p password database name table Name 1 table 2> the file name of the backup can be appended with several tables

Table Backup: Each table is backed up separately, not all unified backup table structure: MYSQLDUMP-U user name-p password-d database table > backup file backup data: Mysqldump-u user name-p password-t database table > backup file

　　

Back up the whole database, all things mysqldump-u user name-p password-a-b--events|gzip > file    

3. Episode

When you enable Bin-log, you need to open-F refresh Bin-log in the configuration file, cut Bin-log, do not restore old data, restore new data--master-data=1  find Bin-log location, and the function of-F is the same   = 2 will be annotated # Lock table-----lock table-L read-only lock table--single-transaction  is suitable for InnoDB transactional database backup, it is usually used to ensure the consistency of the backup, in fact it works to set the isolation level of this session is: repeatable Read to ensure that this session dump does not see data that has been submitted by other sessions

4. A complete backup process

MyISAM Engine: mysqldump-uroot-p123-a-B--master-data=2-f-X--events|gzip >/opt/test.sql.gzinnodb:mysqldump-uroot-p1 23-a-B--master-data=2-f--single-transaction--events|gzip >/opt/test.sql.gz If it's a hybrid engine, MyISAM is the main

5. Recovery

1, source recovery   into MySQL source/opt/13123.sql    2, Standard recovery command MySQL     mysql-uroot-p123 Oldboy </opt/13123.sql Compressed need to first decompression gzip-d mysql.sql.gz-d is to save the file as the current name, remove GZ

　　

MySQL Backup and recovery