MySQL backup and recovery-cold backup

MySQL backup and recovery-cold backup-What is cold backup-General cold backup is to stop database services, such as MySQL and Oracle, and then back up the data directory using the copy, package, or compression commands. If the data is abnormal, you can recover the data through backup. Cold backup usually requires a customized plan, such as when to back up and

MySQL backup and recovery cold backup what is cold backup in one sentence is to stop database services, such as MySQL and Oracle, and then back up the data directory using the copy, package, or compression commands. If the data is abnormal, you can recover the data through backup. Cold backup usually requires a customized plan, such as when to back up and

Cold backup for MySQL backup and recovery
1. What is cold backup?

In one sentence, cold backup refers to stopping database services, such as MySQL and Oracle, and then backing up the data directory using the copy, package, or compression commands. If the data is abnormal, you can recover the data through backup. Cold backup generally requires customization plans, such as when to back up and which data is backed up each time. However, because such backups occupy too much space and are not suitable for environments with large data volumes, the production environment is rarely used.

Secondary cold backup

3. Cold Standby Experiment

Step 1: Create a test database and insert Test Data

mysql> use larrydb;Database changedmysql> show tables;+-------------------+| Tables_in_larrydb |+-------------------+| access            |+-------------------+1 row in set (0.00 sec)mysql> drop table access;Query OK, 0 rows affected (0.00 sec)mysql> clearmysql> show tables;Empty set (0.00 sec)mysql> mysql> create table class(    -> cid int,    -> cname varchar(30));Query OK, 0 rows affected (0.01 sec)mysql> show create table class \G;*************************** 1. row ***************************       Table: classCreate Table: CREATE TABLE `class` (  `cid` int(11) DEFAULT NULL,  `cname` varchar(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)ERROR: No query specifiedmysql> create table stu(    -> sid int,    -> sname varchar(30),    -> cid int) engine=myisam;Query OK, 0 rows affected (0.00 sec)mysql> show create table stu \G;*************************** 1. row ***************************       Table: stuCreate Table: CREATE TABLE `stu` (  `sid` int(11) DEFAULT NULL,  `sname` varchar(30) DEFAULT NULL,  `cid` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedmysql> insert into class values(1,'linux'),(2,'oracle');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> desc class;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| cid   | int(11)     | YES  |     | NULL    |       || cname | varchar(30) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc stu;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| sid   | int(11)     | YES  |     | NULL    |       || sname | varchar(30) | YES  |     | NULL    |       || cid   | int(11)     | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into stu values(1,'larry01',1),(2,'larry02',2);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from stu;+------+---------+------+| sid  | sname   | cid  |+------+---------+------+|    1 | larry01 |    1 ||    2 | larry02 |    2 |+------+---------+------+

Step 2: Stop MySQL

[root@serv01 ~]# /etc/init.d/mysqld stopShutting down MySQL... SUCCESS! 

Step 3: create a backup directory and modify the owner and group

[root@serv01 ~]# mkdir /databackup[root@serv01 ~]# chown mysql.mysql /databackup/ -R[root@serv01 ~]# ll /databackup/ -ddrwxr-xr-x. 2 mysql mysql 4096 Sep 10 17:46 /databackup/[root@serv01 ~]# cd /databackup/

Step 4: Cold backup (use the tar command)

[root@serv01 databackup]# tar -cvPzf mysql01.tar.gz 

Step 5: test whether the cold backup data is normal. We delete all the data under the data.

[root@serv01 databackup]# rm -rf /usr/local/mysql/data/*

Step 6: The database cannot be started after all data is deleted

[root@serv01 databackup]# /etc/init.d/mysqld startStarting MySQL.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/serv01.host.com.pid).

Step 7: Restore data (use the tar command)

[root@serv01 databackup]# tar -xvPf mysql01.tar.gz 

Step 8: Start MySQL and log on to MySQL to check whether the data is lost. If the data is normal, the cold backup is successful.

[root@serv01 databackup]# /etc/init.d/mysqld startStarting MySQL.. SUCCESS! [root@serv01 ~]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.29-log Source distributionCopyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use larrydb;Database changedmysql> select * from class;+------+--------+| cid  | cname  |+------+--------+|    1 | linux  ||    2 | oracle |+------+--------+2 rows in set (0.00 sec)mysql> select * from stu;+------+---------+------+| sid  | sname   | cid  |+------+---------+------+|    1 | larry01 |    1 ||    2 | larry02 |    2 |+------+---------+------+2 rows in set (0.00 sec)

My mailbox: wgbno27@163.com Sina Weibo: @ justdb public platform: JustOracle (No.: justoracle) database technology exchange group: 336882565 (when adding group verification From csdn xxx)All is well  November 2, 2013  By Larry Wen

	@ Wentasy blog is for your reference only. Welcome to visit. I hope to criticize and correct any mistakes. If you need to repost the original blog, please indicate the source. Thank you :) [CSDN blog]