Mysql Backup and Recovery hot standby (3) _mysql

In the last two articles (MySQL backup and restore cold standby, MySQL backup and restore real-world use Lengbei), we mentioned the use of cold standby in the Lengbei and real environment. So starting from this article, let's look at the hot standby. It is clear that hot spare and Lengbei are two relative concepts, Lengbei the database services, such as mysql,oracle, and then use Copy, package, or compression commands to back up the data directory; then it's easy to think that hot-standby is backed up by MySQL or other database services while it's running. However, there is a problem, because the production library in the case of running, read and write to the library, the frequency of reading and writing may be high, and may be low, regardless of frequency, will always result in the backup of the data and production database inconsistent with the situation. Hot standby This time, other people can not operate is not realistic, because you can not terminate the user access to the Web program. To solve this problem, you can specify a backup strategy, such as which time period to backup, what data to back up, and so on, in short, to ensure the integrity and consistency of data, remember, backup is more than everything!!!
Hot standby can back up multiple libraries, and you can back up a single table or a few tables. However, you cannot back up multiple tables at the same time, only separate backups. Here we look at the hot standby schematic and perform a hot standby simulation.
schematic

Hot Standby Simulation

1. Backup to a single library
in the first step, remove the LVM snapshots. (if not created, ignore this step)

[Root@serv01 data]# lvremove/dev/data/smydata 
Do your really want to remove active logical volume? [y/n]: y
 Logical Volume "smydata" successfully removed

Step two, set the password for MySQL

Mysql> set Password=password ("123456");
Query OK, 0 rows Affected (0.00 sec)

The third step is to see if MySQL starts. Requires MySQL service to start because it is hot standby

[Root@serv01 data]#/etc/init.d/mysqld status
 success! MySQL Running (2664)

Step fourth, export a single database

[Root@serv01 data]# cd/databackup/#本质是导出为SQL [root@serv01 databackup]# mysqldump-uroot-p123456--database larrydb-- MySQL dump 10.13 Distrib 5.5.29, for Linux (x86_64)----host:localhost database:larrydb---------------------------- ------------------------------Server version 5.5.29-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT =@ @CHARACTER_SET_
CLIENT * *;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS =@ @CHARACTER_SET_RESULTS * *;
/*!40101 SET @OLD_COLLATION_CONNECTION =@ @COLLATION_CONNECTION * *;
/*!40101 SET NAMES UTF8 * *;
/*!40103 SET @OLD_TIME_ZONE =@ @TIME_ZONE * *;
/*!40103 SET time_zone= ' +00:00 ' * *;
/*!40014 SET @OLD_UNIQUE_CHECKS =@ @UNIQUE_CHECKS, unique_checks=0 * *;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS =@ @FOREIGN_KEY_CHECKS, foreign_key_checks=0 * *;
/*!40101 SET @OLD_SQL_MODE =@ @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' * *;

/*!40111 SET @OLD_SQL_NOTES =@ @SQL_NOTES, sql_notes=0 * *; ----Current Database: ' Larrydb '--CREATE Database/*!32312 IF not exists*/' larrydb '/*!40100 DEFAULT CHARACTER SET latin1 * *;

Use ' larrydb ';
----Table structure for table ' class '--DROP Table IF EXISTS ' class ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' class ' (' cid ' int () default null, ' CNAME ' varchar () default null) ENGINE=INNODB default Charset=lati
N1;

/*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' class '--LOCK TABLES ' class ' WRITE;
/*!40000 ALTER TABLE ' class ' DISABLE KEYS * * *;
INSERT into ' class ' VALUES (1, ' Linux '), (2, ' Oracle ');
/*!40000 ALTER TABLE ' class ' ENABLE KEYS */;

UNLOCK TABLES;
----Table structure for table ' Stu '--DROP Table IF EXISTS ' Stu ';
/*!40101 SET @saved_cs_client = @ @character_set_client * *;
/*!40101 SET character_set_client = UTF8 * *; CREATE TABLE ' stu ' (' Sid ' int () default null, ' sname ' varchar ' default NULL, ' CID ' int (one) default null) engine=
MyISAM DEFAULT charset=latin1; /*!40101 SET character_set_client = @saved_cs_client * *;
----dumping data for table ' Stu '--LOCK TABLES ' Stu ' WRITE;
/*!40000 ALTER TABLE ' stu ' DISABLE KEYS * * *;
INSERT into ' Stu ' VALUES (1, ' larry01 ', 1), (2, ' larry02 ', 2);
/*!40000 ALTER TABLE ' stu ' ENABLE KEYS * * *;
UNLOCK TABLES;

/*!40103 SET time_zone= @OLD_TIME_ZONE * *;
/*!40101 SET sql_mode= @OLD_SQL_MODE * *;
/*!40014 SET foreign_key_checks= @OLD_FOREIGN_KEY_CHECKS * *;
/*!40014 SET unique_checks= @OLD_UNIQUE_CHECKS * *;
/*!40101 SET character_set_client= @OLD_CHARACTER_SET_CLIENT * *;
/*!40101 SET character_set_results= @OLD_CHARACTER_SET_RESULTS * *;
/*!40101 SET collation_connection= @OLD_COLLATION_CONNECTION * *;

 /*!40111 SET sql_notes= @OLD_SQL_NOTES * *;  Dump completed on 2013-09-10 18:56:06 #将输出结果保存到文件中 [root@serv01 databackup]# mysqldump-uroot-p123456--database
 > Larrydb.sql

Fifth step, simulate data loss, enter MySQL, delete database

[Root@serv01 data]# mysql-uroot-p123456 Welcome to the MySQL monitor. Commands End With;
or \g. Your MySQL Connection ID is 4 Server version:5.5.29-log Source distribution Copyright (c), and Oracle and/or its Affiliates.

All rights reserved. Oracle is a registered trademark to Oracle Corporation and/or its affiliates.

The other names may is trademarks of their respective owners. Type ' help, ' or ' \h ' for help.

Type ' \c ' to clear the current input statement.
mysql> show databases; +--------------------+
|
Database | +--------------------+
| Information_schema | | CRM | | Game | | Hello | | Larrydb | | mnt | | MySQL | | Performance_schema | |
Test |
+--------------------+ 9 rows in Set (0.00 sec) mysql> drop database larrydb;
Query OK, 2 rows affected (0.01 sec) mysql> show databases; +--------------------+
|
Database | +--------------------+
| Information_schema | | CRM | | Game | | Hello | | mnt | | MySQL | | Performance_schema | |
Test | +--------------------+ 8 rows in Set (0.00 sec) mysql> Exit Bye

 

Step sixth, import data

[Root@serv01 databackup]# mysql-uroot-p123456 <larrydb.sql

step Seventh, log in to MySQL to see if the data is normal

[Root@serv01 data]# mysql-uroot-p123456 Welcome to the MySQL monitor. Commands End With;
or \g. Your MySQL Connection ID is 6 Server version:5.5.29-log Source distribution Copyright (c), and Oracle and/or its Affiliates.

All rights reserved. Oracle is a registered trademark to Oracle Corporation and/or its affiliates.

The other names may is trademarks of their respective owners. Type ' help, ' or ' \h ' for help.

Type ' \c ' to clear the current input statement.
Mysql> Show Database; Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' database ' at line 1 MySQL
> show databases; +--------------------+
|
Database | +--------------------+
| Information_schema | | CRM | | Game | | Hello | | Larrydb | | mnt | | MySQL | | Performance_schema | |
Test |
+--------------------+ 9 rows in Set (0.00 sec) mysql> use Larrydb;
Database changed mysql> 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)

To back up multiple libraries
The first step is to see which databases

mysql> show databases;
+--------------------+
| Database  |
+--------------------+
| information_schema |
| CRM  |
| game  |
| |  | | | larrydb
| | | mnt | | Performance_schema |
| Test  |
+--------------------+
9 rows in Set (0.00 sec)
mysql> use game;
Database changed
mysql> show tables;
+----------------+
| Tables_in_game |
+----------------+
| country |
| Fight  |
| hero  |
+----------------+
3 rows in Set (0.00 sec)

mysql> select * from country;
+-----+---------+----------+
| cno | cname | location
| +-----+---------+----------+
| | caowei | luoyang | |
20 | Shuhan | Chengdou |
| 30 | Sunwu | Nanjing |
| 40 | Houhan | Luoyang |
| 50 | Beisong | Kaifeng |
| 60 | Wei | Luoyang |
+-----+---------+----------+
6 rows in Set (0.00 sec)

Step two, back up multiple libraries

[Root@serv01 databackup]# mysqldump-uroot-p123456--databases larrydb game > Larrydb_game.sql
[root@serv01 databackup]# ll Larrydb_game.sql 
-rw-r--r--. 1 root 6159 Sep 19:05 larrydb_game.sql

The third step is to simulate data loss.

mysql> drop database game;
Query OK, 3 rows affected (0.01 sec)

mysql> drop database larrydb;
Query OK, 2 rows Affected (0.00 sec)
mysql> use CRM;
Database changed
mysql> show tables;
+---------------+
| Tables_in_crm |
+---------------+
| Test  |
+---------------+
1 row in Set (0.00 sec)

mysql> select * from test;
Empty Set (0.00 sec)

mysql> drop database CRM;
Query OK, 1 row Affected (0.00 sec)

Step fourth, recover the data

[Root@serv01 databackup]# mysql-uroot-p123456 < Larrydb_game.sql 

Step Fifth, see if the data is normal

[Root@serv01 data]# mysql-uroot-p123456 Welcome to the MySQL monitor. Commands End With;
or \g. Your MySQL Connection ID is 9 Server version:5.5.29-log Source distribution Copyright (c), and Oracle and/or its Affiliates.

All rights reserved. Oracle is a registered trademark to Oracle Corporation and/or its affiliates.

The other names may is trademarks of their respective owners. Type ' help, ' or ' \h ' for help.

Type ' \c ' to clear the current input statement.
mysql> show databases; +--------------------+
|
Database | +--------------------+
| Information_schema | | Game | | Hello | | Larrydb | | mnt | | MySQL | | Performance_schema | |
Test |
+--------------------+ 8 rows in Set (0.00 sec) mysql> use game;
Database changed mysql> select * from country; +-----+---------+----------+
| CNO | CNAME |
Location | +-----+---------+----------+
| 10 | Caowei | Luoyang | | 20 | Shuhan | Chengdou | | 30 | Sunwu | Nanjing | | 40 | Houhan | Luoyang | | 50 | Beisong | KaifenG | | 60 | Wei |
Luoyang |
+-----+---------+----------+ 6 rows in Set (0.00 sec) mysql> use Larrydb;
Database changed mysql> select * from class; +------+--------+
| CID |
CNAME | +------+--------+
| 1 | Linux | | 2 |
Oracle |

 +------+--------+ 2 rows in Set (0.00 sec)

back up all the libraries

[root@serv01 databackup]# mysqldump--help | grep all-database
OR mysqldump [Options]--all-databases [options]
 -A,--all-databases Dump all databases.  This would be same as--databases
   --databases= or--all-databases), the logs would be
   --all-databases or--databases is given.
all-databases   FALSE

[root@serv01 databackup]# mysqldump-uroot-p123456--all-databases > All_ Databases.sql
[root@serv01 databackup]# ll all_databases.sql-h
-rw-r--r--. 1 root 506K Sep 19:16 all_dat Abases.sql

Back up a table or a few tables
The first step is to back up a table and a few tables

[Root@serv01 databackup]# mysqldump game hero country-uroot-p123456 > Game_hero_country.sql
[root@serv01 databackup]# ll Game_hero_country.sql 
-rw-r--r-1 root 3955 Sep 19:11 game_hero_country.sql

The second step is to simulate data loss

mysql> use game;
Database changed
mysql> show tables;
+----------------+
| Tables_in_game |
+----------------+
| country |
| Fight  |
| hero  |
+----------------+
3 rows in Set (0.00 sec)

mysql> drop table hero;
Query OK, 0 rows Affected (0.00 sec)

mysql> drop table country;
Query OK, 0 rows Affected (0.00 sec)
 

Step three to see if the data is normal

[Root@serv01 databackup]# mysql-uroot-p123456 < game_hero_country.sql 
ERROR 1046 (3d000) at line 22:no database Selected
[root@serv01 databackup]# mysql-uroot-p123456--database Game < Game_hero_country.sql 

[ ROOT@SERV01 databackup]# mysql-uroot-p123456-e "select * from Game.country"
+-----+---------+----------+
| CNO | CNAME | Location |
+-----+---------+----------+
| | caowei | luoyang | |
20 | Shuhan | Chengdou |
| 30 | Sunwu | Nanjing |
| 40 | Houhan | Luoyang |
| 50 | Beisong | Kaifeng |
| 60 | Wei | Luoyang |
+-----+---------+----------+

Through these two days of MySQL hot spare and Lengbei learning, we are not on the MySQL backup and recovery of the understanding more in-depth, whether it is Lengbei or hot preparation Its purpose is consistent to ensure the integrity and consistency of data, remember, backup is more than everything!!!

I believe that the knowledge I learned today is helpful to all of you in the study work.