Database articles-full, incremental backup and recovery

Prepare the database environment

1. Set up the installation environment

Yum-y install\ gcc\ gcc-c++\ make\ ncurses\ ncurses-devel\ bison\ Cmake

2. Add database users

Useradd-s / sbin/nologin mysql / / add database user

3. Install mysql database

Tar xf mysql-boost-5.7.20.tar.gz-C / opt/ extract the source package cmake\ / / check the installation configuration environment-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock\-DSYSCONFDIR=/etc\-DSYSTEMD_PID_DIR=/usr/local/mysql\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH _ ARCHIVE_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\-DMYSQL_DATADIR=/usr/local/mysql/data\-DWITH_BOOST=boost\-DWITH_SYSTEMD=1make & & make install / / compile and install chown-R mysql.mysql / usr/local/mysql/ give user mysqlvi / etc/my.cnf / / the permissions of the database installation directory to add the following content [client] Port = 3306default-character-set=utf8socket = / usr/local/mysql/ mysql.sock [MySQL] port = 3306default-character-set=utf8socket = / usr/local/mysql/ mysql.sock [mysqld] user = mysqlbasedir = / usr/local/mysqldatadir = / usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = / usr/local/mysql/mysqld.pidsocket = / usr/local/mysql/mysql.sockserver-id = 1sql_mode=NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT ANSI_QUOTESchown mysql:mysql / etc/my.cnf / / change the permissions of the main configuration file echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' > > / etc/profileecho' export PATH' > > / etc/profile / / add the system environment variable source / etc/profile / / Refresh immediate effective cd / usr/local/mysql/ enter the installation directory initialization database bin/mysqld\ -- initialize-insecure\-- user=mysql\-- basedir=/usr/local/mysql\-- datadir=/usr/local/mysql/datacp usr/lib/systemd/system/mysqld.service / usr/lib/systemd/system/ create database system startup command systemctl daemon-reload / / refresh identify startup script systemctl start mysqld / / start database netstat-anpt | grep 3306 / / check whether the port is listening. 2. Direct backup

Stop the database and package the database data directory / usr/local/mysql/data directly

Tar cJvf / opt/mysql-$ (date +% F) .tar.xz / usr/local/mysql/data/

III. Full backup

Full backup: make a full backup of the data each time. You can back up the entire database, including all database objects such as user tables, system tables, indexes, views, and stored procedures.

Advantages: simple and convenient backup and recovery operation

Disadvantages: there are a lot of duplication in the database, which takes up a lot of backup space and takes a long time.

1. Use mysqldump command to back up

# mysqldump-u root-p-- all-databases > all-data-$ (date +% F). Sql / / back up all databases # mysqldump-u root-p-databases auth mysql > auth-mysql.sql / / back up multiple databases # mysqldump-u root-p auth > auth-$ (date +% F). Sql / / back up a single auth database # mysqldump-u root-p mysql user > mysql-user-$ (date +% F). Sql / / back up the user table in mysql's library

2. Restore the backed up database

Mysql-u root-p

< all-data-$(date +%F).sql //恢复所有的数据库mysql -u root -p mysql < mysql-user-$(date +%F).sql //恢复mysql的user表，需要指定库名mysql -uroot -p auth < /tmp/desc-mysql-user.sql //将数据结构恢复到auth库中，单库备份的时候要指定还原到哪个数据库中，需要建立空库，库名可以任意 3、案列演示 create database client; //创建client库use client; //进库create table user_info(××× int(18),姓名 varchar(20),性别 varchar(20),用户ID号 int(20),资费 int(48)); //创建user_info表desc user_info; //查看表结构 插入一些数据 insert into user_info values('000000001','孙空武','男','011','100');insert into user_info values('000000002','蓝凌','女','012','98');insert into user_info values('000000003','姜纹','女','013','12');insert into user_info values('000000004','关园','男','014','38');insert into user_info values('000000005','罗中昆','男','015','39'); 查看数据 select * from user_info; a、备份整个client数据库 mysqldump -uroot -p client >

Client-$ (date +% F) .sql

Delete the original client library and build a new new_client empty library

Drop database client;create database new_client

Restore the client library

Mysql-u root-p new_client

< client-2018-12-10.sql 进入数据库查看验证 b、备份client库的中的表 mysqldump -uroot -p client user_info >

Client_user_info-$ (date +% F) .sql

Delete the user_ info table from the client library

Drop tables client.user_info;show tables in client

Restore the user_ information table in the client library

Mysql-uroot-p client

< client_user_info-2018-12-10.sql 进入数据库查看验证 c、备份client库中的表结构（无数据） mysqldump -uroot -p -d client user_info >

Desc-client_user_info.sql

Create a new new_client empty database

Create database new_client

Restore the structure in the user_ information table in the client library

Mysql-uroot-p new_client

< desc-client_user_info.sql 进入数据库查看验证 desc new_client.user_info;select * from new_client.user_info; 四、增量备份 增量备份：只有那些在上次完全备份或增量备份后被修改的文件才会被备份。 优点：没有重复数据，备份量不大，时间短。 缺点：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且对所有增量备份进行逐个反推恢复，操作较为繁琐。 为什么使用增量备份？ 解决完全备份存在的问题，完全备份每次都把所有的数据备份，存在大量重复数据，备份与恢复的时间长。 增量备份的特点 （1）二进制日志保存了所有更新或者可能更新数据库的操作 （2）二进制日志在启动mysql服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。 （3）只需要定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。 1、开始二进制日志功能 vi /etc/my.cfg在[mysqld]下添加log_bin=/usr/local/mysql/data/mysql_bin systemctl restart mysqld //重启数据库 2、查看生成的二进制文件 cd /usr/local/mysql/data/ 3、案列演示 下面的数据库中的操作会被记录在mysql_bin.000001二进制日志中 create database client;use client;create table info(××× int(18),姓名 varchar(20),性别 varchar(20),用户ID号 int(20),资费 int(48));insert into info values('000000001','孙空武','男','011','100');insert into info values('000000002','蓝凌','女','012','98');insert into info values('000000003','姜纹','女','013','12');select * from info; 先做一次全量备份 mysqldump -u root -p client info >

Client_info-$ (date +% F) .sql

Refresh log files

Mysqladmin-uroot-p flush-logs

Insert file (this operation is recorded in the mysql_ bin.000002 binary log)

Insert into info values ('000000004' Guanyuan', 'male', '014' 'recorder' 38'); insert into info values ('000000005'' Luo Zhongkun', 'male', '015' June 39')

Refresh log files

Mysqladmin-uroot-p flush-logs

Simulate deleting data (this operation will be recorded in the mysql_ bin.000003 binary log)

Delete from info where × × = 4 * delete from info where × × = 5

Recover deleted files from binary logs

Mysqlbinlog-- no-defaults mysql_bin.000002 | mysql-u root-p / / actually restores the insert operation saved in mysql_bin.000002 and enters the database to verify whether the recovery is successful select * from client.info

Make a full backup of the successfully restored table again

Mysqldump-u root-p client info > client_info-$ (date +% F). Sql

Refresh the log

Mysqladmin-uroot-p flush-logs

Simulate database operations (both correct and incorrect operations are recorded in mysql_bin.000004)

Correct operation:

Insert into info values ('000000006)' Lan Linglin', 'female', '012''); insert into info values ('000000007')' Jiang Wenwen', 'female', '013'')

Erroneous action:

Delete from info where × × = 1

Correct operation:

Insert into info values ('000000008recording' Ling Lin', 'female', '012' recorder' 98'); insert into info values ('000000009' recorder' Wenwen', 'female', '013Fei')

Breakpoint recovery (to avoid incorrect operations)

For the most recent full backup and restore

Mysql-u root-p client < clientinfo-2018-12-05.sql

View the internal details of the binary log:

Mysqlbinlog-no-defaults-base64-output=decode-rows-v mysql_bin.000004

Get the location variable of the first correct operation

Get the location variable of the second correct operation

Start recovery

Mysqlbinlog-- no-defaults-- start-position='293'-- stop-position='732' mysql_bin.000004 | mysql-uroot-p / / breakpoint recovery first mysqlbinlog-- no-defaults-- start-position='1160'-- stop-position='1593' mysql_bin.000004 | mysql-uroot-p / / breakpoint recovery second place

Verify that the endpoint restore is successful

Note: this operation successfully avoids the log file error operation delete from info where × × = 1; the corresponding position offset range will restore the correct operation.

Attachment: about the screening methods of location variables and time variables

Location based: at

Skip the wrong

Mysqlbinlog-no-defaults-stop-position='871' mysql_bin.000005 | mysql-uroot-p

Mysqlbinlog-no-defaults-start-position='1160' mysql_bin.000005 | mysql-uroot-p

Filter out the correct

Mysqlbinlog-no-defaults-start-position='871'-stop-position='1160' mysql_bin.000005 | mysql-uroot-p

Based on point in time: time

Mysqlbinlog-- no-defaults-- stop-datetime='2018-03-11 21 stop-datetime='2018 30 mysql_bin.000002 30 mysql_bin.000002 | mysql-u root-p

Mysqlbinlog-- no-defaults-- start-datetime='2018-03-11 21 start-datetime='2018 30 mysql_bin.000002 30 mysql_bin.000002 | mysql-u root-p

Screen for a period of time

Mysqlbinlog-- no-defaults-- start-datetime='2018-12-05 21 start-datetime='2018 30 stop-datetime='2018 39'--stop-datetime='2018-12-05 22 22 purl 01 purl 20 'mysql_bin.000002 | mysql-u root-p

Key to record the time point:

Time point of misoperation: 2018-12-05 22:01:13

After that, add correct: 2018-12-05 22:01:20