在 CentOS 7 上 安装 MySQL 8.0.22 单实例

1. mysql 社区版下载

https://dev.mysql.com/downloads/mysql/

主要分为 3 个步骤：

• 选择 Linux-Generic

• 选择 glibc 2.12 的 64 位

• 下载 tar 包

2. mysql 家目录(basedir)准备工作

 # 创建 MySQL 的家目录
 mkdir -p /usr/local/
 # 解压
 tar -xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
 # 更名
 mv mysql-8.0.22-linux-glibc2.12-x86_64 mysql
 # 添加 mysql 用户
 useradd mysql
 # 授权 mysql
 chown mysql:mysql -R /usr/local/mysql 

3. mysql 数据目录(datadir)准备工作

 # 创建 mysql 数据目录
 mkdir -p /data/mysql
 # 对数据目录授权
 chown mysql:mysql -R data/mysql

4. 修改 mysql 配置文件

 vim /etc/my.cnf
 ##################### mysql8.22 配置文件#############################################
 [client]
 port    = 3306
 socket  = /tmp/mysql.sock
 #default-character-set=utf8
  [mysql]
 #default-character-set=utf8
 ​
 [mysqld]
 port    = 3306
 socket  = /tmp/mysql.sock
 basedir = /usr/local/mysql
 datadir = /data/mysql
 open_files_limit    = 3072
 back_log = 103
 max_connections = 512
 max_connect_errors = 100000
 table_open_cache = 512
 external-locking = FALSE
 max_allowed_packet = 128M
 sort_buffer_size = 2M
 join_buffer_size = 2M
 thread_cache_size = 51
 tmp_table_size = 96M
 max_heap_table_size = 96M
 slow_query_log = 1
 slow_query_log_file = /data/mysql/slow.log
 log-error = /data/mysql/error.log
 long_query_time = 0.05
 server-id = 1233306
 log-bin = /data/mysql/mysql-bin
 sync_binlog = 1
 binlog_cache_size = 4M
 max_binlog_cache_size = 128M
 max_binlog_size = 1024M
 expire_logs_days = 7
 key_buffer_size = 32M
 read_buffer_size = 1M
 read_rnd_buffer_size = 16M
 bulk_insert_buffer_size = 64M
 character-set-server=utf8
 default-storage-engine=InnoDB
 binlog_format=row
 #gtid_mode=on                                                                                                                              
 #log_slave_updates=1                                                                                                                       
 #enforce_gtid_consistency=1                                                                                                                                                                                                                                    
 interactive_timeout=100                                                                                                                    
 wait_timeout=100                                                                                                                                                                                                                                             
 transaction_isolation = REPEATABLE-READ                                                                                                    
 innodb_buffer_pool_size = 1434M                                                                                                            
 innodb_data_file_path = ibdata1:1024M:autoextend                                                                                           
 innodb_flush_log_at_trx_commit = 1                                                                                                         
 innodb_log_buffer_size = 16M                                                                                                               
 innodb_log_file_size = 256M                                                                                                                
 innodb_log_files_in_group = 2                                                                                                              
 innodb_max_dirty_pages_pct = 50                                                                                                            
 innodb_file_per_table = 1                                                                                                                  
 [mysqldump]                                                                                                                                
 quick                                                                                                                                      
 max_allowed_packet = 32M     

5. 初始化&启动 mysql

 # 配置 mysql 环境变量
 vim /etc/profile
 ......
 export MYSQL_HOME=/usr/local/mysql
 export PATH=${MYSQL_HOME}/bin:$PATH
 ......
 [root@hadoop001 ~]# source /etc/profile
 # 初始化
 /usr/local/mysql/bin/mysqld  --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql/ --initialize-insecure
 # 启动
 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

6. 修改 mysql 密码

 # 进入mysql
 $MYSQL_HOME/bin/mysql
 use mysql;
 # 修改密码
 update user set authentication_string='' where user='root';
 alter user 'root'@'localhost' identified by 'root';
 # 修改mysql远程连接
 update user set host='%' where user='root';
 # 刷新权限
 flush privileges;

7. 验证

 [root@hadoop001 bin]# ./mysql -uroot -p
 Enter password: 
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 12
 Server version: 8.0.22 MySQL Community Server - GPL
 ​
 Copyright (c) 2000, 2020, 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> 

8. 账户与安全

8.1 创建用户与授权

注意：mysql 8.0 创建用户和用户授权命令必须分开写

 # 先创建用户
 mysql> CREATE user 'test'@'%' identified by '1qaz@WSX';
 # 然后授权，注意授权的地方不要加上 identified by 'password'
 mysql> GRANT SELECT  on *.* to 'test'@'%';
 # 查看权限
 mysql> SELECT * FROM user where user='test' \G;
 *************************** 1. row ***************************
                     Host: %
                     User: test
              Select_priv: Y
              Insert_priv: N
              Update_priv: N
              Delete_priv: N
              Create_priv: N
                Drop_priv: N
              Reload_priv: N
            Shutdown_priv: N
             Process_priv: N
                File_priv: N
               Grant_priv: N
          References_priv: N
               Index_priv: N
               Alter_priv: N
             Show_db_priv: N
               Super_priv: N
    Create_tmp_table_priv: N
         Lock_tables_priv: N
             Execute_priv: N
          Repl_slave_priv: N
         Repl_client_priv: N
         Create_view_priv: N
           Show_view_priv: N
      Create_routine_priv: N
       Alter_routine_priv: N
         Create_user_priv: N
               Event_priv: N
             Trigger_priv: N
   Create_tablespace_priv: N
                 ssl_type: 
               ssl_cipher: 0x
              x509_issuer: 0x
             x509_subject: 0x
            max_questions: 0
              max_updates: 0
          max_connections: 0
     max_user_connections: 0
                   plugin: caching_sha2_password
    authentication_string: $A$005$•!k•••:iDbha9•iABktjfU3m5ov6hUeLHZeGc0faWmzZQODV80jHgB0XN7bK.
         password_expired: N
    password_last_changed: 2020-12-02 23:50:14
        password_lifetime: NULL
           account_locked: N
         Create_role_priv: N
           Drop_role_priv: N
   Password_reuse_history: NULL
      Password_reuse_time: NULL
 Password_require_current: NULL
          User_attributes: NULL
 1 row in set (0.01 sec)
 ​

8.2 修改用户认证插件

 mysql> alter user  'root'@'%'  identified  with mysql_native_password  by 'password';
 mysql> flush privileges;

修改配置文件，然后重启 mysql

 vim /etc/my.cnf 
 [mysqld]
 default-authentication-plugin=mysql_native_password

查看用户认证插件

 mysql> show variables like 'default_authentication%';
 +-------------------------------+-----------------------+
 | Variable_name                 | Value                 |
 +-------------------------------+-----------------------+
 | default_authentication_plugin | caching_sha2_password |
 +-------------------------------+-----------------------+
 1 row in set (0.15 sec)
 ​

8.3 密码管理

 mysql> show variables like 'password%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | password_history         | 0     |
 | password_require_current | OFF   |
 | password_reuse_interval  | 0     |
 +--------------------------+-------+
 3 rows in set (0.00 sec)

9. mysql远程客户端测试验证

10. 配置 mysql 开机自启动

 vim /usr/lib/systemd/system/mysql.service
 ​
 [Unit]
 Description=MySQL Server
 Documentation=man:mysqld(8)
 Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
 After=network.target
 After=syslog.target
 ​
 [Install]
 WantedBy=multi-user.target
 ​
 [Service]
 User=mysql
 Group=mysql
 ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql
 LimitNOFILE = 5000

 # 启动MySQL 服务
 [root@hadoop001 ~]# systemctl start mysql.service
 Warning: mysql.service changed on disk. Run 'systemctl daemon-reload' to reload units.
 # 设置开机自启动
 [root@hadoop001 ~]# systemctl enable mysql.service