centos7下搭建mysql5.7实录

安装之前准备

• 系统初始化(略)
• 网络初始化(略)
• 基础组件初始化(略)

创建用户组和用户

groupadd mysql
useradd -g mysql mysql

1. 下载安装包

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz 

2. 解压安装包

tar -zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

3. 将解压的安装包改名为mysql

mv mysql-5.7.18-linux-glibc2.5-x86_64 mysql

4. 验证下基础路径

[root@localhost opt]# cd mysql
[root@localhost mysql]# pwd
/opt/mysql
[root@localhost mysql]# 

5. 新建一个数据存储目录

[root@localhost mysql]# ls
bin  COPYING  data  docs  include  lib  man  README  share  support-files
[root@localhost mysql]# 

[root@localhost mysql]# 
[root@localhost mysql]# mkdir data

6. 给解压的MySQL目录分配所属

[root@localhost opt]# chown mysql:mysql /opt/mysql
[root@localhost opt]# ll
-rw-r--r--.  1 root  root        790 May 22  2018 my.cnf
drwxr-xr-x. 10 mysql mysql      4096 Nov 16 10:53 mysql
[root@localhost opt]# 
[root@localhost opt]# 

7. 初始化

执行命令,/opt/mysql/mysql_initialize.txt 文件是为了拿到密码

[root@localhost mysql]# bin/mysqld --initialize --user=mysql --basedir=/opt/mysql --datadir=/opt/mysql/data > /opt/mysql/mysql_initialize.txt 2>&1

8.取出来密码

[root@localhost mysql]# cat mysql_initialize.txt 
2019-11-16T02:57:24.362118Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-11-16T02:57:24.834413Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-11-16T02:57:24.931489Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-11-16T02:57:25.019573Z 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: d1345355-081c-11ea-8131-000c29c28910.
2019-11-16T02:57:25.022023Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-11-16T02:57:25.026559Z 1 [Note] A temporary password is generated for root@localhost: atlw(aJur8r6
[root@localhost mysql]# 
[root@localhost mysql]# 

9. 修改 support-files中mysql.server的文件

• basedir
• datadir
• mysqld_pid_file_path

执行命令

vim /opt/mysql/support-files/mysql.server

需要修改的项目

basedir=/opt/mysql
datadir=/opt/mysql/data
mysqld_pid_file_path=/opt/mysql/data/mysql.pid

10. 将mysqld添加到系统服务中

• 方式一

# 增加服务文件
vim /usr/lib/systemd/system/mysqld.service

# 下面是追加内容
[Unit]
Description=Mysql
After=syslog.target network.target remote-fs.target nss-lookup.target

[Service]
Type=forking
# pid是/opt/mysql/support-files/mysql.server 配置文件中配置的 mysqld_pid_file_path选项
PIDFile=/opt/mysql/data/mysqld.pid
ExecStart=/opt/mysql/support-files/mysql.server start
ExecStop=/opt/mysql/support-files/mysql.server stop
ExecRestart=/opt/mysql/support-files/mysql.server restart
ExecReload=/opt/mysql/support-files/mysql.server reload
PrivateTmp=true

[Install]

WantedBy=multi-user.target

• 方式二

cp /opt/mysql/support-files/mysql.service /usr/lib/systemd/system/mysqld.service

11. 拷贝/opt目录中的my.cnf到/etc/中(可选操作)

[root@localhost opt]# cp /opt/my.cnf /etc/
[root@localhost opt]# chown 777 /etc/my.cnf
[root@localhost opt]# 

etc文件内容

[client]
#default-character-set=utf8
#default-character-set = utf8mb4
[mysql]
#default-character-set=utf8
default-character-set = utf8mb4

[mysqld]
max_allowed_packet=40M
lower_case_table_names=1
#init_connect='SET collation_connection = utf8_unicode_ci'
#init_connect='SET NAMES utf8'
#character-set-server=utf8
#collation-server=utf8_unicode_ci
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

#default_storage_engine=INNODB
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

12. 为安装目录中的bin下文件 赋予执行权限

[root@localhost opt]# chmod a+x /opt/mysql/bin/*

13.启动后台服务

# 使配置生效
systemctl daemon-reload
# 增加开机自启功能
systemctl enable mysqld.service
# 现在开启mysqld服务
systemctl start mysqld.service

14. 检查服务是否正常

[root@localhost opt]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:35243           0.0.0.0:*               LISTEN      1559/rpc.statd      
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1501/rpcbind        
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      2534/sshd           
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      1594/cupsd          
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1912/master         
tcp6       0      0 :::42178                :::*                    LISTEN      1559/rpc.statd      
tcp6       0      0 :::3306                 :::*                    LISTEN      3932/mysqld         
tcp6       0      0 :::111                  :::*                    LISTEN      1501/rpcbind        
tcp6       0      0 :::22                   :::*                    LISTEN      2534/sshd           
tcp6       0      0 ::1:631                 :::*                    LISTEN      1594/cupsd          
tcp6       0      0 ::1:25                  :::*                    LISTEN      1912/master         
[root@localhost opt]# 

15. 配置mysql命令

[root@localhost bin]# ln -s /opt/mysql/bin/mysql /usr/bin/mysql
[root@localhost bin]# 
[root@localhost bin]# 
[root@localhost bin]# 

15. 检查登录

[root@localhost bin]# mysql -uroot -p
mysql: [Warning] World-writable config file '/etc/my.cnf' is ignored.
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18

Copyright (c) 2000, 2017, 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.

16. 试试查询

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 

说我没改密码,不让我用. 好吧,改个密码先

mysql> 
mysql> 
mysql> set PASSWORD = PASSWORD('1230');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

再试试

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

常见异常处理

1. /etc/my.cnf 被忽略

exec sqlline [main/afterData.sql]
mysql: [Warning] World-writable config file '/etc/my.cnf' is ignored.
ERROR 1146 (42S02) at line 54 in file: 'main/afterData.sql': Table 'affin_cbs.Cfb_Person_Base' doesn't exist

数据库不能读取/etc/my.cnf

报错内容大概是忽略/etc/my.cnf

解决方案
# 处理掉其他用户的所有权限
chmod 700 /etc/my.cnf

# 重启mysqld即可
systemctl restart mysqld.service

2. mysql.sock文件找不到

添加个软连接就搞定了