CentOS 7.5安装MySQL5.7.23

注：安装问题请参考文章最后！

1.服务器版本

# lsb_release -a
LSB Version:	:core-4.1-amd64:core-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.5.1804 (Core)
Release:	7.5.1804
Codename:	Core

2.下载MySQL

# cd /home/install-files
# wget http://ftp.kaist.ac.kr/mysql/Downloads/MySQL-5.7/mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz

3.解压修改安装名称

# tar zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# cd /usr/local/
# ls
aegis  bin  etc  games  include  lib  lib64  libexec  mysql-5.7.23-linux-glibc2.12-x86_64  sbin  share  src
# mv mysql-5.7.23-linux-glibc2.12-x86_64/ mysql
# ls
aegis  bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src

4.创建目录

# mkdir -p /mysql/data
# mkdir -p /mysql/sock
# mkdir -p /mysql/log
# mkdir -p /mysql/pid

5.创建组和用户

# groupadd mysql
# useradd mysql -g mysql -d /usr/local/mysql -s /sbin/nologin

6.安装

# cd /usr/local/mysql
# ls
COPYING  README  bin  docs  include  lib  man  share  support-files

6.1.配置文件

注：第一次，请注释“bind-address”，需要root本地登录，初始化mysql用户

[mysqld]
user=mysql
bind-address=192.168.80.168
port=3306
basedir=/usr/local/mysql
datadir=/mysql/data
socket=/mysql/sock/mysql.sock
pid-file=/mysql/pid/mysql.pid
log-error=/mysql/log/mysql-error.log

symbolic-links=0
default-storage-engine=INNODB
character_set_server=utf8
collation-server=utf8_general_ci
transaction_isolation=REPEATABLE-READ
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip_ssl
default-time-zone='+8:00'
innodb_buffer_pool_instances=1
max_connections=200

[mysql]
default-character-set=utf8
port=3306
socket=/mysql/sock/mysql.sock

注:禁用ssl mode，在[mysqld]下配置（独占一行）: skip_ssl

6.2.启动服务文件

# cd /usr/local/mysql/support-files/
# cp mysql.server /etc/init.d/mysqld

注：修改启动文件：

# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/mysql/data

6.3.更改涉及拥有者

# chown -R mysql:mysql /mysql
# chown -R mysql:mysql /etc/my.cnf
# chown -R mysql:mysql /usr/local/mysql

6.4.初始化 mysql 的数据库

注：mysq默认root本地登录密码: avi>2Wlg%Khz

# cd /usr/local/mysql/bin
innochecksum       myisamchk   mysql_client_test_embedded  mysql_install_db           mysql_tzinfo_to_sql  mysqlcheck    mysqld_safe    mysqlpump           mysqlxtest          resolveip
lz4_decompress     myisamlog   mysql_config                mysql_plugin               mysql_upgrade        mysqld        mysqldump      mysqlshow           perror              zlib_decompress
my_print_defaults  myisampack  mysql_config_editor         mysql_secure_installation  mysqladmin           mysqld-debug  mysqldumpslow  mysqlslap           replace
myisam_ftdump      mysql       mysql_embedded              mysql_ssl_rsa_setup        mysqlbinlog          mysqld_multi  mysqlimport    mysqltest_embedded  resolve_stack_dump
# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data
# cat /mysql/log/mysql-error.log
2018-08-05T03:42:33.946662Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-05T03:42:33.946725Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2018-08-05T03:42:33.946729Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2018-08-05T03:42:35.019167Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-05T03:42:35.140139Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-05T03:42:35.208001Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9747442b-9861-11e8-b272-00163e04364b.
2018-08-05T03:42:35.211031Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-05T03:42:35.211514Z 1 [Note] A temporary password is generated for root@localhost: avi>2Wlg%Khz

6.5.mysql数据库加密

# ./mysql_ssl_rsa_setup --datadir=/mysql/data
Generating a 2048 bit RSA private key
.......................+++
......+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
............................+++
........+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
............................................................................................+++
......................+++
writing new private key to 'client-key.pem'

6.6.启动mysql数据库

# chown -R mysql:mysql /mysql
# chown -R mysql:mysql /etc/my.cnf
# chown -R mysql:mysql /usr/local/mysql
# service mysqld start
Starting MySQL.                                            [  OK  ]

# ps -ef |grep mysql
root      2320     1  0 12:10 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mysql/data --pid-file=/mysql/pid/mysql.pid
mysql     2583  2320  1 12:10 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysql/log/mysql-error.log --pid-file=/mysql/pid/mysql.pid --socket=/mysql/sock/mysql.sock --port=3306
root      2632  1016  0 12:11 pts/0    00:00:00 grep --color=auto mysql

6.7.本地root用户登录mysql，初始化远程登录用户

# ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23

Copyright (c) 2000, 2018, 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> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant all privileges on *.* to idba@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

7.设置mysql服务开机自启动

# chkconfig --add mysqld
# chkconfig mysqld on
# chkconfig --list | grep mysqld

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld          	0:off	1:off	2:on	3:on	4:on	5:on	6:off

8.配置mysql环境变量

# vim /etc/profile

注：最后添加

export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH

注：生效环境变量

# source /etc/profile

9.测试mysql安装所有的安装成功？

# mysql --version
mysql  Ver 14.14 Distrib 5.7.23, for linux-glibc2.12 (x86_64) using  EditLine wrapper

# mysql -h 192.168.80.168  -uidba -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT COUNT(*) FROM `user`;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

# reboot
Connection to 192.168.80.168 closed by remote host.
Connection to 192.168.80.168 closed.

➜  ~ ssh root@192.168.80.168
root@192.168.80.168's password:
Last login: Sun Aug  5 10:53:44 2018 from 222.22.22.222

Welcome to Alibaba Cloud Elastic Compute Service !

-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory
# ps -ef |grep mysql
root       787     1  0 12:33 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mysql/data --pid-file=/mysql/pid/mysql.pid
mysql     1201   787  0 12:33 ?        00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysql/log/mysql-error.log --pid-file=/mysql/pid/mysql.pid --socket=/mysql/sock/mysql.sock --port=3306
root      1320  1302  0 12:33 pts/0    00:00:00 grep --color=auto mysql

successfully.

附录1:

Error: ./mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory


yum install libaio*

Error: # ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data
2018-08-05T03:39:51.400548Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-05T03:39:51.400615Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2018-08-05T03:39:51.400620Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2018-08-05T03:39:51.402275Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2018-08-05T03:39:51.402308Z 0 [ERROR] Aborting

1.检查：配置文件没有问题
2.检查：所有涉及文件已归属mysql用户没问题
3.检查：数据库“bind-address”没闻到
4.清空“/mysql/data”下编译的所有，即数据库文件，重新执行：
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data

-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory

vim /etc/environment
LC_ALL=en_US.UTF-8
LANG=en_US.UTF-8

附录2：忘记Mysql的root用户密码的解决方法

-- 先打开一个Terminal，在 /usr/local/mysql/bin ，下执行:

# sudo mysqld_safe --skip-grant-tables

-- 再新打开一个Terminal, 打开安全模式的mysql, 连接的时候不需要密码:

# mysql -u root -p
# UPDATE mysql.user SET authentication_string=PASSWORD('123456') WHERE User='root';
# FLUSH PRIVILEGES;

附录3：创建数据库

# CREATE DATABASE IF NOT EXISTS my_test DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
# GRANT ALL PRIVILEGES ON *.* TO dba@'%' IDENTIFIED BY '123456';
# FLUSH PRIVILEGES;