MySql企业版Percona-Server部署方案

一.MySql数据库版本说明
自从甲骨文公司收购了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.下载三个源文件：
 
2.安装以上三个文件：
 
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)
【结果：】恢复成功
 
posted @ 2022-11-10 18:21  麦哥编程  阅读(357)  评论(0)  编辑  收藏  举报