Shulou(Shulou.com)06/03 Report--

This article mainly introduces the simple method of full and incremental backup and recovery of MySQL. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone. Let's learn about the simple method of full and incremental backup and recovery of MySQL with the author.

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

Disasters (such as earthquakes) and theft

Classification of database backup

1. From a physical and logical point of view, backup can be divided into:

1. Physical backup: physical backup of physical files (such as data files, log files, etc.) of the database operating system 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 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)

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

1. Full backup: complete backup of data every time. 2. Differential backup: back up those files that have been modified since the last full backup. 3. Incremental backup: only those in. The files modified after the last full backup or incremental backup will be backed up. Note: differential backup and incremental backup complement each other, MySQL full backup (full backup) 1, full backup is the backup of the entire database, database structure and file structure backup 2, full backup saves the database at the time of backup completion 3, full backup is the basis of incremental backup

Advantages of full backup

Backup and recovery operation is simple and convenient.

Disadvantages of full backup

1. There are a lot of duplicates in the data. Take up a lot of backup space 3. Backup method with long backup and recovery time

1. MySQL database can be backed up in a variety of ways

1. Package the database folder directly, such as / usr/local/mysql/data2. Use the dedicated backup tool mysqldump**

II. Mysqldump command

1.MySQL comes with backup tools, which is quite convenient for MySQL backup 2. Through this command tool, you can export specified libraries, tables, or all libraries as SQL scripts, and you can restore data when you need to restore.

Third, make a full backup of a single database using the mysqldump command

Mysqldump-u username-p [password] [option] [database name] > / backup path / backup file name

4. Make a full backup of multiple libraries using the mysqldump command

Mysqldump-u username-p [password] [option]-- databases library name 1 [library name 2]. > / backup path / backup file name

Fifth, use the mysqldump command to make a full backup of all libraries

Mysqldump-u user name-p [password] [option]-- all-databases > / backup path / backup file name mysqldump backup table

In the actual production environment, there is a maintenance operation for a specific table, and mysqldump also plays an important role.

1. The operation of using mysqldump backup table

Mysqldump-u user name-p [password] [option] Database name Table name > / backup path / backup File name

two。 Operation of backing up table structure using mysqldump

Mysqldump-u user name-p [password] [option]-d database name table name > / backup path / backup file name restore database

1. SQL backup scripts exported using the mysqldump command can be imported using the following methods during data recovery

Source Command # run in database mode mysql command # run in Linux mode

1. Steps to restore a database using source

● logs in to MySQL database ● to perform source backup sq | the path to the script (absolute path) MySQL [(none)] > source / backup/all-data.sql#source command restore

2. Use the mysql command to recover data

Mysql-u user name-p [password]

< 库备份脚本的路径mysql -u root -p < /backup/all-data.sql#mysql命令恢复 二、使用mysqldump进行完全备份的存在的问题 ●备份数据中有重复数据●备份时间与恢复时间长恢复数据表操作1、恢复表时同样可以使用source或者mysql命令进行2、source恢复表的操作与恢复库的操作相同3、当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在.生产环境中，可以使用shell脚本自动实现定期备份mysql -u用户名-p [密码] < 表备份脚本的路径mysql -u root -p mysql < /backup/mysql-user.sql#mysql命令恢复表MySQL备份思路1、定期实施备份，制定备份计划或者策略，并严格遵守2、除了进行完全备份，开启MySQL云服务器的日志功能是很重要的●完全备份加上日志，可以对MySQL进行最大化还原MySQL-bin：MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息。可以使用mysqlbin命令查看二进制日志的内容3、使用统一的和易理解的备份文件名称●不要使用backup1、backup2等这样没有意义的名字●推荐使用库名或者表名加上时间的命名规则二、MySQL增量备份 增量备份就是备份自上一次备份之后增加或变化的文件或者内容 增量备份的特点 ●没有重复数据，备份量不大，时间短●恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复 MySQL没有提供直接的增量备份方法 可以通过MySQL提供的二进制日志(binary logs)间接实现增量备份 MySQL二进制日志对备份的意义 ●二进制日志保存了所有更新或者可能更新数据库的操作●二进制日志在启动MySQL云服务器后开始记录，并在文件达到max_ binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件●只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份查看二进制日志文件（解码）mysqlbinlog --no-defaults --base64-output=decode-rows -V mysql-bin.000002 >

/ opt/ bak. Overview of txtMySQL Database incremental recovery General recovery

Reply based on location

Is to import the binary log at a certain starting time into the database, thus skipping a certain point in time where an error occurs to achieve data recovery.

Point-in-time recovery

Using point-in-time recovery, there may be both correct and wrong operations at a point in time, so we need a more accurate recovery.

Methods of incremental recovery

1. General recovery

Mysqlbinlog [--no-defaults] incremental backup files | mysql-u user name-p

two。 Location-based recovery

Restore data to a specified location

Mysqlbinlog-- stop-position= operation 'id' binary log | mysql-u username-p password

Restore data from the specified location

Mysqlbinlog-- start-position= operation 'id' binary log | mysql-u username-p password

3. Point-in-time based recovery

Recovery from the beginning of the log to a certain point in time

Mysqlbinlog [--no-defaults]-- stop-datetime=' year-month-day hour: minute: second 'binary log | mysql-u username-p password

Recovery from a certain point in time to the end of the log

Mysqlbinlog [--no defaults]-- start-datetime=' year-month-day hour: minute: second 'binary log | mysql-u username-p password

Recovery from a certain point in time to a certain point in time

Mysqlbinlog [--no defaults]-- start-datetime=' year-month-day hour: minute: second'- stop-datetime=' year-month-day hour: minute: second 'binary log | mysql-u username-p password

After reading the above simple methods of full and incremental backup and recovery of MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.