阿里雲RDS物理備份恢復至本地伺服器8.0
# 全新的linux環境
[root@mysql ~]# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)
[root@mysql ~]# vim /etc/selinux/config
enforing -> disabled
[root@mysql ~]# init 6
[root@mysql ~]# free -m
total used free shared buff/cache available
Mem: 3789 144 2312 8 1332 3415
Swap: 4095 0 4095
[root@mysql ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 1.9G 0 1.9G 0% /dev/shm
tmpfs 1.9G 8.6M 1.9G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/sda3 196G 3.2G 193G 2% /
/dev/sda1 197M 119M 78M 61% /boot
tmpfs 379M 0 379M 0% /run/user/0
# 所需檔案
[root@mysql ~]# ll
total 12757612
-rw-------. 1 root root 1448 Dec 4 09:52 anaconda-ks.cfg
-rw-r--r-- 1 root root 12546663547 Dec 4 10:04 hins14325543_data_20201201022336_qp.xb
-rw-r--r-- 1 root root 503854832 Dec 4 09:58 mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
-rw-r--r-- 1 root root 13263384 Dec 4 09:58 percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
# 安裝percona-xtrabackup
[root@mysql ~]# yum install -y
[root@mysql ~]# yum localinstall percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
[root@mysql ~]# yum install qpress -y
# 安裝mysql
[root@mysql ~]# yum install libaio libaio-devel -y
[root@mysql ~]# groupadd mysql
[root@mysql ~]# useradd mysql -s /sbin/nologin -M -g mysql
[root@mysql ~]# mkdir -p /usr/local/mysql/data
[root@mysql ~]# mv mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz /usr/local/mysql/
[root@mysql ~]# cd /usr/local/mysql/
[root@mysql mysql]# ls
data mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@mysql mysql]# tar xf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@mysql mysql]# mv mysql-8.0.18-linux-glibc2.12-x86_64/* ./
[root@mysql mysql]# rm -rf mysql-8.0.18-linux-glibc2.12-x86_64 mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@mysql mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql mysql]# chmod +x /etc/init.d/mysqld
[root@mysql mysql]# cd /usr/local
[root@mysql local]# chown -R mysql.mysql /usr/local/mysql
# /usr/local/mysql下的檔案和許可權
[root@mysql mysql]# ll
total 524
drwxr-xr-x 2 mysql mysql 4096 Sep 20 2019 bin
drwxr-xr-x 2 mysql mysql 6 Dec 4 10:08 data
drwxr-xr-x 2 mysql mysql 86 Sep 20 2019 docs
drwxr-xr-x 3 mysql mysql 282 Sep 20 2019 include
drwxr-xr-x 7 mysql mysql 4096 Sep 20 2019 lib
-rw-r--r-- 1 mysql mysql 408918 Sep 20 2019 LICENSE
-rw-r--r-- 1 mysql mysql 102977 Sep 20 2019 LICENSE.router
drwxr-xr-x 4 mysql mysql 30 Sep 20 2019 man
-rw-r--r-- 1 mysql mysql 687 Sep 20 2019 README
-rw-r--r-- 1 mysql mysql 700 Sep 20 2019 README.router
drwxrwxr-x 2 mysql mysql 6 Sep 20 2019 run
drwxr-xr-x 28 mysql mysql 4096 Sep 20 2019 share
drwxr-xr-x 2 mysql mysql 77 Sep 20 2019 support-files
drwxr-xr-x 3 mysql mysql 17 Sep 20 2019 var
# mysql環境變數
[root@mysql mysql]# echo 'export PATH="$PATH:/usr/local/mysql/bin"' >>/etc/profile
[root@mysql mysql]# source /etc/profile
# mysql初始化
[root@mysql mysql]# cd /usr/local/mysql/bin
[root@mysql bin]# ./mysqld --initialize-insecure --lower-case-table-names=1 --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data
2020-12-04T02:12:46.974474Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-12-04T02:12:46.974576Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) initializing of server in progress as process 1817
2020-12-04T02:12:50.445746Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
# 初始化後/usr/local/mysql/data下的檔案和許可權
[root@mysql ~]# ll /usr/local/mysql/data/
total 154680
-rw-r----- 1 mysql mysql 56 Dec 4 10:21 auto.cnf
-rw------- 1 mysql mysql 1676 Dec 4 10:21 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Dec 4 10:21 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Dec 4 10:21 client-cert.pem
-rw------- 1 mysql mysql 1676 Dec 4 10:21 client-key.pem
-rw-r----- 1 mysql mysql 5349 Dec 4 10:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Dec 4 10:21 ibdata1
-rw-r----- 1 mysql mysql 50331648 Dec 4 10:21 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Dec 4 10:21 ib_logfile1
drwxr-x--- 2 mysql mysql 6 Dec 4 10:21 #innodb_temp
drwxr-x--- 2 mysql mysql 143 Dec 4 10:21 mysql
-rw-r----- 1 mysql mysql 24117248 Dec 4 10:21 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Dec 4 10:21 performance_schema
-rw------- 1 mysql mysql 1680 Dec 4 10:21 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Dec 4 10:21 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Dec 4 10:21 server-cert.pem
-rw------- 1 mysql mysql 1680 Dec 4 10:21 server-key.pem
drwxr-x--- 2 mysql mysql 28 Dec 4 10:21 sys
-rw-r----- 1 mysql mysql 10485760 Dec 4 10:21 undo_001
-rw-r----- 1 mysql mysql 10485760 Dec 4 10:21 undo_002
# 修改核心引數
[root@mysql ~]# vim /etc/security/limits.conf
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
#備份檔案還原
[root@mysql ~]# ll
total 12265564
-rw-------. 1 root root 1448 Dec 4 09:52 anaconda-ks.cfg
-rw-r--r-- 1 root root 12546663547 Dec 4 10:04 hins14325543_data_20201201022336_qp.xb
-rw-r--r-- 1 root root 13263384 Dec 4 09:58 percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
[root@mysql ~]# mkdir /data
[root@mysql ~]# chown -R mysql.mysql /data
[root@mysql ~]# cat hins14325543_data_20201201022336_qp.xb | xbstream -x -v -C /data
# 無錯誤，解壓後如下
[root@mysql data]# ll /data
total 317104
-rw-r----- 1 root root 487 Dec 4 10:24 backup-my.cnf.qp
....
-rw-r----- 1 root root 4168335 Dec 4 10:24 ibdata1.qp
-rw-r----- 1 root root 125 Dec 4 10:24 master-log-bin.index.qp
drwxr-x--- 2 root root 215 Dec 4 10:24 mysql
-rw-r----- 1 root root 253 Dec 4 10:24 mysql-bin.000747.qp
-rw-r----- 1 root root 9811874 Dec 4 10:24 mysql.ibd.qp
drwxr-x--- 2 root root 8192 Dec 4 10:24 performance_schema
drwxr-x--- 2 root root 23 Dec 4 10:24 __recycle_bin__
drwxr-x--- 2 root root 31 Dec 4 10:24 sys
-rw-r----- 1 root root 152703113 Dec 4 10:24 undo_001.qp
-rw-r----- 1 root root 93911762 Dec 4 10:24 undo_002.qp
-rw-r----- 1 root root 195 Dec 4 10:24 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 103 Dec 4 10:24 xtrabackup_checkpoints
-rw-r----- 1 root root 823 Dec 4 10:24 xtrabackup_info.qp
-rw-r----- 1 root root 63904991 Dec 4 10:24 xtrabackup_logfile.qp
-rw-r----- 1 root root 158 Dec 4 10:24 xtrabackup_slave_filename_info.qp
-rw-r----- 1 root root 240 Dec 4 10:24 xtrabackup_slave_info.qp
-rw-r----- 1 root root 225 Dec 4 10:24 xtrabackup_tablespaces.qp
# 解壓
[root@mysql data]# xtrabackup --decompress --remove-original --target-dir=/data
# 整個解壓過程無錯誤，最後顯示completed OK! /data目錄檔案如下
[root@mysql data]# ll
total 1203964
-rw-r--r-- 1 root root 524 Dec 4 10:35 backup-my.cnf
....
-rw-r--r-- 1 root root 209715200 Dec 4 10:35 ibdata1
-rw-r--r-- 1 root root 44 Dec 4 10:35 master-log-bin.index
drwxr-x--- 2 root root 191 Dec 4 10:35 mysql
-rw-r--r-- 1 root root 235 Dec 4 10:35 mysql-bin.000747
-rw-r--r-- 1 root root 54525952 Dec 4 10:35 mysql.ibd
drwxr-x--- 2 root root 8192 Dec 4 10:35 performance_schema
drwxr-x--- 2 root root 20 Dec 4 10:35 __recycle_bin__
drwxr-x--- 2 root root 28 Dec 4 10:35 sys
-rw-r--r-- 1 root root 452984832 Dec 4 10:35 undo_001
-rw-r--r-- 1 root root 310378496 Dec 4 10:35 undo_002
-rw-r--r-- 1 root root 112 Dec 4 10:35 xtrabackup_binlog_info
-rw-r----- 1 root root 103 Dec 4 10:24 xtrabackup_checkpoints
-rw-r--r-- 1 root root 896 Dec 4 10:35 xtrabackup_info
-rw-r--r-- 1 root root 205048320 Dec 4 10:35 xtrabackup_logfile
-rw-r--r-- 1 root root 74 Dec 4 10:35 xtrabackup_slave_filename_info
-rw-r--r-- 1 root root 158 Dec 4 10:35 xtrabackup_slave_info
-rw-r--r-- 1 root root 244 Dec 4 10:35 xtrabackup_tablespaces
# 恢復解壓好的備份檔案(包括日誌)
[root@mysql data]# xtrabackup --prepare --target-dir=/data
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:200M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=1572864000 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=2352233637 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/data
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
xtrabackup: cd to /data/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
...
201204 10:37:25 completed OK!
# 刪除datadir（/usr/local/mysql/data）目錄下檔案，進行copy-back
[root@mysql data]# xtrabackup --datadir=/usr/local/mysql/data --copy-back --target-dir=/data
恢復完成後無錯誤，最後顯示 201204 10:51:04 completed OK!，完整日誌可見附件1
# 目錄授權
[root@mysql data]# chown -R mysql.mysql /usr/local/mysql/
# 編輯my.cnf
[root@mysql data]# cat /etc/my.cnf
[mysql]
prompt = [\\u@\\p][\\d]>\\_
no-auto-rehash
[mysqld]
user = mysql
server-id = 1
lower_case_table_names = 1
datadir=/usr/local/mysql/data
skip-grant-tables = 1
# 啟動mysql
[root@mysql data]# service mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/mysql.err'.
.... SUCCESS!
# 錯誤日誌檔案內容
[root@mysql data]# cat /usr/local/mysql/data/mysql.err
2020-12-04T02:57:17.719522Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) starting as process 25297
2020-12-04T02:57:20.654298Z 0 [ERROR] [MY-011947] [InnoDB] Cannot open '/usr/local/mysql/data/ib_buffer_pool' for reading: No such file or directory
2020-12-04T02:57:20.798503Z 0 [Warning] [MY-010075] [Server] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6d503e5a-35dc-11eb-b6e3-0800275671b7.
2020-12-04T02:57:21.093832Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-04T02:57:21.302542Z 0 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysql-relay-bin' to avoid this problem.
2020-12-04T02:57:21.315993Z 0 [ERROR] [MY-010544] [Repl] Failed to open the relay log '/home/mysql/data3415/mysql/slave-relay.001975' (relay_log_pos 93340).
2020-12-04T02:57:21.316220Z 0 [ERROR] [MY-011059] [Repl] Could not find target log file mentioned in relay log info in the index file './mysql-relay-bin.index' during relay log initialization.
2020-12-04T02:57:21.318687Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2020-12-04T02:57:21.318888Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2020-12-04T02:57:21.319080Z 0 [Warning] [MY-010530] [Repl] Detected misconfiguration: replication channel '' was configured with AUTO_POSITION = 1, but the server was started with --gtid-mode=off. Either reconfigure replication using CHANGE MASTER TO MASTER_AUTO_POSITION = 0 FOR CHANNEL '', or change GTID_MODE to some value other than OFF, before starting the slave receiver thread.
2020-12-04T02:57:21.320060Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 0 MySQL Community Server - GPL.
2020-12-04T02:57:21.516347Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock'
# 進入mysql
[root@mysql data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@mysql.sock][(none)]> use mysql;
[root@mysql.sock][(none)]>
flush privileges;
[root@mysql.sock][(none)]>
alter user 'aliyun_root'@'127.0.0.1' identified by '123456';
[root@mysql.sock][(none)]>
flush privileges;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29812844/viewspace-2740220/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- Mysql備份與恢復(1)---物理備份MySql
- MySQL 非常規恢復與物理備份恢復MySql
- 詳解叢集級備份恢復：物理細粒度備份恢復
- 【物理熱備】（下）備份恢復系統表空間 手工備份恢復
- Oracle 聯機備份 離線備份 物理備份 恢復Oracle
- OceanBase物理備份恢復實踐
- 物理冷備份與恢復的操作命令
- 從nub備份恢復（同平臺）恢復RAC至單例項單例
- 【備份恢復】從備份恢復資料庫資料庫
- 【管理篇備份恢復】備份恢復基礎
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- Python自定義阿里雲RDS備份策略Python阿里
- 備份與恢復--利用備份的控制檔案恢復
- Mysql備份恢復MySql
- Postgresql 備份恢復SQL
- redis備份恢復Redis
- mysql 備份恢復MySql
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- 備份與恢復系列 十一 控制檔案的備份與恢復
- windwos server 路由備份和恢復 路由表備份和恢復Server路由
- 【備份恢復】資料恢復指導資料恢復
- RMAN備份與恢復之加密備份加密
- Grafana 備份恢復教程Grafana
- redis 備份和恢復Redis
- Postgresql 備份與恢復SQL
- Nifi flow 備份恢復Nifi
- 備份和恢復redisRedis
- MySQL備份與恢復MySql
- Oracle 備份恢復概念Oracle
- Xtrabackup備份恢復原理
- Mysql備份和恢復MySql
- oracle備份恢復PPTOracle
- mysqlbinlog 恢復、備份MySql
- RMAN備份恢復原理
- Oracle 備份和恢復Oracle