Debian11 MySQL8 Install & Configuration

Prepare

官网下载

https://downloads.mysql.com/archives/community/

https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-server_8.0.31-1debian11_amd64.deb-bundle.tar

https://downloads.mysql.com/archives/get/p/23/file/mysql-server_5.7.33-1debian10_amd64.deb-bundle.tar
帐密

192.168.56.159
root / xxxxxxxxxxxx

Install

创建安装目录
```
mkdir -pv /usr/local/mysql/
```

将安装包通过SFTP上传至Debian11服务器中指定目录，这里放在/opt/mysql目录下

mkdir -pv /opt/mysql & cd /opt/ & ls -l;

或者

wget -p /opt https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-server_8.0.31-1debian11_amd64.deb-bundle.tar

解压至安装目录，依照需要安装的MySQL版本选择

MySQL 8.0.31

tar -xvf /opt/mysql-server_8.0.31-1debian11_amd64.deb-bundle.tar -C /opt/mysql

MySQL 5.7.33

tar -xvf /opt/mysql-server_5.7.33-1debian10_amd64.deb-bundle.tar -C /opt/mysql

安装debconf-utils配置预设
```
apt install -y debconf-utils
```
设置MySQL的apt安装预设，(lowercase-table-names只能在初始化时配置才能生效)
```
debconf-set-selections <<< "mysql-server mysql-server/lowercase-table-names select Enabled"
```
安装MySQL
```
apt install /opt/mysql/*.deb
```
查看MySQL运行状态
```
systemctl status mysql
```
查看端口占用
```
netstat -lnpt
```

编辑MySQL配置文件，并修改默认端口

vim /etc/mysql/conf.d/mysql.cnf

**简单配置**如下

[client]
default-character-set=utf8MB4

[mysql]

[mysqld]
port=33096

**MySQL MGR Master**配置文件如下

# Copyright (c) 2015, 2022, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

#
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
default-character-set=utf8MB4

[mysql]

[mysqld]
port=33096

datadir=/var/lib/mysql
socket=/tmp/mysql.sock
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
symbolic-links=0
#lower_case_table_names=1

server-id=10001
log-bin=mysql-bin
log-bin-index=binlogs.index
binlog_format=row
binlog_rows_query_log_events=on
binlog_checksum=none

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=1

# GTID
gtid_mode=on
enforce_gtid_consistency=1
log-slave-updates=1
binlog_gtid_simple_recovery=1

#relay_log=/usr/mysql/relay.log
#relay-log-index=/usr/mysql/relay.index
master_info_repository=table
relay_log_info_repository=table

# MGR
plugin_load="group_replication=group_replication.so"

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name='665e4f5b-77be-4c84-ad23-b924af66037b'
#loose-group_replication_ip_whitelist='127.0.0.1/8,192.168.56.0/24,10.10.10.0/24'
loose-group_replication_start_on_boot=off
loose-group_replication_local_address='192.168.56.159:33097'
#loose-group_replication_group_seeds='192.168.56.159:33097,192.168.56.158:33097,192.168.56.157:33097'
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight = 50

skip-host-cache
skip-name-resolve
skip-external-locking
character-set-server=utf8MB4
#lower_case_table_names=1
event_scheduler=on
log_bin_trust_function_creators=on

#max_connections=3000
external-locking=FALSE
#max_allowed_packet=32M
#sort_buffer_size=8M
#join_buffer_size=2M
thread_cache_size=300
#query_cache_size=64M
#query_cache_limit=4M
#read_rnd_buffer_size=8M

#innodb_buffer_pool_size=8096M
#innodb_log_file_size=128M
innodb_log_files_in_group=2
#innodb_log_buffer_size=2M
innodb_flush_log_at_trx_commit=1
sync_binlog=1

[mysqld_safe]
log-error=/var/log/mysqld.log

重启MySQL
```
systemctl restart mysql
```
查看MySQL运行状态
```
systemctl status mysql
```

查看MySQL运行日志

tail -f -n 500 /var/log/mysql/error.log

配置数据库

进入MySQL
```
mysql -uroot -pxMqPnvXmZaGD
```

远程访问的授权

create user 'root'@'%' identified with mysql_native_password by 'xxxxxxxxxxxx';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

修改加密规则

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER;
grant all privileges on *.* to 'root'@'localhost' identified by 'xxxxxxxxxxxx' with grant option;
flush privileges;

退出
```
exit
```

确认大小写敏感配置
```
show global variables like "%lower%"
```
配置防火墙
- 查看MySQL使用端口33096
```
netstat -lnpt
```
- 开放端口，这里使用33096端口
```
ufw allow 33096/tcp
```
- 查看防火墙，已开放端口列表
```
ufw status
```