Backup and restore of MySQL

Backup and restore of MySQL

Backup: Copy

RAID1,RAID10: Guaranteed hardware corruption without termination of business, no guarantee of logical destruction

Backup type:

Hot backup, warm backup and cold backup

Hot backup: Read, write is not affected by the image

Warm backup: Can only perform read operations

Cold backup: Offline backup, read and write operations are aborted

Physical and logical backups

Physical Backup: Copy data files;

Logical backup: Export data to a text file;

Full backups, incremental and differential backups:

Full backup: Back up all data;

Incremental backup: Backs up only data that has changed since the last full or incremental backup:

Differential backup: Backs up only data that has changed since the last full backup

Online: Physical Full backup

Restores:

Backup what:

Data

Configuration file

Binary log

Transaction log

Hot backup:

MyISAM: Warm Backup

INNODB: Hot backup can be done directly xtrabackp,mysqldump

Physical Backup: Speed block,

Logical backup: Slow speed, loss of floating point accuracy, easy to use text processing tools for processing, portability strong;

Backup strategy: Full + Delta; Full + diff

MySQL Backup tool

Mysqldump logical Backup tool, MyISAM warm backup, InnoDB hot backup

Mysqlhotcopy Physical Backup tool, warm backup

File System Tools:

CP: Cold Backup

LV: Fast function of logical volume, almost hot standby;

mysql> flush Tables;

mysql> lock tables;

Create a snapshot: Release the lock and then copy the data

InnoDB:

Third set of tools:

Ibbackup Business Tools

Xtrabackup Open Source Tools

Mysqldump: Logical Backup

Mysqldump (full backup) + binary log

Full + increment:

Mysqldump db_name [TB1][TB2]

--master-data=n (N={0|1|2})

0: Do not log binary log files and path location

1: Record location in Chnage master to, can be used to start the server directly after recovery;

2: The location is recorded in Chnage master to, but is commented by default;

--lock-all-tables: Lock All tables

--flush-logs: Execute log flush;

If the table type in the specified library is inodb, use (do not use with--lock-all-tables)

--single-transaction Starting a hot backup

To back up multiple databases:

--all-databases; back up all libraries

--databases db_name,db_name,...: Backing up the specified library

--events

--routines

--triggers

To back up multiple databases:

[Email protected] ~]# mysqldump-uroot-p--lock-all-tables--flush-logs--all-databases--master-data=2 >/root/all. Sql

[[email protected] ~]# less all.sql You can view the backup to the first few binary logs

Mysql> purge binary logs to ' mysql-bin.000011 '; Delete the binary log before the backup

Mysql> show binary logs;

Incremental backup:

mysql> flush logs;

CP mysql-bin.000011/root/

Or

mysql> mysqlbinlog mysql-bin.000011 >/root/mon-incremental.sql;

Restoring a database using binary logs

Initialize the database First

Cd/usr/local/mysql

scripts/mysql_install_db--user=mysql--datadir=/mydata/data/

Service mysqld Start

Mysql-uroot-p < All.sql Import Database

Mysql-uroot-p < Mon-incremental.sql Import Incremental backups

Mysqlbinlog mysql-bin.000012 > Temp.sql

Mysql-uroot-p < Temp.sql

To back up a single database or table:

Mysql> flush tables With read lock, refresh the table first, and lock the table in a read manner

mysql> flush logs; Refresh Log

Mysql> show binary logs; To view the backup from the first few binary logs

[[email protected] ~]# mysqldump-uroot-p students >/root/students.sql start Backup

Can view backup files with vim

mysql> unlock tables; Release lock

To restore a single database or table:

mysql> CREATE database studb; restore requires the creation of databases before importing data

[email protected] ~]# MySQL studb< studenst.sql

Backup strategy: Weekly finish + daily increment

Full backup: mysqldump

Incremental backup: Backing up binary log files (flush logs)

This article from "Operation and maintenance Growth Road" blog, declined reprint!

Backup and restore of MySQL