Create a new lvm disk. Here I built lv as mydatalv and mounted under /data
[root@localhost ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert mydatalv mydata -wi-ao---- 1.00g [root@localhost ~]# df -h File system capacity Used Available Used% Mounting point /dev/mapper/mydata-mydatalv 976M 2.6M 907M 1% /data
Copy the original database file to /data directory
[root@localhost ~]# cp -a /var/lib/mysql /data/
Modify the configuration file, place the mysql database file in the lvm disk, and place the binary file in the /var/lib/mysql/ directory of the non-lvm disk
[root@ns1 ~]# vim /etc/ [mysqld] log_bin=/var/lib/mysql/mysql-bin datadir=/data/mysql [root@localhost ~]# service mariadb restart [root@localhost ~]# ls /data/mysql aria_log.00000001 ibdata1 ib_logfile1 mysql-bin.000001 mysql-bin.000003 performance_schema aria_log_control ib_logfile0 mysql mysql-bin.000002 test
You can see that the database file has been stored in the /data/mysql directory after restart
Backup of mysql
[root@localhost ~]# mysql -e 'flush tables with read lock;'[root@localhost ~]# mysql -e 'flush logs;' scrolls the log,[root@localhost ~]# mysql -e 'show master status;' > /root/back.$(date +%F+%T) [root@localhost ~]# ls back.2016-07-13+10:14:29
Create a snapshot of lv
[root@localhost ~]# lvcreate -L 1G -n mysqlback -p r -s /dev/mydata/mydatalv
Release the lock
[root@localhost ~]# mysql -e 'unlock tables;'
Create a backup directory on another disk, mount the snapshot read-only and back up to the backup directory
[root@localhost ~]# mkdir /myback [root@localhost ~]# mount -r /dev/mydata/mysqlback /mnt [root@localhost ~]# cp -a /mnt/mysql /myback
Modify the table contents, and then delete the database file contents, that is, the contents in /data/mysql
[root@localhost ~]# mysql
MariaDB [hellodb]> use hellodb;
MariaDB [hellodb]> insert into classes (class,numofstu) values ('xxoo',39);
[root@localhost ~]# rm -rf /data/*
Modify the location of binary logs and database files in the configuration file
[root@localhost ~]# vim /etc/ [mysqld] log_bin=/data/mysql/mysql-bin datadir=/data/mysql
Restore using content in /myback/
[root@localhost ~]# cp -a /myback/* /data/ [root@localhost ~]# service mariadb restart
The operation after restoring the snapshot using binary logs is used to view the location of the binary log when the snapshot is executed.
[root@localhost ~]# cat back.2016-07-13+10\:14\:29 File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000014 245
Make the operations after 245 in 000014 into SQL files and restore them
[root@localhost ~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000014 > [root@localhost ~]# mysql < /root/
Check recovery status
[root@localhost ~]# mysql MariaDB [(none)]> use hellodb; MariaDB [hellodb]> select * from classes; +---------+----------------+----------+ | ClassID | Class | NumOfStu | +---------+----------------+----------+ | 1 | Shaolin Pai | 10 | | 2 | Emei Pai | 7 | | 3 | QingCheng Pai | 11 | | 4 | Wudang Pai | 12 | | 5 | Riyue Shenjiao | 31 | | 6 | Lianshan Pai | 27 | | 7 | Ming Jiao | 27 | | 8 | Xiaoyao Pai | 15 | | 9 | xxoo | 39 | +---------+----------------+----------+ 9 rows in set (0.00 sec)