一、安装说明

本文使用二进制包安装及简单配置MySQL-v8.0.24版本。

8.0版本下载：

本文中使用本地VM虚机部署测试。

OS：CentOS Linux release 7.8.2003 (Core) 3.10.0-1160.15.2.el7.x86_64

虚机配置：2核CPU、4G内存

二、清理系统环境

CentOS 7 版本的系统默认自带安装了MariaDB，需要先清理。

## 查询已安装的mariadb

rpm -qa |grep mariadb

## 卸载mariadb包，文件名为上述命令查询出来的文件

rpm -e --nodeps mariadb-libs-5.5.65-1.el7.x86_64

三、创建mysql用户.组及数据目录

## 创建数据保存目录mkdir -p /data/mysql## 创建用户组mysqlgroupadd -r mysql## 创建用户mysql并设置不能登录系统，指定宿主目录useradd -g mysql -r -s /sbin/nologin -M -d /data/mysql mysql## 修改数据保存目录属性及权限chown mysql.mysql /data/mysqlchmod 0700 /data/mysql

四、安装MySQL

1.下载二进制包

cd /data/toolswget 

2.解压安装到指定目录(/usr/local)

tar xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz -C /usr/local/cd /usr/local## 创建软链接，方便后续操作ln -sv mysql-8.0.24-linux-glibc2.12-x86_64 mysql

五、配置MySQL

1.创建配置文件

vi /etc/my.cnf //输入以下内容[client]port = 3306socket = /usr/local/mysql/mysql.sockdefault-character-set=utf8mb4[mysqld]port = 3306user = mysqlsocket = /usr/local/mysql/mysql.sockbasedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4collation-server = utf8mb4_general_ciinit_connect='SET NAMES utf8mb4'lower_case_table_names = 1skip-external-lockingskip-name-resolveserver-id = 1transaction_isolation = REPEATABLE-READexplicit_defaults_for_timestamp = truethread_stack = 512Kexternal-locking = FALSEgtid_mode = ONenforce_gtid_consistency = ON##最大连接数max_connections=5000max_connect_errors = 1000back_log = 300##二进制日志log-bin = /data/mysql/mysql-binbinlog_format = ROWbinlog_row_image = FULLbinlog_expire_logs_seconds = 0binlog_cache_size=16Mmax_binlog_cache_size=1Gmax_binlog_size=512Msync_binlog = 1##慢日志slow_query_log = onslow_query_log_file = /data/mysql/db-slow.loglong_query_time = 1log_slow_admin_statements = 1#作为从库时生效log_slow_slave_statements = 1log-queries-not-using-indexes=0log_throttle_queries_not_using_indexes=60##缓冲区table_open_cache = 600table_definition_cache = 700table_open_cache_instances = 64max_allowed_packet = 32Msort_buffer_size = 16Mjoin_buffer_size = 8Mtmp_table_size = 64Mmax_heap_table_size = 64M##对MyISAM表起作用key_buffer_size = 128Mread_buffer_size = 8Mread_rnd_buffer_size = 32Mbulk_insert_buffer_size = 64Mthread_cache_size = 16##主从复制配置log_slave_updates=1relay-log = /data/mysqlrelay-log-recovery=on##innodb性能设置default_storage_engine = InnoDBinnodb_thread_concurrency = 0innodb_buffer_pool_size = 1Ginnodb_buffer_pool_instances = 8innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:10M:autoextendinnodb_log_buffer_size = 16Minnodb_log_files_in_group = 3innodb_log_file_size = 512Minnodb_undo_log_truncate = 1innodb_max_undo_log_size = 1G#innodb_undo_directory = /data/mysql_logs/undolog##CPU多核处理能力设置innodb_write_io_threads = 4innodb_read_io_threads = 4innodb_purge_threads = 2innodb_page_cleaners = 2innodb_open_files = 65535innodb_max_dirty_pages_pct = 50innodb_lock_wait_timeout = 60innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_autoinc_lock_mode = 1innodb_file_per_table = oninnodb_flush_log_at_trx_commit = 1##设置时区default_time_zone = "+8:00"##超时时间interactive_timeout = 3600wait_timeout = 3600sql_mode = NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES[mysqldump]quickmax_allowed_packet = 64M[mysql]no-auto-rehashdefault-character-set=utf8mb4[myisamchk]key_buffer_size = 64Msort_buffer_size = 64Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]log-error = /data/mysql/mysql_err.logpid-file = /data/mysql/mysqld.pid

注：请根据实际环境需求修改参数，此处配置仅供参考。

注意：my.cnf配置文件中若要指定binlog及relay-bin等日志目录路径不能在datadir目录下，否则初始化时会报错。因为初始化是datadir目录需为一个空目录。此文档my.cnf配置文件中去掉binlog及relay-bin日志目录设置。

## 创建日志目录并修改权限

mkdir -p /data/mysql_logs/{binlog,relay-bin,undolog}chown -R mysql.mysql /data/mysql_logs

2.权限设置

cd /usr/local/mysqlchown -R mysql:mysql .

3.初始化数据库

初始化方法一：

/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

初始化方法二：

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql

注：①"--initialize"会生成一个随机密码(~/.mysql_secret)，而"--initialize-insecure"不会生成密码

②--datadir目标目录下不能有数据文件

## 开启SSL连接，安全性提高了，但是相对地降低了QPS，还是比较影响性能的，谨慎选择！本文档中不开启！

/usr/local/mysql/bin/mysql_ssl_rsa_setup

4.安装MySQL的man帮助文件、头文件及lib库

cd /usr/local/mysql## 帮助文件echo "MANPATH /usr/local/mysql/man" >> /etc/man.config## 头文件ln -sv /usr/local/mysql/include /usr/include/mysql## lib库echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf## 加载动态库文件ldconfig

5.配置MySQL环境变量，方便操作命令的使用

echo '######MySQL' >> /etc/profileecho 'PATH=$PATH:/usr/local/mysql/bin' >> /etc/profilesource /etc/profile

6.配置MySQL服务启动命令

cd /usr/local/mysqlcp support-files/mysql.server /etc/rc.d/init.d/mysqld

7.启动数据库

/etc/init.d/mysqld start/usr/local/mysql/bin/mysql --version && echo -e  "\e[31m mysql install is OK\e[0m"

六、设置root账号密码

在MySQL 8.0.4前，执行：SET PASSWORD=PASSWORD(‘[新密码]’);但是MySQL8.0.4开始，这样是不行的。因为之前，MySQL的密码认证插件是“mysql_native_password”，而现在使用的是“caching_sha2_password”。可在配置文件my.cnf中使用选项“default_authentication_plugin = mysql_native_password”修改默认使用的密码插件认证为‘mysql_native_password’

①使用caching_sha2_password认证方式

## 设置本机登录密码

mysql -uroot -p //因初始化数据时没有设置密码，可以按两次确认键进入数据库 mysql> use mysql; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES;

## 设置远程密码

 mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;

②使用mysql_native_password认证方式

## 设置本机登录密码

mysql -uroot -p //因初始化数据时没有设置密码，可以按两次确认键进入数据库 mysql> use mysql; mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; mysql> FLUSH PRIVILEGES;

## 远程登录密码

 mysql> CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;

七、服务管理

启动：/etc/init.d/mysqld start

关闭：/etc/init.d/mysqld stop

重启：/etc/init.d/mysqld restart

查看状态：/etc/init.d/mysqld status

配置开机启动：echo "/etc/init.d/mysqld start" >> /etc/rc.local

八、测试

1.创建测试数据库

mysql -uroot -p//输入上述步骤中设置的密码登录数据库 mysql> CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; mysql> show databases;

2.创建普通用户及授权

mysql -uroot -p//输入上述步骤中设置的密码登录数据库## 切换至mysql库 mysql> use mysql;## 创建用户 mysql> CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'Pass@123';## 授权 mysql> GRANT select,insert,update,delete,create on test.* to 'test'@'%' ;## 刷新权限 mysql> FLUSH PRIVILEGES;## 查看用户 mysql> select user,host from user;## 查看test用户权限 mysql> show GRANTS for 'test'@'%';