Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief talk about Mysql in several commonly used data backup and recovery methods, related technical terms you can check online or find some related books to supplement, here is not involved, we will go straight to the topic, I hope Mysql in several commonly used data backup and recovery methods This article can bring you some practical help.

Mysql provides several data backup and recovery methods, I write a few commonly used;

1. mysqldump command;

2. Percona Xtrabackup tool;

First, a few definitions are needed:

Full backup: All data from the beginning to the moment when the backup is performed;

incremental backup: data that has changed since the last full backup or incremental backup;

Differential backup: backup data that has changed since the last full backup;

Hot backup: operations performed in a readable and writable state;

warm backup: operations performed in a readable but not writable state;

Cold backup: unreadable, unwritable, backup operations performed offline;

Physical backup: copying data files for data backup;

logical backup: backup sql statements rather than data;

Mysqldump tool is a very effective and commonly used backup tool, its content can help innodb transaction engine to achieve warm backup. It can also help MYISAM non-transactional storage engine backup tools faster;

Common options:

--all-databases, -A: backup all databases (full backup);

--databases, -B: Specify the databases to be backed up;

--flush-logs, -F: manually brush binary logs;

--quick, -q: Quickly export data, useful for exporting large tables;

--lock-all-tables, -x: Apply read locks to all tables until the end of data export;

--single-transaction: When exporting tables of innodb storage engine, open a transaction to ensure database consistency and persistence;

[root@localhost ~]# mysqldump --lock-tables --flush-logs --all-databases -q > ./ alldb.sql

It is recommended that binary be temporarily turned off while recovering the database

MariaDB [(none)]> set @@sql_log_bin=0;MariaDB [(none)]> \. /alldb.sqlMariaDB [(none)]> set @@sql_log_bin=1;

OR

[root@www ~]# mysql -uroot -p < /alldata.sqlEnter password:

Mysql in several common data backup and recovery methods will first give you here, for other related issues you want to know can continue to pay attention to our industry information. Our section content captures some industry news and expertise to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.