首页 > 数据库 >MySql企业版Percona-Server部署方案

MySql企业版Percona-Server部署方案

时间:2022-11-10 18:36:41浏览次数:74  
标签:16 text marks value Server Percona MySql type id

一.MySql数据库版本说明 官方文档:https://www.percona.com/downloads/Percona-Server-LATEST/# 自从甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险。漏洞修补和版本升级的速度一段时间非常缓慢,所以业界对MySQL的未来普遍不抱有乐观态度。因此社区采用分支的方式来避开这个风险,例如谷歌、Facebook、RedHat都将MySQL替换成了衍生版,国内也有企业纷纷放弃了MySQL官方版,像阿里和腾讯还做起了自己的MySQL衍生版。   目前MySQL领域存在着众多的衍生版数据库,主流的有官方MySQL、MariaDB以及Percona Server,小众一些的还有阿里的OceanBase和腾讯的CDB等等。那么Percona和MariaDB这两个主要的MySQL分支是怎么产生的呢?   MariaDB 起初MySQL之父Monty在1979年写下MySQL的第一行代码,后来逐渐创建起MySQL公司,后将其以10亿美金卖给Sun,结果Sun又把MySQL转手卖给Oracle,Monty愤而出走,以MySQL5.5为基础创造了MariaDB数据库,这样就诞生出了MySQL分支里知名度最高的一个衍生版。   Percona Server Percona Server是MySQL咨询公司Percona发布的性能最接近MySQL企业版的MySQL产品。Percona公司在MySQL数据库优化方面做了非常多的工作,以至于Percona Server数据库是MySQL众多分支中,在高负载、高并发情况下表现非常突出,乃至阿里巴巴的OceanBase数据库都要借鉴Percona Server。   部署平台 MariaDB的跨平台性更好一些,支持Windows平台和Linux平台,但是不支持MacOS. Percona Server不支持跨平台,,只能在Linux平台安装。   二、MySql数据库企业版Percona-Serve部署步骤 环境: CentOS7,Vagrant,VirtualBox; Win10,Navicat12 Preminum/Workbench(建议8.0以下版本,8.0以上版本存在SSL配置问题)   前置操作说明: 1.安装vagrant + VirtualBox,构建虚拟环境(用于个人电脑测试,如有服务器已经安装CentOs,则不用安装此环境) 2.安装CentOS 7 3.win10环境安装Navicat12 Preminum/Workbench   安装步骤: 1.下载三个源文件: sudo wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.38-83.0/binary/redhat/7/x86_64/Percona-Server-client-56-5.6.38-rel83.0.el7.x86_64.rpm sudo wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.38-83.0/binary/redhat/7/x86_64/Percona-Server-server-56-5.6.38-rel83.0.el7.x86_64.rpm sudo wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.38-83.0/binary/redhat/7/x86_64/Percona-Server-shared-56-5.6.38-rel83.0.el7.x86_64.rpm   2.安装以上三个文件: sudo yum -y localinstall Percona-Server-shared-56-5.6.38rel83.0.el7.x86_64.rpm sudo yum -y localinstall Percona-Server-server-56-5.6.38rel83.0.el7.x86_64.rpm sudo yum -y localinstall Percona-Server-client-56-5.6.38rel83.0.el7.x86_64.rpm   3.配置MySQL配置文件my.cnf sudo vim /etc/my.cnf  

 

/home/test.sql","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"8MSn-1644559940741","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"uawe-1644559940732","leaves":[{"text":" 进入home目录查看结果:","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"yOUH-1644559953312","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"CxXS-1644559953312","leaves":[{"text":" [root@localhost /]# cd home","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"nTXf-1644559972023","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"1tG8-1644559972023","leaves":[{"text":" [root@localhost home]# ls","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"bAWP-1644559972023","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"BEgZ-1644559972023","leaves":[{"text":" mysql ","marks":[{"type":"fontSize","value":16}]},{"text":"test.sq","marks":[{"type":"fontSize","value":16},{"type":"color","value":"#393939FF"},{"type":"bold"}]},{"text":"l","marks":[{"type":"fontSize","value":16},{"type":"color","value":"#393939FF"}]},{"text":" vagrant","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"hF6v-1644560233417","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"ZazB-1644560233416","leaves":[{"text":" ","marks":[{"type":"fontSize","value":16}]},{"text":"【结果:】备份成功","marks":[{"type":"fontSize","value":16},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"TB0F-1644560002108","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"YZwb-1644560002107","leaves":[{"text":" 恢复:","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"KoUf-1644560035514","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"6yrI-1644560035512","leaves":[{"text":" ","marks":[{"type":"fontSize","value":16}]},{"text":"在恢复数据之前,需要建立一个新的数据库 ","marks":[{"type":"color","value":"#cc0000"},{"type":"backgroundColor","value":"rgb(255, 255, 255)"},{"type":"fontFamily","value":"SimHei"},{"type":"fontSize","value":16}]},{"text":",建立的数据库名称是 Test1","marks":[{"type":"color","value":"#4d4d4d"},{"type":"backgroundColor","value":"rgb(255, 255, 255)"},{"type":"fontFamily","value":"SimHei"},{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"6rHF-1644560057721","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"esIJ-1644560057721","leaves":[{"text":" ","marks":[{"type":"color","value":"#4d4d4d"},{"type":"backgroundColor","value":"rgb(255, 255, 255)"},{"type":"fontFamily","value":"SimHei"},{"type":"fontSize","value":16}]},{"text":"[root@localhost /]# mysql -uroot -pmypassword123! Test1 show databases;","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"czBm-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"Fzuy-1644560215926","leaves":[{"text":"+--------------------+","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"Z8ZV-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"3V7C-1644560215926","leaves":[{"text":"| Database |","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"jkOu-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"ZBuz-1644560215926","leaves":[{"text":"+--------------------+","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"g3K0-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"B6Yp-1644560215926","leaves":[{"text":"| information_schema |","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"0TfE-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"yIV7-1644560215926","leaves":[{"text":"| Test |","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"nYK7-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"nNqr-1644560215926","leaves":[{"text":"| ","marks":[{"type":"fontSize","value":16}]},{"text":"Test1 ","marks":[{"type":"fontSize","value":16},{"type":"bold"}]},{"text":" |","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"5nye-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"0ce5-1644560215926","leaves":[{"text":"| mysql |","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"KwrP-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"huH6-1644560215926","leaves":[{"text":"| performance_schema |","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"Yp3W-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"A5le-1644560215926","leaves":[{"text":"+--------------------+","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"47mg-1644560215926","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"lJ6K-1644560215926","leaves":[{"text":"5 rows in set (0.00 sec)","marks":[{"type":"fontSize","value":16}]}]}],"state":{}},{"type":"block","id":"9G9Q-1644560169904","name":"paragraph","data":{"version":1},"nodes":[{"type":"text","id":"PTVM-1644560169896","leaves":[{"text":" ","marks":[{"type":"fontSize","value":16}]},{"text":"【结果:】恢复成功","marks":[{"type":"bold"},{"type":"fontSize","value":16}]}]}],"state":{}}]'>

配置说明: (1)主要配置三个配置节点,【mysqld】,【mysql_safe】,【client】 (2) 鉴于数据库安全性考虑,建议port值设置为10000~20000范围内的某个数,如port=10306 (3) 设置bind-address=0.0.0.0,确保mysql可以被远程连接 (4)配置完成后,务必保存后在退出(:wq!)   4.启动MySQL服务 [root@localhost /]# mysql_install_db --defaults-file=/etc/my.cnf [root@localhost /]# cd bin [root@localhost bin]# systemctl start mysqld.service   5.连接数据库,并创建用户和授权 (1)以root用户连接(修改root用户密码请参考以下第三部分说明)、 方式1: [root@localhost /]# mysql -u root -p Enter password: 方式2: [root@localhost /]#mysql -h localhost -uroot -p123456 (注意-u,-p后面没有空格) (2)创新新用户并授权 grant all on *.* to michael@'%' identified by 'a123456!' with grant option; flush privileges; 三.CentOs修改MySql数@'%' i据库的root用户的密码: 【注意:】初次安装mysql数据库,root账户没有密码。 1.用帐号登录mysql mysql -u root 2.切换到用户数据库 use mysql 【执行结果:】Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changed 3.修改密码,记得密码要用password()函数进行加密,一定不要忘记!!! update user set password=password('mypassword123!') where user='root'; 【执行结果:】 Query OK, 1 row affected (0.04 sec)Rows matched: 1   Changed: 1   Warnings: 0 4.刷新权限表 flush privileges; 【执行结果:】 Query OK, 0 rows affected (0.00 sec) 5.退出用户数据库mysql quit 【执行结果:】 Bye 6.对mysql进行重启 service mysqld restart;   四.其他操作说明 1.CentOS下安装nc命令工具 sudo yum -y install nc 命令格式:nc -v ip 端口 nc -v 192.168.5.100 10306 Ncat: Connected to 192.168.5.100:10306.【表示端口开放】 2.查看数据库当前端口号 进入mysql模式,执行 show global variables like 'port'; 3.mysql创建新用户并授权 grant all on *.* to mysql@'%' identified by 'a123456!' with grant option; flush privileges; 4.查看MySql数据库的状态 sudo systemctl status mysqld.service 5.启动MySql sudo systemctl start mysqld.service 6.停止MySql sudo systemctl stop mysqld.service 7.重启MySql sudo systemctl restart mysqld.service 8.退出mysql命令行模式 用control + D 9.CentOS环境MySQL数据库的备份与恢复   备份: [root@localhost bin]# cd bin [root@localhost bin]# mysqldump -uroot -pmypassword123! Test>/home/test.sql 进入home目录查看结果: [root@localhost /]# cd home [root@localhost home]# ls mysql test.sql vagrant 【结果:】备份成功 恢复: 在恢复数据之前,需要建立一个新的数据库 ,建立的数据库名称是 Test1 [root@localhost /]# mysql -uroot -pmypassword123! Test1 进入mysql命令行模式,查看恢复后是否有Test1库: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Test | | Test1 | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.00 sec) 【结果:】恢复成功  

标签:16,text,marks,value,Server,Percona,MySql,type,id
From: https://www.cnblogs.com/michael999/p/16877994.html

相关文章

  • MySQL-InnerDB原理
     InnerDB实现原理 它是MySQL从5.5版本卡死的默认的存储引擎,是第一份支持ACID特性的MySQL存储引擎,特点是行锁设计,支持MVCC(多版本并发控制),支持外键......
  • MySQL-数据库优化
     数据库优化: 数据库设计:1.字段选型:数字类型:tinyintsmalintmediumintintbigint字符类型:charvarchar事件类型:datedate......
  • MySQL-InnoDB的MVCC
     InnoDBMVCCInnoDB的MVCC,其实是通过undolog来实现的,可以理解为是通过在每行记录后面保存两个隐藏的列来实现的,分别保存了这个行的创建时间,一个保......
  • MySQL-索引类型优缺点
     MySQL主要集中索引类型:FULLTEXT,HASH,BTREE,RTREE 1.FULLTEXT即为全文索引,目前只有MyISAM支持。不过目前只有CHAR,VARCHAR,TEXT列上可以创建全文索引。......
  • MySQL聚簇索引和非聚簇索引
     聚簇索引和非聚簇索引 在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表......
  • The server encountered an unexpected condition that prevented it from fulfilling
     在网上也是兜兜转转找了一圈问题,最终解决了,总结一下先说原因:JDK版本过高解决方法:换低版本JDK--》    随后我将JDK版本换成了1.8就可以了哈~~~  ......
  • mysql多个结果集拼接一行
    五个不同的结果,查询出来,拼接成一行 SELECT*FROM ( SELECT plant_nameasplantName FROMpv_power_plant <iftest="plantId!=nullandplantId!=''"......
  • MySQL的InnerDB和MySAM索引实现
     InnoDB索引实现 InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。InnoDB的主索引:MyISAM索引文件和数据文件是分离的,索引文件仅保......
  • Centos mysql主从复制配置
    1、需要准备两台mysql服务器。我这里主机mt1 从机mt2查看两个服务器的防火墙是否关闭;如果没有关闭需要关闭。查看防火墙状态命令。statusfirewalld;如果是通过虚拟机克隆......
  • 关于使用pt-heartbeat监测MySQL主从复制延迟的方法
    pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表,然后在从库上读取被更新特定表里的时间戳,再与本地系统时间对比来得出其延迟。具体流程:1)在主库上创建一张heart......