二进制安装mysql8.0

MySQL下载地址如下

• http://dev.mysql.com/downloads/mysql/

• 卸载mariadb

[root@gitlab ~]# yum -y remove mariadb-libs

• 创建目录和用户

[root@gitlab ~]# mkdir /data
[root@gitlab ~]# useradd mysql -M -s /sbin/nologin

• 解压

[root@gitlab ~]# tar -xJvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz 
[root@gitlab ~]# mv mysql-8.0.20-linux-glibc2.12-x86_64 /data/mysql

• 创建数据目录和日志目录

[root@gitlab ~]# mkdir /data/mysql/data
[root@gitlab ~]# mkdir /data/mysql/logs

• 安装依赖

[root@gitlab ~]# yum install gcc gcc-c++ openssl openssl-devel libaio libaio-devel  ncurses  ncurses-devel

• 授权

[root@gitlab ~]# chown mysql.mysql -R /data/mysql

• 初始化

[root@gitlab ~]# /data/mysql/bin/mysqld --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data --initialize
2020-07-11T12:44:24.735608Z 0 [System] [MY-013169] [Server] /data/mysql/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process 1577
2020-07-11T12:44:24.742834Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-11T12:44:25.158608Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-07-11T12:44:25.836921Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Opcjv>;uf0wz

• 创建日志文件

[root@gitlab ~]# touch /data/mysql/logs/mysql.log
[root@gitlab ~]# touch /data/mysql/logs/mysql-error.log
[root@gitlab ~]# chown mysql.mysql -R /data/mysql/logs/*

• 新建mysql配置文件 my.cnf

cat > /etc/my.cnf << EOF
[client]
#no-beep
socket =/data/mysql/mysql.sock
# pipe
# socket=0.i0
port=3306
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
port=3306
pid-file=/data/mysql/mysqld.pid
bind-address=0.0.0.0
#skip-grant-tables
skip-name-resolve
socket = /data/mysql/mysql.sock
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
default-time-zone = '+8:00'
#字符集
character-set-client-handshake = FALSE
character_set_filesystem = utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
skip-character-set-client-handshake = true

# Server Id
server-id=1
log-bin = mysql-bin
max_connections=2000
table_open_cache=2000
tmp_table_size=246M
thread_cache_size=300

#此参数在mysql8.0中是没有的，需要注释，
#query_cache_size=0

#logs初始化时如果指定配置文件并配置好了log，密码相关信息会写入logs中不会前台打印，日志文件需要提前手动创建
general_log = ON
general_log_file = /data/mysql/logs/mysql.log
log_error = /data/mysql/logs/mysql-error.log


#限定用于每个数据库线程的栈大小。默认设置足以满足大多数应用
thread_stack = 192k
key_buffer_size=512M
read_buffer_size=4M
read_rnd_buffer_size=32M
innodb_data_home_dir = /data/mysql/data
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=16M
innodb_buffer_pool_size=256M
innodb_log_file_size=256M
innodb_thread_concurrency=128
innodb_autoextend_increment=1000
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=128M
max_allowed_packet=1024M
max_connect_errors=2000
open_files_limit=4161
sort_buffer_size=32M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
#批量插入数据缓存大小，可以有效提高插入效率，默认为8M
bulk_insert_buffer_size = 64M
interactive_timeout = 28800
wait_timeout = 28800
log-bin-trust-function-creators=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE
EOF

• mysqld配置,拷贝启动文件到/etc/init.d/下并重命名为mysqld

[root@gitlab ~]# cp /data/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@gitlab ~]# chmod +x /etc/init.d/mysqld
[root@gitlab ~]# vim /etc/init.d/mysqld
basedir=/data/mysql
datadir=/data/mysql/data

• 启动服务

[root@gitlab ~]# service mysqld start
Starting MySQL.. SUCCESS! 
[root@gitlab ~]# echo "export PATH=${PATH}:/data/mysql/bin"  >>  /etc/profile
[root@gitlab ~]# source /etc/profile

• 修改原始密码

[root@gitlab ~]# mysql -uroot -p
Enter password: 
mysql> alter user 'root'@'localhost' identified by 'GUo2020.lucky';
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

• 修改IP限制

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> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'GUo2020.lucky' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'GUo2020.lucky';
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, authentication_string, plugin from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *7A7ED96FCE77DAA96E0A45D05F2E5D845E8A2E70                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)