MySQL Backup and recovery

Database backup

Incremental backup, differential backup difference

Increments are daily relative to the previous day's backup

Difference is a point in time relative to a full backup

1, cold backup offline, read and write operation terminated

2. Hot backup backup is not affected by read and write

3, warm backup can only read operation

4. Physical Backup: Backup data file, logical backup: Export data to Text

5. Backup content: Database configuration file, data, binary log, transaction log

6, Hot backup: Innodb:xtrabackup,mysqldump

MyISAM: Temperature Backup

mysql===== Master-Slave architecture, offline backup

Physical Backup: Fast speed

Logical backup: Slow, loss of floating point accuracy

7. Backup strategy

Full + incremental, full + diff

Make a full backup once a week; make a difference or increment one day at a time. Full + one hour difference or increment per day

8. mysql Backup tool

8.1 Mysqldump: Logical Backup tool, Myisam (warm Backup), InnoDB (hot Backup)

Mysqlhotcopy: Physical Backup tool, Win Bei, or cold Backup tool

File System Tools

8.2 CP: Cold standby

LV: Logical volume snapshot feature, almost hot standby

Mysql>flush tables;

Mysql>lock tables;

Create snapshots, release locks, and then copy data

8.3 Third set of tools

Ibbackup: Commercial Backup

Xtrabackup: Open Source Backup

9. Backup tool operation

9.1 mysqldump (full backup) + binary backup

Command: mysqldump db_name [TB1] [TB2] backup db_name database or table [TB1]

Mysqldump-uroot-p Database name >/backupdir/backup.sql

Mysqldump Backup INSERT statement

MySQL Database name </backupdir/backup.sql Import Database

Mysql>lock tables;

Mysql>flush tables with read lock; The lock table is locked in read mode before backup, backup MySQL data in dump

Perform backup MYSQLDUMP-UROOT-P database name >/backupdir/backup.sql

Whether the backup is locked after it is completed

mysql> lock tables;

Mysql>unlock tables; Release lock

Back up the binary log:

Msyql>flush tables with read lock; Read-only Form lock table

Mysql>flush logs;

Msyql>show binary logs; viewing binary logs

--MASTER-DATA=N{0|1|2} 0: No binary files and locations are recorded

1: Record location in Change master to, for recovery after direct start from server

2: Back up in Change Master to mode, default is commented

Record Binary log backups:

Mysqldump-uroot-p--master-data=1 msyqldb >/back/back-date+%f-%h-%m-%s.sql

Warm Backup locks All tables

--lock-all-tables parameter locks all tables for backup

Mysqldump-uroot-p--lock-all-tables--master-data=1 msyqldb >/back/back-date+%f-%h-%m-%s.sql

--flush-logs: Perform log flush; log scrolling before performing a backup

* If the table type in the specified library is InnoDB, you can use--single-transaction to start the hot standby;

Backing up multiple libraries

--all-databases: Backing Up all libraries

--databases Db-name backing up a specified library

These two commands back up the library name; do not create the library manually before restoring

Back up all libraries: Mysqldump-uroot-p--lock-all-tables--flush-logs--all-databases--master-data=2 >/back/dbs+%F-%H-%M-% S.sql

Incremental backup: Back up the binary log of the day

10. Backup strategy: Weekly full + daily increment

Full backup: mysqldump msyqldump-uroot-p--master-data=2--flush-logs--all-databases--lock-all-tables>/back/ Alldatabases.sql

Mysql>purge binary logs to ' msyql-bin-000011 '; Delete the binary log before ' msyql-bin-000011 ';

Msyql>show binary logs;

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

1, Mysql>flush logs; First log in MySQL refresh the log;

2. cd/mysql/data/(This directory is the data storage path for the database)

CP mysql-bin-000011/back/

3, or can be another way of incremental backup, the mysql-bin-000011 data read out after the backup

Mysqlbinlog mysql-bin.000011 >/back/mon-01bin.sql

recover MySQL database;

If the MySQL data directory, the whole has been deleted;

1. Cd/usr/local/mysql/mysql installation directory

2. Initialize mysql:scripts/mysql_install_db--user=mysql--datadir=/mydat/data/

3. Start MySQL

4. #mysql-u root-p </back/alldatabases.sql import full Backup library

5, #mysql-u root-p </back/mon-01bin.sql re-import incremental binary backup

6. Import the most recent binary log without backup: #mysqlbinlog mysql-bin.000012 >/back/tu.sql

#mysql-uroot-p </back/tu.sql

MySQL Backup script:

MySQL Backup and recovery