Hot Backup for MySQL backup and recovery

One-hot standby

In the last two articles (MySQL backup and recovery for cold backup, MySQL backup and recovery for the real environment for cold backup), we mentioned the use of cold backup in the cold backup and real environment. From this article, let's look at Hot Standby. Obviously, hot standby and Cold Standby are two opposite concepts. Cold Standby refers to stopping database services, such as MySQL and Oracle, and then backing up the data directory using the copy, package, or compression commands; it is easy to think that hot backup is performed when MySQL or other database services are running. However, there is a problem here, because when the production database is running, there is read/write to the database, the read/write frequency may be high or low, regardless of the frequency, the data backed up is inconsistent with the data in the production database. During the Hot Standby period, it is unrealistic for others to operate, because you cannot terminate user access to Web programs. To solve this problem, you can use the specified backup policy, such as the time range for backup and the data to be backed up. In short, to ensure data integrity and consistency, remember that backup is more important than everything else !!!

 

Hot Backup uses the mysqldump command for backup. This tool is a built-in MySQL backup and recovery tool with powerful functions. It can back up the entire database, you can back up multiple databases and back up a single table or several tables. However, you cannot back up multiple tables in multiple databases at the same time, but only backup separately. Next we will take a look at the hot standby and conduct hot standby simulation.

 

II

Three-Hot Standby Simulation

 

 

Back up a single database

 

Step 1: Remove LVM snapshots. (Skip this step if no one is created)

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

 

Step 2: Set the MySQL password

mysql> set password=password("123456");Query OK, 0 rows affected (0.00 sec)

 

Step 3: Check whether MySQL is started. Because it is a hot standby, MySQL service is required to start

[root@serv01 data]# /etc/init.d/mysqld status SUCCESS! MySQL running (2664)

 

Step 4: export a single database

[Root @ serv01 data] # cd/databackup/# The essence is to export 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 = '+ '*/;/*! 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 (11) default null, 'cname' varchar (30) default null) ENGINE = InnoDB default charset = latin1 ;/*! 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 (11) default null, 'sname' varchar (30) default null, 'cid' int (11) 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 'std' disable keys */; insert into 'std' 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 18:56:06 # Save the output result to the file [root @ serv01 databackup] # mysqldump-uroot-p123456 -- database larrydb> larrydb. SQL

 

For more details, please continue to read the highlights on the next page:

Implementation of MySQL backup and recovery

MySQL backup: mylvmbackup introduction and use

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases

Disk management-LVM

1 2 Next Page