YltProject/操作文档/7、Mysql数据库下载与还原.md

## 云数据库 $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
```