Several commonly used mariadb backup and restore means--the previous article

On a mariadb of several replication models, this time lost a few MARAIDB common backup recovery simulation bar.

Backup related knowledge why to back up

Backup is mainly used to deal with the following situations: disaster recovery, hardware failure, software failure, natural disaster, xxx malicious xxx, human error caused by the damage of data and so on.
The focus of the backup is to tolerate the most lost data volume, in today's basic and money-equivalent reality, the data can be less lost, less lost it; how long will the recovery data be completed; What data needs to be recovered.
Note :
A simple backup is not feasible, do a good job after the backup to consider whether the backup is available (restore test); regular restore drills (in the personal Machine Experiment).

Classification of backups

Full backup-full backup of the entire data set.
Incremental backup-Backup is relatively fast, but the restore is complex and needs to be consolidated-just back up the last full or incremental backup (if there is an increment) after the changed data.
Differential backup-only the data that has changed since the full backup is backed up, the backup speed is slower relative to the incremental backup, but the restore is simple.
Partial backup-backs up only a subset of the data, such as partial libraries and partial tables.

Keep the data files and binary log files separate as much as possible.

Cold: Database Read and write operations are all stopped until the backup is complete.
Win Bei: The read operation can be performed, but the write operation is not possible.
Hot-Standby: Read and write operations are not affected, can be normal.
MyISAM: Win Bei, hot standby is not supported.
InnoDB: All supported (now mostly used, and also support transactions)
Physical backup: Directly copy data files for backup, related to the storage engine, occupy more space and speed up.
Logical backup: Exporting data from a database to other places, regardless of the storage engine, takes up relatively little space, is slow, and may lose data accuracy.

Extra consideration

We can do it before we think about it before we make a backup, after all the data is priceless.
1. Win Bei time to keep the lock on
2. How much system resources will be consumed during the backup process
3. Time spent in backup
4. Time spent in data recovery
5. What should we back up
Data
Binary log, innodb transaction log file
Program code (refers to the database)
Configuration file for the server

A brief demonstration of several backup methods

Simple CP and tar do not demonstrate, CP and tar are physical backup tools for all storage engines, but only support cold; You can fully back up mixed partial backups.

LVM-based backup environment

centos7.4 mariadb 10.2.15
Here's my data directory in/data/mysql
Binary Files directory in/data/binlog

Preparing the MARIADB Configuration

Modify the configuration file for the database to store the binaries and data files separately and restart the database, rereading the configuration file.

    #mariadb的高版本配置文件被拆分到几个文件中去了    vim /etc/my.cnf.d/server.cnf    [mysqld]    #指定数据目录    datadir=/data/mysql    #指定二进制文件路径和名称前缀    log_bin=/data/binlog/mysql-bin    #将每个数据库都单独存放    innodb_file_per_table

Because LVM-based backups are implemented here, data is stored on an LVM volume

    #创建目录        mkdir /data/{binlog,mysql} -pv    #挂载逻辑卷        mount /dev/vg0/lv_data  /data/mysql        mount /dev/vg0/lv_binlog  /data/binlog        chown -R mysql:mysql /data/    #重启数据库    systemctl restart mariadb    #如果出现提示就执行以下命令    #低版本一般不会出现，mariadb在10.2.15包括以上要手动重新初始化数据库才能正常启动    mysql_install_db --datadir=/data/mysql --user=mysql    systemctl restart mariadb

Implementing a snapshot backup of LVM

Since we're doing backup experiments, we'll first import some data into the database,

mysql 

1. Add read locks to the database to restrict other users from writing to the data

#进入数据库，我这里没有设密码可以直接登入mysql#添加读锁mysql>flush tables with read lock;#查看二进制日志文件，记录文件名和pos编号mysql>show master logs;


2. Create a snapshot, here to open a new terminal

 lvcreate -n lv_mysql_snap -L 1G -s -p r /dev/vg0/lv_data

3. Unlocking the Database

 mysql>unlock tables;

4. Mount the snapshot and back up the data file

mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt#复制文件，由于是做演示所以就进备份到本地了cp -a /mnt/ /backup/

5. Deleting a snapshot

#卸载umount /mnt#删除快照（快照存在会影响数据的写入）lvremove /dev/vg0/lv_mysql_snap

Back to here basic completion

Enabling the restoration of LVM snapshots

1. Simulated damage

    #模拟在损坏前有用户写入了其他数据信息    mysql> create database db1;    mysql> create database db2;    #删除数据库数据    rm -rf /data/mysql/*    #停止数据库    systemctl stop mariadb

2. Restoring a backed up data file

    cp -av /backup/*  /data/mysql/

3. Prohibit external users from connecting

    vim /etc/my.cnf.d/server.cnf  #加入以下字段到mysqld    skip_networking    #重启数据库    systemctl start maraidb

4. To restore the latest log in the Binlog to the database, first log in to the database to see the following master logs

 cd /data/binlog/mysqlbinlog --start-position=8960 mysql-bin.000002 > /backup/bin.sqlmysqlbinlog  mysql-bin.000003 >> /backup/bin.sqlmysqlbinlog  mysql-bin.000004 >> /backup/bin.sqlmysqlbinlog  mysql-bin.000005 >> /backup/bin.sqlmysql < /backup/bin.sql

5. Restore User access

vim /etc/my.cnf.d/server.cnf  #删除字段    skip_networking    systemctl restart mariadb

6. Review the data information and find that the information you added later has also been restored.

Backup recovery based on mysqldump

Here is the virtual machine that restored the new environment

Database data file corruption, how to restore the latest state

The premise here is to require a full backup, and to get the latest from the binary log file normally

1. Full backup of the database

mysqldump -A -F --single-transaction --master-data=2 > /backup/full.sql

2. Corrupt data after database modification

rm -rf /data/mysql/*

3. Restart the database and restore the backup

systemctl stop mariadbmysql_install_db --datadir=/data/mysql --user=mysql# 编辑配置文件并禁止其他用户访问 vim /etc/my.cnf.d/server.cnf innodb_file_per_table systemctl start mariadb#将二进制文件生成sql文件#查看全备份的sql文件可以看到备份时候的二进制文件和起始位置（如果是恢复失误删除的表可以在整合完二进制文件生成的sql文件后，找到误删除表的语句，删除该语句即可）mysqlbinlog --start-position=385 mysql-bin.000005 >bin.sql # 恢复完全备份数据 mysql < /backup/full.sql  mysql < bin.sql

4. Modify the configuration file delete innodb_file_per_table field restart

Single-table backup

Single-table recovery (involving association problems between tables)
Backup

mysqldump hellodb students > /backup/stu_bak.sql

Restoring a backup

mysql hellodb < /backup/stu_bak.sql

Backup recovery based on Xtrabackup

MARIADB 10.2.15 to use the latest version 2.4.11

Full backup and Recovery

1. Installing Percona-xtrabackup

yum localinstall percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm -y

2. Full backup (piloted with a self-built HELLODB as test data)

# innobackupex (--user=root) /backup/默认就是以root用户去备份，这里省略了innobackupex  /backup/

3. Delete Data simulation corruption

rm -rf /data/mysql/*

4. Organize log information

innobackupex --apply-log /backup/2018-06-15_22-12-06/

5. Stop the database

systemctl stop mariadb

6. Recover data to directory and modify permissions

innobackupex --copy-back /backup/2018-06-15_22-12-06/chown -R mysql:mysql /data/mysql/

7. Start MARIADB to verify success

systemctl start mariadb

Several commonly used mariadb backup and restore means--the previous article