MYSQL 多实例部署和xtrabackup(1)

[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.pid

log-error=/var/log/mysql_3306.log

[mysqld3307]

datadir = /opt/data/3307

port = 3307

socket = /tmp/mysql3307.sock

pid-file = /opt/data/3307/mysql.pid

log-error=/var/log/mysql_3307.log

[mysqld3308]

datadir = /opt/data/3308

port = 3308

socket = /tmp/mysql3308.sock

pid-file = /opt/data/3308/mysql.pid

log-error=/var/log/mysql_3308.log

启动各实例

====================================================================

[root@hhr ~]# mysqld_multi start

[root@hhr ~]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 0.0.0.0:111 0.0.0.0:*

LISTEN 0 32 192.168.122.1:53 0.0.0.0:*

LISTEN 0 128 0.0.0.0:22 0.0.0.0:*

LISTEN 0 5 127.0.0.1:631 0.0.0.0:*

LISTEN 0 80 *:3306 :

LISTEN 0 80 *:3307 :

LISTEN 0 80 *:3308 :

LISTEN 0 128 [::]:111 [::]😗

LISTEN 0 128 [::]:22 [::]😗

LISTEN 0 5 [::1]:631 [::]😗

[root@hhr ~]# ps -ef | grep mysqld //查看mysql进程号

[root@hhr ~]# kill -9 //强制杀

初始化密码

====================================================================

[root@hhr ~]# cat 3306_pass

L8vg_klv9A%/

[root@hhr ~]# mysql -uroot -p’L8vg_klv9A%/’ -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.34

Copyright © 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@hhr ~]# cat 3307_pass

l_OqkMUC2aur

[root@hhr ~]# mysql -uroot -p’l_OqkMUC2aur’ -h127.0.0.1 -P3307

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.34

Copyright © 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@hhr ~]# cat 3308_pass

H1WZks(R9oiC

[root@hhr ~]# mysql -uroot -p’mysql -uroot -p’ -S /tmp/mysql3308.sock -e ‘set password = password(“redhat23”);’ --connect-expired-password

mysql: [Warning] Using a password on the command line interface can be insecure.

登录

=================================================================

//默认端口登录

[root@hhr ~]# mysql -uroot -predhat123 -h127.0.0.1

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 6

Server version: 5.7.34 MySQL Community Server (GPL)

Copyright © 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> exit

Bye

//指定3307端口登录

[root@hhr ~]# mysql -uroot -predhat123 -h127.0.0.1 -P3307

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 4

Server version: 5.7.34 MySQL Community Server (GPL)

Copyright © 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> exit

Bye

//使用路径3308登录

[root@hhr ~]# mysql -uroot -predhat123 -S /tmp/mysql3308.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 5

Server version: 5.7.34 MySQL Community Server (GPL)

Copyright © 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> exit

Bye

测试

=================================================================

//在3306上创建数据库

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

±-------------------+

4 rows in set (0.00 sec)

mysql> create database hhr;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| hhr |

| mysql |

| performance_schema |

| sys |

±-------------------+

5 rows in set (0.00 sec)

//3307上查看

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

±-------------------+

4 rows in set (0.00 sec)

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

±-------------------+

4 rows in set (0.00 sec)

设置开机自启

=====================================================================

[root@hhr ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi.server

[root@hhr ~]# vim /etc/init.d/mysqld_multi.server

basedir=/usr/local/mysql

bindir=/usr/local/mysql/bin

export PATH=/usr/local/mysql/bin:$PATH

[root@hhr ~]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 0.0.0.0:111 0.0.0.0:*

LISTEN 0 32 192.168.122.1:53 0.0.0.0:*

LISTEN 0 128 0.0.0.0:22 0.0.0.0:*

LISTEN 0 5 127.0.0.1:631 0.0.0.0:*

LISTEN 0 80 *:3306 :

LISTEN 0 80 *:3307 :

LISTEN 0 80 *:3308 :

LISTEN 0 128 [::]:111 [::]😗

LISTEN 0 128 [::]:22 [::]😗

LISTEN 0 5 [::1]:631 [::]😗

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

[root@hhr ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含

原生 systemd 服务。SysV 配置数据

可能被原生 systemd 配置覆盖。

要列出 systemd 服务，请执行 ‘systemctl list-unit-files’。

查看在具体 target 启用的服务请执行

‘systemctl list-dependencies [target]’。

mysqld_multi.server 0:关 1:关 2:开 3:开 4:开 5:开 6:关

测试

=================================================================

[root@hhr ~]# pkill mysqld

[root@hhr ~]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 0.0.0.0:111 0.0.0.0:*

LISTEN 0 32 192.168.122.1:53 0.0.0.0:*

LISTEN 0 128 0.0.0.0:22 0.0.0.0:*

LISTEN 0 5 127.0.0.1:631 0.0.0.0:*

LISTEN 0 128 [::]:111 [::]😗

LISTEN 0 128 [::]:22 [::]😗

LISTEN 0 5 [::1]:631 [::]😗

[root@hhr ~]# reboot

[root@hhr ~]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 0.0.0.0:111 0.0.0.0:*

LISTEN 0 32 192.168.122.1:53 0.0.0.0:*

LISTEN 0 128 0.0.0.0:22 0.0.0.0:*

LISTEN 0 5 127.0.0.1:631 0.0.0.0:*

LISTEN 0 80 *:3306 :

LISTEN 0 80 *:3307 :

LISTEN 0 80 *:3308 :

LISTEN 0 128 [::]:111 [::]😗

LISTEN 0 128 [::]:22 [::]😗

LISTEN 0 5 [::1]:631 [::]😗

安装xtrabackup,数据备份及恢复

===================================================================================

Xtrabackup介绍

---

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

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

Xtrabackup优点

---

• 备份速度快，物理备份可靠

• 备份过程不会打断正在执行的事务（无需锁表）

• 能够基于压缩等功能节约磁盘空间和流量

• 自动备份校验

• 还原速度快

• 可以流传将备份传输到另外一台机器上

• 在不增加服务器负载的情况备份数据

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子进程结束后退出。

Xtrabackup中主要包含两个工具

---

• xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；

• innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

常用选项:

| 选项 | 含义 |

| — | :-- |

| –host | 指定主机 |

| –user | 指定用户名 |

| –password | 指定密码 |

| –port | 指定端口 |

| –databases | 指定数据库 |

| –incremental | 创建增量备份 |

| –incremental-basedir | 指定包含完全备份的目录 |

| –incremental-dir | 指定包含增量备份的目录 |

| –apply-log | 对备份进行预处理操作。

一般情况下，在备份完成后，数据尚且不能用于恢复操作，

因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。

因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及

同步已经提交的事务至数据文件也使得数据文件处于一致性状态。 |

| –edo-only | 不回滚未提交事务 |

| –copy-back | 恢复备份目录 |

xtrabackup全量备份与恢复

---

• 全库备份与恢复三步曲：

• innobackupex全量备份，并指定备份目录路径；

• 在恢复前，需要使用–apply-log参数先进行合并数据文件，确保数据的一致性要求；

• 恢复时，直接使用–copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。

实战演练

===================================================================

xtrabackup下载与安装

---

[root@hhr ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

–2021-08-27 20:29:02-- https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

正在解析主机 repo.percona.com (repo.percona.com)… 167.71.118.3, 157.245.119.64, 167.99.233.229

正在连接 repo.percona.com (repo.percona.com)|167.71.118.3|:443… 已连接。

已发出 HTTP 请求，正在等待回应… 200 OK

长度：8060704 (7.7M) [application/x-redhat-package-manager]

正在保存至: “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm”

percona-xtrabackup-24- 100%[===========================>] 7.69M 32.4KB/s 用时 3m 13s

2021-08-27 20:32:18 (40.8 KB/s) - 已保存 “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm” [8060704/8060704])

[root@hhr ~]# ls pe*

percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

安装

---

[root@hhr ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

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.

……

mariadb-connector-c-config-3.1.11-2.el8_3.noarch

percona-xtrabackup-24-2.4.23-1.el8.x86_64

perl-DBD-MySQL-4.046-3.module_el8.1.0+203+e45423dc.x86_64

完毕！

删除

=================================================================

删除配置文件中/etc/my.cnf中3307、3308

---

[root@hhr ~]# vim /etc/my.cnf

[root@hhr ~]# cat /etc/my.cnf

[3306]

datadir = /opt/data/3306

port = 3306

socket = /tmp/mysql3306.sock

pid-file = /opt/data/3306/mysql.pid

log-error=/var/log/mysql_3306.log

//配置文件

[root@hhr ~]# cd /usr/local/mysql/support-files

[root@hhr support-files]# vim mysql.server

basedir=/usr/local/mysql

datadir=/opt/data/3306

[root@hhr support-files]# ./mysql.server start

SUCCESS!

[root@hhr support-files]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 0.0.0.0:111 0.0.0.0:*

LISTEN 0 32 192.168.122.1:53 0.0.0.0:*

LISTEN 0 128 0.0.0.0:22 0.0.0.0:*

LISTEN 0 5 127.0.0.1:631 0.0.0.0:*

LISTEN 0 80 *:3306 :

LISTEN 0 128 [::]:111 [::]😗

LISTEN 0 128 [::]:22 [::]😗

LISTEN 0 5 [::1]:631 [::]😗

将密码和用户写入配置文件

---

[root@hhr ~]# vim ~/.my.cnf

[root@hhr ~]# cat ~/.my.cnf

[client]

user=root

password=redhat123

创建一个存放备份文件的目录方便等下放置备份文件

---

[root@hhr ~]# mkdir /backup

创建表

---

[root@hhr ~]# mysql -uroot -predhat123

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.34 MySQL Community Server (GPL)

Copyright © 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> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| hhr |

| mysql |

| performance_schema |

| sys |

±-------------------+

5 rows in set (0.00 sec)

mysql> create table student (id int not null primary key auto_increment,name varchar(50) not null,age tinyint);

Query OK, 0 rows affected (0.01 sec)

mysql> insert student (name,age) values(‘tom’,20),(‘jerry’,20),(‘wnagermazi’,15),(‘zhangsan’,19),(‘lisi’,25);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from student;

±—±-----------±-----+

| id | name | age |

±—±-----------±-----+

| 1 | tom | 20 |

| 2 | jerry | 20 |

| 3 | wnagermazi | 15 |

| 4 | zhangsan | 19 |

| 5 | lisi | 25 |

±—±-----------±-----+

5 rows in set (0.00 sec)

备份

=================================================================

[root@hhr ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=redhat123 --host=127.0.0.1 /backup

xtrabackup: recognized server arguments:

xtrabackup: recognized client arguments:

……

210827 21:31:01 [00] Writing /backup/2021-08-27_21-30-57/xtrabackup_info

210827 21:31:01 [00] …done

xtrabackup: Transaction log of lsn (2755296) to (2755305) was copied.

210827 21:31:01 completed OK!

[root@hhr ~]# cd /backup/

[root@hhr backup]# ls

2021-08-27_21-30-57

误删除数据

====================================================================

[root@hhr ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.34 MySQL Community Server (GPL)

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Android开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
[00] …done

xtrabackup: Transaction log of lsn (2755296) to (2755305) was copied.

210827 21:31:01 completed OK!

[root@hhr ~]# cd /backup/

[root@hhr backup]# ls

2021-08-27_21-30-57

误删除数据

====================================================================

[root@hhr ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.34 MySQL Community Server (GPL)

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

[外链图片转存中…(img-qthC3gtU-1715539560923)]

[外链图片转存中…(img-p9FoHTlJ-1715539560923)]

[外链图片转存中…(img-fPqlD1Nx-1715539560924)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Android开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！