MySQL Data backup and recovery

MySQL Data backup:

1 Data backup method:

Way classification:

Physical backup: Direct copy of library or corresponding file, file owner/group must be MySQL

Cp-r/var/lib/mysql Directory/backup file name

Cp/var/lib/mysql/user.*/mydata/

tar-zcvf/mydata/mysal.tar.gz/var/lib/mysql/mysql/*

MyISAM Storage engine only table Mysqlhotcopy-u root-p 123456 database name directory/backup file name

Mysqlhotcopy backup is dependent on two installation packages: ls Packages | grep DBD yum-y Install Perl-dbd-mysql

Mysql> show engines; View the database default storage engine

Logical backup: Backup SQL statement that produces current data, logical Backup tool with database service software, or install third party software, provide logical backup tool;

Status classification: Hot backup (online Backup) cold backup (not online backup)

2 Data backup Strategy

Full backup: Backs up all data (all data on a single server, or backs up all data from a database server, a library, or a table)

Differential backup: Back up all newly generated data since the full backup

Incremental backup: Back up the newly generated data since the last backup

Common backup methods at work: Full backup + incremental backup

Full backup:

[[Email protected] Desktop]# mysqldump-uroot-p123456 database name > directory/xxx. Sql

How the database name is represented:

--all-databases backing up all data on the database server

Database name backs up all data for a library on the database server

Database name Table name backs up all data from a single table on a database server

-B Database Name 1 database name 2 back up all data for a few libraries on the database server

Recover data using Backup files: [[email protected] Desktop]# mysql-uroot-p123456 database name < directory name/xxx.sql

This article is from "Linux Cloud Computing" blog, reprint please contact the author!

MySQL data backup and recovery