Use mysqldump to back up and restore the database,

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