Percona--Server-Mysql8.0.25 安装_percona-server-优快云博客

本文链接：https://blog.youkuaiyun.com/Neviller_Ma/article/details/127847596

5 mysql 安装

<span style="background-color:#f8f8f8"><span style="color:#333333">Windows 下可以完全实现图形化管理，Linux下的安装方式有所差别，从难度上来说，还好不算太难。</span></span>

5.1 三种安装方式

rpm安装：可以通过官网下载对应的rpm 包，直接通过yum 的方式来安装，这种方式实际中使用的比例不高，主要是因为yum 安装的路径和配置难以定制化。
** 二进制安装：官方可以下载编译软件包，有了这个软件包就不需要额外准备依赖的环境软件了。安装会变得更加轻量，软件包解压就可以基本实现，主要学习些种安装方式**
源码安装：通过对指定的环境配置适用的软件环境，进行源码的编译安装，这种方法对环境的依赖较高，通过批量安装的方式耗时难以控制，作为环境调试比较适用。

5.2 mysql安装规范

为了能够避免在工作中出现各种奇怪的环境问题，还是需统一一环境的配置，方便定位和管理MYSQL。软件安装主考虑软件安装目录和数据目录上，核心思想就是软件和数据要分离开，减少彼此之间的耦合，数据目录和日志文件最好放在不同分区上，以提高软件性能。

MYSQL安装规范如下图

linux 软件安装目录一般放在/usr/local/下，数据文件放在根目录/data下，根据生产环境定义如/data/bdcddjdata ,可以把日志文件和临时文件单独拆分目录。

5.3 MySQL 8.0 安装部署实践

安装包分别可以去官方站点下载，二进制包是以.tar.gz 为后缀名的文件。

官方下载地址：MySQL :: Download MySQL Community Server (Archived Versions)

percona for myql8.0下载地址: Download Percona Distribution for MySQL

MariaDB 下载地址:Download MariaDB Server - MariaDB.org

5.3.1 删除之前安装的MySQL包

<span style="background-color:#f8f8f8"><span style="color:#333333">rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@redis01 mysql-8.0.25]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64</span></span>

5.3.2 磁盘分区创建LV DATA 卷

<span style="background-color:#f8f8f8"><span style="color:#333333">fdisk /dev/sdb   类型LVM
pvcreate /dev/sdb1  创建PV
vgcreate datavg /dev/sdb1   创建VG
lvcrate -L 18g -n data datavg   创建LV

mkfs.xfs /dev/datavg/data    格式化
meta-data=/dev/datavg/data       isize=512    agcount=4, agsize=1179648 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=4718592, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

挂载 lv 
mount /dev/datavg/data /data
[root@redis01 ~]# df
文件系统                   1K-块    已用     可用 已用% 挂载点
/dev/mapper/centos-root 17811456 6265012 11546444   36% /
devtmpfs                 1001888       0  1001888    0% /dev
tmpfs                    1014056       0  1014056    0% /dev/shm
tmpfs                    1014056    9764  1004292    1% /run
tmpfs                    1014056       0  1014056    0% /sys/fs/cgroup
/dev/sda1                1038336  135376   902960   14% /boot
tmpfs                     202812       0   202812    0% /run/user/0
/dev/mapper/datavg-data 18864128   32992 18831136    1% /data
</span></span>

5.3.3 安装路径

<span style="background-color:#f8f8f8"><span style="color:#333333">cd /usr/local
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.25-el7-x86_64.tar.gz
tar -zxvf mysql-8.0.25-el7-x86_64.tar.gz</span></span>

5.3.4 环境变量设置

<span style="background-color:#f8f8f8"><span style="color:#333333">vi /etc/profile
export basedir=/usr/local/mysql-8.0.25
export datadir=/data/bdcdjdata

groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql  #创建用户
chown -R mysql:mysql $basedir $datadir
chown mysql:mysql $basedir/log $datadir/tmp</span></span>

设置参数文件,或者基于现有项目的模板也可以。vi my.cnf (单实例、多实例)

<span style="background-color:#f8f8f8"><span style="color:#333333">#客户端配置
[client]
port=3306
[mysql]
port = 3306
socket = /data/tmp/mysql.sock
#服务端配置
[mysqld]
port = 3306
server-id=mysql_1
mysqlx_port = 33060
mysqlx_socket = /data/tmp/mysqlx.sock
basedir = /usr/local/mysql-8.0.25
datadir = /data/bdcdjdata
socket = /data/tmp/mysql.sock
pid-file = /data/bdcdjdata/mysqld.pid
log-error = /data/log/error.log
#这个就是用之前的身份认证插件
default-authentication-plugin = mysql_native_password
#保证日志的时间正确
log_timestamps = SYSTEM
character-set-server=utf8
#慢日志
slow_query_log=on 
slow_query_log_file =/data/log/mysql-db01-slow.log
#导出配置
secure_file_priv= ''
#事务模式
transaction_isolation=read-Committed
#federated
</span></span>

多实例 my.cnf模版

<span style="background-color:#f8f8f8"><span style="color:#333333">vi /etc/my.cnf
#客户端配置
[client]
port=3306

[mysql]
port = 3306
socket = /data/tmp/mysql.sock

#服务端配置
[mysqld]
port = 3306
mysqlx_port = 33060
mysqlx_socket = /data/tmp/mysqlx.sock
basedir = /usr/local/mysql-8.0.25
datadir = /data/bdcdjdata
socket = /data/tmp/mysql.sock
pid-file = /data/bdcdjdata/mysqld.pid
# 配置MASTER主机开启二进日志
server-id=mysql_1
log-bin=/data/log/master.bin
log-error = /data/log/error.log
#这个就是用之前的身份认证插件
default-authentication-plugin = mysql_native_password
#保证日志的时间正确
log_timestamps = SYSTEM
character-set-server=utf8
#dblink
#federated
#user=mysql</span></span>

验证配置：mysqld --validate-config

5.3.5 初始化数据库,并查看日志

<span style="background-color:#f8f8f8"><span style="color:#333333">
cd /usr/local/mysql-8.0.25/bin
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql-8.0.25 --datadir=/data/bdcdjdata --lower-case-table-names=1
tail -f /data/log/error.log

--lower-case-table-names=1 大小写配置必须先初始化
tail -f error.log 
2021-07-26T16:59:36.446583+08:00 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.25/bin/mysqld (mysqld 8.0.25) initializing of server in progress as process 4121
2021-07-26T16:59:36.720283+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-07-26T16:59:37.172693+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-07-26T16:59:38.898402+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Bagl&flko5gl</span></span>

记住这个临时密码，后边要用到 root@localhost: Bagl&flko5gl

5.3.6 启动服务

<span style="background-color:#f8f8f8"><span style="color:#333333">cp  $basedir/support-files/mysql.server /etc/init.d/mysqld    #复制启动脚本，放到自动设置中
service mysqld start

 ./mysqld start
Starting MySQL.. SUCCESS! </span></span>

5.3.7 登录并配置远程登录

<span style="background-color:#f8f8f8"><span style="color:#333333">mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25

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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc!@#123'; #修改密码
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)</span></span>

配置远程登陆

我们需要使用navicat远程登陆mysql 8.0,但是由于mysql8.0 加强了安全性，与mysql 5.5之前版本连接加密协议不一致，而且貌似不支持直接root用户远程登陆。所以我们要配置一个远程登陆用户user,同时配置加密规则

<span style="background-color:#f8f8f8"><span style="color:#333333">create user 'gtmap'@'%' identified by 'NJ@gtmap123!';  #WITH MAX_USER_CONNECTIONS 10;

grant all privileges on *.* to 'mxltest'@'%' with grant option;
flush privileges;

mysql -h 192.168.43.144 -uroot -p
Enter password: ****
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)
</span></span>

5.3.7 配置运行环境变量

而下面的设置就是创建一些软连接，是／usr/bin 中可以正常访问mysql 的几个常用命令行工具。

<span style="background-color:#f8f8f8"><span style="color:#333333">ln -f -s  /usr/local/mysql-8.0.25/bin/mysql /usr/bin/mysql
ln -f -s  /usr/local/mysql-8.0.25/bin/mysqldump /usr/bin/mysqldump
ln -f -s  /usr/local/mysql-8.0.25/bin/mysqladmin /usr/bin/mysqladmin
ln -f -s  /usr/local/mysql-8.0.25/bin/mysqlshow /usr/bin/mysqlshow
#ln -f -s  /usr/local/mysql-8.0.25/bin/mysqld /usr/bin/mysqld
ln -f -s /usr/local/mysql-8.0.25/support-files/mysql.server /usr/bin/mysqld</span></span>

CentOS 7的服务systemctl脚本存放在：/usr/lib/systemd/，有系统（system）和用户（user）之分，像需要开机不登陆就能运行的程序，还是存在系统服务里吧，即：/usr/lib/systemd/system目录下

<span style="background-color:#f8f8f8"><span style="color:#333333">vim /usr/lib/systemd/system/mysql.service 
[Unit]
Description=mysql
After=network-online.target
[Service]
Type=forking
#当意外中止时是否重启
#Restart=always
#在ExecStart之前执行，可以放多个
#ExecStartPre=
#服务执行的路径
ExecStart=/etc/init.d/mysqld start
#ExecStart后运行
#ExecStopPost=
#指明停止unit要运行的命令或脚本
ExecStop=/etc/init.d/mysqld stop
#指明重启unit要运行的命令或脚本
ExecReload=/etc/init.d/mysqld restart
User=mysql
Group=mysql
[Install]
WantedBy=multi-user.target</span></span>

总结：安装部署核心思想就是软件和数据要分离开，这样能够减少彼此之间的耦合，同时建议把数据目录、日志目录分不同分区存放，以提高性能。

5.3.8 错误处理

<span style="background-color:#f8f8f8"><span style="color:#333333">./mysql_secure_installation 
Securing the MySQL server deployment.

Enter password for user root: 
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

 Solution
    这个问题主要是不能通过 '/tmp/mysql.sock'连到服务器，新建一个到真正mysql.sock位置的软连接就可以了。
    其实之前已经创建了，因为tmp里的内容在重启后消失了，之前创建的软连接没了，所以出现了这个错误，需要再次新建软连接。

2 Step
ln -s /data/tmp/mysql.sock /tmp/mysql.sock</span></span>

5.3.9 安全配置向导

<span style="background-color:#f8f8f8"><span style="color:#333333">oot@server1 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):<–初次运行直接回车
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] <– 是否删除匿名用户,生产环境建议删除，所以直接回车
… Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
… Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] <– 是否删除test数据库,直接回车
- Dropping test database…
… Success!
- Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，直接回车
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!</span></span>