# 全新的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

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;

阿里雲RDS物理備份恢復至本地伺服器8.0

相關文章