多实例部署、开机自启、xtrabackup备份与恢复

eHV4aW5n

已于 2022-04-15 05:12:37 修改

阅读量256

点赞数

文章标签： mysql vue.js html5

于 2021-08-29 21:05:48 首次发布

原文链接：https://blog.csdn.net/kuangni5808/article/details/113248195

版权

1. 多实例部署

1.1 下载二进制格式的mysql软件包

[root@192 ~]# cd /usr/src/
[root@192 src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

URL transformed to HTTPS due to an HSTS policy
--2021-08-30 06:20:07--  https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz [following]
--2021-08-30 06:20:08--  https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 72.247.97.40
Connecting to cdn.mysql.com (cdn.mysql.com)|72.247.97.40|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 666328842 (635M) [application/x-tar-gz]
Saving to: 'mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz'

mysql-5.7.35-l 100% 635.46M  1.99MB/s    in 1m 47s       

2021-08-30 06:21:56 (5.95 MB/s) - 'mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz' saved [666328842/666328842]

1.2 解压文件至/usr/local

//解压
[root@192 src]# tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@192 src]# ls /usr/local/
bin      lib      mysql-5.7.35-linux-glibc2.12-x86_64
etc      lib64    sbin
games    libexec  share
include  mysql    src


//创建软连接
[root@192 src]# cd /usr/local/
[root@192 local]# ln -s mysql-5.7.35-linux-glibc2.12-x86_64/  mysql
[root@192 local]# ll
total 0
drwxr-xr-x. 6 root root  58 Aug  9 23:38 apache
drwxr-xr-x. 6 root root  58 Aug  9 23:16 apr
drwxr-xr-x. 6 root root  58 Aug  9 23:37 apr-util
drwxr-xr-x. 2 root root   6 Aug 12  2018 bin
drwxr-xr-x. 2 root root   6 Aug 12  2018 etc
drwxr-xr-x. 2 root root   6 Aug 12  2018 games
drwxr-xr-x. 2 root root   6 Aug 12  2018 include
drwxr-xr-x. 2 root root   6 Aug 12  2018 lib
drwxr-xr-x. 2 root root   6 Aug 12  2018 lib64
drwxr-xr-x. 2 root root   6 Aug 12  2018 libexec
lrwxrwxrwx. 1 root root  36 Aug 30 07:03 mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Aug 30 06:54 mysql-5.7.35-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Aug 12  2018 sbin
drwxr-xr-x. 5 root root  49 Jul 21 14:25 share
drwxr-xr-x. 2 root root   6 Aug 12  2018 src

1.3 创建用户

[root@192 ~]# useradd -r -M -s /sbin/nologin mysql
[root@192 ~]# chown -R mysql.mysql /usr/local/mysql
[root@192 ~]# ll /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 Aug 30 07:03 /usr/local/mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/

1.4 配置环境变量

[root@192 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@192 ~]# source /etc/profile.d/mysql.sh
[root@192 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

1.5 创建各实例数据存放的目录

[root@192 ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@192 ~]# chown -R mysql.mysql /opt/data/
[root@192 ~]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Aug 30 07:13 3306
drwxr-xr-x. 2 mysql mysql 6 Aug 30 07:13 3307
drwxr-xr-x. 2 mysql mysql 6 Aug 30 07:13 3308
[root@192 ~]# tree /opt/data/
/opt/data/
|-- 3306
|-- 3307
`-- 3308

1.6 初始化各实例

//初始化3306实例
[root@192 ~]# mysqld --initialize --datadir=/opt/data/3306  --user=mysql
2021-08-29T23:16:34.791480Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T23:16:35.845331Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T23:16:36.199397Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T23:16:36.320876Z 0 [Warning] 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: 281c160f-091f-11ec-b1b7-000c29f9d650.
2021-08-29T23:16:36.323926Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T23:16:39.780735Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T23:16:39.780792Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T23:16:39.783426Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T23:16:40.823252Z 1 [Note] A temporary password is generated for root@localhost: fg3,8ZM%VMiu
[root@192 ~]# echo 'fg3,8ZM%VMiu' > 3306_pass

//初始化3307实例
[root@192 ~]# mysqld --initialize --datadir=/opt/data/3307  --user=mysql
2021-08-29T23:17:49.914285Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T23:17:50.357011Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T23:17:50.441737Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T23:17:50.500671Z 0 [Warning] 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: 54530799-091f-11ec-826e-000c29f9d650.
2021-08-29T23:17:50.502603Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T23:17:52.831888Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T23:17:52.831921Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T23:17:52.833798Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T23:17:53.092720Z 1 [Note] A temporary password is generated for root@localhost: d!-JoUoTs5uO
[root@192 ~]# echo 'd!-JoUoTs5uO' > 3307_pass

//初始化3308实例
[root@192 ~]# mysqld --initialize --datadir=/opt/data/3308  --user=mysql
2021-08-29T23:18:43.400618Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T23:18:43.828918Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T23:18:43.950293Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T23:18:44.025443Z 0 [Warning] 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: 743a3f49-091f-11ec-baa0-000c29f9d650.
2021-08-29T23:18:44.030626Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T23:18:46.374206Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T23:18:46.374278Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T23:18:46.377519Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T23:18:46.588615Z 1 [Note] A temporary password is generated for root@localhost: 0#yQ9=*f6y(F
[root@192 ~]# echo '0#yQ9=*f6y(F' > 3308_pass

1.7 安装perl、ncuress-compat-libs依赖包

[root@192 ~]# yum -y install perl
Failed to set locale, defaulting to C.UTF-8
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 0:29:34 ago on Mon Aug 30 06:54:34 2021.
Package perl-4:5.26.3-416.el8.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!


[root@192 ~]# yum -y install ncurses-compat-libs
Failed to set locale, defaulting to C.UTF-8
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 0:32:16 ago on Mon Aug 30 06:54:34 2021.
Dependencies resolved.
......
Installed:
  ncurses-compat-libs-6.1-7.20180224.el8.x86_64           

Complete!

1.8 配置文件/etc/my.cnf

[root@192 ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

1.9 启动实例

[root@192 ~]# mysqld_multi start 3306

[root@192 ~]# mysqld_multi start 3307

[root@192 ~]# mysqld_multi start 3308

[root@192 ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port Peer Address:Port 
LISTEN 0      128          0.0.0.0:22        0.0.0.0:*    
LISTEN 0      128             [::]:22           [::]:*    
LISTEN 0      80                 *:3306            *:*    
LISTEN 0      80                 *:3307            *:*    
LISTEN 0      80                 *:3308            *:*

1.10 初始化密码

[root@192 ~]# cat 3306_pass 
fg3,8ZM%VMiu
[root@192 ~]# mysql -uroot -p'fg3,8ZM%VMiu' -S /tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

mysql> set password = password('redhat123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye


[root@192 ~]# mysql -uroot -p'd!-JoUoTs5uO -S /tmp/mysql3307.sock -e 'set password = password("redhat123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.



[root@192 ~]# mysql -uroot -p'0#yQ9=*f6y(F' -S /tmp/mysql3308.sock -e 'set password = password("redhat123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.


## 1.11 验证登录
```shell
//端口号登录
[root@192 ~]# mysql -uroot -p'redhat123' -h127.0.0.1 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

mysql> 


//路径登录
[root@192 ~]# mysql -uroot -p'redhat123' -S /tmp/mysql3307.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

mysql>

1.10.1 测试

//3306上创建数据库
mysql> create database xux;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xux                |
+--------------------+
5 rows in set (0.01 sec)


//3307上查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

//3308上查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

1.11 设置开机自启

//安装目录里找到mysqld_multi.server文件,复制到/etc/init.d/
[root@192 ~]# cd /usr/local/mysql/support-files
[root@192 support-files]# ls
magic             mysql.server
mysql-log-rotate  mysqld_multi.server
[root@192 support-files]# cp mysqld_multi.server /etc/init.d/
[root@192 support-files]# cd /etc/init.d/
[root@192 init.d]# ls
README  functions  mysqld_multi.server

//mysqld_multi中添加环境变量
[root@192 init.d]# vi mysqld_multi.server 
export PATH=/usr/local/mysql/bin:$PATH

[root@192 ~]# chkconfig mysqld_multi.server on

1.11.1 测试

[root@192 ~]# pkill mysqld
[root@192 ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port Peer Address:Port 
LISTEN 0      128          0.0.0.0:22        0.0.0.0:*    
LISTEN 0      128             [::]:22           [::]:*    
[root@192 ~]# reboot

[root@192 ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port Peer Address:Port 
LISTEN 0      128          0.0.0.0:22        0.0.0.0:*    
LISTEN 0      80                 *:3307            *:*    
LISTEN 0      80                 *:3308            *:*    
LISTEN 0      128             [::]:22           [::]:*    
LISTEN 0      80                 *:3306            *:*

2. xtrabackup备份与恢复

2.1 xtrabackup 介绍

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。

Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

2.2 xtrabackup 优点

备份速度快，物理备份可靠
备份过程不会打断正在执行的事务（无需锁表）
能够基于压缩等功能节约磁盘空间和流量
自动备份校验
还原速度快
可以流传将备份传输到另外一台机器上
在不增加服务器负载的情况备份数据

2.3 xtrabackup备份原理

（1）innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；

（2）xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）

（4）xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）

（5）innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。

（6）当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；

（7）xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；

（8）innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；

（9）最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

3. xtrabackup的安装部署

3.1 Xtrabackup中主要包含的两个工具：

xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

常用选项:

选项	注释
–host	指定主机
–user	指定用户名
–password	指定密码
–port	指定端口
–databases	指定数据库
–incremental	创建增量备份
–incremental-basedir	指定主机
–incremental-dir	指定包含完全备份的目录
–apply-log	对备份进行预处理操作

3.2 xtrabackup的安装

[root@192 ~]# wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/tarball/percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt183.tar.gz
--2021-08-30 08:39:52--  https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/tarball/percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt183.tar.gz
Resolving www.percona.com (www.percona.com)... 104.22.8.28, 172.67.8.157, 104.22.9.28, ...
Connecting to www.percona.com (www.percona.com)|104.22.8.28|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/tarball/percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt183.tar.gz [following]
--2021-08-30 08:39:53--  https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/tarball/percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt183.tar.gz
Resolving downloads.percona.com (downloads.percona.com)... 162.220.4.221, 162.220.4.222, 74.121.199.231
Connecting to downloads.percona.com (downloads.percona.com)|162.220.4.221|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 112546054 (107M) [application/x-gzip]
Saving to: 'percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt183.tar.gz'

percona-xtraba 100% 107.33M  3.23MB/s    in 75s          

2021-08-30 08:41:09 (1.43 MB/s) - 'percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt183.tar.gz' saved [112546054/112546054]

[root@192 ~]# tar xf percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt183.tar.gz

3.3 实例

3.3.1 创建一个存放备份文件的目录

[root@192 ~]# mkdir beifen

3.3.2 创建表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xux                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use xux
Database changed
mysql>  create table student (id int not null primary key auto_increment,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert student (name,age) values('tom',20),('jerry',20),('wnagermazi',15);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
+----+------------+------+
3 rows in set (0.00 sec)

3.3.3 备份

[root@192 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=redhat123 --host=127.0.0.1 /beifen
xtrabackup: recognized server arguments: 
xtrabackup: recognized client arguments: 
......
[root@192 ~]# cd /beifen/
[root@192 beifen]# ls
2021-08-29_08-53-56

3.3.4 误删除数据

mysql> drop database xux;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

3.3.5 恢复

[root@192 ~]# innobackupex  --defaults-file=/etc/my.cnf --copy-back -uroot -predhat123 --host=127.0.0.1  /beifen/2021-08-29_08-53-56/
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| xux                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)