Mysqldump back up and restore a MySQL database

Mysqldump back up and restore a MySQL database

Brief Introduction:

The mysqldump command can be used to export all specified databases and tables as SQL scripts, which can be used on Mysql in different sections. For example, to upgrade the Mysql database, you can use mysqldump to back up all the databases and then import them directly to the upgraded Mysql database.

Basic operations:
Back up a single database or a specific table in the database (the database name is followed by the table name)
Mysqldump back up the jiaowu Database
[Root @ localhost ~] # Mysqldump-uroot-p jiaowu>/root/jiaowu. SQL
Delete the database of jiaowu
Mysql> drop database jiaowu;
After deletion, import the backup file, saying there is no jiaowu Database
Note: The databases backed up by mysqldump are all inserted statements. During restoration, there is no way to create a database. You need to manually create a database.
[Root @ localhost ~] # Mysql <jiaowu. SQL
ERROR 1046 (3D000) at line 22: No database selected
Manually create a database of jiaowu
Mysql> create database jiaowu;
Restore the jiaowu Database
[Root @ localhost ~] # Mysql jiaowu <jiaowu. SQL

If all tables need to be locked during backup in the production environment, otherwise, Data Writing by users during Backup may lead to different time points.
Lock all tables
Mysql> flush tables with read lock;
Release lock
Mysql> unlock tables;

Parameter description:
-- Master-data = {0 | 1 | 2}
0: do not record the location of the binary log file;
1: The position is recorded as a chnage master to, which can be used TO directly start the slave server after recovery;
2: positions are recorded in the form of change master to, but are annotated by default;

Back up the jiaowu Database
[Root @ localhost ~] # Mysqldump-uroot-p -- master-data = 2 jiaowu>/root/jiaowu-'date + % F-% H-% M-% s'. SQL

View the location of a binary log
[Root @ localhost ~] # Vim jiaowu-2014-11-27-17-02-38. SQL.
-- Change master to MASTER_LOG_FILE = 'mysql-bin.000005 ', MASTER_LOG_POS = 9749;

-- Lock-all-tables: lock all tables

-- Flush-logs: Execution log scrolling

If the table type in the specified database is InnoDB, you can use -- single-transaction to start hot backup. Do not use it with -- lock-all-tables.

Multiple databases are backed up: the database name is automatically created during backup. You do not need to create a database manually during restoration.
-- All-databases: backs up all databases
-- Databases DB_NAME, DB_NAME,...: Backup specified database

-- Events backup event scheduler's
-- Routines backs up stored procedures and functions
-- Triggers backup trigger

Simulation experiment: Back up all databases and restore them when all databases are broken
Use the root user to back up all databases, scroll log files, record the location and path of binary files, and lock all databases
[Root @ localhost ~] # Mysqldump-uroot-p -- lock-all-table -- flush-logs -- all-databases -- master-data = 2>/root/alldatabases. sqlEnter password:

Check the backed up database and find that the rolling log reaches 000007.
[Root @ localhost ~] # Less alldatabases. SQL
-- Change master to MASTER_LOG_FILE = 'mysql-bin.000007 ', MASTER_LOG_POS = 107;

Delete the previous rolling log file (we recommend that you copy the file before deleting it in the production environment)
Mysql> purge binary logs to 'mysql-bin.000007 ';
Query OK, 0 rows affected (0.19 sec)

Mysql> show binary logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000007 | 107 |
+ ------------------ + ----------- +

View data in the tutors table
Mysql> USE jiaowu;
Database changed
Mysql> SELECT * FROM tutors;
+ ----- + -------------- + -------- + ------ +
| TID | Tname | Gender | Age |
+ ----- + -------------- + -------- + ------ +
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+ ----- + -------------- + -------- + ------ +

Delete rows older than 80
Mysql> delete from tutors WHERE Age> 80;
Query OK, 2 rows affected (0.00 sec)

Then one day passes and Incremental backup is required.

Rolling logs
Mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

Mysql> show binary logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000007 | 343 |
| Mysql-bin.000008 | 107 |
+ ------------------ + ----------- +

Back up log files
[Root @ localhost ~] # Cd/mydate/date/
[Root @ localhost date] # cp mysql-bin.000007/root/

Insert a row of data to the table the next day.
Mysql> insert into tutors (Tname) VALUES ('hangsan ');

Delete the database, but copy the binary log file. Assume that the log file and data are not stored in the same directory. Otherwise, if the binary log file is deleted, there is no way to restore it at a time point.
[Root @ localhost date] # cp mysql-bin.000008/root/
[Root @ localhost date] # rm-rf ./*

MySQL cannot be stopped, so you only need to close the process.
[Root @ localhost date] # service mysqld stop
MySQL server PID file cocould not be found! [Failed]
[Root @ localhost date] # killall mysqld

Initialize the MySQL database
[Root @ localhost date] # cd/usr/local/mysql/
[Root @ localhost mysql] # scripts/mysql_install_db -- user = mysql -- datadir =/mydate/date/

First, restore the fully backed up database file.
[Root @ localhost ~] # Mysql-uroot-p <alldatabases. SQL
At this time, the data is restored before the full backup.
Two deleted users older than 80 years old still exist.
Mysql> USE jiaowu;
Database changed
Mysql> SELECT * FROM tutors;
+ ----- + -------------- + -------- + ------ +
| TID | Tname | Gender | Age |
+ ----- + -------------- + -------- + ------ +
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+ ----- + -------------- + -------- + ------ +
9 rows in set (0.00 sec)

Convert the binary files of the first and second backups into SQL files, and then import the first Incremental backup and the second Incremental backup.
[Root @ localhost ~] # Mysqlbinlog mysql-bin.000007> diyici. SQL
[Root @ localhost ~] # Mysqlbinlog mysql-bin.000008> dierci. SQL
[Root @ localhost ~] # Mysql-uroot-p <diyici. SQL
Enter password:
[Root @ localhost ~] # Mysql-uroot-p <dierci. SQL
Enter password:

Then the data is restored.
Mysql> SELECT * FROM tutors;
+ ----- + -------------- + -------- + ------ +
| TID | Tname | Gender | Age |
+ ----- + -------------- + -------- + ------ +
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
| 10 | zhangsan | M | NULL |
+ ----- + -------------- + -------- + ------ +

Note: Production Environment: Disable the binary log file when restoring the database. Otherwise, a lot of useless records will be generated. After data recovery is complete, enable the recording binary log file.
Temporarily disable binary Logging
Mysql> SET SQL _log_bin = 0;
Query OK, 0 rows affected (0.00 sec)
Enable binary log file Logging
Mysql> SET SQL _log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

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

Use mysqldump in Linux to regularly back up MySQL Databases

Mysqldump missing-q Parameter causes MySQL to be killed by oom

Mysqldump and LVM logical volume Snapshot

MySQL backup solution --> (using mysqldump and binlog binary logs)

[MySQL] Using mysqldump for text backup

This article permanently updates the link address: