一.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