Implement logical database backup using mysqldump

Mysqldump is a tool used by mysql for logical backup. During database backup, you need to back up the data and binary files of the database.

Mysqldump is a tool used by mysql for logical backup. During database backup, you need to back up the data and binary files of the database.

Mysqldump is a tool used by mysql for logical backup. During database backup, you need to back up the data and binary files of the database, even if it is restored ).

The following is a brief introduction.

Backup start:

①: During mysql login, you need to enter a password. To avoid the hassle of entering a password every time, you can create a password in the home directory of the login user. my. the hidden cnf file. Take the root user as an example.

Vi/root/. my. cnf

[Client]

User = 'root'

Password = 'exit'

Host = 'localhost'

When you log on again, you can directly log on to the mysql system.

② Back up the database

Connect mysql to the database

Show master status; // view the binary log file currently in use.

Mysqldump-all-databases-lock-all-tables-flush-logs-master-data = 2>/root 'date + % F-% H-% M-% s '. SQL

Now the database has been backed up, and the following is a backup of the binary log file. After the backup or data, the binary log file is refreshed, which is generated using a new binary log file, the file name is called the serial number of the previous binary file plus one.

Back up the previously refreshed binary log files

Mysqlbinlog/data/mydata/mysql-bin.000005>/root/1. SQL

Connect mysql to the database for incremental operations

Use stu;

Insert into score (name, score) values ('xiaozhang', 96 );

Select * from score;

Quit

Incremental backup after one day

Mysqlbinlog/data/mydata/mysql-bin.000006>/root/2. SQL

③ Simulate database damage.

Cd/data/mydata

Rm-rf *

Killall mysqld // force stop all mysql processes.

Reinitialize the database

Cd/usr/local/mysql

Scripts/mysql_install-db -- user = mysql-datadir =/data/mydata

Connect to mysql Before restoring the database and disable the binary log file function.

Mysql

Set SQL _log_bin = 0 // The binary log function does not need to be enabled during database restoration.

Quit

Mysql

Mysql

Mysql

After restoration, connect to mysql to enable the binary log function.

Mysql

Set SQL _log_bin = 1

Purge binary logs to 'mysql-bin.000006 '; // Delete unnecessary binary log files.

Now, let's talk about the logical restoration of mysqldump. Continue later.

Use mysqldump to back up MariaDB

Use mysqldump to export a database

Quickly build a slave Database Based on mysqldump

Restore the backup set created by mysqldump

Use mysqldump command line tool to create logical backup

This article permanently updates the link address: