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