image
1.database准备阶段
业务数据库如下
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
业务表如下
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| testlog |
| toc |
+-------------------+
8 rows in set (0.00 sec)
表testlog有十万行数据
mysql> select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.08 sec)
hellodb库下一个存储过程procedure
(root@localhost) [hellodb]> create table testlog(id int auto_increment primary key,name char(10),age int default 20);
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [hellodb]> delimiter //
(root@localhost) [hellodb]> create procedure sp_testlog()
begin
declare i int;
set i=1;
while i <= 100000
do insert into testlog(name,age) values(concat("wang",i),i);
set i=i+1;
end while;
end//
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [hellodb]> delimiter ;
目录文件结构
root@8 mysql]# tree -d /mysql/
/mysql/
└── 3306
├── binlog
├── data
│ ├── hellodb
│ ├── #innodb_temp
│ ├── lost+found
│ ├── mysql
│ ├── performance_schema
│ └── sys
├── etc
├── log
├── pid
├── sock
└── trans_log
#数据目录如下 在逻辑卷上 剩下的所有目录包括二进制目录都在本地根分区的磁盘（懒得做了），生产需要二进制日志所在目录与数据目录都分离为单独的固态硬盘
root@8 mapper]# lsblk /dev/vg_mysql_data/lv_data
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
vg_mysql_data-lv_data 253:0 0 5G 0 lvm /mysql/3306/data
2.backupserver准备阶段
准备好各种目录
mkdir -p /mysql/3306/{data,log,binlog,trans_log,sock,pid,etc}
chown -R mysql.mysql /mysql/
准备相同版本的软件并配置好变量
root@8 ~]# which mysql
/usr/local/mysql/bin/mysql
root@8 ~]# mysql -V
mysql Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
准备好unit文件
root@17 data]# cat /lib/systemd/system/mysqld3306.service
[Unit]
Description=mysql database server
After=network.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3306/etc/my.cnf
LimitNOFILE=65535
3.database服务器备份操作
backupserver准备脚本
yum -y install autoconf libaio-devel numactl-libs libtinfo* net-tools vim wget gcc gcc-c++ lrzsz rsync &>/dev/null
id mysql &>/dev/null && { action "mysql already exists";usermod -u306 -md /data/mysql -s /sbin/nologin mysql &>/dev/null; } || { useradd -r -u306 -d /data/mysql -s /sbin/nologin mysql &>/dev/mull; }
mkdir -p /mysql/3306/{data,log,binlog,trans_log,sock,pid,etc} &>/dev/null
chown -R mysql.mysql /mysql/
准备database LVM全备份脚本
cat > /root/mysqlbackup.sh <<EOF
#!/bin/bash
#写PATH变量
PATH=/usr/local/mysql/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
#定义一些变量 我测试数据库没密码 生产中需要mysql_password变量写进密码
bk_server="root@10.0.0.17"
mysql_user="root"
mysql_password=""
bk_password="123456" #备份服务器密码
bk_user=root #备份服务器登陆用户
data_dir="/mysql/3306/data" #数据库数据目录
cnf_dir="/mysql/3306/etc" #数据库配置文件目录
vg_dir="/dev/vg_mysql_data" #卷组
snap_name="snap_data" #创建时候快照卷名字
snap_data="\${vg_dir}/\${snap_name}" #数据库数据目录所在的逻辑卷名称
lv_data="\${vg_dir}/lv_data" #快照卷名称
#调用expect免交互 执行数据库服务器到备份服务器的公钥推送
expect <<END
set timeout 10
spawn ssh-copy-id \$bk_server
expect {
"yes/no" { send "yes\n";exp_continue }
"password" { send "\${bk_password}\n" }
}
expect eof
END
#数据库加 这里生产用密码上面变量有定义。加全局读锁，刷新二进制日志，记录此时最新的二进制日志位置
mysql -uroot -e 'FLUSH TABLES WITH READ LOCK; FLUSH LOGS;show master status' > ~/binlog_flush.sql
#做数据目录的逻辑卷快照
lvcreate -n \${snap_name} -s -p r -L 100M \${lv_data}
#解锁
mysql -uroot -e 'UNLOCK TABLES;'
#挂载快照卷并压缩打包备份
mount -o ro \${vg_dir}/\${snap_name} /mnt
cd /mnt && tar -czf /backup/mysql_bk`date +%F-%H`.gz *
rsync -auv /backup/ \${bk_server}:\${data_dir} && umount /mnt
#我这里将10.0.0.8数据库主机的/mnt逻辑卷目录下的文件压缩到/backup目录，并执行rsync远程增量备份 -av保留权限所有者所属主 -u表示目标比源新 不会覆盖更新。
#免交互删除快照卷
expect <<END
set timeout 5
spawn lvremove \${snap_data}
expect "Do" { send "y\n" }
expect eof
END
#rsync备份二进制日志位置文件到备份主机家目录
#rsync备份配置文件到备份主机的配置文件目录
rsync -av ~/binlog_flush.sql \${bk_server}:~/
rsync -av \${cnf_dir}/my.cnf \${bk_server}:\${cnf_dir}/
#备份unit服务文件
rsync -av /lib/systemd/system/mysqld.service \${bk_server}:/lib/systemd/system/mysql3306.service
EOF
定时任务备份文件
cat >> /var/spool/cron/root <<EOF
0 24 * * */1 /bin/bash /root/mysqlbackup.sh
0 23 */1 * * /bin/bash /root/binlogbackup.sh
EOF
4.还原时候看看有没有之前创建的十万行数据
先全备份还原。
再追加二进制日志还原
备份还原则需要根据/root/binlog_flush.sql的位置之后的二进制文件来追加还原。mysqlbinlog /mysql/3306/binlog/mysql-bin.0000xx > /PATH/TO/FILE二进制文件
root@17 data]# tar xf mysql_bk2021-01-19-12.gz
root@17 data]# ll
total 73016
-rw-r----- 1 mysql mysql 56 Jan 18 18:28 auto.cnf
-rw------- 1 mysql mysql 1676 Jan 18 18:28 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 18 18:28 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 18 18:28 client-cert.pem
-rw------- 1 mysql mysql 1680 Jan 18 18:28 client-key.pem
drwxr-x--- 2 mysql mysql 6 Jan 18 21:13 db1
drwxr-x--- 2 mysql mysql 4096 Jan 18 21:13 hellodb
-rw-r----- 1 mysql mysql 5801 Jan 18 18:28 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan 19 11:52 ibdata1
-rw-r----- 1 mysql mysql 12582912 Jan 18 18:28 ibtmp1
drwxr-x--- 2 mysql mysql 187 Jan 18 18:28 #innodb_temp
drwx------ 2 root root 6 Jan 18 18:25 lost+found
drwxr-x--- 2 mysql mysql 143 Jan 18 18:28 mysql
-rw-r--r-- 1 root root 3401634 Jan 19 12:35 mysql_bk2021-01-19-12.gz
-rw-r----- 1 mysql mysql 25165824 Jan 18 21:22 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Jan 18 18:28 performance_schema
-rw------- 1 mysql mysql 1680 Jan 18 18:28 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 18 18:28 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 18 18:28 server-cert.pem
-rw------- 1 mysql mysql 1680 Jan 18 18:28 server-key.pem
drwxr-x--- 2 mysql mysql 28 Jan 18 18:28 sys
-rw-r----- 1 mysql mysql 10485760 Jan 18 21:22 undo_001
-rw-r----- 1 mysql mysql 10485760 Jan 18 21:22 undo_002
我们看到权限被保留了
root@17 data]# systemctl start mysqld3306
root@17 data]# ss -lnt|awk {print}
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 70 [::]:33060 [::]:*
LISTEN 0 128 [::]:3306 [::]:*
LISTEN 0 128 [::]:22 [::]:*
root@17 data]# mysql hellodb -S /mysql/3306/sock/mysql.sock -e 'select count(*) from testlog'
+----------+
| count(*) |
+----------+
| 100001 |
+----------+
我们看到全备份数据被保留。剩下的就是追加二进制日志
根据家目录下binlog_flush.sql文件位置确定数据库主机全备份后的二进制文件
root@17 ~]# ll binlog_flush.sql
-rw-r--r-- 1 root root 86 Jan 19 12:35 binlog_flush.sql
root@17 ~]# cat binlog_flush.sql
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000030 155
我们看到 在mysql-bin.000030文件的position=155之后是全备份后的二进制日志
#在数据库主机操作
mysqlbinlog start-position=155 mysql-bin.000030 > /root/binlogbk.sql
把该文件scp到备份主机上 mysql < /root/binlogbk.sql
网友评论