数据库冷备份和还原
停机备份
#备份过程,停机备份
[root@ubuntu2204 mysql]#systemctl stop mysql.service
#备份数据,即将数据库文件在停服务后,拷贝到另一台服务器
#ubuntu默认没有rsync工具,也可使用其他如scp等拷贝命令
[root@ubuntu2204 lib]#apt install rsync
#打包拷贝数据库文件到远程主机/data/下
[root@ubuntu2204 lib]#tar -cvf mysql.tar mysql/
[root@ubuntu2204 lib]#scp /var/lib/mysql.tar root@10.0.0.202:/data
#还原
#安装mysql服务
[root@ubuntu2204data]#apt install mysql-server
#ubuntu安装完之后数据库服务会自动启动,需先停止服务,然后删除安装完数据库之后初始的数据
[root@ubuntu2204 mysql]#systemctl stop mysql
[root@ubuntu2204 mysql]#rm -rf /var/lib/mysql/*
#解压备份的数据库文件至数据库安装目录
[root@ubuntu2204 mysql]#tar xvf /data/mysql.tar -C /var/lib/
[root@ubuntu2204 mysql]#ll
总用量 81452
drwx------ 9 mysql mysql 409611月 1210:59 ./
drwxr-xr-x 36 root root 409611月 1211:35 ../
-rw-r--r-- 1 root root 2864 11月 11 22:19 157.sql
-rw-r----- 1 mysql mysql 5611月 420:32 auto.cnf
-rw-r----- 1 mysql mysql 18011月 420:32 binlog.000001
-rw-r----- 1 mysql mysql 40411月 420:32 binlog.000002
-rw-r----- 1 mysql mysql 18011月 421:46 binlog.000003
-rw-r----- 1 mysql mysql 254911月 515:55 binlog.000004
-rw-r----- 1 mysql mysql 18011月 515:57 binlog.000005
-rw-r----- 1 mysql mysql 18011月 516:25 binlog.000006
#启动数据库服务,查看是否正常恢复
[root@ubuntu2204 mysql]#systemctl start mysql
[root@ubuntu2204 mysql]#systemctl start mysql
[root@ubuntu2204 mysql]#mysql
mysql>showdatabases;
+--------------------+
|Database |
+--------------------+
| class |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows inset(0.01 sec)
Mariadb10.3
#在目标服务器(10.0.0.18)安装mariadb-server,不启动服务
[root@centos8 ~]#dnf install mariadb-server
#在源主机(10.0.0.8)执行
[root@centos8 ~]# systemctl stop mariadb
#复制相关文件
[root@centos8 ~]# scp -r /var/lib/mysql/* 10.0.0.18:/var/lib/mysql/
[root@centos8 ~]# scp /etc/my.cnf.d/mariadb-server.cnf 10.0.0.18:/etc/my.cnf.d/
[root@centos8 ~]# scp -r /data/logbin/ 10.0.0.18:/data/ #10.0.0.18须事先存在/data/目录
#复制相关文件并保留属性:可以用rsync
[root@centos8 ~]#rsync /etc/my.cnf.d/mariadb-server.cnf 10.0.0.18:/etc/my.cnf.d/
[root@centos8 ~]#rsync -av /var/lib/mysql/ 10.0.0.18:/var/lib/mysql/
[root@centos8 ~]#rsync -av/data/logbin/ 10.0.0.18:/data/ #10.0.0.18 须事先存在/data/目录
#在目标主机(10.0.0.18)执行
[root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql/
[root@centos8 ~]#chown -R mysql.mysql /data/logbin/
[root@centos8 ~]#systemctl start mariadb
mysqldump备份工具方式
特定数据库的备份脚本
[root@centos8 ~]#cat mysql_backup.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=wang
[ -d$DIR ] || mkdir$DIR
mysqldump -uroot-p"$PASS"-F-E-R--triggers--single-transaction--masterdata=
2--default-character-set=utf8 -q-B$DB | gzip >
${DIR}/${DB}_${TIME}.sql.gz
分库备份并压缩
[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db |gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema\sys)$'|while read db;do mysqldump -B$db | gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema|sys)$' | sed-rn's#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn
'/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
完全备份和还原
#开启二进制,默认mysql8.0开启
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#备份,-A所有数据库,-F备份前滚动日志(每备份一个数据库,当前的二进制日志文件会更新变化)
[root@rocky8 mysql]#mysqldump -uroot -A -F --single-transaction --master-data=2 > /data/backup/all1.sql
WARNING:--master-datais deprecated and will be removed in a future version. Use--source-data instead.
或者可以压缩,加压缩命令
[root@rocky8 mysql]#mysqldump -uroot -A -F --single-transaction --master-data=2 |gzip > /data/backup/all2.sql.gz
WARNING:--master-datais deprecated and will be removed in a future version. Use--source-data instead.
#删除coc表
mysql>showtables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |mysql>set sql_log_bin=off;
Query OK,0 rows affected (0.00 sec)
mysql>source /data/backup/all2.sql
ERROR:
Failed toopen file '/data/backup/all2.sql', error:2
mysql>source /data/backup/all1.sql;
| toc |
+-------------------+
7 rows inset(0.00 sec)
mysql>droptables coc;
Query OK,0 rows affected (0.01 sec)
mysql>showtables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
6 rows inset(0.00 sec)
#还原
mysql>set sql_log_bin=off;
Query OK,0 rows affected (0.00 sec)
mysql>source /data/backup/all1.sql;
#开启二进制日志功能
mysql>set sql_log_bin=on;
Query OK,0 rows affected (0.00 sec)
#查看已恢复删除的库表
mysql>showtables
->;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows inset(0.00 sec)
利用二进制日志,还原数据库最新状态
修改二进制日志独立存放,避免数据库目录被删除,二进制日志也同步删除,影响数据恢复
#新建二进制日志存放目录
[root@rocky8data]#mkdir binlog
[root@rocky8data]#ll
total 4
drwxr-xr-x 2 root root 56 Nov 1215:39 backup
drwxr-xr-x 2 root root 6 Nov 1216:01binlog
drwxr-xr-x 9 mysql mysql 4096 Nov 1215:39 mysql
#给二进制目录赋权限
[root@rocky8data]#chown mysql.mysql binlog
[root@rocky8data]#ll
total 4
drwxr-xr-x 2 root root 56 Nov 1215:39 backup
drwxr-xr-x 2 mysql mysql 6 Nov 1216:01binlog
drwxr-xr-x 9 mysql mysql 4096 Nov 1215:39 mysql
#修改二进制日志存放位置,新增如下配置
[root@rocky8data]#vim /etc/my.cnf
[mysqld]
log-bin=/data/binlog/mysql-bin
#重启服务
[root@rocky8data]#systemctl restart mysql
#查看已在修改过的目录重新生成二进制文件
[root@rocky8binlog]#ls
mysql-bin.000001 mysql-bin.index
mysql>showmasterstatus;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001| 157| | | |
+------------------+----------+--------------+------------------+-------------------+
1rowinset(0.00 sec)
完全备份,并记录备份的二进制位置
#完全备份,并记录二进制位置(--source-data),默认字符集--default-character-set=utf8mb4,-A备份所有库,-F备份前滚动日志(每备份一个库滚动一次),--single-transaction执行单个事务
[root@rocky8data]#mysqldump -uroot -A -F --default-character-set=utf8mb4 --single-transaction --source-data=2 | gzip > /data/backup/all_`date +%F`.sql.gz
[root@rocky8data]#ll backup/
-rw-r--r-- 1 root root 274959 Nov 12 16:15 all_2022-11-12.sql.gz
#修改数据,删除一个coc表,teachers新增几条记录
mysql>showtables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows inset(0.01 sec)
mysql>droptables coc;
Query OK,0 rows affected (0.01 sec)
mysql>showtables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
6 rows inset(0.00 sec)
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
+-----+---------------+-----+--------+
4 rows inset(0.01 sec)
mysql>insert teachers(name,age,gender)values('Hong Qigong',70,'M'),('Xiao Longnv',18,'F');
Query OK,2 rows affected (0.00 sec)
Records:2 Duplicates:0 Warnings:0
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
| 5| Hong Qigong | 70| M |
| 6| Xiao Longnv | 18| F |
+-----+---------------+-----+--------+
6 rows inset(0.00 sec)
#删库跑路,如下为直接移走数据库,类似直接rm -rf
[root@rocky8data]#mv mysql/* backup_mysql/
[root@rocky8data]#ll mysql/
total 0
#还原
#由于做过删库操作,数据库已崩溃,无法直接执行数据导入操作,这时执行数据库初始化操作如无法启动数据库,需还原配置,如/etc/my.cnf,停止mysql服务开机启动,然后再执行数据库初始化步骤
#还原/etc/my.cnf配置,清空数据库目录
[root@rocky8 mysql]#cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@rocky8 mysql]#rm -rf /data/mysql/*
#关闭开机启动
[root@rocky8 mysql]#systemctl disable --now mysqld
#执行初始化步骤
[root@rocky8 backup]#mysqld --initialize --user=mysql --datadir=/data/mysql
或者执行如下初始化命令,生成空密码
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
#执行完生成密码的命令后,随机密码需要到日志中去找,此实验为方便操作,直接执行生成的空密码
[root@rocky8 mysql]#grep password /data/mysql/mysql.log
#查看数据库文件目录,已生成初始化文件
[root@rocky8 ~]#cd /data/mysql/
[root@rocky8 mysql]#ls
auto.cnf '#ib_16384_0.dblwr' '#innodb_temp' private_key.pem undo_001
ca-key.pem '#ib_16384_1.dblwr' mysql public_key.pem undo_002
ca.pem ib_buffer_pool mysql.ibd server-cert.pem
client-cert.pem ibdata1 mysql.log server-key.pem
client-key.pem '#innodb_redo' performance_schema sys
#启动服务,并开启开机启动服务
[root@rocky8 mysql]#systemctl enable --now mysqld
mysqld.serviceisnot a native service, redirecting to systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-installenable mysqld
#已可正常进入
[root@rocky8 mysql]#mysql
Welcome to the MySQL monitor. Commands endwith;or\g.
Your MySQL connection id is8
Server version:8.0.30 MySQL Community Server- GPL
Type 'help;'or'\h'forhelp. Type '\c'toclear the current input statement.
mysql>
#执行数据库恢复
[root@rocky8 backup]#gzip -d all_2022-11-12.sql.gz
[root@rocky8 backup]#ls
all_2022-11-12.sql
[root@rocky8 backup]#mysql < all_2022-11-12.sql
#查看数据已导入
[root@rocky8 backup]#mysql
mysql>showdatabases;
+--------------------+
|Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows inset(0.00 sec)
mysql>use hellodb
Reading table information forcompletion of tableandcolumn names
You can turn off this feature toget a quicker startup with-A
Databasechanged
mysql>showtables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows inset(0.01 sec)
#查看备份数据后与删除数据库前之间插入的数据未恢复,这是再通过二进制日志恢复的方式,将数据恢复
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
+-----+---------------+-----+--------+
4 rows inset(0.00 sec)
#找到备份时的二进制日志
[root@rocky8binlog]#grep '^-- CHANGE MASTER TO' /data/backup/all_2022-11-12.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;
#将找到的备份时的二进制日志文件以后的二进制日志文件导出,如下开始位置--start-position为备份时记录的节点
[root@rocky8binlog]#mysqlbinlog mysql-bin.000003 --start-position=157 > restore.sql
#如二进制日志功能开启,则关闭日志记录功能,恢复期间日志量过大同时也没记录价值
mysql>select@@log_bin
->;
+-----------+
|@@log_bin|
+-----------+
| 1|
+-----------+
1rowinset(0.00 sec)
mysql>select@@sql_log_bin
->;
+---------------+
|@@sql_log_bin|
+---------------+
| 1|
+---------------+
1rowinset(0.00 sec)
mysql>set sql_log_bin=0;
Query OK,0 rows affected (0.00 sec)
mysql>select@@sql_log_bin;
+---------------+
|@@sql_log_bin|
+---------------+
| 0|
+---------------+
1rowinset(0.00 sec)
#执行二进制日志恢复操作
[root@rocky8binlog]#mysql < restore.sql
#查看数据已恢复
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
| 5| Hong Qigong | 70| M |
| 6| Xiao Longnv | 18| F |
+-----+---------------+-----+--------+
6 rows inset(0.00 sec)
#再开启日志功能
mysql>set sql_log_bin=1;
Query OK,0 rows affected (0.00 sec)
mysql>select@@sql_log_bin;
+---------------+
|@@sql_log_bin|
+---------------+
| 1|
+---------------+
1rowinset(0.00 sec)
恢复误删除的表
场景说明:每天1:00做完全备份,早上09:00误删除了表students,10:00才发现故障,现需要将数据库还原到10:00的状态,且恢复被删除的students表
#完全备份,要求必须开启二进制日志
[root@rocky8 ~]#mysqldump -uroot -A -F --single-transaction --master-data=2 > /data/backup/allbackup_`date +%F`.sql
[root@rocky8 ~]#ll /data/backup/* -d
-rw-r--r-- 1 root root 1244447 Nov 13 16:17 /data/backup/allbackup_2022-11-13.sql
#完全备份后,进行数据修改,插入部分数据
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
| 5| Liu Dehua | 55| M |
| 6| Liu Huang | 60| M |
+-----+---------------+-----+--------+
6 rows inset(0.00 sec)
mysql>insert teachers(name,age,gender)values('Guo Jing',80,'M');
Query OK,1row affected (0.02 sec)
mysql>insert teachers(name,age,gender)values('Qiao Feng',70,'M');
Query OK,1row affected (0.01 sec)
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
| 5| Liu Dehua | 55| M |
| 6| Liu Huang | 60| M |
| 7| Guo Jing | 80| M |
| 8| Qiao Feng | 70| M |
+-----+---------------+-----+--------+
8 rows inset(0.00 sec)
#9:00误删除了一个重要的表
mysql>showtables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows inset(0.00 sec)
mysql>droptable toc;
Query OK,0 rows affected (0.03 sec)
mysql>showtables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
+-------------------+
6 rows inset(0.00 sec)
#后续其他表继续更新
mysql>select*from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1| Shi Zhongyu | 22| M | 2| 3|
| 2| Shi Potian | 22| M | 1| 7|
.............................................................
| 23| Ma Chao | 23| M | 4| NULL|
| 24| Xu Xian | 27| M | NULL| NULL|
| 25| Sun Dasheng |100| M | NULL| NULL|
+-------+---------------+-----+--------+---------+-----------+
25 rows inset(0.00 sec)
mysql>insert students(name,age)values('Yang Guo',25);
Query OK,1row affected (0.01 sec)
mysql>insert students(name,age)values('Ling Huchong',28);
Query OK,1row affected (0.01 sec)
mysql>select*from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1| Shi Zhongyu | 22| M | 2| 3|
| 2| Shi Potian | 22| M | 1| 7|
.............................................................
| 25| Sun Dasheng |100| M | NULL| NULL|
| 26| Yang Guo | 25| F | NULL| NULL|
| 27| Ling Huchong | 28| F | NULL| NULL|
+-------+---------------+-----+--------+---------+-----------+
27 rows inset(0.00 sec)
#10:00发现表删除,进行还原
#停止数据库访问
#从完全备份中,找到二进制位置
[root@rocky8 ~]#grep '\-\- CHANGE MASTER TO' /data/backup/allbackup_2022-11-13.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000008', MASTER_LOG_POS=157;
#备份从完全备份后的二进制日志
[root@rocky8 ~]#mysqlbinlog --start-position=157 /var/lib/mysql/binlog.000008 > /data/backup/inc.sql
#后面如果还有好几个日志,可以采用追加的方式,写入到同一个备份的二进制文件
[root@rocky8 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000009 >>
/data/backup/inc.sql
[root@rocky8 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000010 >>
/data/backup/inc.sql
#找到误删除的语句,从备份中删除此语句
[root@rocky8 ~]#vim /data/backup/inc.sql
DROPTABLE`toc`/* generated by server */
#如果文件过大,可以使用sed实现
[root@rocky8 ~]#sed -i '/DROP TABLE `toc`/d' /data/backup/inc.sql
#利用完全备份和修改过的二进制日志进行还原
#停二进制日志
mysql>set sql_log_bin=0;
Query OK,0 rows affected (0.01 sec)
#导入备份的数据库
mysql>source /data/backup/allbackup_2022-11-13.sql;
#导入修改过的二进制日志
mysql>source /data/backup/inc.sql;
#开启二进制日志
mysql>set sql_log_bin=1;
Query OK,0 rows affected (0.00 sec)
#查看数据已恢复
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
| 5| Liu Dehua | 55| M |
| 6| Liu Huang | 60| M |
| 7| Guo Jing | 80| M |
| 8| Qiao Feng | 70| M |
+-----+---------------+-----+--------+
8 rows inset(0.00 sec)
mysql>select*from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1| Shi Zhongyu | 22| M | 2| 3|
..............................................................
| 26| Yang Guo | 25| F | NULL| NULL|
| 27| Ling Huchong | 28| F | NULL| NULL|
+-------+---------------+-----+--------+---------+-----------+
27 rows inset(0.00 sec)
利用xtrabackup备份工具实现备份和还原
xtrabackup备份工具可实现增量备份
利用 xtrabackup 实现完全备份及还原
#安装xtrabackup包,先到percona官网下载安装包
[root@rocky8 ~]#ll
-rw-r--r-- 1 root root 18156524 Jun 23 09:06 percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm
#yum方式安装,解决依赖问题
[root@rocky8 ~]#yum install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm
#查看主机数据库表
12:25:58(root@localhost)[(none)]>showdatabases;
+--------------------+
|Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows inset(0.01 sec)
#在原主机做完全备份到/data/backup目录,提示版本不支持,需降级mysql
[root@rocky8 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/data/backup/base
.............................
xtrabackup version 8.0.28-20 based on MySQL server8.0.28 Linux (x86_64)(revision id:4cc3081873d)
2022-11-13T12:33:57.151369+08:000[ERROR][MY-011825][Xtrabackup] Unsupported server version 8.0.30
#接下去重新安装mysql8.0.28版本,先下载mysql bundle安装包,使用yum命令安装,否则使用rpm命令安装仍然会出现报依赖问题,需一个一个解决
[root@rocky8 mysql]#ls
mysql-community-client-8.0.28-1.el8.x86_64.rpm
mysql-community-client-debuginfo-8.0.28-1.el8.x86_64.rpm
mysql-community-client-plugins-8.0.28-1.el8.x86_64.rpm
mysql-community-client-plugins-debuginfo-8.0.28-1.el8.x86_64.rpm
mysql-community-common-8.0.28-1.el8.x86_64.rpm
mysql-community-debuginfo-8.0.28-1.el8.x86_64.rpm
mysql-community-debugsource-8.0.28-1.el8.x86_64.rpm
mysql-community-devel-8.0.28-1.el8.x86_64.rpm
mysql-community-icu-data-files-8.0.28-1.el8.x86_64.rpm
mysql-community-libs-8.0.28-1.el8.x86_64.rpm
mysql-community-libs-debuginfo-8.0.28-1.el8.x86_64.rpm
mysql-community-server-8.0.28-1.el8.x86_64.rpm
mysql-community-server-debug-8.0.28-1.el8.x86_64.rpm
mysql-community-server-debug-debuginfo-8.0.28-1.el8.x86_64.rpm
mysql-community-server-debuginfo-8.0.28-1.el8.x86_64.rpm
mysql-community-test-8.0.28-1.el8.x86_64.rpm
mysql-community-test-debuginfo-8.0.28-1.el8.x86_64.rpm
#rpm方式仍然报依赖问题
[root@rocky8 mysql]#rpm -ivh *
warning: mysql-community-client-8.0.28-1.el8.x86_64.rpm: Header V4 RSA/SHA256 Signature,key ID 3a79bd29: NOKEY
error: Failed dependencies:
pkgconfig(openssl)is needed by mysql-community-devel-8.0.28-1.el8.x86_64
mariadb-connector-c-config is obsoleted by mysql-community-server-8.0.28-1.el8.x86_64
perl(JSON)is needed by mysql-community-test-8.0.28-1.el8.x86_64
perl(Memoize)is needed by mysql-community-test-8.0.28-1.el8.x86_64
perl(Time::HiRes)is needed by mysql-community-test-8.0.28-1.el8.x86_64
#采用yum命令安装
[root@rocky8 mysql]#yum install mysql*
Last metadata expiration check:0:10:28 ago on Sun 13 Nov 202212:50:26 PM CST.
Dependencies resolved.
=============================================================================================
Package Arch Version Repository Size
=============================================================================================
Installing:
mysql-community-client x86_64 8.0.28-1.el8 @commandline 14 M
mysql-community-client-debuginfo x86_64 8.0.28-1.el8 @commandline 34 M
mysql-community-client-plugins x86_64 8.0.28-1.el8 @commandline 2.4 M
mysql-community-client-plugins-debuginfo x86_64 8.0.28-1.el8 @commandline 2.9 M
mysql-community-common x86_64 8.0.28-1.el8 @commandline 633 k
mysql-community-debuginfo x86_64 8.0.28-1.el8 @commandline 3.7 M
mysql-community-debugsource x86_64 8.0.28-1.el8 @commandline 21 M
mysql-community-devel x86_64 8.0.28-1.el8 @commandline 2.2 M
mysql-community-icu-data-files x86_64 8.0.28-1.el8 @commandline 2.1 M
mysql-community-libs x86_64 8.0.28-1.el8 @commandline 1.5 M
mysql-community-libs-debuginfo x86_64 8.0.28-1.el8 @commandline 2.9 M
mysql-community-server x86_64 8.0.28-1.el8 @commandline 53 M
replacing mariadb-connector-c-config.noarch3.1.11-2.el8_3
mysql-community-server-debug x86_64 8.0.28-1.el8 @commandline 22 M
mysql-community-server-debug-debuginfo x86_64 8.0.28-1.el8 @commandline 98 M
mysql-community-server-debuginfo x86_64 8.0.28-1.el8 @commandline 251 M
mysql-community-test x86_64 8.0.28-1.el8 @commandline 231 M
mysql-community-test-debuginfo x86_64 8.0.28-1.el8 @commandline 23 M
Installing dependencies:
keyutils-libs-devel x86_64 1.5.10-9.el8 BaseOS 47 k
krb5-devel x86_64 1.18.2-14.el8 BaseOS 559 k
libcom_err-devel x86_64 1.45.6-4.el8 BaseOS 37 k
libkadm5 x86_64 1.18.2-14.el8 BaseOS 186 k
libselinux-devel x86_64 2.9-5.el8 BaseOS 199 k
libsepol-devel x86_64 2.9-3.el8 BaseOS 86 k
libverto-devel x86_64 0.3.0-5.el8 BaseOS 17 k
openssl-devel x86_64 1:1.1.1k-7.el8_6 BaseOS 2.3 M
pcre2-devel x86_64 10.32-3.el8_6 BaseOS 604 k
pcre2-utf16 x86_64 10.32-3.el8_6 BaseOS 228 k
pcre2-utf32 x86_64 10.32-3.el8_6 BaseOS 219 k
perl-JSON noarch 2.97.001-2.el8 AppStream 95 k
perl-Memoize noarch 1.03-421.el8 AppStream 117 k
perl-Time-HiRes x86_64 4:1.9758-2.el8 AppStream 60 k
zlib-devel x86_64 1.2.11-19.el8_6 BaseOS 57 k
Transaction Summary
#安装完mysql后出现无法启动问题
[root@rocky8 ~]#systemctl enable --now mysqld.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service"and"journalctl -xe"for details.
#查询日志,怀疑升降级mysql存在遗留旧文件问题
[root@rocky8 ~]#cat /var/log/mysqld.log
2022-11-13T05:06:39.739504Z 0[System][MY-010116][Server] /usr/sbin/mysqld (mysqld 8.0.28)startingas process 9429
2022-11-13T05:06:39.753216Z 1[System][MY-013576][InnoDB]InnoDB initialization has started.
2022-11-13T05:06:40.013871Z 1[ERROR][MY-013171][InnoDB] Cannot boot server version 80028ondatadirectory built by version 80030. Downgrade isnot supported
mysqld: Can't open file: 'mysql.ibd' (errno: 0 - )
2022-11-13T05:06:45.014768Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-11-13T05:06:45.014948Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-11-13T05:06:45.015077Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-11-13T05:06:45.015552Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.
#删除/var/lib/mysql下所有文件
[root@rocky8 ~]#rm -rf /var/lib/mysql/*
#以空密码初始化数据库
[root@rocky8 ~]#mysqld --initialize-insecure --user=mysql
#这时可正常启动mysql服务
[root@rocky8 ~]#systemctl enable --now mysqld.service
#接下来继续备份
#继续使用xtrabackup命令备份,这时正常完成备份
[root@rocky8 ~]#xtrabackup -uroot --backup --target-dir=/data/backup/base
...............
2022-11-13T13:15:42.083882+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
#查看备份完成的文件
[root@rocky8 ~]#ll /data/backup/base/
total 72756
-rw-r----- 1 root root 475 Nov 13 13:15 backup-my.cnf
-rw-r----- 1 root root 157 Nov 13 13:15 binlog.000002
-rw-r----- 1 root root 16 Nov 13 13:15 binlog.index
drwxr-x--- 2 root root 132 Nov 13 13:15 hellodb
-rw-r----- 1 root root 6121 Nov 13 13:15 ib_buffer_pool
-rw-r----- 1 root root 12582912 Nov 13 13:15 ibdata1
drwxr-x--- 2 root root 143 Nov 13 13:15 mysql
-rw-r----- 1 root root 28311552 Nov 13 13:15 mysql.ibd
drwxr-x--- 2 root root 8192 Nov 13 13:15 performance_schema
drwxr-x--- 2 root root 28 Nov 13 13:15 sys
-rw-r----- 1 root root 16777216 Nov 13 13:15 undo_001
-rw-r----- 1 root root 16777216 Nov 13 13:15 undo_002
-rw-r----- 1 root root 18 Nov 13 13:15 xtrabackup_binlog_info
-rw-r----- 1 root root 102 Nov 13 13:15 xtrabackup_checkpoints
-rw-r----- 1 root root 463 Nov 13 13:15 xtrabackup_info
-rw-r----- 1 root root 2560 Nov 13 13:15 xtrabackup_logfile
-rw-r----- 1 root root 39 Nov 13 13:15 xtrabackup_tablespaces
#将备份文件拷贝至需还原的主机,这里我直接还原本机,拷贝远程主机可以采用scp拷贝,如下为恢复远程主机的命令
scp -r /data/backup/ 目标主机:/
远程主机安装percona-xtrabackup软件
#整理备份文件
[root@rocky8 ~]#xtrabackup --prepare --target-dir=/data/backup/base
............................
2022-11-13T13:31:25.461294+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
#按日期排序,可以看到整理完成后新增了三个如下文件或者做了更改
[root@rocky8 ~]#ll /data/backup/base/
.....................
-rw-r----- 1 root root 8388608 Nov 13 13:31 xtrabackup_logfile
-rw-r--r-- 1 root root 1 Nov 13 13:31 xtrabackup_master_key_id
-rw-r----- 1 root root 39 Nov 13 13:31 xtrabackup_tablespaces
#删除hellodb数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> drop database hellodb;
Query OK, 7 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#验证还原数据库,这里注意需删除数据库目录/var/lib/mysql下的所有文件,同时数据库服务需关闭,否则会导致无法恢复,同时需注意/var/lib/mysql目录的属主属性
#这里因为/var/lib/mysql下仍有文件,所以报错了
[root@rocky8 ~]#xtrabackup --copy-back --target-dir=/data/backup/base
..........................
directory /var/lib/mysql is not empty!
#关闭数据库服务
[root@rocky8 ~]#systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: d
#清空数据库目录/var/lib/mysql下文件
[root@rocky8 ~]#rm -rf /var/lib/mysql/*
[root@rocky8 ~]#ll /var/lib/mysql/
total 0
#使用xtrabackup --copy-back命令将备份的数据库文件还原,也可用cp复制文件回数据库目录
[root@rocky8 ~]#xtrabackup --copy-back --target-dir=/data/backup/base
...............................................
2022-11-13T13:49:46.420747+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
#查看数据库目录存在属主问题,更改属主
[root@rocky8 ~]#ll /var/lib/mysql/
total 183332
-rw-r----- 1 root root 157 Nov 13 13:49 binlog.000002
-rw-r----- 1 root root 14 Nov 13 13:49 binlog.index
drwxr-x--- 2 root root 132 Nov 13 13:49 hellodb
-rw-r----- 1 root root 6121 Nov 13 13:49 ib_buffer_pool
-rw-r----- 1 root root 12582912 Nov 13 13:49 ibdata1
-rw-r----- 1 root root 50331648 Nov 13 13:49 ib_logfile0
-rw-r----- 1 root root 50331648 Nov 13 13:49 ib_logfile1
-rw-r----- 1 root root 12582912 Nov 13 13:49 ibtmp1
drwxr-x--- 2 root root 143 Nov 13 13:49 mysql
-rw-r----- 1 root root 28311552 Nov 13 13:49 mysql.ibd
drwxr-x--- 2 root root 8192 Nov 13 13:49 performance_schema
drwxr-x--- 2 root root 28 Nov 13 13:49 sys
-rw-r----- 1 root root 16777216 Nov 13 13:49 undo_001
-rw-r----- 1 root root 16777216 Nov 13 13:49 undo_002
-rw-r----- 1 root root 463 Nov 13 13:49 xtrabackup_info
-rw-r----- 1 root root 1 Nov 13 13:49 xtrabackup_master_key_id
[root@rocky8 ~]#chown -R mysql.mysql /var/lib/mysql
[root@rocky8 ~]#ll /var/lib/mysql/
total 183332
-rw-r----- 1 mysql mysql 157 Nov 13 13:49 binlog.000002
-rw-r----- 1 mysql mysql 14 Nov 13 13:49 binlog.index
drwxr-x--- 2 mysql mysql 132 Nov 13 13:49 hellodb
-rw-r----- 1 mysql mysql 6121 Nov 13 13:49 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Nov 13 13:49 ibdata1
-rw-r----- 1 mysql mysql 50331648 Nov 13 13:49 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Nov 13 13:49 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Nov 13 13:49 ibtmp1
drwxr-x--- 2 mysql mysql 143 Nov 13 13:49 mysql
-rw-r----- 1 mysql mysql 28311552 Nov 13 13:49 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Nov 13 13:49 performance_schema
drwxr-x--- 2 mysql mysql 28 Nov 13 13:49 sys
-rw-r----- 1 mysql mysql 16777216 Nov 13 13:49 undo_001
-rw-r----- 1 mysql mysql 16777216 Nov 13 13:49 undo_002
-rw-r----- 1 mysql mysql 463 Nov 13 13:49 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Nov 13 13:49 xtrabackup_master_key_id
#启动数据库
[root@rocky8 ~]#systemctl start mysqld.service
#验证数据已恢复
[root@rocky8 ~]#mysql
......................
Server version: 8.0.28 MySQL Community Server - GPL
............................
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
手动下载的话,下载rpm bundle包,或者配置8.0.28的yum源
利用xtrabackup完全,增量备份及还原
#安装xtrabackup软件与上述方式一致,先到percona官网下载安装包,同时需注意与mysql版本匹配
[root@rocky8 ~]#ll
-rw-r--r-- 1 root root 18156524 Jun 23 09:06 percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm
#yum方式安装,解决依赖问题
[root@rocky8 ~]#yum install percona-xtrabackup-80-8.0.28-20.1.el8.x86_64.rpm
#新建备份文件存放目录
[root@rocky8 ~]#mkdir /data/backup/
#备份过程
#完全备份
[root@rocky8 ~]#xtrabackup -uroot --backup --target-dir=/data/backup/base
#第一次修改数据
mysql>select*from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1| Song Jiang | 45| M |
| 2| Zhang Sanfeng | 94| M |
| 3| Miejue Shitai | 77| F |
| 4| Lin Chaoying | 93| F |
+-----+---------------+-----+--------+
4 rows inset(0.00 sec)
mysql>insert teachers(name,age,gender)values('Liu Dehua',100,'M');
Query OK,1row affected (0.01 sec)
#第一次增量备份
[root@rocky8 ~]#xtrabackup -uroot --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
............................
2022-11-13T14:26:43.709133+08:000[Note][MY-011825][Xtrabackup] completed OK!
#第二次修改数据
mysql>insert teachers(name,age,gender)values('Liu Huang',100,'M');
Query OK,1row affected (0.00 sec)
#第二次增量
[root@rocky8 ~]#xtrabackup -uroot --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1
2022-11-13T14:29:33.778686+08:000[Note][MY-011825][Xtrabackup] completed OK!
#查看备份目录
[root@rocky8 ~]#du -sh /data/backup/*
74M /data/backup/base
2.0M /data/backup/inc1
2.1M /data/backup/inc2
#如需还原到另一台远程主机,则可以用scp命令拷贝至远程主机,同时远程主机也许安装xtrabackup备份软件
scp -r /data/backup/* 目标主机:/backup/
#还原过程
#预准备完成备份整理,--apply-log-only 阻止回滚未完成的事务
[root@rocky8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
..........................
2022-11-13T14:35:24.447703+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
#合并第一次增量备份到完全备份
[root@rocky8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1
.................................
2022-11-13T14:36:49.155953+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
#合并第二次增量备份到完全备份,由于后面没有备份节点了,所有最后一次还原不需要加选项--apply-log-only
[root@rocky8 ~]#xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2
..............
[root@rocky8 ~]#xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2
#整理完成后查看备份目录,明显大小增大了
[root@rocky8 ~]#du -sh /data/backup/*
190M /data/backup/base
10M /data/backup/inc1
11M /data/backup/inc2
#验证恢复,这里需停止mysql服务,以及清空数据库文件目录
[root@rocky8 ~]#systemctl stop mysqld.service
[root@rocky8 ~]#rm -rf /var/lib/mysql/*
[root@rocky8 ~]#ll /var/lib/mysql/
total 0
#恢复数据库文件,使用xtrabackup --copy-back命令或者直接使用cp拷贝命令直接拷贝至数据库文件目录
[root@rocky8 ~]#xtrabackup --copy-back --target-dir=/data/backup/base
.............................
2022-11-13T14:44:03.732369+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
或者用cp命令
cp -r /data/backup/base/* /var/lib/mysql
#还原数据库目录属性
[root@rocky8 ~]#ll /var/lib/mysql
total 183332
-rw-r----- 1 root root 157 Nov 13 14:44 binlog.000006
-rw-r----- 1 root root 14 Nov 13 14:44 binlog.index
drwxr-x--- 2 root root 132 Nov 13 14:44 hellodb
-rw-r----- 1 root root 6121 Nov 13 14:44 ib_buffer_pool
-rw-r----- 1 root root 12582912 Nov 13 14:44 ibdata1
-rw-r----- 1 root root 50331648 Nov 13 14:44 ib_logfile0
-rw-r----- 1 root root 50331648 Nov 13 14:44 ib_logfile1
-rw-r----- 1 root root 12582912 Nov 13 14:44 ibtmp1
drwxr-x--- 2 root root 143 Nov 13 14:44 mysql
-rw-r----- 1 root root 28311552 Nov 13 14:44 mysql.ibd
drwxr-x--- 2 root root 8192 Nov 13 14:44 performance_schema
drwxr-x--- 2 root root 28 Nov 13 14:44 sys
-rw-r----- 1 root root 16777216 Nov 13 14:44 undo_001
-rw-r----- 1 root root 16777216 Nov 13 14:44 undo_002
-rw-r----- 1 root root 510 Nov 13 14:44 xtrabackup_info
-rw-r----- 1 root root 1 Nov 13 14:44 xtrabackup_master_key_id
[root@rocky8 ~]#chown -R mysql.mysql /var/lib/mysql
[root@rocky8 ~]#ll /var/lib/mysql
total 183332
-rw-r----- 1 mysql mysql 157 Nov 13 14:44 binlog.000006
-rw-r----- 1 mysql mysql 14 Nov 13 14:44 binlog.index
drwxr-x--- 2 mysql mysql 132 Nov 13 14:44 hellodb
-rw-r----- 1 mysql mysql 6121 Nov 13 14:44 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Nov 13 14:44 ibdata1
-rw-r----- 1 mysql mysql 50331648 Nov 13 14:44 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Nov 13 14:44 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Nov 13 14:44 ibtmp1
drwxr-x--- 2 mysql mysql 143 Nov 13 14:44 mysql
-rw-r----- 1 mysql mysql 28311552 Nov 13 14:44 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Nov 13 14:44 performance_schema
drwxr-x--- 2 mysql mysql 28 Nov 13 14:44 sys
-rw-r----- 1 mysql mysql 16777216 Nov 13 14:44 undo_001
-rw-r----- 1 mysql mysql 16777216 Nov 13 14:44 undo_002
-rw-r----- 1 mysql mysql 510 Nov 13 14:44 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Nov 13 14:44 xtrabackup_master_key_id
#启动数据库服务
[root@rocky8 ~]#systemctl start mysqld.service
#验证数据库是否已恢复
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Liu Dehua | 55 | M |
| 6 | Liu Huang | 60 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)