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

The importance of data backup in a production environment, data security is critical. Any loss of data can have serious consequences. Causes of data loss: program errors, human errors, computer failures, disk failures, disasters and theft of database backups classified physical backups: physical files (such as data files) of the database operating system The physical backup of log files is divided into offline backup (cold backup) and online backup (hot backup): it is a hot backup when the database is closed: the database is running. this backup method depends on the logical backup of the log files of the database: the backup of database logical components (such as tables and other database objects) from the point of view of database backup strategy. Backup can be divided into full backup: full backup of data each time differential backup: backup of files that have been modified since the last full backup: only those files that have been modified after the last full backup or incremental backup will be backed up MySQL full backup is a backup of the entire database Full backup of data structure and file structure full backup of database is the basis of incremental backup full backup and recovery operation is simple and convenient full backup data has a lot of repeated backup space backup and recovery time long mysqldump backup MySQL database backup can be packaged directly in a variety of ways For example, / usr/local/mysql/data uses the special backup tool mysqldumpmysqldump command MySQL's own backup tool, which is quite convenient to back up MySQL. Through this command tool, you can use specified libraries, tables and all libraries everywhere for SQL scripts. Data recovery can be performed when recovery is needed. Mysqldump command makes a full backup of a single library mysqldump-u username-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 > / backup/mysql.sqlmysqldump command makes a full backup of mysqldump-u username-p [password for multiple libraries ] [option]-- database library name 1 [library name 2]. > / backup path / backup file name Multi-library backup example mysqldump-u root-p-- databases auth mysql > / back/databases-auth-mysql.sql full backup of all libraries mysqldump-u user name-p [password] [option]-- all-databases > / backup path / backup file name multiple backup examples mysqldump-u root-p-- opt-- all-databases > / backup/all-data.sql mysqldump backup table in time production environment When there is a maintenance operation for a particular table, mysqldump also plays an important role in using the operation of mysqldump backup table mysqldump-u username-p [password] [option] database name table name > / backup path / backup file name backup table example mysqldump-u root-p mysql user > / backup/mysql-user.sql restore database SQL backup script exported using the mysqldump command You can use the following method to import source commands mysql commands to restore a database using source to log in to the MySQL database to execute a source backup sql script path source recovery example MySQL [(none)] > source / backup/all-data.sql use the mysql command to restore data mysql-u username-p [password]

< 库备份脚本的路径mysql命令恢复例子mysql -u root -p < /backup/all-data.sql恢复表的操作恢复表时同样可以使用source或者mysql命令进行source恢复表的操作与恢复库的操作相同当备份文件中只包含表的备份，而不包括创建库的语句时，必须制定库名，且目标库必须存在mysql -u 用户名 -p [密码] < 表备份脚本的路径mysql -u root -p mysql < /backup/mysql-user.sql在生产环境中，可以使用shell脚本自动实现定期备份MySQL备份思路定期实施备份，制定备份计划或者策略，并严格遵守除了进行完全备份，开启MySQL服务器的日志功能时很重要的完全备份加上日志，可以对MySQL进行最大化的还原使用同一的和易理解的备份文件名称推荐使用库名或者表名加上时间的命名规则MySQL增量备份使用mysqldump进行完全备份的存在的问题备份数据中有重复数据备份时间与恢复时间长增量备份就是备份自上一次备份之后增加或变化的文件或者内容增量备份的特点：没有重复数据，备份量不大，时间短恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复MySQL没有提供直接的增量备份方法可以通过MySQL提供的二进制日志(binary logs)间接实现增量备份MySQL二进制日志对备份的意义：  二进制日志保存了所有更新或者可能更新数据库的操作  二进制日志在启动MySQL服务器后开始记录，并在文件达到max_ binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件  只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份 MySQL数据库增量恢复一般恢复： 基于位置恢复：就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复基于时间点恢复使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式增量恢复的方法一般恢复：mysqlbinlog [--no-defaults]增量备份文件 | mysql -u用户名 -p基于位置的恢复：恢复数据到指定位置mysqlbinlog --stop-position=操作'id' 1进制日志 | mysql -u用户名 -p 密码从指定的位置开始恢复数据mysqlbinlog --start-position=操作'id'二进制日志 | mysql -u用户名 -p 密码基于时间点的恢复：从日志开头截止到某个时间点的恢复mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒'二进制日志 | mysql -u用户名 -p 密码从某个时间点到日志结尾的恢复mysqlbinlog [--no defaults] --start-datetime='年-月-日 小时:分钟:秒'二进制日志 | mysql -u用户名 -p 密码从某个时间点到某个时间点的恢复mysqlbinlog [--no defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒'二进制日志 | mysql -u用户名 -p 密码查看二进制日志文件（解码）mysqlbinlog --no-defaults --base64-output=decode-rows -V mysql-bin.000002 >

/ opt/ bak. Txt, thank you for reading!

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.