|
|
## 云数据库 $RDS$
|
|
|
|
|
|
> $RDS$为$5.7$版本,有自动备份设置，每天都会有一个新的备份生成。这个备份是物理备份。
|
|
|
|
|
|
下载的文件名称类似于：
|
|
|
|
|
|
```
|
|
|
hins25487844_data_20240730052418_qp.xb
|
|
|
hins25487844_data_20240813052415_qp.xb
|
|
|
```
|
|
|
|
|
|
> **[阿里云官方参考文档](https://www.alibabacloud.com/help/zh/rds/apsaradb-rds-for-mysql/restore-the-data-of-an-apsaradb-rds-for-mysql-instance-from-a-physical-backup-file-to-a-self-managed-mysql-database)**
|
|
|
|
|
|
#### 安装$Percona$ $XtraBackup$ 和 $qpress$
|
|
|
|
|
|
在自建库所在服务器中安装`Percona XtraBackup`和`qpress`后，再执行以下解压命令。若不安装，后续执行如下命令将失败。
|
|
|
|
|
|
> **[参考文档](https://www.jianshu.com/p/3af4c7cf70f7)**
|
|
|
|
|
|
```shell
|
|
|
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
|
|
|
percona-release enable-only tools release
|
|
|
yum install lz4 zstd -y
|
|
|
yum install percona-xtrabackup-80 -y
|
|
|
yum -y install qpress
|
|
|
|
|
|
# 检查安装的版本
|
|
|
xtrabackup --version
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
#### 冷知识：为什么不使用$MysqlDump$备份数据库，而是采用了$XtraBackup$进行备份数据库?
|
|
|
|
|
|
**答**：因为逻辑备份时，会导致锁表，数据库性能下降，如果此时有大量用户使用，就带来的非常不好的体验，这时，$XtraBackup$的无感备份机制就有了用武之地。
|
|
|
|
|
|
|
|
|
|
|
|
#### 上传备份文件到主机
|
|
|
|
|
|
```
|
|
|
cd /usr/local
|
|
|
```
|
|
|
|
|
|
因为备份文件太大，无法使用$rz$ $-be$,所以选择$sftp$上传
|
|
|
|
|
|
```
|
|
|
hins25487844_data_20240813052415_qp.xb
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
#### 前提条件
|
|
|
|
|
|
要执行恢复备份，必须满足以下条件：
|
|
|
|
|
|
- 备份集要完成**准备**。
|
|
|
|
|
|
- 数据目录**$datadir$必须为空**。
|
|
|
|
|
|
- 不能恢复到正在运行的$mysqld$实例的数据目录(除非导入部分备份)，在执行恢复之前需要**关闭$MySQL$服务器**。
|
|
|
|
|
|
#### 还原
|
|
|
|
|
|
```shell
|
|
|
rm -rf /var/mysql_bkdata/ && mkdir /var/mysql_bkdata/ -p
|
|
|
|
|
|
# 解压
|
|
|
cat hins25487844_data_20240813052415_qp.xb | xbstream -x -v -C /var/mysql_bkdata/
|
|
|
|
|
|
innobackupex --decompress --remove-original /var/mysql_bkdata/
|
|
|
|
|
|
# 应用日志
|
|
|
innobackupex --defaults-file=/var/mysql_bkdata/backup-my.cnf --apply-log /var/mysql_bkdata/
|
|
|
|
|
|
# 修改数据库文件属性
|
|
|
chown -R mysql:mysql /var/mysql_bkdata
|
|
|
```
|
|
|
|
|
|
#### 编辑数据库配置文件
|
|
|
|
|
|
```shell
|
|
|
vi /etc/my.cnf
|
|
|
```
|
|
|
|
|
|
**内容如下：**
|
|
|
|
|
|
```shell
|
|
|
[mysqld]
|
|
|
user=mysql
|
|
|
basedir=/usr/local/mysql
|
|
|
#datadir=/data/mysql
|
|
|
datadir=/var/mysql_bkdata
|
|
|
server_id=1
|
|
|
port=22066
|
|
|
socket=/tmp/mysql.sock
|
|
|
|
|
|
lower_case_table_names=1
|
|
|
max_connections = 2048
|
|
|
max_connect_errors = 30
|
|
|
table_open_cache = 2048
|
|
|
open_files_limit = 16384
|
|
|
max_allowed_packet = 16M
|
|
|
read_buffer_size = 8M
|
|
|
read_rnd_buffer_size = 32M
|
|
|
sort_buffer_size = 2M
|
|
|
join_buffer_size = 2M
|
|
|
thread_cache_size = 16
|
|
|
query_cache_size = 128M
|
|
|
query_cache_limit = 4M
|
|
|
slow_query_log = 1
|
|
|
long_query_time = 2
|
|
|
innodb_file_per_table = 1
|
|
|
max_allowed_packet = 64M
|
|
|
|
|
|
innodb_data_file_path = ibdata1:10M:autoextend
|
|
|
innodb_buffer_pool_size = 2G
|
|
|
innodb_write_io_threads = 8
|
|
|
innodb_read_io_threads = 8
|
|
|
innodb_flush_log_at_trx_commit = 2
|
|
|
innodb_log_buffer_size = 16M
|
|
|
innodb_log_file_size = 170M
|
|
|
innodb_lock_wait_timeout = 60
|
|
|
|
|
|
#sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
|
|
|
|
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
|
|
|
|
|
[mysql]
|
|
|
socket=/tmp/mysql.sock
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
#### 启动$Mysql$
|
|
|
|
|
|
```shell
|
|
|
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --datadir=/var/mysql_bkdata/&
|
|
|
```
|
|
|
|
|
|
#### 进入$Mysql$
|
|
|
|
|
|
```shell
|
|
|
/etc/init.d/mysqld stop
|
|
|
|
|
|
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
|
|
|
mysql
|
|
|
```
|
|
|
|
|
|
**删除多余的触发器**
|
|
|
|
|
|
```sql
|
|
|
mysql> select trigger_schema,trigger_name from information_schema.triggers;
|
|
|
+----------------+----------------------------+
|
|
|
| trigger_schema | trigger_name |
|
|
|
+----------------+----------------------------+
|
|
|
| sys | sys_config_insert_set_user |
|
|
|
| sys | sys_config_update_set_user |
|
|
|
+----------------+----------------------------+
|
|
|
2 rows in set (0.02 sec)
|
|
|
|
|
|
有两个没有删除掉的触发器，会导致后面的修改密码失败，
|
|
|
|
|
|
找到自己数据恢复的目录，查找以“.trg”为结尾的触发器文件，这里数据恢复目录为/var/mysql_bkdata/，执行find查找口令:
|
|
|
|
|
|
find /var/mysql_bkdata/ -iname *.trg
|
|
|
|
|
|
rm -rf /var/mysql_bkdata/mysql/user.TRG
|
|
|
rm -rf /var/mysql_bkdata/mysql/proxies_priv.TRG
|
|
|
```
|
|
|
|
|
|
#### 授权与创建用户
|
|
|
|
|
|
```sql
|
|
|
退出Mysql，重新登录进来，切换到mysql.user表，重新更新密码
|
|
|
|
|
|
update mysql.user set authentication_string=PASSWORD("DsideaL147258369") where user='root' and host='localhost';
|
|
|
|
|
|
flush privileges;
|
|
|
|
|
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'DsideaL147258369' WITH GRANT OPTION;
|
|
|
|
|
|
flush privileges;
|
|
|
exit
|
|
|
|
|
|
/etc/init.d/mysqld restart
|
|
|
mysql -uroot -pDsideaL147258369
|
|
|
|
|
|
# 添加上项目中的ycharge用户
|
|
|
CREATE USER 'ycharge'@'localhost' IDENTIFIED BY 'ycharge';
|
|
|
GRANT ALL PRIVILEGES ON *.* TO 'ycharge'@'%'IDENTIFIED BY 'ycharge' WITH GRANT OPTION;
|
|
|
flush privileges;
|
|
|
|
|
|
|
|
|
# 切换三张表的引擎为MyISAM,防止出现 1105 ER_UNKNOWN_ERROR
|
|
|
use mysql;
|
|
|
|
|
|
ALTER TABLE `mysql`.`proc` ENGINE = MyISAM;
|
|
|
ALTER TABLE `mysql`.`event` ENGINE = MyISAM;
|
|
|
ALTER TABLE `mysql`.`func` ENGINE = MyISAM;
|
|
|
```
|
|
|
|
|
|
#### 移动到$SSD$硬盘上
|
|
|
|
|
|
```Shell
|
|
|
df -h
|
|
|
```
|
|
|
|
|
|
```
|
|
|
[root@RockyLinux local]# df -h
|
|
|
Filesystem Size Used Avail Use% Mounted on
|
|
|
devtmpfs 4.0M 0 4.0M 0% /dev
|
|
|
tmpfs 7.7G 112K 7.7G 1% /dev/shm
|
|
|
tmpfs 3.1G 8.8M 3.1G 1% /run
|
|
|
/dev/mapper/rl-root 491G 80G 412G 17% /
|
|
|
/dev/sda2 960M 220M 741M 23% /boot
|
|
|
/dev/sda1 599M 7.1M 592M 2% /boot/efi
|
|
|
tmpfs 1.6G 0 1.6G 0% /run/user/0
|
|
|
/dev/sdb1 98G 28G 65G 31% /usr/local/db
|
|
|
```
|
|
|
|
|
|
```shell
|
|
|
cd /usr/local/db
|
|
|
rm -rf mysql_newdata
|
|
|
cp -r /var/mysql_bkdata/ mysql_bkdata/
|
|
|
|
|
|
service mysqld stop
|
|
|
修改一下 /etc/my.cnf
|
|
|
|
|
|
data_dir=/usr/local/db/mysql_bkdata
|
|
|
|
|
|
# 修改数据库文件属性
|
|
|
chown -R mysql:mysql /usr/local/db/mysql_bkdata
|
|
|
|
|
|
service mysqld start
|
|
|
```
|
|
|
|
