实战三种方式部署 MySQL5.7

2019-03-21 10:53:32 401

实战三种方式部署 MySQL5.7
常见的 MySQL 安装方式有如下三种:

RPM 包方式:这种方式安装适合对数据库要求不太高的场合,安装速度快;
通用二进制包方式:安装速度相较于源码方式快,可以自定义安装目录。
源码编译安装:安装过程比较慢,机器性能不好的情况下,大约需要30分钟左右,通常适用于mysql定制化的安装,比如需要加入一些第三方的插件及依赖库等
环境说明
OS 版本 MySQL 版本
CentOS 7.5.1804 5.7.25
一、RPM 包方式安装
1.1 获取 RPM 包
访问 MySQL 官网,下载最新版 mysql5.7 的 rpm 包。

点击 DOWNLOADS --> 点击 Community 社区版 --> 选择 MySQL Community Server
-w1030

选择 MySQL Community Server 5.7 -> 而后选择对应的软件平台版本
-w990

选择下载 RPM Bundle 这里包含了所有 MySQL 的 RPM 包。
-w1041

-w902

1.2 安装 MySQL
下载 Bundle 包解压以后,可以看到包含了所有 MySQL 相关的 RPM 包:

-w1178

其中 client、common、libs、server 四个程序包是必须安装的:

mysql-community-client-5.7.25-1.el7.x86_64.rpm
mysql-community-common-5.7.25-1.el7.x86_64.rpm
mysql-community-libs-5.7.25-1.el7.x86_64.rpm
mysql-community-server-5.7.25-1.el7.x86_64.rpm
在执行安装之前,先检查是否已经安装过(CentOS7 以后默认安装的 mariadb)

$ rpm -qa|egrep "mariadb|mysql"
mariadb-libs-5.5.60-1.el7_5.x86_64

我这里存在 mariadb-libs 会造成冲突,所以卸载掉

rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64

卸载之后就可以进行安装使用 yum 或者 rpm -ivh

$ yum -y install mysql-community-client-5.7.25-1.el7.x86_64.rpm mysql-community-common-5.7.25-1.el7.x86_64.rpm mysql-community-libs-5.7.25-1.el7.x86_64.rpm mysql-community-server-5.7.25-1.el7.x86_64.rpm
安装完成后 MySQL 的默认配置文件为 /etc/my.cnf 接下来我们就可以启动 MySQL 啦

$ systemctl start mysqld.service
$ systemctl enable mysqld.service
$ systemctl status mysqld.service
1.3 修改 MySQL 默认密码
MySQL 5.7 以后,不在允许使用空密码进行登录,默认会初始化一个密码到 MySQL Error 日志中,配置参数 log-error= 指定的文件。

$ cat /var/log/mysqld.log | grep password
2019-03-20T02:44:49.359004Z 1 [Note] A temporary password is generated for root@localhost: /qrsXHttL6Mr
连接实例并修改默认密码

$ mysql -uroot -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.25

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

  1. Other names may be trademarks of their respective
    owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> set password for 'root'@'localhost'=password('MyNewPass4!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
以后通过 update set 语句修改密码:

mysql> use mysql;
mysql> update user set authentication_string=PASSWORD('NewPass@2019') where user='root';
mysql> flush privileges;
注意:mysql 5.7 默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号,并且长度不能少于8位。否则会提示 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 错误。查看 MySQL 密码策略

二、通用二进制包方式安装
官方文档:https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

2.1 获取安装包
选择 Linux - generic 64 位安装包

-w1049

2.2 安装 MySQL
MySQL 依赖于 libaio 库。 如果未在本地安装此库,则数据目录初始化和后续服务器启动步骤将失败。 如有必要,请使用适当的包管理器进行安装。 例如,在基于Yum 的系统上:

$ yum -y install libaio
创建 MySQL 用户和组

$ groupadd mysql
$ useradd -r -g mysql -s /bin/false mysql
解压到指定目录

$ tar xf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
$ cd /usr/local/
$ ln -sv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql
修改解压目录下所有文件属主及属组

$ cd /usr/local/mysql
$ chown -R root.mysql ./*
创建数据目录,以 /data/mysql/data 为例

$ mkdir -pv /data/mysql/{data,log}
$ chown -R mysql.mysql /data/mysql
准备 MySQL 配置文件,我这里用的是在线工具生成的 my.cnf 文件,工具链接

$ cat /etc/my.cnf

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

[mysqld]
port = 3306
socket = /data/mysql/mysql.sock
pid_file = /var/run/mysql.pid
datadir = /data/mysql/data
basedir = /usr/local/mysql
default_storage_engine = InnoDB
max_allowed_packet = 128M
max_connections = 2048
open_files_limit = 65535
skip-name-resolve
lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size = 128M
innodb_log_file_size = 128M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 16M
log-error = /data/mysql/log/mysql_error.log
log-bin = /data/mysql/log/mysql_bin.log
slow_query_log = 1
slow_query_log_file = /data/mysql/log/mysql_slow_query.log
long_query_time = 5
tmp_table_size = 16M
max_heap_table_size = 16M
query_cache_type = 0
query_cache_size = 0
server-id=1
复制启动脚本

$ cp support-files/mysql.server /etc/init.d/mysqld
$ chmod +x /etc/init.d/mysqld
$ chkconfig --add mysqld
初始化数据库

$ ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
此时会生成一个临时密码,可以在 mysql_error.log 文件找到

$ grep password /data/mysql/log/mysql_error.log
2019-03-20T05:37:28.267207Z 1 [Note] A temporary password is generated for root@localhost: H_wgkXR&f1=t
生成 SSL

$ ./bin/mysql_ssl_rsa_setup --basedir=/usr/local/mysql --datadir=/data/mysql/data/
启动 MySQL 服务

$ service mysqld start
$ ss -tnlp | grep 3306
配置 MySQL 环境变量

$ vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
$ source /etc/profile.d/mysql.sh
2.3 MySQL 用户初始化
$ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: # 输入初始密码,在错误日志中

The existing password for the user account root has expired. Please set a new password.

New password: # 输入新密码

Re-enter new password: # 输入新密码

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: Y # 是否启用密码安全策略

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 # 设置密码复杂度
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : N # 是否修改 root 密码,刚才已经新设置了,输入 N

... skipping.
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? (Press y|Y for Yes, any other key for No) : Y # 是否移除匿名用户
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? (Press y|Y for Yes, any other key for No) : Y # 是否禁止 root 用户远程登录
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? (Press y|Y for Yes, any other key for No) : Y # 是否删除 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? (Press y|Y for Yes, any other key for No) : Y # 是否刷新权限表
Success.

All done!
验证 MySQL 安装

mysqladmin version -u root -p
三、源码编译方式安装
3.1 安装依赖包
$ yum -y install gcc gcc-c++ ncurses ncurses-devel cmake bison
安装 Boost 库,获取程序包请访问 Boost 官网

$ cd /usr/local/src/
$ wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
$ tar xf boost_1_59_0.tar.gz -C /usr/local/
3.2 获取 MySQL 源代码包
-w1043

$ cd /usr/local/src/
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.25.tar.gz
3.3 创建 MySQL 用户组
$ groupadd mysql
$ useradd -r -g mysql -s /bin/false mysql
3.4 预编译
$ tar xf mysql-5.7.25.tar.gz
$ cd mysql-5.7.25
$ cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/data \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_BOOST=/usr/local/boost_1_59_0 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_CSV_STORAGE_ENGINE=1 \
-DWITH_HEAP_STORAGE_ENGINE=1 \
-DWITH_MYISAMMRG_STORAGE_ENGINE=1 \
-DWITH_ZLIB=system \
-DWITH_EMBEDDED_SERVER=1
更多 cmake 指令参考官方文档

3.5 编译安装
$ make -j grep processor /proc/cpuinfo | wc -l
$ make install
3.6 配置文件
准备 MySQL 配置文件,我这里用的是在线工具生成的 my.cnf 文件,工具链接

$ cat /etc/my.cnf

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

[mysqld]
port = 3306
socket = /data/mysql/mysql.sock
pid_file = /var/run/mysql.pid
datadir = /data/mysql/data
basedir = /usr/local/mysql
default_storage_engine = InnoDB
max_allowed_packet = 128M
max_connections = 2048
open_files_limit = 65535
skip-name-resolve
lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size = 128M
innodb_log_file_size = 128M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 16M
log-error = /data/mysql/log/mysql_error.log
log-bin = /data/mysql/log/mysql_bin.log
slow_query_log = 1
slow_query_log_file = /data/mysql/log/mysql_slow_query.log
long_query_time = 5
tmp_table_size = 16M
max_heap_table_size = 16M
query_cache_type = 0
query_cache_size = 0
server-id=1
创建数据目录

$ mkdir -pv /data/mysql/{log,data}
$ chown -R mysql.mysql /data/mysql/
3.7 初始化
$ cd /usr/local/mysql/
$ ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data/
3.8 设置启动脚本配置环境变量
$ cp support-files/mysql.server /etc/init.d/mysqld
$ chmod +x /etc/init.d/mysqld
$ echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
3.9 启动数据库
$ systemctl enable mysqld
$ systemctl start mysqld
$ systemctl status mysqld
$ ss -tnlp|grep 3306
$ ps aux|grep mysql
3.10 初始化用户
与二进制方式一样,初始密码在错误日志内。

$ mysql_secure_installation
3.11 验证 MySQL
$ mysqladmin version -uroot -p
以上就是 MySQL 5.7 版本的三种安装方式,欢迎大家多留言交流。
作者:不悔
原文链接:https://www.opsbj.com/2019/03/20/mysql-install/

mysql LOG 配置 rpm test database boost Other service

作者

优惠券活动
TA的文章

相关文章