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

Backup:

Backups can be divided into:

L full backup

Incremental backup: refers to backing up data that has changed since the last backup (incremental or complete); features: saving space and restoring hassle.

Differential backup: refers to the data characteristics that have changed since the last full backup: wasted space and easier to restore than incremental backup.

In general, it is combined according to the backup strategy: full + increment; complete + difference.

Common backup tools:

1. Mysqldump: logical backup tool, suitable for all storage engines, supporting warm backup, full backup, partial backup, hot backup for InnoDB storage engine

2. Cp, tar and other archive replication tools: physical backup tool, suitable for all storage engines, cold backup, full backup, partial backup

3. Lvm2 snapshot: almost hot standby, backup with the help of file system management tools

4. Mysqlhotcopy: a tool that doesn't live up to its name, almost cold standby, and only supports MyISAM storage engine.

5. Xtrabackup: a very powerful InnoDB/XtraDB hot backup tool that supports full backup and incremental backup, provided by percona

Backup strategy:

According to different scenarios, we should make different backup strategies to back up the database. Generally speaking, the backup strategies are as follows:

a. Copy database files directly with cp,tar

B. mysqldump+ copy BIN LOGS

C. Lvm2 Snapshot + replication BIN LOGS

D. Xtrabackup

The above solutions are specific to different scenarios:

If the amount of data is small, you can use the first way to copy the database file directly. [if the copied file is copied to a new database, the version number of the new data is higher than that of the old database. It is only supported by MYISAM engine, but not by INNODB]

If the amount of data is OK, you can use the second way, first use mysqldump to make a full backup of the database, and then back up BINARY LOG regularly to achieve the effect of incremental backup.

If the amount of data is mediocre and does not unduly affect the operation of the business, you can use the third way to back up the data files using the snapshot of lvm2, and then back up BINARY LOG regularly to achieve the effect of incremental backup.

ZR

If the amount of data is large and does not unduly affect the operation of the business, you can use the fourth way, after using xtrabackup for full backup, use xtrabackup for incremental backup or differential backup regularly.

1. Backup and restore with Mysqldump

[in windows environment]

1) back up a single database or a specified table in a single database:

Mysqldump-h hostname-P port-u username-p password-database database name table name > file name. Sql

2) back up multiple databases:

Mysqldump-h hostname-P port-u username-p password-database db1 db2 db3 > file name. Sql

3) back up all databases:

Mysqldump-- all-databases > backupname.sql

4) compress backup:

Mysqldump-h hostname-u username-p password-database databasename | gzip > backupfile.sql.gz

5) the command to restore MySQL database:

Mysql-hhostname-uusername-ppassword databasename

< backupfile.sql 6) 还原压缩的MySQL数据库 gunzip < backupfile.sql.gz | mysql -u username -p password dbname 数据迁移： 可以分为：同版本MYSQL迁移；不同版本MYSQL迁移；不同数据库迁移 1、直接迁移（备份还原同时进行）【同版本】 mysqldump -h hostname1-u root -p password=password1 -all-databases | mysql -h hostname2 -u root -password=password2 2、【不同版本】低版本可以向高版本迁移，高版本很难向低版本迁移。 3、不同数据库迁移，可以实现，但比较复杂。 ===========================================提高============================================================== 增量备份 小量的数据库可以每天进行完整备份，因为这也用不了多少时间，但当数据库很大时，就不太可能每天进行一次完整备份了，这时候就可以使用增量备份。增量备份的原理就是使用了mysql的binlog志。 1、首先做一次完整备份： mysqldump -h20.6.208.183 -utest2 -p123 -P3310 --single-transaction --master-data=2 test>

Test.sql

At this point, you will get a full file test.sql.

In the sql file, we will see:

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107; means that all changes after backup will be saved to the bin-log.000002 binary file.

2. Add two records to the t_student table of the test library, and then execute the flush logs command. A new binary log file, bin-log.000003,bin-log.000002, will be generated and all the changes after the completion will be saved, both adding the recorded operation and saving it in the bin-log.00002.

3. Add two records to the a table in the test library, and then delete the tstudent table and a table by mistake. The operations of adding records in an and deleting tables an and t_student are recorded in bin-log.000003.

Restore

1. Import complete data first

Mysql-h20.6.208.183-utest2-p123-P3310 < test.sql

You can also import with source directly under the mysql command line

2. Restore bin-log.000002

Mysqlbinlog bin-log.000002 | mysql-h20.6.208.183-utest2-p123-P3310

3. Restore part of bin-log.000003

Find the time point of the erroneous deletion in the general_log, and then find the corresponding position point in the bin-log.000003 at a more corresponding time point. You need to restore to the previous position point of the erroneous deletion.

The interval of binlog can be controlled with the following parameters

-- start-position start point-- stop-position end point

-- start-date start time-- stop-date end time

Once the recovery point is found, you can start the recovery.

Mysqlbinlog mysql-bin.000003-- stop-position=208 | mysql- h20.6.208.183-utest2-p123-P3310

Reference:

1. Learn 4 MYSQL backup methods 2. Common MSQL backup and restore commands

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.