[数据库] MySQL之数据库备份与升级：MySQL Percona(RPM) 5.7.24-27 升级到 5.7.31-34

1 数据库升级方式：RPM包方式升级

[亲测有效]

环境
OS: CENTOS 7
DB: MYSQL 5.7.24-27

1.1 数据库备份

备份以防止升级失败

备份数据库的2个主要方法:
1)用MySQL自带的备份工具：mysqldump
2)直接拷贝数据库文件

[情况1]备份N个实例库 (推荐方式)
(备份databasename1 / databasename2 / databasename3数据库)

#shell command#mysqldump -uroot -p -h23.56.89.76 --databases databasename1 databasename2 databasename3 > dbs.mysql.bak.202104111111.sql

[情况2]备份全库(所有实例库)

[本地主机]#shell command# mysqldump -uroot -p --all-databases > '/va/var/lib/mysql-files/mysqlr/lib/mysql-files/dbs.mysql.bak.202104111111.sql
or (SQL文件路径中含特殊字符，如英文括号时，可对SQL文件路径加英文单引号解决)
[远程主机]#shell command# mysqldump -uroot -p -h23.56.89.76 --all-databases > /var/lib/mysql-files/dbs.mysql.bak.202104111111.sql

生成的.sql文件中包含: 创建表(DROP TABLE IF EXISTS Person;CREATE TABLE Person (...)...)，插入表记录，加/解锁等SQL语句

[情况3]备份1个库的N张表
(备份CJ_TEST_DB数据库的tb_student表、tb_teacher表)

#shell command#mysqldump -uroot -p -h23.56.89.76 CJ_TEST_DB tb_student tb_teacher > tables.mysql.bak.202104111111.sql

[情况4]复制实例库A内的表及数据到另一实例库B中

mysql -uroot -p --port=3306 -h10.0.8.xx -e 'create database DATAEYE_ZYTM_BAK20210820;'
  # 创建空实例库B
mysqldump DATAEYE -uroot -p123456 -h10.0.8.xx | mysql DATAEYE_ZYTM_BAK20210820 -uroot -p123456 -h10.0.8.xx
  # 复制实例库A到实例库B

【mysqldump 参数说明】

//mysqldump 语法：
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

//常用的OPTIONS：
-uUSERNAME      //指定数据库用户名
-hHOST          //指定服务器主机，请使用ip地址
-pPASSWORD      //指定数据库用户的密码
-P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-A， --all-databases 备份所有数据库，含create database
-B , --databases db_name… 指定备份的数据库，包括 create database语句
-E, --events：备份相关的所有event scheduler
-R, --routines：备份所有存储过程和存储函数
--triggers：备份表相关的触发器，默认启用,用--skip-triggers，不备份触发器
--master-data[=#]： 此选项须启用二进制日志
    1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
    2：记录为注释的CHANGE MASTER TO语句
    此选项会自动关闭--lock-tables功能，自动打开--lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用--flush-logs和-x，--master-data或-single-transaction,此时只刷新一次
    建议：和-x，--master-data或 --single-transaction一起使用
--compact 去掉注释，适合调试，生产不使用
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,--no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关时需要使用
-f, --force 忽略SQL错误，继续执行
--hex-blob 使用十六进制符号转储二进制列（例如，“abc”变为0x616263），受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT
-q, --quick 不缓存查询，直接输出，加快备份速度

--no-data    　　　　只备份表结构，不包含数据，可以简写为 -d
--no-create-info  　只备份数据，不备份建表信息，也可以简写为-t
--routines     　　　备份存储过程及函数,可以简写为 -R
--events     　　　　备份事件，可以简写为 -E
--triggers   　　　　备份触发器
--flush-logs  　　　 备份完成后切换日志
--flush-privileges  备份完成后刷新权限
--set-gtid-purged   开启了GTID的库需要设置该参数，值可以是ON, OFF 或 AUTO
--where       　　　 指定条件，例如每张表导出1000行的记录或者 导出每张表id<=10的记录等，可以参考历史文章查看示例
--skip-add-drop-table 不生成删除表的语句

1.2 设置参数，使数据库关闭时会清空缓存，避免版本间缓存格式差异

mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

1.3 停掉数据库服务

[centos7] systemctl stop mysqld 
[centos6/7] service mysql stop

1.4 数据库升级

前置条件： 上传 MySQL Percona 安装包到服务器 /root/Percona-Server-shared-57-5.7.31-34/目录下
MySQL Percona-57版 官方下载地址： https://www.percona.com/downloads/Percona-Server-5.7/LATEST/
MySQL Oracle-57版 官方下载地址: https://dev.mysql.com/downloads/mysql/5.7.html/

(博主本次升级下载的子安装包: server / client / shared)

cd /root/Percona-Server-shared-57-5.7.31-34/

rpm -Uvh *.rpm
    或者(亲测 MYSQL Percona 5.7.31-34 升级到 MYSQL Percona 5.7.34-37): rpm -ivh --replacefiles Percona-Server-shared-compat-57-5.7.34-37.1.el7.x86_64.rpm  Percona-Server-server-57-5.7.34-37.1.el7.x86_64.rpm  Percona-Server-client-57-5.7.34-37.1.el7.x86_64.rpm Percona-Server-shared-57-5.7.34-37.1.el7.x86_64.rpm

其中：
i表示安装，
U表示升级，
v表示显示安装过程，
h表示显示进度，
--replacefiles表示文件冲突时用新文件替换之。

如果要安装的软件包中有一个文件已在安装其它包时被安装，会显示以下信息:
...
file /usr/lib64/mysql/plugin/auth.so from install of Percona-Server-server-57-5.7.34-37.1.el7.x86_64 conflicts with file from package Percona-Server-test-57-5.7.31-34.1.el6.x86_64
file /usr/lib64/mysql/plugin/debug/auth.so from install of Percona-Server-server-57-5.7.34-37.1.el7.x86_64 conflicts with file from package Percona-Server-test-57-5.7.31-34.1.el6.x86_64
...

【备注】
有些rpm包前后有依赖，故建议：

  • 先升级 libs-compat 、libs、embeded-compat、devel

  • 然后，升级 common包（可能会升级失败 用rpm -Uvh common.....*.rpm --force --nodeps 强制升级）

  • 最后，升级 client和server包

1.5 启动MySQL服务

[centos7] systemctl start mysqld 
[centos6/7] service mysql start

1.6 检查/确认: 升级情况

[方式1]

mysql_upgrade -uroot -p

[方式2]

[root@test Percona-Server-shared-57-5.7.31-34]# mysql -V
mysql  Ver 14.14 Distrib 5.7.31-34, for Linux (x86_64) using  6.0

[方式3]

[root@test Percona-Server-shared-57-5.7.34-37]# mysql -uroot -p123456 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 5.7.34-37 |
+-----------+

2 数据库升级方式：tar.gz方式升级

[未实测，待验证]

2.1 停mysql服务

service mysql stop

2.2 根据具体的安装目录，参考如下的步骤

（conf下面主要是my.cnf文件，有时默认安装的路径在/etc目录下面）

mv mysql/conf mysqlconf

mv mysql/data mysqldata

rm -rf mysql

tar -xzf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz 

mv mysql-5.7.30-linux-glibc2.12-x86_64 mysql

cp -r mysqlconf mysql/conf

cp -r mysqldata mysql/data

chown -R *：* mysql/

2.3 启动mysql服务

service mysql start

【备注】my.cnf加如下配置: 禁止GRANT创建密码为空的用户

`sql_mode=NO_AUTO_CREATE_USER`

3 延申：数据恢复与还原

CASE1 数据库备份SQL脚本解读（刷库前必读）

SQL脚本创库、创表的方式说明，以了解mysqldump中生成的数据库产生的sql脚本是如何的，这将影响到你会采用何种方式去刷库，并评估刷库的风险

结论：

  • 创库时，不会删除原先存在的库内的表及其数据。
  • 创表时，会在指定库内删除掉原先存在的表及其数据集；并重新覆盖为新的数据集

库

  • 其sql脚本中的创库语句：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `ga_jrsjy` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `ETL_TASK_SOURCE_234`;

1）不存在删库语句： DROP DATABASE ...

2）/!32312 、/*!40100是指: 注解里!后紧跟版本号表示只有当前mysql版本高于这个版本号(3.23.12、4.01.00)时才执行注解里的命令。

3）IF NOT EXISTS：只有在该数据库(Eg：ga_jrsjy)不存在时，才创建该库。即 若该库已经存在，则：不会再重新创建。

即 不会删除原先存在的库内的表及其数据。

表

  • 其sql脚本中的创表语句：
USE `ETL_TASK_SOURCE_234`;

--
-- Table structure for table `R_CLUSTER`
--

DROP TABLE IF EXISTS `R_CLUSTER`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `R_CLUSTER` (
  `ID_CLUSTER` bigint(20) NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `BASE_PORT` varchar(255) DEFAULT NULL,
  `SOCKETS_BUFFER_SIZE` varchar(255) DEFAULT NULL,
  `SOCKETS_FLUSH_INTERVAL` varchar(255) DEFAULT NULL,
  `SOCKETS_COMPRESSED` char(1) DEFAULT NULL,
  `DYNAMIC_CLUSTER` char(1) DEFAULT NULL,
  PRIMARY KEY (`ID_CLUSTER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `R_CLUSTER`
--

LOCK TABLES `R_CLUSTER` WRITE;
/*!40000 ALTER TABLE `R_CLUSTER` DISABLE KEYS */;
/*!40000 ALTER TABLE `R_CLUSTER` ENABLE KEYS */;
UNLOCK TABLES;

1）存在删表语句： DROP TABLE

2）sql脚本会覆盖式重新建表

CASE2 如何利用备份数据恢复数据库?[数据库还原]

假设误删了某个数据库后，该怎么恢复数据库?

[前置条件]已利用mysqldump等工具预先对被误删/待恢复的数据库做了备份，假定其备份文件为mysql-backup-202010211005.sql文件

(建议: 执行前，判断.sql文件中创建的表结构、插入的数据记录是否是在目标数据库内执行)

刷库方式： Shell命令行(管道流) VS 数据库内导入(source)

[方式1] < 管道流

#shell command# mysql -uroot -p -h127.0.0.1 < mysql-backup-202010211005.sql

[方式2] source 导入数据库

mysql> use databaseName;
mysql> source d:\demo.sql;

刷库时指定刷库粒度（By Shell命令）

  • 仅恢复指定的库（其它库的数据不恢复）
mysql -h主机名 --port=端口 -u用户名 -p'用户对应的数据库密码' --one-database 数据库名 < ./dump.sql
  • 1）【前置条件】--one-database操作 需要提前创建好对应的库。否则，创建恢复失败。
  • 2）即使sql脚本内存在其它数据库实例的表数据，但并不会被一同恢复进去。解决了此问题：一般备份情况是，将N个DB备份到1个sql文件中，怎么从这1份存在N个DB数据集的sql中恢复1个DB?

5 延申：其它

case2 完全备份 / 增量备份

case3 双机热备份 / MySQL主从复制集群的备份

[未实测，仅供思路层参考]
主服务器的配置
+ 找到配置文件my.ini
+ 编辑my.ini文件,定义到mysqld，加入内容
Server-id=1
Log-bin=c:/log-bin.log
Binlog-do-db=dbname
+ 创建dbname数据库
+ 进入mysql操作，为从服务器授权访问数据库的用户名和密码
grant replication slave on . to ‘root’@从服务器IP identified by ‘密码’
+ 重启MySQL服务器，使更改生效
+ 重新进入到MySQL的命令，执行命令检测配置是否生效:
Show master status\G

从服务器的配置
+ 在从服务器中创建与主服务器中相同的数据库
+ 找到从服务器中的my.ini文件，定位到[mysqld],加入以下内容：
Server-id=2
Master-host=主服务器IP
Master-port=3306(主服务器端口号)
Master-user=root(主服务器用户名)
Master-password=密码(主服务器密码)
Master-connect-retry=60(等待尝试重新连接的秒数)
+ 重新启动MySQL服务器，使配置生效
+ 进入到从服务器MySQL操作,执行启动进程
Slave start

部署在两台服务器中的共享存储双机热备软件，负责管理两台服务器的应用程序启停、存储访问并进行故障检测，双机热备集群正常工作时由主服务器对外服务并访问存储设备。
如果主服务器故障，ServHA Cluster会将业务自动切换至备用服务器运行并将存储设备访问权交由备用服务器，实现业务应用的7X24小时不间断运行。
集群拓扑如下图：

#1 共享存储单活双机热备方案

本方案摘自: 双机热备 - ServHA Cluster 软件简介 - 微彩华创

此方案中两台服务器一主一备，业务应用由主服务器运行。
如果主服务器故障，ServHACluster自动将业务应用切换至备用服务器。

此方案优点是:结构简单明晰易维护，适合只有一个业务核心的系统，方案拓扑与故障处理如上图：

#2 共享存储双活双机热备方案

本方案摘自: 双机热备 - ServHA Cluster 软件简介 - 微彩华创

此方案中两台服务器各自运行【不同】的应用，两台服务器互为备份。
如果其中一台服务器故障，ServHA会将故障服务器的应用自动切换至另一台服务器运行，此时健康的服务器同时运行多个应用。

此方案优点是:资源利用率高，服务器不呈闲置状态，适合多个有业务核心的系统，方案拓扑与故障处理如上图：

#3 容灾集群方案

本方案摘自: 镜像群集方案系列介绍 - EterneData

一般群集系统都用于本地服务的高可用性，但对于一些非常关心数据和服务可靠性的客户，其担心本地因为火灾，地震等不可意料的情况造成本地数据和业务都不可恢复情况下，仍然能够提供业务的可用性。
因而希望建立异地一个备份群集系统，当本地群集出现灾难时，异地的备份群集能够替代本地群集的工作，达到容灾效果。
这种需求在发生类似911事件、海啸事件后，很多企业都把这个方案提上日程，避免出现严重灾难时数据和服务出现崩溃的情况。
EterneMirrorCluster软件本身支持这种异地容灾的群集方案，而不需要额外购买其他软件进行配合，其可以在本地建立一个共享磁盘的群集，异地再建立另外一个共享磁盘的群集。
这两个群集的共享磁盘进行镜像保证数据实时一致，当本地群集出现问题时，异地的群集会立即接管企业的核心业务。

具体拓扑图如下：

这个环境中的每台主机都必须安装EterneMirrorCluster软件，共享磁盘之间的数据通过EterneMirrorCluster的镜像功能保证数据实时一致。
当本地群集没有出现问题时，即使其内部某台服务器出现故障，核心业务仍然在本地群集内切换，一旦本地群集出现问题时核心业务才切换到异地。
这里本地群集可以是在同一个房间，也可以在不同房间；
而异地群集（也可以是单独异地备份服务器）可以是隔壁房间，也可以是相隔很远的空间，条件是共享磁盘之间的网络带宽要足够，不要因为网络瓶颈造成本地系统性能的降低或数据没有实时同步。

X 参考与推荐文献

chapter-3 内容参考文献如下:

posted @ 2020-10-21 00:56  千千寰宇  阅读(830)  评论(0)  编辑  收藏  举报