备份
安装MySQL
安装mysql,可通过lnmp一键安装包安装,https://lnmp.org
修改目录
修改/etc/my.cnf下datadir,basedir,log-bin,innodb_data_home目录位置,挂载到逻辑卷上,目录结构如下:
datadir=/data/mysql/datadir
log-bin=/data/mysql/log/文件名
innodb_data_home_dir=/data/mysql/var
innodb_data_file_path=/data/mysql/var
数据目录
/data/mysql/datadir
/data/mysql/var
日志目录
/data/mysql/log
注意:目录所有者,以及相应权限需要重新配置。
sudo chown -R mysql:mysql /data/mysql
目录更改后,需要初始化mysql
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/datadir
启动mysql
su mysql//切换到mysql用户,启动服务
service mysql start
创建备份用户
GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'localhost' IDENTIFIED BY 'lvm';
FLUSH PRIVILEGES;
记录备份点
mysql -ulvm -hlocalhost -plvm -e 'SHOW MASTER STATUS' > /tmp/backup_point.txt
创建快照并挂载
lvcreate -L 1G -s -n lvmbackup -p r /dev/vg0/lv1
mount -t ext4 /dev/test_vg/lvmbackup /backup
释放锁
mysql -ulvm -hlocalhost -plvm -e 'UNLOCK TABLES'
做一些写入操作
create database testdb2
复制文件
# cp /backup/mysql/* /tmp/backup_mysqldata -r
备份完成卸载,删除快照卷
umount /backup
lvmremove /dev/vg0/lvmbackup
还原
假设mysql数据目录误删
数据复制
cp -r /tmp/backup_mysqldata/mysql/datadir /data/mysql
cp -r /tmp/backup_mysqldata/mysql/var /data/mysql
注意:复制之后,也需要修改用户权限,以及目录所有者。
查看之前的记录点,向后还原
# cat /tmp/backup_point.txt FilePositionBinlog_Do_DBBinlog_Ignore_DB mysql-bin.000001245
#mysqlbinlog /data/binlog/mysql-bin.000001 --start-position 245 > tmp.sql
MariaDB [test]> source /data/mysqldata/tmp.sql
MariaDB [test]> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| mysqldata |
| openstack |
| performance_schema |
| test |
| testdb2 |
+--------------------+
8 rows in set (0.00 sec)
testdb2 已经被还原回来。