備份恢復
準備工作
16主機-ubuntu系統
準備日志目錄
mkdir -p /data/mysql/logs/
chown mysql:mysql -R /data/mysql
定制日志配置
vim /etc/mysql/mariadb.conf.d/50-server.cnf
log_bin=/data/mysql/logs/binlog
systemctl restart mariadb
刪除db1數據庫
drop database db1;
13主機-ubuntu系統
準備工作
mkdir -p /data/mysql/logs
chown mysql:mysql -R /data/mysql
vim /etc/mysql/mariadb.conf.d/50-server.cnf
log_bin=/data/mysql/logs/binlog
systemctl restart mariadb
CREATE TABLE `student` (-> `id` int(11) NOT NULL AUTO_INCREMENT,-> `name` varchar(255) NOT NULL,-> `age` int(11) NOT NULL,-> `gender` enum('M', 'F') NOT NULL,-> PRIMARY KEY (`id`)-> );
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F');
insert into student(name,age,gender)values('u11',11,'M'),('u22',22,'F');
冷備份
16主機創建目錄
mkdir -p /data/backup
cd /data/backup
13主機冷備份
關閉13主機服務
systemctl stop mariadb.service
備份數據并傳給16主機
mkdir -p /data/backup
cd /data/backup
tar zcf base_data.tar.gz /var/lib/mysql
tar zcf binlog_data.tar.gz /data/mysql/logs
scp ./* root@10.0.0.16:/data/backup/
冷還原
16主機清空數據
systemctl stop mariadb.service
\rm -rf /var/lib/mysql/*
\rm -rf /data/mysql/logs/*
tar xf base_data.tar.gz
tar xf binlog_data.tar.gz
還原數據
mv ./data/mysql/logs/* /data/mysql/logs/
mv ./var/lib/mysql/* /var/lib/mysql/
systemctl start mariadb
測試
Mysqldump備份還原
單個數據庫備份還原
準備工作
創建數據庫
create database db2;
create table db2.student select * from db1.student;
create table db2.student2 select * from db1.student;
create table db2.student3 select * from db1.student;
insert into db1.student(name,age,gender) values('db1-user',55,'M');
insert into db2.student(name,age,gender) values('db2-user1',55,'M');
insert into db2.student2(name,age,gender) values('db2-user2',55,'M');
insert into db2.student3(name,age,gender) values('db2-user3',55,'M');
\rm -rf ./*
備份
mysqldump db1 > /data/backup/db1-bak.sql
破壞db1數據庫
drop table db1.student;
show tables from db1;
還原
mysql db1 < ./db1-bak.sqlselect * from db1.student;
多個數據庫備份還原
備份
mysqldump -A > /data/backup/allsql-bak.sql
破壞數據庫
drop database db1;
drop database db2;
還原
mysql < ./allsql-bak.sql
show databases;
二進制 POS 信息選擇性恢復
準備工作
\rm -rf *
執行備份
mysqldump -B db2 --single-transaction --master-data > ./db2.sql
進行誤操作
use db2;
select * from student;
insert into student (name,age) values('db2-user1',66);
update student set age=44 where id=4;
select * from student;
drop table student;誤操作
insert into student2 (name,age) values('111',11);
查找誤操作日志
grep "CHANGE MASTER" /data/backup/db2.sql
mysqlbinlog --start-position=2497717 /data/mysql/logs/binlog.000002 > db2_logbin.sql
grep -i 'drop' db2_logbin.sql
vim db2_logbin.sql
對誤操作添加注釋--
傳遞給13主機
scp ./db2_logbin.sql root@10.0.0.13:/root/
scp ./db2.sql root@10.0.0.13:/root/
13主機還原數據
set sql_log_bin=0;臨時關閉下
source /root/db2.sql;
set sql_log_bin=1;導入后重開
誤操作數據傳回16主機
mysqldump db2 student > db2-student.sql
scp db2-student.sql root@10.0.0.16:/root/
再回16主機還原誤操作數據
set sql_log_bin=0;臨時關閉
source /root/db2-student.sql;
set sql_log_bin=1;導入后再開啟
