mysqldump backup and restore MySQL database

Brief introduction:

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

Basic operation:
Back up a single database, or a specific table in the library (the name of the library is appended with the table name)
Mysqldump backing up the JIAOWU library
[Email protected] ~]# mysqldump-uroot-p Jiaowu >/root/jiaowu.sql
To delete a JIAOWU database
mysql> DROP DATABASE Jiaowu;
Import backup file After delete says there is no JIAOWU database
Note: Mysqldump backed up databases are INSERT statements, there is no way to create a database when you need to manually create a database
[[email protected] ~]# MySQL < jiaowu.sql
ERROR 1046 (3d000) at line 22:no database selected
Manually creating a JIAOWU database
mysql> CREATE DATABASE Jiaowu;
Restore the JIAOWU database
[[email protected] ~]# MySQL Jiaowu < jiaowu.sql


If you need to lock all tables when you are backing up your production environment, you will not have time to write data when you are backing up.
Lock All Tables
Mysql> FLUSH TABLES with READ LOCK;
Release lock
Mysql> UNLOCK TABLES;

Parameter description:
--MASTER-DATA={0|1|2}
0: Do not log the binary log file record location;
1: Record location in Chnage MASTER to, can be used to start the server directly after recovery;
2: Record the position in the form of change MASTER to, but the default is to be commented;

Backing Up the JIAOWU database
[Email protected] ~]# mysqldump-uroot-p--master-data=2 jiaowu >/root/jiaowu-' Date +%f-%h-%m-%s '. sql

View Binary Logging Locations
[Email protected] ~]# 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: Performing log scrolling

If the table type in the specified library is InnoDB, use--single-transaction to start the hot spare and do not use it with the--lock-all-tables


Back up multiple libraries: Automatically create a library name when you back up, no need to create a library manually when restoring
--all-databases: Backing Up all libraries
--databases db_name,db_name,...: Backing up the specified library

--events the Backup Event Scheduler
--routines Backup stored procedures and stored functions.
--triggers backup triggers

simulation Experiment: Back up all libraries, and all libraries are broken how to restore
Use ROOT to back up all libraries, scroll log files, record binary file locations and paths, and lock all libraries
[Email protected] ~]# mysqldump-uroot-p--lock-all-table--flush-logs--all-databases--master-data=2 >/root/allda Tabases.sqlenter Password:

View the backed up database find the scrolling log to 000007.
[email protected] ~]# less Alldatabases.sql
--Change MASTER to master_log_file= ' mysql-bin.000007 ', master_log_pos=107;

Delete previous scroll log files (production environment is recommended to be copied and then deleted)
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 the 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 a row older than 80
Mysql> DELETE from Tutors WHERE age>80;
Query OK, 2 rows Affected (0.00 sec)


And then a day went by, to do an incremental backup

Scrolling log
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 |
+------------------+-----------+

Backing Up log files
[Email protected] ~]# cd/mydate/date/
[email protected] date]# CP mysql-bin.000007/root/

Insert a row of data into the table the next day
Mysql> INSERT into Tutors (tname) VALUES (' Zhangsan ');

Delete the database, but copy the binary log files, assuming that the log files and data are not stored in the same directory, or if the binary log files are deleted, there is no way to do a point-in-time recovery
[email protected] date]# CP mysql-bin.000008/root/
[Email protected] date]# RM-RF./*

You'll find that MySQL can't stop, so just close the process.
[[Email protected] date]# service mysqld stop
MySQL server PID file could not being found! Failed
[Email protected] date]# Killall mysqld

Initializing MySQL Database
[Email protected] date]# cd/usr/local/mysql/
[Email protected] mysql]# scripts/mysql_install_db--user=mysql--datadir=/mydate/date/


Restore a fully backed up database file first
[Email protected] ~]# Mysql-uroot-p < Alldatabases.sql
The data is then restored to a full backup.
Deleted two users older than 80 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 binaries of the first and second backups into SQL files, then import the first incremental and second incremental backups
[Email protected] ~]# Mysqlbinlog mysql-bin.000007 > Diyici.sql
[Email protected] ~]# Mysqlbinlog mysql-bin.000008 > Dierci.sql
[Email protected] ~]# Mysql-uroot-p < Diyici.sql
Enter Password:
[Email protected] ~]# 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: Close the binary log file when recovering the database, or generate a lot of unused records, after the data recovery is complete, open the record binary log file
Temporarily turn off binary logging
Mysql> SET sql_log_bin=0;
Query OK, 0 rows Affected (0.00 sec)
Open binary log file record
Mysql> SET sql_log_bin=1;
Query OK, 0 rows Affected (0.00 sec)

This article from "Plum blossom fragrance from bitter cold" blog, please be sure to keep this source http://wangjunkang.blog.51cto.com/8809812/1586092

mysqldump backup and restore MySQL database