MySQL cold backup process

Time:2021-7-20

MySQL cold backup process

Package and compress the data file of MySQL, and copy the corresponding file to cover when you want to recover.

Cold backup is actually the simplest backup method (well, I almost crashed the database the first time). However, due to the requirement of stopping MySQL service for backup and recovery, and only full backup, the practicability is not high.

backups

First, enter the database and find out the directory where the data files are stored

mysql> show variables like ‘%dir%‘;

| Variable_name | Value |

| — | — |

| basedir | /usr |

| binlog_direct_non_transactional_updates | OFF |

| character_sets_dir | /usr/share/mysql/charsets/ |

| datadir | /var/lib/mysql/ |

| innodb_data_home_dir | |

| innodb_log_group_home_dir | ./ |

| innodb_max_dirty_pages_pct | 75 |

| lc_messages_dir | /usr/share/mysql/ |

| plugin_dir | /usr/lib/mysql/plugin/ |

| slave_load_tmpdir | /tmp |

| tmpdir | /tmp |

+—————————————–+—————————-+

11 rows in set (0.00 sec)

The data directory is where dataDir is located, that is, / var / lib / MySQL/

Then execute:

CD / var / lib / # into its superior directory

Service MySQL stop # close the MySQL service. I didn't actually close it here, but I haven't tested it under high data volume

Tar jcvf ~ / backup.tar.bz2 MySQL / # package and compress the directory to the root directory

recovery

Execute the following command:

Cd ~ / # enter the storage directory of the backup file

Tar jxvf backup.tar.bz2 MySQL / #

Gunzip - R MySQL / # recursive decompression

Service MySQL stop # the service must be shut down first

RM - R / var / lib / MySQL / # delete original directory

MV ~ / MySQL // var / lib / # move the backup file to / var / lib / to replace the original mysql

Service mysql start # restart MySQL service

It should be noted as like as two peas, the database is identical after the restoration, including all the modifications and account passwords.

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]