1、软件下载和安装
//配置网络源
[root@wjm ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-8.repo
//下载二进制格式的mysql软件包
[root@wjm ~]# cd /usr/src/
[root@wjm src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz /usr/src
……
mysql-5.7.34-linux-gli 100%[===========================>] 634.56M 14.3MB/s 用时 61s
2021-08-28 19:55:22 (10.5 MB/s) - 已保存 “mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz” [665389778/665389778])
//配置用户并压缩二进制程序
[root@wjm src]# cd /usr/local/
[root@wjm local]# ls
bin etc games include lib lib64 libexec sbin share src
//解压软件至/usr/local/
[root@wjm src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@wjm src]# cd /usr/local/
[root@wjm local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share
//创建软连接
[root@wjm local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@wjm local]# ll mysql
lrwxrwxrwx. 1 root root 47 8月 28 17:02 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
//创建用户
[root@wjm local]# useradd -r -M -s /sbin/nologin mysql
[root@wjm local]# chown -R mysql.mysql /usr/local/mysql*
[root@wjm local]# ll /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 47 8月 28 19:56 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
//添加环境变量
[root@wjm ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@wjm ~]# source /etc/profile.d/mysql.sh
[root@wjm ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
//初始化各实例
//初始化3306实例
[root@wjm ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2021-08-28T09:11:30.359416Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:11:30.517732Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:11:30.542228Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:11:30.546668Z 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: c44976d0-0716-11ec-b400-000c292a28a3.
2021-08-28T09:11:30.547669Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:11:31.627301Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:11:31.753843Z 1 [Note] A temporary password is generated for root@localhost: L8vg_klv9A%/
[root@wjm ~]# echo 'L8vg_klv9A%/' > 3306_pass
//初始化3307实例
[root@wjm ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2021-08-28T09:12:31.985800Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:12:32.141828Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:12:32.177101Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:12:32.235015Z 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: e90e5c02-0716-11ec-92d9-000c292a28a3.
2021-08-28T09:12:32.236748Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:12:32.604836Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:12:32.831085Z 1 [Note] A temporary password is generated for root@localhost: l_OqkMUC2aur
[root@wjm ~]# echo 'l_OqkMUC2aur' > 3307_pass
//初始化3308实例
[root@wjm ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2021-08-28T09:13:00.709454Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:13:00.861253Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:13:00.888880Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:13:00.893827Z 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: fa2358c8-0716-11ec-be60-000c292a28a3.
2021-08-28T09:13:00.894394Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:13:01.490271Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:13:01.704002Z 1 [Note] A temporary password is generated for root@localhost: H1WZks(R9oiC
[root@wjm ~]# echo 'H1WZks(R9oiC' > 3308_pass
[root@wjm ~]# ls 33*
3306_pass 3307_pass 3308_pass
//安装Perl,ncuress-compat-libs
[root@wjm ~]# yum -y install perl
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.
……
rust-srpm-macros-5-2.el8.noarch
systemtap-sdt-devel-4.4-11.el8_4.x86_64
完毕!
//查看某一个程序的依赖包
ldd /usr/local/mysql/bin/mysql
yum whatprovides libncurses.so.5
[root@wjm ~]# yum -y install ncurses-compat-libs
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.
……
已安装:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
完毕!
//配置文件/etc/my.cnf
vim /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.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
2、配置开机自启
[root@wjm ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi.server
[root@wjm ~]# vim /etc/init.d/mysqld_multi.server
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
export PATH=/usr/local/mysql/bin:$PATH
[root@wjm ~]# 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@wjm ~]# chkconfig mysqld_multi.server on
[root@wjm ~]# 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@wjm ~]# pkill mysqld
[root@wjm ~]# 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@wjm ~]# reboot
[root@wjm ~]# 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 [::]:*
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优点
- 备份速度快,物理备份可靠
- 备份过程不会打断正在运行的事物
- 能够基于压缩等功能节约磁盘空间和流量
- 自动备份效验
- 在不增加服务器负载的情况备份数据
3.1 xtrabackup下载、安装、备份、恢复
[root@wjm ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
--2021-08-28 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-28 20:32:18 (40.8 KB/s) - 已保存 “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm” [8060704/8060704])
[root@wjm ~]# ls pe*
percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
//安装
[root@wjm ~]# 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
完毕!
//创建表
[root@wjm ~]# 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 (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 |
| wjm1 |
| 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@wjm ~]# 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-28_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@wjm ~]# cd /backup/
[root@wjm backup]# ls
2021-08-28_21-30-57
//误删数据
[root@wjm ~]# 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)
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 |
| wjm1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database wjm1;
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@wjm ~]# innobackupex --apply-log /backup/2021-08-28_21-30-57/ //检查日志
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=
……
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2755624
210827 21:37:41 completed OK!
//删除
[root@wjm ~]# rm -rf /opt/data/3306/*
[root@wjm ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back -uroot -predhat123 --host=127.0.0.1 /backup1/2021-08-28_22-22-59/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --
……
InnoDB: Shutdown completed; log sequence number 2755624
210827 21:50:10 completed OK!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| wjm1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)