MySQL's mysqldump backup recovery

MySQL data backup, its importance is self-evident. In general, we typically use mysqldump, xtrabackup, and LVM snapshots for backup, or a dedicated MySQL replication backup server.

Characteristics:

Logical Backup tool is slower than physical backup, but more flexible, can modify some information, and cross-platform is also simple.

If the amount of data is greater than 1G it is best to use a physical backup.

Single-threaded Backup tool, online with a multi-threaded logical backup tool called Mysqldumper, interested friends can see.

The InnoDB storage engine can be implemented as a hot standby or MyISAM, Aria Win Bei.

You cannot implement incremental or differential backups by itself, and if you are backing up a full library, you can do so by backing up the binary log file.

The equivalent is the MySQL client, as long as the ability to connect to the server can be remotely backed up.

Backup users should have the following permissions:

SELECT, RELOAD, SUPER, REPLICATION CLIENT

command usage and common options:

mysqldump [Options] [db_name [Tbl_name ...]

-A,--all-databases	Back up all libraries.
-B,--databases	Specifies the library to be backed up.
-F,--flush-logs	Scrolls the binary log. It is easier to record the location of the binary log when the backup is made.
-X,--lock-all-tables	Locks all tables. Read lock. For Win Bei. Used for storage engines that do not support transactions.
--single-transaction	Create a large transaction to use for hot provisioning. Can only be used with the transaction store engine, such as InnoDB.
--MASTER-DATA[0|1|2]	Synchronize the binary log location tags. 0: No record, 1: Record change master statement 2: Change master statement recorded as comment

Backup code:
--events: Backing up event Scheduler code
--routines: Backing up stored procedures and storage functions
--triggers: Backup Trigger

Backup:

To add a backup user:

MariaDB [(None)]> GRANT SELECT, RELOAD, SUPER, REPLICATION CLIENT on *. * to ' backup ' @ ' localhost ' identified by ' abcd123 4 '; Query OK, 0 rows Affected (0.00 sec)

As I backup the HELLODB library:

[Email protected] binlogs]# mysqldump-ubackup-p--lock-all-tables--flush-logs--master-data=2--all-databases >/ backup/' Date +%y%m%d_%h:%m '. Full.sql[[email protected] backup]# cd/backup[[email protected] backup]# ls20151107_ 22:29.full.sql

This command is output directly to the screen, so you need to redirect to the file.

Using Head-n 30来 to view the backup file, you can see:

--Change MASTER to master_log_file= ' master-bin.000017 ', master_log_pos=245;

This is the information added by--master-data=2, indicating the location of the binary log file when backing up the table.

This is a new binary log file and is a feature of--flush-log. This makes it easier to restore or make incremental backups of binary logs.

Because some of the table is MyISAM, so with the--lock-all-tables do the lock, to achieve Win Bei. If you back up a single library, the tables are InnoDB and can be converted to--single-transaction to generate large transactions to make hot spares.

Note Use head or other commands to see if the file is a backup file, in case the error message will be crying.

Incremental Backup: prevents corruption in binary logs.

Because the binary log files are global, you can use this method if you have backed up all the libraries. If you have multiple libraries and only one library is backed up or only one table is backed up. That's the trouble, generally it's better not to do so.

Backup:

If: Because of data modification, 000017 log has a lot of information, then want to make an incremental backup, you can directly flush LOGS, to scroll the log. Then copy the original log content.

mariadb [hellodb]> show master status;        # See if the log currently in use is still 000017. +-------------------+----------+--------------+------------------+| file               | Position | Binlog_Do_DB |  binlog_ignore_db |+-------------------+----------+--------------+------------------+|  master-bin.000017 |     8012 |               |                   |+-------------------+----------+--------------+------------------+1  row in set  (0.00 sec)      #如果日志不是000017, that will copy 17 to the current log file. mariadb [hellodb]> flush logs; query ok, 0 rows affected  (0.01 sec) Mariadb [hellodb]> show master status;     #一定要做好所使用的日志的记录 +-------------------+------ ----+--------------+------------------+| file               | position | binlog_do_db | binlog_ignore_db |+--------- ----------+----------+--------------+------------------+| master-bin.000018 |       245 |               |                   |+-------------------+----------+--------------+------------------+1 row in set  ( 0.00 SEC) mariadb [hellodb]>

The log file that was found to be used has changed. Then we can use Mysqlbinlog to copy the contents of the log file.

Because the binary log file is binary, you can use this command to open the log.

[[email protected] binlogs]# ls #二进制日志文件的目录master-bin.000001 master-bin.000004 master-bin.000007 master-bin.0000 Ten master-bin.000013 master-bin.000016 master-bin.indexmaster-bin.000002 master-bin.000005 master-bin.000008 master-  bin.000011 master-bin.000014 master-bin.000017master-bin.000003 master-bin.000006 master-bin.000009 master-bin.000012 master-bin.000015 master-bin.000018

[Email protected] binlogs]# mysqlbinlog--start-position=245 master-bin.000017 >/backup/' Date +%y%m%d_%h:%m '. 1. Sql[[email protected] binlogs]# cd/backup[[email protected] backup]# ls20151107_22:29.full.sql 20151107_22:52.1.sql

Recovery:

1. Close the Sql_log_bin session variable and temporarily stop the binary log's entry. The modification statements in the recovery process are not written to the log.

2. Restore a full backup.

3. Restore incremental backups (no incremental backups are all binary logs after a full backup)

MariaDB [hellodb]> SET sql_log_bin=0; Query OK, 0 rows Affected (0.00 sec) MariaDB [hellodb]> source/backup/20151107_22:29.full.sqlmariadb [hellodb]> sou RCE/BACKUP/20151107_22:52.1.SQLMARIADB [hellodb]> SET sql_log_bin=1;

Do in time point back to the original and then the incremental backup after the binary log files to restore a bit. It's too much trouble to make a backup with mysqldump. In general, if the binary log disk is high security also do not have to do incremental, then directly restore the binary log is the same. And if the amount of data is large there is no need to mysqldump backup.

This two days lit, mouth cracked and oral ulcer, a cold rhinitis also committed. Oh my God. 650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0012.gif "alt=" J_0012.gif "/>

This article is from the "Big Tomato" blog, be sure to keep this source http://fanqie.blog.51cto.com/9382669/1710719

MySQL's mysqldump backup recovery