實戰三種方式部署 MySQL5.7

作者：不悔
原文鏈接：https://www.opsbj.com/2019/03/20/mysql-install/

常見的 MySQL 安裝方式有如下三種:

1. RPM 包方式：這種方式安裝適合對資料庫要求不太高的場合，安裝速度快；
2. 通用二進位包方式：安裝速度相較於源碼方式快，可以自定義安裝目錄。
3. 源碼編譯安裝：安裝過程比較慢，機器性能不好的情況下，大約需要30分鐘左右，通常適用於mysql定製化的安裝，比如需要加入一些第三方的插件及依賴庫等

環境說明

一、RPM 包方式安裝

1.1 獲取 RPM 包

訪問 MySQL 官網，下載最新版 mysql5.7 的 rpm 包。

• 點擊 DOWNLOADS --> 點擊 Community 社區版 --> 選擇 MySQL Community Server

• 選擇 MySQL Community Server 5.7 -> 而後選擇對應的軟體平臺版本

• 選擇下載 RPM Bundle 這裡包含了所有 MySQL 的 RPM 包。

1.2 安裝 MySQL

下載 Bundle 包解壓以後，可以看到包含了所有 MySQL 相關的 RPM 包：

其中 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
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> 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 位安裝包

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 源代碼包

$ 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 版本的三種安裝方式，歡迎大家多留言交流。