MySQL多实例部署

MySQL多实例部署

软件下载

# 下载二进制格式的mysql软件包
[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

[root@localhost ~]# ls /usr/src
debug  kernels  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

解压软件至/usr/local/目录下

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

创建用户和组

[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql

创建软连接

[root@localhost local]# ln -s mysql-5.7.34-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ll
.....
lrwxrwxrwx. 1 root root  35 Aug 27 02:34 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Aug 27 02:32 mysql-5.7.34-linux-glibc2.12-x86_64
....

# 修改属主属组
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
......
lrwxrwxrwx. 1 mysql mysql  35 Aug 27 02:34 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Aug 27 02:32 mysql-5.7.34-linux-glibc2.12-x86_64
......

配置环境变量

[root@localhost local]# echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh 
[root@localhost local]# which mysql
/usr/local/mysql/bin/mysql
[root@localhost local]# source /etc/profile.d/mysql.sh

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

[root@localhost data]# mkdir -p /opt/data/{3306,3307,3308}

[root@localhost data]# chown mysql.mysql /opt/data/{3306,3307,3308}
[root@localhost data]# ll
total 0
drwxr-xr-x. 2 mysql mysql 6 Aug 27 02:40 3306
drwxr-xr-x. 2 mysql mysql 6 Aug 27 02:40 3307
drwxr-xr-x. 2 mysql mysql 6 Aug 27 02:40 3308

[root@localhost data]# tree /opt/data/
/opt/data/
|-- 3306
|-- 3307
`-- 3308

初始化各实例

# 初始化3306
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/3306 
2021-08-27T06:58:47.728491Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-27T06:58:47.877253Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-27T06:58:47.904660Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-27T06:58:47.961891Z 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: 3a3772fb-0704-11ec-8ce4-000c29bb4cb2.
2021-08-27T06:58:47.962565Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-27T06:58:48.699927Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-27T06:58:49.005824Z 1 [Note] A temporary password is generated for root@localhost: mQu!kWyk&0*d
[root@localhost ~]# echo 'mQu!kWyk&0*d' > 6_pass
[root@localhost ~]# cat 6_pass 
mQu!kWyk&0*d

# 初始化3307
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/3307
2021-08-27T07:02:31.500111Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-27T07:02:31.636144Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-27T07:02:31.663266Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-27T07:02:31.667737Z 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: bf8e4091-0704-11ec-9a9d-000c29bb4cb2.
2021-08-27T07:02:31.668400Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-27T07:02:32.268268Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-27T07:02:32.376293Z 1 [Note] A temporary password is generated for root@localhost: Fn+SrgtCC2a0
[root@localhost ~]# echo 'Fn+SrgtCC2a0' > 7_pass
[root@localhost ~]# cat 7_pass 

# 初始化3308
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/3308
2021-08-27T07:04:46.307842Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-27T07:04:46.454345Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-27T07:04:46.479088Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-27T07:04:46.482351Z 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: 0fe95488-0705-11ec-a47a-000c29bb4cb2.
2021-08-27T07:04:46.482928Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-27T07:04:47.304986Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-27T07:04:47.517888Z 1 [Note] A temporary password is generated for root@localhost: sXDI&?0kuslF
[root@localhost ~]# echo 'sXDI&?0kuslF' > 8_pass
[root@localhost ~]# cat 8_pass 
sXDI&?0kuslF

安装perl

[root@localhost ~]# yum -y install perl

查看是否却少依赖包

[root@localhost ~]# ldd /usr/local/mysql/bin/mysql(ldd是看某一个程序文件它所依赖的包,如果没有,就不能用,就需要用yum安装,查找哪个包提供的(yum whatprovides pkgs_name))
        linux-vdso.so.1 (0x00007ffdf09ea000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fdfeb8dd000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fdfeb6d4000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fdfeb4d0000)
        libncurses.so.5 => /lib64/libncurses.so.5 (0x00007fdfeb2aa000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fdfeaf15000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fdfeab93000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fdfea97b000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fdfea5b9000)
        libtinfo.so.5 => /lib64/libtinfo.so.5 (0x00007fdfea38e000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fdfebafd000)

配置文件/etc/my.cnf (如果有这个文件就备份一份放这,没有就创建一个写入内容)

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

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

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

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

启动各实例

# 启动时如果报错
[root@localhost ~]# mysqld_multi start
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "zh_CN.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
# 执行以下操作
[root@localhost ~]# vim /root/.bashrc 
[root@localhost ~]# echo "export LC_ALL=C" >> /root/.bashrc
[root@localhost ~]# source /root/.bashrc 
# 再次启动
[root@localhost ~]# mysqld_multi start
[root@localhost ~]# 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                               *:3306                            *:*           
LISTEN        0             80                               *:3307                            *:*           
LISTEN        0             80                               *:3308                            *:*           
LISTEN        0             128                           [::]:22                           [::]:*           

修改临时密码密码

[root@localhost ~]# mysql -uroot -p'mQu!kWyk&0*d' -P3306 -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 3
Server version: 5.7.34

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('redhat');         # 修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# cat 7_pass 
Fn+SrgtCC2a0
[root@localhost ~]# mysql -uroot -p'Fn+SrgtCC2a0' -P3307 -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 3
Server version: 5.7.34

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('redhat');        # 修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# cat 8_pass 
sXDI&?0kuslF
[root@localhost ~]# mysql -uroot -p'sXDI&?0kuslF' -P3308 -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 3
Server version: 5.7.34

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('redhat');      # 修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

多实例设置服务:

停止MySQL服务用 pkill mysqld , 启动服务用 /usr/local/mysql/support-files/mysqld_multi.server start

1.设置开机自启 :把 /usr/local/mysql/bin/support.files/mysqld_multi.server 文件复制到 /etc/init.d/mysqld.server

[root@localhost init.d]# cp /usr/local/mysql/support-files/mysqld_multi.server ./mysqld.server
[root@localhost init.d]# ls
README  functions  mysqld.server

2. 在/etc/init.d/mysqld.server 文件中增加环境变量设置 export PATH=/usr/local/mysql/bin: $PATH

[root@localhost init.d]# cat mysqld.server 
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#
export PATH=/usr/local/mysql/bin/:$PATH  # 添加环境变量
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
......

3. chkconfig --add mysqld.server

[root@localhost init.d]# chkconfig --add mysqld.server 
[root@localhost init.d]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld.server   0:off   1:off   2:on    3:on    4:on    5:on    6:off

xtrabackup简单使用

简单介绍:

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

Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具,支持MySQL、Percona server和MariaDB,开源免费,是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表,而不能备份MyISAM数据表。Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。

优点

1)备份速度快,物理备份可靠

(2)备份过程不会打断正在执行的事务(无需锁表)

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

(4)自动备份校验

(5)还原速度快

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

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

下载地址
https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

安装

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

注意:备份时需启动MySQL,恢复时需关闭MySQL,清空mysql数据目录且不能重新初始化,恢复数据后应该立即进行一次完全备份

查看内容

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

mysql> select * from cool.student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangshan |   20 |
|  2 | lishi     |   21 |
|  3 | wangwu    |   22 |
|  4 | qianliu   |   25 |
|  5 | liuqiu    |   24 |
|  6 | liuqian   |   22 |
+----+-----------+------+
6 rows in set (0.01 sec)

完全备份

[root@localhost backup]# innobackupex --defaults-files=/etc/my.cnf --password=redhat --user=root -P3306 --host=127.0.0.1 /opt/backup/
xtrabackup: recognized server arguments: --datadir=/opt/data/3306 
xtrabackup: recognized client arguments: 
210827 08:29:26 innobackupex: Starting the backup operation
......
210827 08:29:28 [00]        ...done
xtrabackup: Transaction log of lsn (2755443) to (2755452) was copied.
210827 08:29:28 completed OK!

删除库

[root@localhost backup]# mysql -uroot -predhat -P3306 -h127.0.0.1 -e "drop database cool;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost backup]# mysql -uroot -predhat -P3306 -h127.0.0.1 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

删除/opt/data数据准备恢复数据

[root@localhost ~]# rm -rf /opt/data/*
[root@localhost ~]# 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                                    *:*                   
[root@localhost ~]# pkill mysqld
[root@localhost ~]# 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@localhost ~]# innobackupex --copy-back /opt/backup/2021-08-27_08-29-26/
xtrabackup: recognized server arguments: --datadir=/opt/data/3306 
xtrabackup: recognized client arguments: 
210827 08:39:32 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
......
210827 08:39:32 [01] Copying ./xtrabackup_info to /opt/data/3306/xtrabackup_info
210827 08:39:32 [01]        ...done
210827 08:39:32 completed OK!

[root@localhost ~]# ls /opt/data/
3306
[root@localhost ~]# ls /opt/data/3306
cool  ib_buffer_pool  ibdata1  mysql  performance_schema  sys  xtrabackup_info


查看数据恢复

[root@localhost ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.. SUCCESS! 
[root@localhost ~]# 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                                                 *:3306                                            *:*                   
LISTEN                0                      128                                             [::]:22                                           [::]:*                   

[root@localhost ~]# mysql -uroot -predhat -P3306 -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 2
Server version: 5.7.34 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cool               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from cool.student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | zhangshan |   20 |
|  2 | lishi     |   21 |
|  3 | wangwu    |   22 |
|  4 | qianliu   |   25 |
|  5 | liuqiu    |   24 |
|  6 | liuqian   |   22 |
+----+-----------+------+
6 rows in set (0.00 sec)
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值