One, the database full backup operation
1, physical cold backup and recovery; Use the TAR command to package the database folder directly and use the stop MySQL service before backing up
back up the database, create a/backup directory as the backup database path, and use tar to create the backup file. The entire database folder backup belongs to a full backup [[email protected] ~]# systemctl stop mysqld.service[[email protected] ~]# mkdir/backup[[ Email protected] ~]# tar zcf/backup/mysql_all-$ (date +%f). tar.gz/usr/local/mysql/data/[[email protected] ~]# ls-l/backup/Total dosage 736-rw-r--r--1 root root 751542 August 08:40 mysql_all-2018-08-15.tar.gz Restore the database; Perform the following operation to/usr/lo the database file The cal/mysql/data/is transferred to the Bak directory, simulating the failure. [[[email protected] ~]# mkdir/bak[[email protected] ~]# mv/usr/local/mysql/data//bak/perform the following operations to recover data from a backup file [[ Email protected] ~]# mkdir restore/[[email protected] ~]# tar zxf/backup/mysql_all-2018-08-15.tar.gz-c Restore/[[email protected] ~]# mv restore/usr/local/mysql/data//usr/local/mysql/[[email protected] ~]# Systemctl Start Mysqld.service
2, mysqldump backup and restore
back up the database, when you export data by using the mysqldump command, the default is displayed directly in the terminal, to save to a file, you also need to combine the Shell's > Redirection
Output operation. The command format is as follows.
Format 1; Back up some tables in the specified database
mysqldump [options] Library name [table name 1] [table Name 2] ... >/backup path/backup file name
Format 2; Back up one or more complete libraries (including all of them)
Mysqldump [ Options]--databases Library name 1 [library Name 2] ... >/backup path/backup file name
Format 3; Back up all libraries in MySQL database
mysqldump [options]--all-databases >/backup path/backup file name
The commonly used options are "-u", "-P", respectively, to specify the user name and password for the database.
For example, the following operations use Format 1, format 2, the user table in the MySQL library as Mysql-user.sql, export the entire Benet library
as a benet.sql file, all operations are authenticated as root user
[[email protected] ~]# mysqldump-u root-p mysql user >mysql-user.sql
Enter password: # #mysql中root密码
[[email protected] ~]# mysqldump-u root-p--databases benet > Benet.sqlenter Password: # #mysql中root密码若需要备份整个mysql服务器中的所有库, Format 3 should be used. When the amount of data exported is large, you can add the "--opt" option to optimize execution speed. For example, do the following to create a backup file, All-data.sql, that contains all the libraries in the MySQL server. [[email protected] ~]# mysqldump-u root-p--opt--all-databases > All-databases.sqlenter Password: # #mysql中root密码查看备份文件通过mysqldump工具导出的SQL脚本文本文件, where the "/*...*/" section or the line starting with-- Represents the comment information. You can view the contents of a file using grep, less, cat, and other text tools. For example, perform the following actions to filter out the database action statements in the Benet.sql script [[email protected] ~]# grep-v ^--benet.sql | Grep-v "^/" | Grep-v "^$" CREATE DATABASE/*!32312 IF not exists*/' Benet '/*!40100 DEFAULT CHARACTER SET UTF8 */; Use ' Benet ';D rop TABLE IF EXISTS ' users '; CREATE TABLE ' users ' (' user_name ' char (\) ' Not NULL, ' user_passwd ' char (+) ' DEFAULT ', PRIMARY KEY (' user_name ')) engine=i Nnodb DEFAULT Charset=utf8; LOCK TABLES ' users ' write;insert into ' uSers ' VALUES (' Lisi ', ' 123123 '), (' Zhangsan ', ' *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 '); UNLOCK TABLES; restore the database; Use the mysqldump command to export the SQL backup script, which can be imported via the MySQL command when recovery is required, with the following command format. mysql [options] [library name] [table name] </backup path/backup filename When the backup file contains only a backup of the table, not the statement that created the library, the library name must be established when the import operation is performed, and the target library must exist. For example, the following operation can import a table from the backup file Mysql-user.sql to the test library. Where the "-e" option is used to specify a command to connect to MySQL after execution, the command exits automatically after execution. [[email protected] ~]# mysql-u root-p test < mysql-user.sql Enter Password: # #mysql中root密码 [[email protected] ~]# mysql-u root-p-E ' Show tables from test; ' Enter Password: # #mysql中root密码 +----------------+| Tables_in_test |+----------------+| User |+----------------+ If the backup file already contains complete database information, you do not need to specify the library name when you perform the import operation. For example, specify that the operation can be recovered from the backup file Benet.sql to the Benet library. [[email protected] ~]# mysql-u root-p-E ' drop database benet; ' # #删除benet库, simulate database failure enter password: [[email protected] ~]# mysql-u root-p-e ' show databases; ' # #查看到benet库已经不存在了Enter Password: +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+[[email protected] ~]# mysql-u root-p < ~/benet.sqlenter password: [[E Mail protected] ~]# mysql-u root-p-e ' show databases; ' Enter Password: +--------------------+| Database |+--------------------+| Information_schema | | Benet | | MySQL | | Performance_schema | | Test |+--------------------+Two, incremental backup
Unlike full backups, incremental backups do not have duplicate data, backup volumes are small, time is short, but recovery headaches require the last full backup and full backup
Incremental backup to recover, and to reverse-push all incremental backups, there is no way to provide a direct incremental backup in MySQL, which can be
My binary log (binary logs) provided by MySQL indirectly implements incremental backups.
二进制日志要进行MySQL的增量备份，首先要开启二进制功能，开启MySQL的二进制日志功能的实现方法有很多种，最常用的是在MySQL配置文件的mysqld项下加入“log-bin=/文件路径/文件名”前缀，如；log-bin=/usr/local/mysql/mysql-bin,然后重启mysqld.service服务，就可以看到指定路径下的二进制文件文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如；mysql-bin.000001[[email protected] ~]# vim /etc/my.cnf ##修改配置文件，启用二进制功能log-bin=/usr/local/mysql/mysql-bin:wq[[email protected] ~]# systemctl restart mysqld.service [[email protected] ~]# ls -l /usr/local/mysql/mysql-bin.*-rw-rw---- 1 mysql mysql 120 8月 15 10:35 /usr/local/mysql/mysql-bin.000001-rw-rw---- 1 mysql mysql 34 8月 15 10:35 /usr/local/mysql/mysql-bin.index
MySQL Incremental recovery
There are three kinds of incremental recovery methods commonly used, general recovery, location-based recovery, point-in-time recovery
一般恢复；将所有备份的二进制日志内容全部恢复，格式命令如下mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u root -p基于位置的恢复；数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。格式一；恢复数据到指定位置mysqlbinlog --stop-position=‘459‘ 二进制日志 | mysql -u root -p ******格式二；从指定的位置开始恢复数据mysqlbinlog --start-position=‘459‘ 二进制日志 | mysql -u root -p ******基于时间点的恢复；跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况。格式1；从日志开头截止到某个时间点恢复mysqlbinlog [--no-defaults] --stop-datetime=‘年-月-日 小时:分钟:秒‘ 二进制日志 | mysql -u 用户 -p 密码格式2；从某个时间点到日志结尾恢复mysqlbinlog [--no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒‘ 二进制日志 | mysql -u 用户 -p 密码格式3；从某个时间点到某个时间点恢复mysqlbinlog [--no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒‘ --stop-datetime=‘年-月-日 小时:分钟:秒‘ 二进制日志 | mysql -u 用户 -p 密码下面我们通过一个企业级的备份案例讲解增量备份与恢复的具体操作。
Three, enterprise backup case
Requirements description; The user information database of Beijing Mobile Company is client, the user tariff data table is User_info, the weekly need to make a full backup, need to make incremental backup every day,
The newly added User information table is as follows
----------------------------------------------------------------------- ××× 姓名 性别 用户ID号 资费----------------------------------------------------------------------- 000006 张三 男 016 10 000007 李四 女 017 19 000008 王五 女 018 23 000009 赵六 男 019 37 000010 孙七 男 020 36-----------------------------------------------------------------------
General recovery
1) Add database, table, input information
[Email protected] ~]# mysql-u root-p
Enter Password:
Mysql> CREATE database client;
Query OK, 1 row Affected (0.00 sec
mysql> use client;
Database changed
Mysql> CREATE TABLE User_info (Xxxchar) not NULL, name char (a) not NULL, gender char (4), User ID number char (TEN) not NULL, tariff int (10)) ;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user_info values (‘000006‘,‘张三‘,‘男‘,‘016‘,‘10‘);Query OK, 1 row affected (0.00 sec)mysql> insert into user_info values (‘000007‘,‘李四‘,‘女‘,‘017‘,‘91‘);Query OK, 1 row affected (0.01 sec)mysql> insert into user_info values (‘000008‘,‘王五‘,‘女‘,‘018‘,‘23‘);Query OK, 1 row affected (0.00 sec)mysql> select * from user_info;+-----------+--------+--------+-------------+--------+| ××× | 姓名 | 性别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男 | 016 | 10 || 000007 | 李四 | 女 | 017 | 91 || 000008 | 王五 | 女 | 018 | 23 |+-----------+--------+--------+-------------+--------+3 rows in set (0.00 sec)
2) make a full backup first
To facilitate verification of the recovery of binary log increments, a full backup is performed on the Client.user_info table that inserts three rows of data, and then under Linux system commands
Execute the "mysqladmin-uroot-p123123 flush-logs" command or execute "flush logs" at the "mysql>" command prompt to generate a new binary log.
[[email protected] ~]# mysqldump -uroot -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sqlEnter password:[[email protected]lhost ~]# ls /mysql_bak/client_userinfo-2018-08-15.sql[[email protected] ~]# mysqladmin -uroot -p flush-logsEnter password:[[email protected] ~]# ls -l /usr/local/mysql/mysql-bin.*-rw-rw---- 1 mysql mysql 1252 8月 15 11:50 /usr/local/mysql/mysql-bin.000001-rw-rw---- 1 mysql mysql 120 8月 15 11:50 /usr/local/mysql/mysql-bin.000002-rw-rw---- 1 mysql mysql 68 8月 15 11:50 /usr/local/mysql/mysql-bin.index
3) Continue to enter new data and make incremental backups
Add two rows of data and perform the "mysqladmin-uroot-p flush-logs" command to refresh the binary log for incremental backups,
The operation of inserting two user data is preserved in the binary log file mysql-bin.000003
mysql> use client;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a
Database changedmysql> INSERT INTO user_info values (' 000009 ', ' Zhao Liu ', ' Male ', ' 019 ', ' 37 '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into user_info values (' 000010 ', ' Sun seven ', ' Male ', ' 020 ', ' 36 '); Query OK, 1 row Affected (0.00 sec) mysql> Select * from user_info;+-----------+--------+--------+-------------+------ --+| Xxx| name | sex | User ID Number | Fee |+-----------+--------+--------+-------------+--------+| 000006 | Zhang San | Male | 016 | 10 | | 000007 | John Doe | Women | 017 | 91 | | 000008 | Harry | Women | 018 | 23 | | 000009 | Zhao Liu | Male | 019 | 37 | | 000010 | Sun Seven | Male | 020 | |+-----------+--------+--------+-------------+--------+5 rows in Set (0.00 sec) [[email protected] ~]# ls-l/usr /LOCAL/MYSQL/MYSQL-BIN.*-RW-RW----1 MySQL mysql 1252 August 11:50/usr/local/mysql/mysql-bin.000001-rw-rw----1 MySQL Mys QL 679 August 12:02/usr/local/mysql/mysql-bin.000002-rw-rw----1 mysql mysql 120 August 12:02/uSR/LOCAL/MYSQL/MYSQL-BIN.000003-RW-RW----1 MySQL mysql 102 August 12:02/usr/local/mysql/mysql-bin.index 4) Analog misoperation Delete User_info table
[Email protected] ~]# mysql-uroot-p-e ' drop table client.user_info; '
Enter Password:
[Email protected] ~]# mysql-uroot-p-E ' select * from Client.user_info; '
Enter Password:
ERROR 1146 (42S02) at line 1:table ' client.user_info ' doesn ' t exist
5) Restore operation
When performing a recovery operation, a full recovery is required before an incremental recovery is performed.
[Email protected] ~]# mysql-u root-p Client </backup_bak/client_userinfo-2018-08-18.sql
Enter Password:
[Email protected] ~]# mysql-u root-p-E ' Selectfrom Client.user_info; '
Enter Password:
+-----------+--------+--------+-------------+--------+
| Xxx| name | sex | User ID Number | Tariff |
+-----------+--------+--------+-------------+--------+
| 000006 | Zhang San | Male | 016 | 10 |
| 000007 | John Doe | Women | 017 | 91 |
| 000008 | Harry | Women | 018 | 23 |
+-----------+--------+--------+-------------+--------+
General recovery
[Email protected] ~]# Mysqlbinlog--no-defaults/usr/local/mysql/mysql-bin.000002 | Mysql-u root-p
Enter Password:
[Email protected] ~]# mysql-u root-p-E ' selectfrom Client.user_info; '
Enter Password:
+-----------+--------+--------+-------------+--------+
| Xxx| name | sex | User ID Number | Tariff |
+-----------+--------+--------+-------------+--------+
| 000006 | Zhang San | Male | 016 | 10 |
| 000007 | John Doe | Women | 017 | 91 |
| 000008 | Harry | Women | 018 | 23 |
| 000009 | Zhao Liu | Male | 019 | 37 |
| 000010 | Sun Seven | Male | 020 | 36 |
+-----------+--------+--------+-------------+--------+
基于位置恢复[[email protected] ~]# mysqlbinlog --no -defaults /usr/local/mysql/mysql-bin.000002 ##查看二进制文件中的内容以便恢复mysqlbinlog: unknown option ‘--no‘
[Email protected] ~]# Mysqlbinlog--no-defaults/usr/local/mysql/mysql-bin.000002
/!50530 SET @ @SESSION. Pseudo_slave_mode=1/;
/!40019 SET @ @session. max_insert_delayed_threads=0/;
/!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0/;
DELIMITER/!/;
# at 4
#180818 14:16:21 Server ID 1 end_log_pos CRC32 0xf20e69c2 start:binlog v 4, Server v 5.6.36-log created 180818 14:16: 21st
BINLOG '
Tbl3ww8baaaadaaaahgaaaaaaaqans42ljm2lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Aaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaaxaaegggaaaaicagcaaaacgokgrkaacjp
Dvi=
‘/!/;
# at
#180818 14:17:53 Server ID 1 end_log_pos 203 CRC32 0x122cbd0b Query thread_id=4 exec_time=0 error_code=0
SET timestamp=1534573073/!/;
SET @ @session. pseudo_thread_id=4/!/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/!/;
SET @ @session. sql_mode=1075838976/!/;
SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/!/;
/!\c UTF8//!/;
SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/!/;
SET @ @session. lc_time_names=0/!/;
SET @ @session. collation_database=default/!/;
BEGIN
/!/;
# at 203
#180818 14:17:53 Server ID 1 end_log_pos 345 CRC32 0xa4c70118 Query thread_id=4 exec_time=0 error_code=0
Useclient
SET timestamp=1534573073/!/;
INSERT into user_info values (' 000009 ', ' Zhao Liu ', ' Male ', ' 019 ', ' 37 ')
/!/;
# at 345
#180818 14:17:53 Server ID 1 end_log_pos 376 CRC32 0x7599a729 Xid = 46
commit/!/;
# at 376
#180818 14:18:04 Server ID 1 end_log_pos 459 CRC32 0x1ff3f3f2 Query thread_id=4 exec_time=0 error_code=0
SET timestamp=1534573084/!/;
BEGIN
/!/;
# at 459
#180818 14:18:04 Server ID 1 end_log_pos 601 CRC32 0xea02a0b2 Query thread_id=4 exec_time=0 error_code=0
SET timestamp=1534573084/!/;
INSERT into user_info values (' 000010 ', ' Sun seven ', ' Male ', ' 020 ', ' 36 ')
/!/;
# at 601
#180818 14:18:04 Server ID 1 end_log_pos 632 CRC32 0xc9b66df7 Xid = 47
commit/!/;
# at 632
#180818 14:19:23 Server ID 1 end_log_pos 679 CRC32 0x2bf94bf2 Rotate to mysql-bin.000003 pos:4
DELIMITER;
# End of log file
ROLLBACK/added by Mysqlbinlog/;
/!50003 SET [email protected]_completion_type/;
/!50530 SET @ @SESSION. Pseudo_slave_mode=0/;
Location-based recovery: Method one
[Email protected] ~]# mysqlbinlog--no-defaults--stop-position= ' 459 '/usr/local/mysql/mysql-bin.000002 | Mysql-u root-p
Enter Password:
[Email protected] ~]# mysql-u root-p-E ' select * from Client.user_info; '
Enter Password:
+-----------+--------+--------+-------------+--------+
| Xxx| name | sex | User ID Number | Tariff |
+-----------+--------+--------+-------------+--------+
| 000006 | Zhang San | Male | 016 | 10 |
| 000007 | John Doe | Women | 017 | 91 |
| 000008 | Harry | Women | 018 | 23 |
| 000009 | Zhao Liu | Male | 019 | 37 |
+-----------+--------+--------+-------------+--------+
Location-based recovery: Method two
[[[email protected] ~]# mysql-u root-p-e ' drop table client.user_info; '
Enter Password:
[[email protected] ~]# mysql-u root-p Client </backup_bak/client_ Userinfo-2018-08-18.sql
Enter Password:
[[email protected] ~]# mysqlbinlog--no-defaults-- start-position= ' 459 '/usr/local/mysql/mysql-bin.000002 | Mysql-u root-p
Enter Password:
[[email protected] ~]# mysql-u root-p-E ' select * from Client.user_info; '
Enter Password:
+-----------+--------+--------+-------------+--------+
|xxx| name | gender | User ID number | tariff |
+-----------+--------+--------+-------------+--------+
| 000006 | Zhang San | male | 016 | 10 |
| 000007 | John Doe | female | 017 | 91 |
| 000008 | harry | female | 018 | 23 |
| 000010 | Sun Seven | men | 020 | 36 |
+-----------+--------+--------+-------------+--------+
Based on point in time
Method: Restore from the beginning of the log to a point in time
[[email protected] ~]# mysql-u root-p-e ' drop table client.user_info; '
Enter Password:
[[email protected] ~]# mysql-u root-p Client </backup_bak/client_ Userinfo-2018-08-18.sql
Enter Password:
[[email protected] ~]# mysqlbinlog--no-defaults--stop-datetime = ' 2018-08-18 14:18:04 '/usr/local/mysql/mysql-bin.000002 | Mysql-u root-p
Enter Password:
[[email protected] ~]# mysql-u root-p-E ' select * from Client.user_info; '
Enter Password:
+-----------+--------+--------+-------------+--------+
|xxx| name | gender | User ID number | tariff |
+-----------+--------+--------+-------------+--------+
| 000006 | Zhang San | male | 016 | 10 |
| 000007 | John Doe | female | 017 | 91 |
| 000008 | harry | female | 018 | 23 |
| 000009 | Zhao LIU | male | 019 | 37 |
+-----------+--------+--------+-------------+--------+
Method Three: Point-in-time [[email protected] ~]# mysql-u root-p-e ' drop table client.user_info; ' Enter password: [[email protected] ~]# mysql-u root-p Client </backup_bak/client_userinfo-2018-08-18.sqlenter Password: [[email protected] ~]# mysqlbinlog--no-defaults--start-datetime= ' 2018-08-18 14:18:04 '-- Stop-datetime= ' 2018-08-18 14:19:23 '/usr/local/mysql/mysql-bin.000002 | Mysql-u root-penter Password: [[email protected] ~]# mysql-u root-p-E ' select * from Client.user_info; ' Enter Password: +-----------+--------+--------+-------------+--------+| Xxx| name | sex | User ID Number | Fee |+-----------+--------+--------+-------------+--------+| 000006 | Zhang San | Male | 016 | 10 | | 000007 | John Doe | Women | 017 | 91 | | 000008 | Harry | Women | 018 | 23 | | 000010 | Sun Seven | Male | 020 | |+-----------+--------+--------+-------------+--------+
(2) centos7--mysql-5.6.36 backup
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
