Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL how to full, incremental backup and restore data", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL how to full, incremental backup and restore data" bar!

The importance of data backup

1. In a production environment, the security of data is crucial, and any loss of data may have serious consequences.

2. The cause of data loss

Program error

Human error

Computer failure

Disk failed

Disaster and theft.

Classification of database backup

From a physical and logical point of view, backups can be divided into:

1. Physical backup: backup of physical files (such as data files, log files, etc.) of the database operating system

Physical backup can be divided into offline backup (cold backup) and online backup (hot backup):

Cold backup: when the database is closed

Hot backup: the database is running, and this backup method depends on the log files of the database

2. Logical backup: backup of database logical components (such as tables and other database objects)

From the point of view of the backup strategy of the database, backup can be divided into:

Full backup: make a full backup of the data each time

Differential backup: back up files that have been modified since the last full backup

Incremental backup: only those in. Files that were modified after the last full or incremental backup will be backed up.

Note: differences and increments complement each other

MySQL full backup 1. Full backup is the backup of the whole database, database structure and file structure. 2. Full backup saves the database at the time when the backup is completed. 3. Full backup is the basis of incremental backup (1) the advantages of full backup ● backup and recovery operation is simple and convenient (2) the disadvantage of full backup ● data has a lot of repetitive ● takes up a lot of backup space ● backup and recovery time long mysqldump backup library 1, backup of MySQL database can be done in a variety of ways ● directly package database folders For example, / usr/local/mysql/data ● uses the special backup tool mysqldump2, the mysqldump command ● MySQL's own backup tool, which is quite convenient for backing up MySQL. ● can export specified libraries, tables or all libraries as SQL scripts through this command tool. Data recovery can be carried out when you need to restore 3. Mysqldump command makes a full backup of a single library mysqldump-u user name-p [password] [option] [database name] > / backup path / backup file list library backup example mysqldump-u root-p auth > / backup/auth.sqlmysqldump-u root-p mysql > / bakcup/mysql.sql4, The mysqldump command makes a full backup of multiple libraries mysqldump-u username-p [password] [option]-- databases library name 1 [library name 2]. > / backup path / backup file name Multi-library backup example mysqldump-u root-p-- databases autth mysql > / backup/databases-auth-mysql.sql5, full backup of all libraries mysqldump-u user name-p [password] [option]-- all-databases > / backup path / backup file name all library backup examples mysqldump-u root-p-- opt-- all-databases > / backup/all-data.sqlmysqldump backup Table 1, in the actual production environment There is a maintenance operation for a particular table At this time, mysqldump also plays an important role. 2. Use the operation of mysqldump backup table mysqldump-u username-p [password] [option] database name table name > / backup path / backup file name example mysqldump-u root-p mysql user > / backup/mysql-user.sql3, Example of using mysqldump backup table structure operation mysqldump-u username-p [password] [option]-d database name table name > / backup path / backup file name backup table structure mysqldump-u root-p mysql- d user > / backup/mysql-user.sql restore database 1, SQL backup script exported using the mysqldump command During data recovery, you can use the following methods to import ● source command database mode and run ● mysq | Command Linux mode to run 2. To restore a database using source to log in to the MySQL database ● to perform a source backup sq | path to the script (absolute path) source recovery example MySQL [(none)] > source / backup/all-data.sql3, Use the mysq | command to restore the data mysql-u user name-p [password] / opt/ bak. Practical practice of full backup and recovery of txt

1. Enter the database, create the table, and insert the table data

[root@master2 ~] # mysql-uroot-p # # enter the database Enter password: mysql > create database school; # # create database Query OK, 1 row affected (0.01 sec) mysql > use school; # # use database Database changedmysql > create table info (# # create tables-> id int (3) not null primary key auto_increment,-> name varchar (10) not null,-> score decimal (4 Magi 1) not null) Query OK, 0 rows affected (0.02 sec) mysql > desc info # # View table structure +-+-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (3) | NO | PRI | NULL | auto_increment | | name | varchar (10) | NO | | NULL | | score | decimal (4) | NO | | NULL | | +-+ -+ 3 rows in set (0.00 sec) mysql > insert into info (name Score) values ('stu01',88), (' stu02',77) # # insert table data Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from info # # View table content +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.01sec) mysql > select * from info limit 1 # # display only the first row in the table +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | +-+ 1 row in set (0.00 sec)

2. Make a full physical backup of the database

[root@master2 ~] # cd / usr/local/mysql/data/ # # switch to the file db.opt info.frm info.ibd [root@master2 school] # in the data directory of the database [root@master2 data] # lsauto.cnf ibdata1 ib_logfile1 mysql school testib_buffer_pool ib_logfile0 ibtmp1 performance_schema sys [root@master2 data] # cd school/ [root@master2 school] # ls # # Cd. [root@master2 data] # tar Jcvf / opt/mysql-$ (date +% F). Tar.xz / usr/local/mysql/data/ # # compress [root@master2 data] # cd / opt/ [root@master2 opt] # lsmysql-2019-11-26.tar.xz mysql-5.7.20 rh in xz format

3. Back up a single database logically

[root@master2 opt] # mysqldump-uroot-p school > / opt/school.sql # # logical backup of a single database Enter password: [root@master2 opt] # lsmysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql [root@master2 opt] # vim school.sql # # View the backup database script. CREATE TABLE `info` (`id`int (3) NOT NULL AUTO_INCREMENT, `name` varchar (10) NOT NULL, `score`decimal (4) NOT NULL PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ... = 'class1' > LOCK TABLES `info` DISABLE KEYS * /; INSERT INTO `info` VALUES.

4. Back up multiple databases

[root@master2 opt] # mysqldump-uroot-p-- databases school mysql > / opt/db_school_mysql.sql## backup multiple databases Enter password: [root@master2 opt] # lsdb_school_mysql.sql mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql

5. Full backup of the database

[root@master2 opt] # mysqldump-uroot-p-- opt-- all-databases > / opt/all.sql # # full backup Enter password: [root@master2 opt] # lsall.sql mysql-2019-11-26.tar.xz rhdb_school_mysql.sql mysql-5.7.20 school.sql

6. Back up the tables in the database

[root@master2 opt] # mysqldump-uroot-p school info > / opt/school_info.sql # # backup tables in the database Enter password: [root@master2 opt] # lsall.sql mysql-2019-11-26.tar.xz rh school.sqldb_school_mysql.sql mysql-5.7.20 school_info.sql

7, back up the table structure in the database

[root@master2 opt] # mysqldump-uroot-p-d school info > / opt/school_info_desc.sql # # back up the table structure Enter password: [root@master2 opt] # lsall.sql mysql-5.7.20 school_info.sqldb_school_mysql.sql rh school.sqlmysql-2019-11-26.tar.xz school_info_desc.sql

8, restore the database based on script

[root@master2 opt] # mysql-uroot-p # # enter the database Enter password: mysql > show databases # # View database +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | sys | | test | +- -+ 6 rows in set (0.00 sec) mysql > use school # # use database Database changedmysql > show tables; # # to view the table +-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec) mysql > drop table info; # # Delete table Query OK, 0 rows affected (0.01 sec) mysql > show tables # View table Empty set (0.00 sec) mysql > source / opt/school.sql # # restore database script file mysql > show tables; # # View table +-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec)

9, restore the database based on external MySQL commands

Mysql > drop table info; # # delete table Query OK, 0 rows affected (0.01 sec) mysql > show tables; # # View table Empty set (0.00 sec) mysql > quit # # exit Bye [root@master2 opt] # mysql-uroot-p123123 school

< /opt/school.sql ##利用mysql命令进行恢复mysql: [Warning] Using a password on the command line interface can be insecure.[root@master2 opt]# mysql -uroot -p123123 ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql>

Use school; # # use database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables # # View Table +-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec) MySQL incremental backup and recovery

1. Open the binary log file

[root@master2 opt] # vim / etc/my.cnf # # Open binary log file [mysqld] user = / usr/local/mysqldatadir = / usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = / usr/local/mysql/mysql.pidsocket = / usr/local/mysql/mysql.socklog-bin=mysql-bin # # Open binary log file server-id = 1 [root@master2 opt] # systemctl restart mysqld.service # # restart mysql service [ Root@master2 opt] # cd / usr/local/mysql/data/ # # switch to the mysql site [root@master2 data] # ls # # View the binary log file auto.cnf ib_logfile0 mysql performance_schema testib_buffer_pool ib_logfile1 mysql-bin.000001 schoolibdata1 ibtmp1 mysql-bin.index sys

2, make a full backup

[root@master2 data] # mysqldump-uroot-p123123 school > / opt/school.sql # # one full backup mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@master2 data] # lsauto.cnf ib_logfile0 mysql performance_schema testib_buffer_pool ib_logfile1 mysql-bin.000001 schoolibdata1 ibtmp1 mysql-bin.index sys [root@master2 data] # mysqladmin-uroot-p123123 flush-logs # # brush New binary log file mysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@master2 data] # ls # # generate a new binary log file The next operation will be saved in mysql-bin.000002 auto.cnf ib_logfile0 mysql mysql-bin.index sysib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema testibdata1 ibtmp1 mysql-bin.000002 school

3. Enter the database to simulate misoperation

[root@master2 data] # mysql-uroot-p123123 # # enter database mysql: [Warning] Using a password on the command line interface can be insecure.mysql > use school; # # use database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from info # # View table +-+ | id | name | score | +-+ | 1 | st01 | 88.0 | 2 | st02 | 77.0 | +-+ 2 rows in set (0.00 sec) mysql > insert into info (name,score) values ('by01',66) # # correct operation of Query OK, 1 row affected (0.00 sec) mysql > select * from info +-1 | st01 | 88.0 | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | +-+ 3 rows in set (0.00 sec) mysql > delete from info where name='st01' # # incorrect operation Query OK, 1 row affected (0.00 sec) mysql > insert into info (name,score) values ('by02',99); # # correct operation of Query OK, 1 row affected (0.00 sec) mysql > select * from info +-+ | id | name | score | +-+ | 2 | st02 | 77.0 | 3 | by01 | 66.0 | | 4 | by02 | 99.0 | + 3 rows in set (0.00 sec) [root@master2 data] # mysqladmin-uroot-p123123 flush-logs # # Brush New binary log file mysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@master2 data] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000002 > / opt/bak.txt## uses a 64-bit decoder to view binary log files And generate a file [root@master2 data] # cd / opt/ [root@master2 opt] # lsbak.txt mysql-5.7.20 rh school.sql [root@master2 opt] # vim bak.txt # # View the binary log file # at 1084' 191127 20:14:01 server id 1 end_log_pos 1132 CRC32 0xdcc90eb5 Write_rows: table id 221flags: STMT_END_F###INSERT INTO `room`.`info` # # correct operation for the first time Time and location of # SET###@1=3###@2='by01'###@3=66.0...#at 1302 # # stop location # 191127 20:14:46 server id 1 end_log_pos 1357 CRC32 0x6648509a Table_map: `room`.`info` mapped to number 221#at 1357 hours 191127 20:14:46 server id 1 end_log_pos 1405 CRC32 0x1eeb752b Delete_rows: table id 191127 flags: STMT_END_F###DELETE FROM `error `.`info` # # time and location of the second error operation 191127 20:14:46###WHERE###@1=1###@2='st01'###@3=88.0#at 1405 # # start location # 191127 20:14:46 server id 1 end_log_pos 1436 CRC32 0xf1c8d903 Xid = 54...#at 1630 191127 20:15:16 server id 1 end_log_pos 1678 CRC32 0x08d9b0f4 Write_rows: Table id 221flags: STMT_END_F###INSERT INTO `room`.`info` # # time and location of the second correct operation 191127 20:15:16###SET###@1=4###@2='by02'###@3=99.0

4. Breakpoint recovery based on point in time

[root@master2 opt] # mysql-uroot-p123123 # # enter database mysql: [Warning] Using a password on the command line interface can be insecure.mysql > use school; # # use database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > drop table info; # # to delete database Query OK, 0 rows affected (0.01 sec) mysql > select * from info # # View table ERROR 1146 (42S02): Table 'school.info' doesn't existmysql > source / opt/school.sql # # restore full backup database script. MySQL > show tables # # View table +-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec) mysql > select * from info # # View table data +-+ | id | name | score | +-+ | 1 | st01 | 88.0 | 2 | st02 | 77.0 | +-+ 2 rows in set (0.00 sec) [root@master2 opt] # mysqlbinlog-no-defaults-stop-datetime='2019 -11-27 20 bin.000002 14 mysql 46'/ usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p123123 statement # restore the previous correct execution statement in bin.000002 (stop from the second error statement point in time) mysql: [Warning] Using a password on the command line interface can be insecure. [root@master2 opt] # mysql- uroot-p123123 statement # enter database mysql: [Warning] Using a password on the command line interface can be insecure.mysql > use school # # using database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from info # # viewing Table data The first correct operation +-+ | id | name | score | +-+ | 1 | st01 | 88.0 | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | +-+ 3 rows in set (0.00 sec) [root@master2 opt] # mysqlbinlog-- No-defaults-- start-datetime='2019-11-27 20 usr/local/mysql/data/mysql-bin.000002 15 usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p123123 # # Skip the wrong node Restore the last correct operation (starting from the last correct operation point) mysql: [Warning] Using a password on the command line interface can be insecure. [root@master2 opt] # mysql-uroot-p123123 # # enter the database mysql: [Warning] Using a password on the command line interface can be insecure.mysql > use school # # using database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from info # # View the table data and restore the second correct operation Incorrect operation +-+ | id | name | score | +-+ | 1 | st01 | 88.0 | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | | 4 | by02 | 99.0 | + 4 rows in set (0.00 sec)

5. Breakpoint recovery based on location point

Mysql > delete from info where name='by01'; # # delete Query OK directly for experimental convenience, 1 row affected (0.01 sec) mysql > delete from info where name='by02'; # # delete Query OK, 1 row affected (0.00 sec) mysql > select * from info # # initial status of full backup +-+ | id | name | score | +-+ | 1 | st01 | 88.0 | | 2 | st02 | 77.0 | +-- + 2 rows in set (0.00 sec) mysql > quitBye [root@master2 opt] # mysqlbinlog-- no-defaults- -stop-position='1302' / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p123123 # # skip the location point of the error operation from the previous location point [root@master2 opt] # mysql- uroot-p123123 # # enter the database mysql: [Warning] Using a password on the command line interface can be insecure.mysql > use school # # using database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from info # # viewing Table data The first correct operation +-+ | id | name | score | +-+ | 1 | st01 | 88.0 | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | +-+ 3 rows in set (0.00 sec) mysql > quitBye [root@master2 opt] # Mysqlbinlog-- no-defaults-- start-position='1405' / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p123123 # # start from the location point after the wrong location Skip the location of the error operation [root@master2 opt] # mysql-uroot-p123123 # enter the database mysql: [Warning] Using a password on the command line interface can be insecure.mysql > use school # # using database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from info # # View table data and skip error operations Restore the second correct operation data +-+ | id | name | score | +-+ | 1 | st01 | 88.0 | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | | 4 | by02 | 99.0 | + 4 rows in set (0.00 sec)

6, restore all for incremental backup

[root@master2 opt] # mysqlbinlog-- no-defaults / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-p123123 restore all incremental recovery Thank you for your reading. The above is the content of "how to back up and restore data in full and incremental MySQL". After the study of this article, I believe you have a deeper understanding of how to back up and restore data in full and incremental MySQL. The specific use situation still needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.