Linux环境 MySQL 8.0 安装

一、官网下载压缩包

1.1 查看glibc版本

[root@mysql8-s ~]# ldd --version

1.2 下载安装包

官网：MySQL :: Download MySQL Community Server (Archived Versions)

二、安装依赖包

2.1 rpm安装

[root@mysql8-m ~]# rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm numactl-2.0.12-5.el7.x86_64.rpm

2.2 yum安装

[root@mysql8-m ~]# yum install libaio

三、安装MySQL8.0

3.1 创建用户组及用户

[root@mysql8-m ~]# groupadd mysql

[root@mysql8-m ~]# useradd -g mysql mysql

3.2 解压

-- 解压到数据库目录下

[root@mysql8-s ~]# tar -xJf mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz -C /home/mysql/

-- 将目录重命名

[root@mysql8-s ~]# cd /home/mysql/
[root@mysql8-s mysql]# mv mysql-8.0.36-linux-glibc2.17-x86_64 mysql

-- 变更用户组及用户

[root@mysql8-s mysql]# chown -R mysql:mysql /home/mysql/mysql*

3.3 修改配置文件

[root@mysql8-m ~]# echo 'export MYSQL_HOME=/home/mysql/mysql' >> /etc/profile
[root@mysql8-m ~]# echo 'export PATH=$PATH:$MYSQL_HOME/bin' >> /etc/profile

-- 保存配置

[root@mysql8-m ~]# source /etc/profile

[root@mysql8-m ~]# which mysql

3.4 编辑my.cnf通用配置

切换到mysql用户下进行操作

[mysql@mysql8-s ~]$ vim /home/mysql/mysql/my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4
prompt=[\d]>\_

[mysqld]
#基本配置
server_id = 1
bind-address = 0.0.0.0
port = 3306
basedir = /home/mysql/mysql
datadir = /home/mysql/data
log-error = /home/mysql/log/error.log
pid-file = mysqld.pid
socket = mysqld.sock
default_time_zone=+8:00
default_week_format=0
character_set_filesystem=binary
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
#init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
transaction-isolation = READ-COMMITTED
gtid_mode = on
enforce_gtid_consistency = 1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #MGR限制
event_scheduler = 1
plugin_load="group_replication=group_replication.so"

#连接相关
max_connections = 1024
thread_stack=256k #每个连接的初始化内存
thread_cache_size=256 #Thread Cache池中存放的连接线程数
connect_timeout=10
max_connect_errors = 512
back_log=3000
wait_timeout = 3600
interactive_timeout = 3600
net_read_timeout=3600 #在中止读取之前等待来自连接的更多数据的秒数。
net_write_timeout=3600
skip-name-resolve = 1
max_allowed_packet=1G
authentication_policy=mysql_native_password #8.0新参数,default_authentication_plugin=mysql_native_password(5.7) 
default_password_lifetime=0
skip-ssl=1
#skip-grant-tables

#内存相关
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 4
innodb_max_dirty_pages_pct = 75
key_buffer_size=32M
join_buffer_size=64M
sort_buffer_size=32M
read_rnd_buffer_size=64M
read_buffer_size=32M
tmp_table_size = 256M
max_heap_table_size = 256M

#IO相关
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads=8
innodb_page_cleaners=4 #与innodb_buffer_pool_instances相同
innodb_io_capacity = 400 #SAS 200 ;SSD 5000
innodb_io_capacity_max = 800
innodb_flush_neighbors=1
innodb_flush_method = O_DIRECT

#监控
performance_schema=ON
performance_schema_consumer_events_statements_current=1
performance_schema_consumer_events_statements_history=1
performance_schema_consumer_statements_digest=1
performance_schema_max_digest_length=8192 #默认值太短
performance_schema_max_sql_text_length=8192 

#SQL
sql_mode=NO_ENGINE_SUBSTITUTION
group_concat_max_len = 102400
eq_range_index_dive_limit=1000
max_execution_time = 0
ft_min_word_len = 4 #全文检索最小字符数
#sql_safe_updates=1

#日志
log_output=file
log_timestamps=system
slow-query-log = 1
slow_query_log_file = slow.log
long_query_time = 3
log_queries_not_using_indexes = 0

log-bin = binlog
binlog-format = ROW
binlog_row_image=full
binlog_checksum=none
sync_binlog = 1
binlog_transaction_dependency_tracking=WRITESET
max_binlog_size=258M
max_binlog_cache_size=1G
log_bin_trust_function_creators = 1
binlog_expire_logs_seconds=604800
#expire_logs_days = 7

relay-log = relaylog
relay-log-recovery = 1
sync_relay_log = 1000

innodb_online_alter_log_max_size = 2G 
#general_log=0
#general_log_file=general.log

#主从
read_only = 0
replica_net_timeout=30 #8.0新参数,slave-net-timeout=30(5.7)
skip_replica_start=1 #8.0新参数,skip_slave_start=1(5.7)
log_replica_updates=1 #8.0新参数,log-slave-updates = 1(5.7)
replica_parallel_type=LOGICAL_CLOCK #8.0新参数,slave-parallel-type=LOGICAL_CLOCK(5.7)
replica_parallel_workers=4 #8.0新参数,slave-parallel-workers=4(5.7)
replica_exec_mode = idempotent
replica_preserve_commit_order=1 #8.0新参数,slave_preserve_commit_order=1 (5.7)
relay-log-info-repository = TABLE
master-info-repository = TABLE
sync_source_info=1000 #8.0新参数,sync_master_info=1000(5.7)
sync_relay_log_info = 1000
#slave_skip_errors=1032,1062,ddl_exist_errors,all

#启动关闭
innodb_fast_shutdown=0 #0,1,2
innodb_buffer_pool_dump_pct=20
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_use_native_aio=on
#innodb_force_recovery=6 #1,2,3,4,5,6

#Innodb
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:16G
innodb_flush_log_at_trx_commit = 1
innodb_log_files_in_group = 2
innodb_ft_max_token_size = 84
innodb_log_buffer_size = 200M
innodb_log_file_size = 1G
innodb_lock_wait_timeout=30
innodb_open_files = 4096
open_files_limit = 65536
table_open_cache = 4096
thread_cache_size = 128
innodb_ft_min_token_size = 3
innodb_doublewrite=1

port：端口号 3306
socker：IP和port
datadir：数据路径
log-error：错误文件文位置
pid-file ：pid文件位置
character-set-server ：字符集
lower_case_table_names：是否忽略表大小写 1为忽略
autocommit：自动提交 1为是

3.5 数据库初始化

[mysql@mysql8-m ~]$ mkdir -p /home/mysql/{data,log}

[mysql@mysql8-s ~]$  /home/mysql/mysql/bin/mysqld --defaults-file=/home/mysql/mysql/my.cnf --user=mysql --datadir=/home/mysql/data --initialize-insecure

3.6 启动数据库

-- 复制mysql.server为mysqld并授权

[root@mysql8-s ~]# cp /home/mysql/mysql/support-files/mysql.server /etc/init.d/mysqld && chmod 755 /etc/init.d/mysqld

-- 创建/etc/my.cnf软连接

[root@mysql8-s mysql]# ln -s /home/mysql/mysql/my.cnf /etc/my.cnf

-- 启动数据库

[root@mysql8-s mysql]# /etc/init.d/mysqld start

---

其他启动数据库方式：

nohup ./mysqld_safe --defaults-file=../my.cnf --user=mysql & 
# 配置文件具体路径，根据实际情况来。

四、设置账号密码

-- 设置root密码
alter user 'root'@'localhost' identified WITH mysql_native_password by 'root';

-- 刷新策略
FLUSH PRIVILEGES;

五、可能出现的问题

5.1 问题一

问题描述：

下载的MySQL glibc版本与本机glibc版本不一致，数据库初始化报错如下：

解决方式：

重新下载版本一致的mysql。

---

记录两个查看此报错版本的命令

[root@mysql8-s mysql]# find / -name libstdc++.so.6
[root@mysql8-s mysql]# strings /usr/lib64/libstdc++.so.6 | grep 'CXXABI'

5.2 问题二

问题描述：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

解决方式：

创建一个软连接

[mysql@node1 ~]$ ln -s /home/mysql/data/mysqld.sock /tmp/mysql.sock