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

The following content mainly brings you the security and importance of MySQL database backup, the knowledge mentioned is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, has a certain experience sharing value, and hopes to bring help to the majority of readers.

The importance of data backup in the production environment, the security of data is very important, any loss of data may have serious consequences, the causes of data loss, program errors, computer failures, disk failures, disasters (such as fire, earthquake) and theft of database backups are classified from a physical and logical point of view. Backup can be divided into physical backup: physical backup of physical files of database operating system (such as data files, log files, etc.) can be divided into offline backup (cold backup) and online backup (hot backup) cold 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 database 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 database MySQL database backup can directly package database folders 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 export specified libraries, tables, or all libraries as SQL0 scripts. You can restore data when you need to restore the mysqldump command to fully backup 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 command to fully backup 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.sql 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 in the actual 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 life > / backup path / backup file name backup table example mysqldump-u root-p mysql user > / backup/mysql-user.sql restore database SQL backup scripts exported using the mysqldump command You can use the following methods 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 restore 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或者mysq|命令进行source恢复表的操作与恢复库的操作相同当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在mysq| -u 用户名 -p [密码] < 表备份脚本的路径mysql -u root -P mysq| < /backup/mysql-user.sql在生产环境中，可以使用shell脚本自动实现定期备份MySQL备份思路定期实施备份，制定备份计划或者策略，并严格遵守除了进行完全备份，开启MySQL云服务器的日志功能是很重要的完全备份加上日志，可以对MySQL进行最大化还原使用统一的和易理解的备份文件名称不要使用backup1、backup2等这样没有意义的名字不推荐使用库名或者表名加上时间的命名规则MySQL增量备份使用mysqldump进行完全备份的存在的问题备份数据中有重复数据备份时间与恢复时间长增量备份就是备份自.上一次备份之后增加或变化的文件或者内容增量备份的特点没有重复数据，备份量不大，时间短恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复MySQL没有提供直接的增量备份方法可以通过MySQL提供的二进制日志（binary logs）间接实现增量备份MySQL二进制日志对备份的意义二进制日志保存了所有更新或者可能更新数据库的操作二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份MySQL数据库增量恢复 一般恢复 添加数据→进行完全备份→录入新的数据→进行增量备份→模拟故障→恢复操作基于位置恢复就是将某个起始时间的二进制日志导入数据库中，c从而跳过某个发生错误的时间点实现数据的恢复 基于时间点的恢复 使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式增量恢复的方法一般恢复mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p基于位置的恢复恢复数据到指定位置mysqlbinlog --stop-position='操作 id' 二进制日志 | 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 密码操作实例创建数据库、编辑表数据[root@master2 ~]# mysql -uroot -p //进入数据库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 sec 1) not null); Query OK, 0 rows affected (0.02 sec) mysql > desc info / / View the table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +- -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 contents +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.01sec) mysql > select * from info limit 1 / / only show the first row in the table +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | +-+ 1 row in set (0.00 sec) full backup operation [root@master2 ~] # cd / usr/local/ Mysql/data/ switch to the file db.opt info.frm info.ibd [root@master2 school] # cd. [root@master2 data] 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 / / # 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 single database logical backup [root@master2 opt] # mysqldump-uroot-p school > / opt/school.sql / / logical backup 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 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 (1), (2) (2), (2), (2), (2), (7, 7, 0). Multiple database backups [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 full backup [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 database tables for backup [root@master2 opt] # mysqldump-uroot-p school info > / opt/school_info.sql / / A backup of 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 table structure backup [root@master2 opt] # mysqldump-uroot-p-d school info > / opt/school_info_desc.sql / / A backup of table structure Enter password: [root@master2 opt] # SQL -5.7.20 school_info.sqldb_school_mysql.sql rh school.sqlmysql-2019-11-26.tar.xz school_info_desc.sql restore database based on script [root@master2 opt] # mysql- uroot-p / / enter 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; / / View 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) restore database based on external MySQL command 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>

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.