mysql多实例部署、xtrabackup
1.mysql多实例部署
软件下载
[root@localhost ~]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
--2021-08-29 20:29:33-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 104.85.245.54
正在连接 cdn.mysql.com (cdn.mysql.com)|104.85.245.54|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:643790848 (614M) [application/x-tar-gz]
正在保存至: “mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz”
mysql-5.7.22-linux-glibc2. 100%[=====================================>] 613.97M 12.8MB/s 用时 52s
2021-08-29 20:30:27 (11.9 MB/s) - 已保存 “mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz” [643790848/643790848])
[root@localhost ~]# ls
anaconda-ks.cfg mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
解压二进制包至/usr/local下、并配置用户和组
//解压二进制包
[root@localhost ~]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@localhost ~]# ls /usr/local/
bin etc games include lib lib64 libexec mysql-5.7.22-linux-glibc2.12-x86_64 sbin share src
//创建软连接
[root@localhost local]# ln -s mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 8月 12 2018 bin
drwxr-xr-x. 2 root root 6 8月 12 2018 etc
drwxr-xr-x. 2 root root 6 8月 12 2018 games
drwxr-xr-x. 2 root root 6 8月 12 2018 include
drwxr-xr-x. 2 root root 6 8月 12 2018 lib
drwxr-xr-x. 2 root root 6 8月 12 2018 lib64
drwxr-xr-x. 2 root root 6 8月 12 2018 libexec
lrwxrwxrwx. 1 root root 36 8月 29 20:44 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月 29 20:41 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 8月 12 2018 sbin
drwxr-xr-x. 5 root root 49 7月 22 00:22 share
drwxr-xr-x. 2 root root 6 8月 12 2018 src
//创建用户和组
[root@localhost local]# useradd -r -M -s /bin/nologin mysql
[root@localhost local]# grep mysql /etc/group
mysql:x:991:
//修改目录属主和属组
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 8月 12 2018 bin
drwxr-xr-x. 2 root root 6 8月 12 2018 etc
drwxr-xr-x. 2 root root 6 8月 12 2018 games
drwxr-xr-x. 2 root root 6 8月 12 2018 include
drwxr-xr-x. 2 root root 6 8月 12 2018 lib
drwxr-xr-x. 2 root root 6 8月 12 2018 lib64
drwxr-xr-x. 2 root root 6 8月 12 2018 libexec
lrwxrwxrwx. 1 mysql mysql 36 8月 29 20:44 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 8月 29 20:41 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 8月 12 2018 sbin
drwxr-xr-x. 5 root root 49 7月 22 00:22 share
drwxr-xr-x. 2 root root 6 8月 12 2018 src
//添加环境变量
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost local]# source /etc/profile.d/mysql.sh
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
创建数据存放目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# tree /opt/
/opt/
└── data
├── 3306
├── 3307
└── 3308
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/
总用量 0
drwxr-xr-x. 5 mysql mysql 42 8月 29 20:53 data
[root@localhost ~]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月 29 20:53 3306
drwxr-xr-x. 2 mysql mysql 6 8月 29 20:53 3307
drwxr-xr-x. 2 mysql mysql 6 8月 29 20:53 3308
初始化实例
两种方法:
mysql --initialize-insecure --user mysql --datadir /opt/data //初始化时系统不会设置密码
mysqld --initialize --user mysql --datadir /opt/data //初始化时系统随机设置密码
//初始化3306实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
2021-08-29T12:58:48.857756Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:58:49.041232Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:58:49.069186Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:58:49.126014Z 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: da570461-08c8-11ec-9030-000c2994948d.
2021-08-29T12:58:49.126926Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T12:58:49.127493Z 1 [Note] A temporary password is generated for root@localhost: CBp1h:ArohiY
[root@localhost ~]# echo 'CBp1h:ArohiY' > 3306-pass
//初始化3307实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2021-08-29T12:59:09.986179Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:59:10.150270Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:59:10.176344Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:59:10.183780Z 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: e6e42ce1-08c8-11ec-ad13-000c2994948d.
2021-08-29T12:59:10.184773Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T12:59:10.188120Z 1 [Note] A temporary password is generated for root@localhost: bM8TNzcsUp!3
[root@localhost ~]# echo 'bM8TNzcsUp!3' > 3307-pass
//初始化3308实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2021-08-29T12:59:14.919108Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:59:15.064331Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:59:15.090898Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:59:15.146845Z 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: e9d97b71-08c8-11ec-b249-000c2994948d.
2021-08-29T12:59:15.149709Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T12:59:15.150239Z 1 [Note] A temporary password is generated for root@localhost: 6Ne)AhrwZkV+
[root@localhost ~]# echo '6Ne)AhrwZkV+' > 3308-pass
[root@localhost ~]# ls
3306-pass 3307-pass 3308-pass anaconda-ks.cfg
安装perl
[root@localhost ~]# yum -y install perl
安装mysql命令
[root@localhost ~]# ldd /usr/local/mysql/bin/mysql //查看程序文件依赖包的安装情况,显示not found 则需要安装
linux-vdso.so.1 (0x00007ffc98789000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f6ec9607000)
librt.so.1 => /lib64/librt.so.1 (0x00007f6ec93fe000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f6ec91fa000)
libncurses.so.5 => not found
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f6ec8e65000)
libm.so.6 => /lib64/libm.so.6 (0x00007f6ec8ae3000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f6ec88cb000)
libc.so.6 => /lib64/libc.so.6 (0x00007f6ec8509000)
/lib64/ld-linux-x86-64.so.2 (0x00007f6ec9827000)
libtinfo.so.5 => not found
[root@localhost ~]# yum whatprovides libncurses.so.5 //查找由那个包提供
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.
上次元数据过期检查:0:35:33 前,执行于 2021年08月29日 星期日 20时36分44秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库 :base
匹配来源:
提供 : libncurses.so.5
[root@localhost ~]# yum whatprovides libtinfo.so.5
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.
上次元数据过期检查:0:32:01 前,执行于 2021年08月29日 星期日 20时36分44秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库 :base
匹配来源:
提供 : libtinfo.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs
配置配置文件/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
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
启动实例
[root@localhost ~]# mysqld_multi start
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 672.
[root@localhost ~]# ss -anltu
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
tcp LISTEN 0 128 [::]:22 [::]:*
tcp LISTEN 0 80 *:3306 *:*
tcp LISTEN 0 80 *:3307 *:*
tcp LISTEN 0 80 *:3308 *:*
//只能使用mysqld_multi 命令开启,停止需要用kill命令杀进程
[root@localhost ~]# ps -ef | grep mysqld
root 138500 1 0 21:21 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3306 --port=3306 --socket=/tmp/mysql3306.sock --pid-file=/opt/data/3306/mysql_3306.pid --log-error=/var/log/3306.log
root 138506 1 0 21:21 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3307 --port=3307 --socket=/tmp/mysql3307.sock --pid-file=/opt/data/3307/mysql_3307.pid --log-error=/var/log/3307.log
root 138515 1 0 21:21 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3308 --port=3308 --socket=/tmp/mysql3308.sock --pid-file=/opt/data/3308/mysql_3308.pid --log-error=/var/log/3308.log
mysql 138895 138506 0 21:21 ? 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data/3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/3307.log --pid-file=/opt/data/3307/mysql_3307.pid --socket=/tmp/mysql3307.sock --port=3307
mysql 138912 138515 0 21:21 ? 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data/3308 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/3308.log --pid-file=/opt/data/3308/mysql_3308.pid --socket=/tmp/mysql3308.sock --port=3308
mysql 138914 138500 0 21:21 ? 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data/3306 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/3306.log --pid-file=/opt/data/3306/mysql_3306.pid --socket=/tmp/mysql3306.sock --port=3306
root 144200 143923 0 21:24 pts/0 00:00:00 grep --color=auto mysqld
[root@localhost ~]# kill -9 138500 138506 138515 138895 138912 138914
[root@localhost ~]# ss -anltu
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
tcp LISTEN 0 128 [::]:22 [::]:*
初始化密码
[root@localhost ~]# cat 3306-pass
CBp1h:ArohiY
[root@localhost ~]# mysql -uroot -p'CBp1h:ArohiY' -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 2
Server version: 5.7.22
Copyright (c) 2000, 2018, 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.
mysql> set password = password('akl'); //设置密码
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 3307-pass
bM8TNzcsUp!3
[root@localhost ~]# mysql -uroot -p'bM8TNzcsUp!3' -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.22
Copyright (c) 2000, 2018, 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.
mysql> set password = password('akl');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 3308-pass
6Ne)AhrwZkV+
[root@localhost ~]# mysql -uroot -p'6Ne)AhrwZkV+' -h127.0.0.1 -P3308
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.22
Copyright (c) 2000, 2018, 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.
mysql> set password = password('akl');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
2.配置开机自启
- 1、把/usr/local/mysql/bin/support_files/mysqld_multi.service文件复制到/etc/init.d/mysqld_multi
- 2、在/etc/init.d/mysqld_multi文件中增加环境变量设置export PATH=/usr/local/mysql/bin:$PATH
- 3、chkconfig --add mysqld_multi
[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
[root@localhost ~]# vim /etc/init.d/mysqld_multi //增加环境变量,添加export这一行
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost ~]# chkconfig --add mysqld_multi
[root@localhost ~]# chkconfig --list
注:该输出结果只显示 SysV 服务,并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。
要列出 systemd 服务,请执行 'systemctl list-unit-files'。
查看在具体 target 启用的服务请执行
'systemctl list-dependencies [target]'。
mysqld_multi 0:关 1:关 2:开 3:开 4:开 5:开 6:关
3.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的备份、恢复
xtrabackup中主要包含两个工具:
xtrabackup:是用于热备innodb, xtradb表中数据的工具, 不能备份其他类型的表,也不能备份数据表结构;
innobackupex:是将xtrabackup进行封装的per 1脚本,提供了备份myi sam表的能力。
常用选项:
- host 指定主机
- user 指定用户名
- password 指定密码
- port 指定端口
- databases 指定数据库
- incremental 创建增量备份
- incremental -basedir 指定包含完全备份的目录
- incremental-dir 指定包含增量备份的目录
- app1y-1og 对备份进行预处理操作
一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。 - redo-only 不回滚未提交事务
- copy-back 恢复备份目录
//安装xtrabackup
[root@localhost ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
--2021-08-29 21:51:19-- 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, 167.99.233.229, 157.245.119.64
正在连接 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-2.4. 100%[=====================================>] 7.69M 43.2KB/s 用时 4m 14s
2021-08-29 21:55:37 (30.9 KB/s) - 已保存 “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm” [8060704/8060704])
备份、恢复
//创建数据库和表,给表中插入数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database wys;
Query OK, 1 row affected (0.00 sec)
mysql> use wys;
Database changed
mysql> create table student(id int not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert student(id,name,age) values(1,'jerry',20),(2,'alice',22),(3,'zhangsan',25);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | jerry | 20 |
| 2 | alice | 22 |
| 3 | zhangsan | 25 |
+----+----------+------+
3 rows in set (0.00 sec)
//备份
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=akl --host=127.0.0.1 -P3306 /backup
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
210829 22:18:28 innobackupex: Starting the backup operation
......
[root@localhost ~]# cd /backup
[root@localhost backup]# ls
2021-08-29_22-18-28
//删除数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database wys;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//恢复
[root@localhost backup]# innobackupex --apply-log /backup/2021-08-29_22-18-28/
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back -uroot -pakl --host=127.0.0.1 --datadir=/opt/data/3306 /backup/2021-08-29_22-18-28/
xtrabackup: recognized server arguments: --datadir=/opt/data/3306
xtrabackup: recognized client arguments:
......