Linux CentOS MySQL8.0

1、下载解压安装包

A.下载解压

  1. [root@iZuligp6e1dyzfZ local]# cd mysql/
  2. [root@iZuligp6e1dyzfZ mysql]# mkdir mysql8.0
  3. [root@iZuligp6e1dyzfZ mysql]# cd mysql8.0/
  4. [root@iZuligp6e1dyzfZ mysql8.0]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
  5. [root@iZuligp6e1dyzfZ mysql8.0]# ll
  6. [root@iZuligp6e1dyzfZ mysql8.0]# tar -zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

image.png

B.创建数据目录（个人习惯放在bin目录同目录）

  1. [root@iZuligp6e1dyzfZ mysql8.0]# cd mysql-8.0.11-linux-glibc2.12-x86_64
  2. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# mkdir data
  3. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# ll

2、创建MySQL用户及用户组进行授权

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# groupadd mysql
  2. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# useradd -g mysql mysql
  3. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# chown -R mysql.mysql /local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64
  4. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]#

3、初始化MySQL数据目录

A.定位当前在MySQL的bin目录下

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# ll
  2. total 332
  3. drwxr-xr-x  2 mysql mysql   4096 Jul 20 22:41 bin
  4. drwxr-xr-x  2 mysql mysql   4096 Jul 20 22:41 data
  5. drwxr-xr-x  2 mysql mysql   4096 Jul 20 22:41 docs
  6. drwxr-xr-x  3 mysql mysql   4096 Jul 20 22:41 include
  7. drwxr-xr-x  5 mysql mysql   4096 Jul 20 22:41 lib
  8. -rw-r--r--  1 mysql mysql 301518 Apr  8  2018 LICENSE
  9. drwxr-xr-x  4 mysql mysql   4096 Jul 20 22:41 man
  10. -rw-r--r--  1 mysql mysql    687 Apr  8  2018 README
  11. drwxr-xr-x 28 mysql mysql   4096 Jul 20 22:41 share
  12. drwxr-xr-x  2 mysql mysql   4096 Jul 20 22:41 support-files

image.png

B.执行初始化数据命令（遇到缺少库）

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# ./bin/mysqld --user=mysql --basedir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/ --datadir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/data/ --initializ
  2. e
  3. ./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
  4. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# bin/mysqld --user=mysql --basedir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/ --datadir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/data/ --initialize
  5. bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

image.png

C.通过yum安装libaio库

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# yum install -y libaio

image.png

D.再次执行初始化MySQL命令成功执行（生成临时初始密码）

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# bin/mysqld --user=mysql --basedir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/ --datadir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/data/ --initialize

4、MySQL配置文件

编辑MySQL配置文件/etc/my.cnf

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# vim /etc/my.cnf

/etc/my.cnf配置如下内容

  1. [client]
  2. port=3306
  3. # mysql socket 文件存放地址
  4. socket=/var/lib/mysql/mysql.sock
  5. # 默认字符集
  6. default-character-set=utf8mb4
  7. [mysqld]
  8. port=3306
  9. character_set_server=utf8mb4
  10. basedir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/
  11. datadir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/data/
  12. socket=/var/lib/mysql/mysql.sock
  13. # Disabling symbolic-links is recommended to prevent assorted security risks
  14. symbolic-links=0
  15. # Settings user and group are ignored when systemd is used.
  16. # If you need to run mysqld under a different user or group,
  17. # customize your systemd unit file for mariadb according to the
  18. # instructions in http://fedoraproject.org/wiki/Systemd
  20. [mysqld_safe]
  21. # log-error=/var/log/mariadb/mariadb.log
  22. # pid-file=/var/run/mariadb/mariadb.pid
  24. #
  25. # include all files from the config directory
  26. #
  27. # !includedir /etc/my.cnf.d

image.png

5、建立MySQL服务

A.当前操作应在bin目录的根目录（当前目录有support-files文件夹）

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# ll
  2. total 332
  3. drwxr-xr-x  2 mysql mysql   4096 Jul 20 22:41 bin
  4. drwxr-xr-x  5 mysql mysql   4096 Jul 20 22:57 data
  5. drwxr-xr-x  2 mysql mysql   4096 Jul 20 22:41 docs
  6. drwxr-xr-x  3 mysql mysql   4096 Jul 20 22:41 include
  7. drwxr-xr-x  5 mysql mysql   4096 Jul 20 22:41 lib
  8. -rw-r--r--  1 mysql mysql 301518 Apr  8  2018 LICENSE
  9. drwxr-xr-x  4 mysql mysql   4096 Jul 20 22:41 man
  10. -rw-r--r--  1 mysql mysql    687 Apr  8  2018 README
  11. drwxr-xr-x 28 mysql mysql   4096 Jul 20 22:41 share
  12. drwxr-xr-x  2 mysql mysql   4096 Jul 20 22:41 support-files

image.png

B.添加MySQL到系统服务

  1. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# cp -a ./support-files/mysql.server /etc/init.d/mysqld
  2. [root@iZuligp6e1dyzfZ mysql-8.0.11-linux-glibc2.12-x86_64]# cd /etc/init.d
  3. [root@iZuligp6e1dyzfZ init.d]# chmod +x /etc/init.d/mysqld
  4. [root@iZuligp6e1dyzfZ init.d]# chkconfig --add mysqld
  5. [root@iZuligp6e1dyzfZ init.d]# chkconfig --list mysqld
  7. Note: This output shows SysV services only and does not include native
  8.       systemd services. SysV configuration data might be overridden by native
  9.       systemd configuration.
  11.       If you want to list systemd services use 'systemctl list-unit-files'.
  12.       To see services enabled on particular target use
  13.       'systemctl list-dependencies [target]'.
  15. mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

image.png

C.启动服务报错

  1. [root@iZuligp6e1dyzfZ bin]# service mysql start
  2. [root@iZuligp6e1dyzfZ bin]# systemctl status -l mysqld.service
  3. ● mysqld.service - LSB: start and stop MySQL
  4.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  5.    Active: failed (Result: exit-code) since Tue 2020-07-21 09:16:33 CST; 1h 16min ago
  6.      Docs: man:systemd-sysv-generator(8)
  7.   Process: 23849 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=1/FAILURE)
  9. Jul 21 09:16:32 iZuligp6e1dyzfZ systemd[1]: Starting LSB: start and stop MySQL...
  10. Jul 21 09:16:32 iZuligp6e1dyzfZ mysqld[23849]: Starting MySQL.Logging to '/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/data/iZuligp6e1dyzfZ.err'.
  11. Jul 21 09:16:32 iZuligp6e1dyzfZ mysqld[23849]: 2020-07-21T01:16:32.992054Z mysqld_safe Directory '/var/lib/mysql' for UNIX socket file don't exists.
  12. Jul 21 09:16:33 iZuligp6e1dyzfZ mysqld[23849]: The server quit without updating PID file (/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/data//iZuligp6e1dyzfZ.pid).[FAILED]
  13. Jul 21 09:16:33 iZuligp6e1dyzfZ systemd[1]: mysqld.service: control process exited, code=exited status=1
  14. Jul 21 09:16:33 iZuligp6e1dyzfZ systemd[1]: Failed to start LSB: start and stop MySQL.
  15. Jul 21 09:16:33 iZuligp6e1dyzfZ systemd[1]: Unit mysqld.service entered failed state.
  16. Jul 21 09:16:33 iZuligp6e1dyzfZ systemd[1]: mysqld.service failed.

image.png

①根据启动错误日志可以关联到/etc/my.cnf文件的socket=/var/lib/mysql/mysql.sock配置

image.png

②创建目录进行mysql用户操作赋权

  1. [root@iZuligp6e1dyzfZ lib]# mkdir mysql
  2. [root@iZuligp6e1dyzfZ lib]# chmod 777 /var/lib/mysql/

D.MySQL服务启动成功

  1. [root@iZuligp6e1dyzfZ bin]# service mysql start
  2. Redirecting to /bin/systemctl start  mysql.service

E.客户端连接成功（操作前会提示重置密码）

①将mysql链接至alias

  1. [root@iZuligp6e1dyzfZ bin 14:26]# alias mysql=/local/dev/mysql/mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql

②连接登录MySQL

  1. [root@iZuligp6e1dyzfZ bin]# mysql -uroot -p
  2. Enter password:
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 8
  5. Server version: 8.0.11
  7. Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> show databases;
  16. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
  17. mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '000000';
  18. Query OK, 0 rows affected (0.01 sec)
  20. mysql> show databases;
  21. +--------------------+
  22. | Database           |
  23. +--------------------+
  24. | information_schema |
  25. | mysql              |
  26. | performance_schema |
  27. | sys                |
  28. +--------------------+
  29. 4 rows in set (0.00 sec)
  31. mysql>

image.png

6、设置远程访问

不配置远程访问则连接会被拒绝
image.png
image.png

A.在/etc/my.cnf配置远程访问的权限插件

  1. [root@iZuligp6e1dyzfZ bin]# vim /etc/my.cnf

image.png

  1. [client]
  2. port=3306
  3. # mysql socket 文件存放地址
  4. socket=/var/lib/mysql/mysql.sock
  5. # 默认字符集
  6. default-character-set=utf8mb4
  7. [mysqld]
  8. port=3306
  9. character_set_server=utf8mb4
  10. basedir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/
  11. datadir=/local/mysql/mysql8.0/mysql-8.0.11-linux-glibc2.12-x86_64/data/
  12. socket=/var/lib/mysql/mysql.sock
  13. # Disabling symbolic-links is recommended to prevent assorted security risks
  14. symbolic-links=0
  15. # Settings user and group are ignored when systemd is used.
  16. # If you need to run mysqld under a different user or group,
  17. # customize your systemd unit file for mariadb according to the
  18. # instructions in http://fedoraproject.org/wiki/Systemd
  19. default_authentication_plugin=mysql_native_password
  20. [mysqld_safe]
  21. # log-error=/var/log/mariadb/mariadb.log
  22. # pid-file=/var/run/mariadb/mariadb.pid
  24. #
  25. # include all files from the config directory
  26. #
  27. # !includedir /etc/my.cnf.d
  29. ~

B.设置远程主机访问以及新的密码加密规则并刷新权限

  1. [root@iZuligp6e1dyzfZ bin]# mysql -uroot -p
  2. Enter password:
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 12
  5. Server version: 8.0.11 MySQL Community Server - GPL
  7. Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
  16. Query OK, 0 rows affected (0.05 sec)
  18. mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
  19. Query OK, 0 rows affected (0.07 sec)
  21. mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '000000';
  22. Query OK, 0 rows affected (0.09 sec)
  24. mysql> FLUSH PRIVILEGES;
  25. Query OK, 0 rows affected (0.00 sec)
  27. mysql>

image.png

C.远程连接成功

image.pngimage.png