Use mysqldump to back up and restore the database,
Database storage engine: InnoDB
Enable binlong
1. Restore the database accidentally deleted to the latest status
1. Back up the database
mysqldump -A -F -uroot -p --master-data=2 --single-transaction > /backups/all-`date +%F_%T`.sql
2. accidental deletion of the database
MariaDB [reset_gq]> drop database reset_gq;
3. Add new data to the database
MariaDB [(none)]> create database kuc ;
MariaDB [(none)]> use kuc;
MariaDB [kuc]> create table cun_dg(id int);
4. Recover the database when a problem is detected
MariaDB [(none)]> flush tables with read lock; # global lock, read-only Database
MariaDB [(none)]> flush logs; # refresh the log
Cp/www/server/data/mysql-bin.000015/backups/# copy a modification
Mysqlbinlog -- start-position = 366 mysql-bin.000015> bin. SQL
Modify the bin. SQL statement and locate the incorrect command comment or delete
MariaDB [(none)]> set SQL _log_bin = 0; # Pause the binary log.
MariaDB [(none)]> unlock tables; # unlock
Cp/backups/all-2018-02-27_17 \: 50 \: 26. SQL/backups/mysql. SQL # Renamed
MariaDB [(none)]> source/backups/mysql. SQL; # restore the database to the backup status
MariaDB [reset_gq]> source/backups/bin. SQL; # restore the database to the latest state
MariaDB [kuc]> set SQL _log_bin = 1; # Start the binary log
5. Check whether the restoration is complete.
Ii. LVM-based Backup Recovery
1. Separate data and binlog to their respective logical volumes
2. Lock all tables
MariaDB [(none)]> flush tables with read lock;
3. Record binary log files and event locations
MariaDB [(none)]> flush logs; # refresh the log
Mysql-uroot-p-e 'show master logs'>/app/pos. log # record the location of the log to the file
4. Create a snapshot
lvcreate -n mysqldata-snapshot -s -p r -L 3G /dev/vg0/mysqldata
5. Release the lock and modify some data
MariaDB [(none)]> unlock tables;
MariaDB [(none)]> create database lvsceshi;
6. Mount the snapshot volume and perform data backup.
mkdir /mnt/snap
mount -o nouuid,norecovery /dev/vg0/mysqldata-snapshot /mnt/snap
mkdir /backups
cp -a /mnt/snap/* /backups/
7. After the backup is complete, delete the snapshot volume.
umount /mnt/snap/lvremove /dev/vg0/mysqldata-snapshot
8. Database destruction
systemctl stop mysqld
rm -rf /data/mysqldata/*
9. Restore
cp -a /backups/* /data/mysqldata/
systemctl start mysqld
10. binlong is restored to the latest status.
MariaDB [(none)]> flush tables with read lock; # lock all tables
Cp-a/data/binlongs/mysql-bin.00000 {4 .. 6}/app/
Cd/app/
Mysqlbinlog -- start-position = 385 mysql-bin.000004> bin. SQL
Mysql-bin.000005> bin. SQL
Mysql-bin.000006> bin. SQL
MariaDB [(none)]> set SQL _log_bin = 0; # temporarily disable binary diaries
MariaDB [(none)]> unlock tables; # unlock
MariaDB [(none)]> source/app/bin. SQL # restore
MariaDB [(none)]> set SQL _log_bin = 1; # enable the binary log