Use mysqldump to back up and restore the database,

Source: Internet
Author: User

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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.