Mysql8.0二进制安装

一、准备工作

1.配置Yum

[root@server01 ~]# mkdir /opt/yum
[root@server01 ~]# cp -r /run/media/admin/CentOS\ 7\ x86_64/*  /opt/yum

[root@server01 ~]# vi /etc/yum.repos.d/CentOS-Base.repo

[CentOS-Base]
baseurl=file:///opt/yum
gpgcheck=0
enabled=1

2.操作系统依赖包

Oracle Linux 8 / Red Hat 8 (EL8): These platforms by default do not install
the file /lib64/libtinfo.so.5, which is required by the MySQL client
bin/mysql for packages mysql-VERSION-el7-x86_64.tar.gz and
mysql-VERSION-linux-glibc2.12-x86_64.tar.xz. To work around
this issue, install the ncurses-compat-libs package:

shell> yum install ncurses-compat-libs

[root@server01 ~]# yum install libaio

3.二进制安装包

mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

4.安装步骤

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar xvf /path/to/mysql-VERSION-OS.tar.xz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

5.Mysql目录结构

二、安装

1.卸载Mariadb

[root@server01 ~]# rpm -qa |grep mariadb
[root@server01 ~]# yum remove mariadb-libs-5.5.68-1.el7.x86_64

[root@server01 ~]# mkdir /data
[root@server01 ~]# mkfs.xfs /dev/sdb

[root@server01 ~]# blkid
/dev/sda1: UUID="c07cdd4a-85e2-4a7a-8c09-30c9e59adf7c" TYPE="xfs"
/dev/sda2: UUID="3ixQnM-tYOF-c8F9-HVW8-Aum9-wOsE-ZcouP7" TYPE="LVM2_member"
/dev/sr0: UUID="2020-11-04-11-36-43-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos"
/dev/sdb: UUID="4db5c6b1-72a1-4bb1-a312-0f52ba13cc2e" TYPE="xfs"
/dev/mapper/centos-root: UUID="4fabfc61-86f9-4a39-a6a4-5d160f05db73" TYPE="xfs"

[root@server01 ~]# vi /etc/fstab

UUID="4db5c6b1-72a1-4bb1-a312-0f52ba13cc2e"      /data    xfs    defaults    0 0

2.创建用户和组

[root@server01 ~]# groupadd -g 600 mysql
[root@server01 ~]# useradd -r -g mysql -u 600 -s /bin/false mysql

温馨提示: -r参数指的是创建系统用户，该用户没有登录权限。

[root@server01 ~]# id mysql
uid=600(mysql) gid=600(mysql) groups=600(mysql)

3.解压安装包

[root@server01 soft]# mkdir /opt/mysql
[root@server01 soft]# tar xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz -C /opt/mysql

温馨提示:如果tar不支持z选项，则可以使用gunzip命令

[root@server01 soft]# xz -dc xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz | tar x

4.创建软件链接

[root@server01 soft]# ln -s /opt/mysql/mysql-8.0.26-linux-glibc2.12-x86_64 /usr/local/mysql

5.添加PATH环境变量

[root@server01 mysql]# vim /etc/profile

export PATH=/usr/local/mysql/bin:$PATH

温馨提示: 也可以使用sed命令添加

[root@server01 mysql]# sed -i '$a export PATH=/usr/local/mysql/bin:$PATH\n' /etc/profile

[root@server01 mysql]# source /etc/profile

[root@server01 mysql]# tail -2 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

6.创建目录及授权

[root@server01 mysql]# mkdir -p /data/mysql/3306/{tmp,log,binlog}
[root@server01 ~]# chown -R mysql.mysql /data

7.创建配置文件

[root@server01 ~]# cd /data/mysql/3306/
[root@server01 3306]# vi my.cnf

[mysqld]
port            = 3306
socket          = /tmp/mysql3306.sock
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
tmpdir=/data/mysql/3306/tmp
server_id=3306195
user=mysql
# mysqlx_port=33060
# admin_port=33062
# admin_address='127.0.0.1'
# create_admin_listener_thread=on

max_connections=500
max_user_connections=400
max_connect_errors=3000
# mysqlx_max_connections=300

#semi

#binlog
log-bin = /data/mysql/3306/binlog/mysql-bin
binlog_format = row
log_slave_updates
gtid_mode=on
enforce_gtid_consistency=on
binlog_cache_size = 4M
max_binlog_size = 512M
binlog_rows_query_log_events=on
sync_binlog = 1
binlog_group_commit_sync_delay=0
binlog_group_commit_sync_no_delay_count=0
binlog_order_commits=on
expire_logs_days = 8

transaction_write_set_extraction='XXHASH64'
binlog_transaction_dependency_tracking='writeset'
binlog_transaction_dependency_history_size=25000

#slow log
#slow_query_log=1
# log_long_query_time=0.5
log_slave_updates=on
slow_query_log_file=slow.log
#error log
log_error=/data/mysql/3306/log/error.log
log_error_verbosity=3

#session
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_stack = 192K
tmp_table_size = 96M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#timeout
interactive_timeout=300
wait_timeout=300
innodb_rollback_on_timeout=on
slave_net_timeout=30
rpl_stop_slave_timeout=180
lock_wait_timeout=300

#relay_log
relay_log=relay-bin
relay_log_info_file=relay-bin.index
relay_log_info_repository=table
relay_log_purge=on
sync_relay_log=10000
sync_relay_log_info=10000

#sql_thread
relay_log_recovery=ON
slave_preserve_commit_order=ON
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16

#innodb
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 5
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_io_capacity = 10000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_thread_concurrency= 0
innodb_read_io_threads     =8
innodb_write_io_threads    =8
innodb_print_all_deadlocks =on
innodb_deadlock_detect     =on
innodb_lock_wait_timeout  =30

###  for performance_schema
performance_schema                                                      =on
performance_schema_consumer_global_instrumentation                      =on    #    on
performance_schema_consumer_thread_instrumentation                      =on    #    on
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_statements_digest                           =on    #    on
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =off   #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance-schema-instrument                                           ='memory/%=COUNTED'

8.初如化目录

--initialize生成一个临时密码:

• 密码12位数字，4种字符组合
• 密码有效期180天
• 如果不想使用这种密码复杂策略，可以使用--initialize-insecure

如果初始化报如下错误:

error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

则安装以下包即可:
[root@server01 mysql]# yum install libaio-devel

[root@server01 ~]# mysqld  --defaults-file=/data/mysql/3306/my.cnf  --initialize

--explicit_defaults_for_timestamp
2021-07-14T15:04:08.596953Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-07-14T15:04:08.617238Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-07-14T15:04:08.684478Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bd581429-e4b4-11eb-8cfb-000c29b39c96.
2021-07-14T15:04:08.686754Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-07-14T15:04:08.689282Z 1 [Note] A temporary password is generated for root@localhost: JY!btpV5tpt_

[root@mysql ~]# ll /data/mysql/3306/data
total 6359352
-rw-r-----. 1 mysql mysql         56 Jul 22 14:28 auto.cnf
-rw-------. 1 mysql mysql       1680 Jul 22 14:28 ca-key.pem
-rw-r--r--. 1 mysql mysql       1112 Jul 22 14:28 ca.pem
-rw-r--r--. 1 mysql mysql       1112 Jul 22 14:28 client-cert.pem
-rw-------. 1 mysql mysql       1676 Jul 22 14:28 client-key.pem
-rw-r-----. 1 mysql mysql    1179648 Jul 22 14:28 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql    9568256 Jul 22 14:28 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql       6168 Jul 22 14:28 ib_buffer_pool
-rw-r-----. 1 mysql mysql 1073741824 Jul 22 14:28 ibdata1
-rw-r-----. 1 mysql mysql 1073741824 Jul 22 14:28 ib_logfile0
-rw-r-----. 1 mysql mysql 1073741824 Jul 22 14:28 ib_logfile1
-rw-r-----. 1 mysql mysql 1073741824 Jul 22 14:28 ib_logfile2
-rw-r-----. 1 mysql mysql 1073741824 Jul 22 14:28 ib_logfile3
-rw-r-----. 1 mysql mysql 1073741824 Jul 22 14:28 ib_logfile4
drwxr-x---. 2 mysql mysql          6 Jul 22 14:28 #innodb_temp
drwxr-x---. 2 mysql mysql        143 Jul 22 14:28 mysql
-rw-r-----. 1 mysql mysql   25165824 Jul 22 14:28 mysql.ibd
drwxr-x---. 2 mysql mysql       8192 Jul 22 14:28 performance_schema
-rw-------. 1 mysql mysql       1680 Jul 22 14:28 private_key.pem
-rw-r--r--. 1 mysql mysql        452 Jul 22 14:28 public_key.pem
-rw-r--r--. 1 mysql mysql       1112 Jul 22 14:28 server-cert.pem
-rw-------. 1 mysql mysql       1680 Jul 22 14:28 server-key.pem
drwxr-x---. 2 mysql mysql         28 Jul 22 14:28 sys
-rw-r-----. 1 mysql mysql   16777216 Jul 22 14:28 undo_001
-rw-r-----. 1 mysql mysql   16777216 Jul 22 14:28 undo_002

9.设置root密码

[root@server01 ~]# mysqld_safe --defaults-file=/data/mysql/3306/my.cnf &

[root@server01 ~]# mysql -S /tmp/mysql3306.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

10.配置SSL支持

[root@server01 data]# mysql_ssl_rsa_setup --help
mysql_ssl_rsa_setup  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2015, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

MySQL SSL Certificate and RSA Key Generation Utility
Usage : mysql_ssl_rsa_setup [OPTIONS]
  -?, --help          Display this help and exit.
  -v, --verbose       Be more verbose when running program
  -V, --version       Print program version and exit
  -d, --datadir=name  Directory to store generated files.
  -s, --suffix=name   Suffix to be added in certificate subject line
  --uid=name          The effective user id to be used for file permission

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE
datadir                           /usr/local/mysql/data
suffix                            8.0.26
uid                               (No default value)

[root@server01 data]# mysql_ssl_rsa_setup --datadir=/data/mysql/3306/data

(1) 查看SSL是否开启:
mysql> show variables like '%have_%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
+---------------+-------+
2 rows in set (0.00 sec)

11.配置systemd启动

1，创建启动文件 .service
[root@server01 ~]# cat > /usr/lib/systemd/system/mysqld3306.service <<EOF

[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
[Service]
User=mysql
Group=mysql
ExecStart= /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my.cnf
ExecStop= /usr/local/mysql/bin/mysqladmin -S /tmp/mysql3306.sock -p shutdown
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target

EOF

2，重新加载一下服务的配置文件
[root@server01 ~]# systemctl daemon-reload

3，开启mysql服务
[root@server01 ~]# systemctl  start  mysqld3306

4.关闭mysql
[root@server01 3306]# mysqladmin -S /tmp/mysql3306.sock -p shutdown

12.查看Mysql进程

[root@server01 ~]# ss -naolp |grep mysql
u_str  LISTEN     0      128    /tmp/mysql3306.sock 113879                * 0                   users:(("mysqld",pid=57743,fd=34))
u_str  LISTEN     0      70     /tmp/mysqlx.sock 113882                * 0                   users:(("mysqld",pid=57743,fd=36))
tcp    LISTEN     0      70     [::]:33060              [::]:*                   users:(("mysqld",pid=57743,fd=35))
tcp    LISTEN     0      128    [::]:3306               [::]:*                   users:(("mysqld",pid=57743,fd=32))

[root@server01 ~]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  57743 mysql   32u  IPv6 113878      0t0  TCP *:mysql (LISTEN)

三、多实例安装

创建一个端口3307的实例

1.创建目录及授权

[root@server01 ~]# mkdir -p /data/mysql/3307/{binlog,log,tmp}

[root@server01 ~]# ll /data/mysql/3307/
total 0
drwxr-xr-x. 2 root root 6 Jul 23 22:30 binlog
drwxr-xr-x. 2 root root 6 Jul 23 22:30 log
drwxr-xr-x. 2 root root 6 Jul 23 22:30 tmp

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

2.创建配置文件

[root@server01 ~]# cp /data/mysql/3306/my.cnf /data/mysql/3307/

[root@server01 ~]# sed -i 's/3306/3307/g' /data/mysql/3307/my.cnf

3.初始化

[root@server01 ~]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf --initialize

4.设置root密码

[root@server01 ~]# mysqld_safe --defaults-file=/data/mysql/3307/my.cnf &

[root@server01 ~]# mysql -S /tmp/mysql3307.sock -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@server01 ~]# mysql -S /tmp/mysql3307.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18

Copyright (c) 2000, 2019, 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>  alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

​```

5.配置systemd启动

1，创建启动文件 .service
[root@server01 ~]# cat > /usr/lib/systemd/system/mysqld3307.service <<EOF

[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
[Service]
User=mysql
Group=mysql
ExecStart= /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
ExecStop= /usr/local/mysql/bin/mysqladmin -S /tmp/mysql3307.sock -p shutdown
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target

EOF

2，重新加载一下服务的配置文件
[root@server01 ~]# systemctl daemon-reload

3，开启mysql服务
[root@server01 ~]# systemctl  start  mysqld3307

4.关闭mysql
[root@server01 ~]# systemctl  stop  mysqld3307
或
[root@server01 ~]# mysqladmin -S /tmp/mysql3307.sock -p shutdown

6.查看Mysql进程

[root@server01 ~]# ss -naolp |grep mysql
u_str  LISTEN     0      128    /tmp/mysql3307.sock 141713                * 0                   users:(("mysqld",pid=61132,fd=34))
u_str  LISTEN     0      128    /tmp/mysql3306.sock 113879                * 0                   users:(("mysqld",pid=57743,fd=34))
u_str  LISTEN     0      70     /tmp/mysqlx.sock 113882                * 0                   users:(("mysqld",pid=57743,fd=36))
tcp    LISTEN     0      70     [::]:33060              [::]:*                   users:(("mysqld",pid=57743,fd=35))
tcp    LISTEN     0      128    [::]:3306               [::]:*                   users:(("mysqld",pid=57743,fd=32))
tcp    LISTEN     0      128    [::]:3307               [::]:*                   users:(("mysqld",pid=61132,fd=32))

[root@server01 ~]# lsof -i:330{6,7}
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  57743 mysql   32u  IPv6 113878      0t0  TCP *:mysql (LISTEN)
mysqld  61132 mysql   32u  IPv6 141712      0t0  TCP *:opsession-prxy (LISTEN)