Ubuntu系统部署 MySQL 8 双主

1 下载 MySQL 8

sudo -s
wget https://cdn.mysql.com/archives/mysql-8.1/mysql-8.1.0-linux-glibc2.28-x86_64.tar
1
2

2 安装依赖

apt-get -y install libaio1 libaio-dev libtinfo5
#centos 出现 glibc 问题：
[root@host mysql]# rpm -qa | grep glibc
glibc-headers-2.17-326.el7_9.x86_64
glibc-common-2.17-326.el7_9.x86_64
glibc-devel-2.17-326.el7_9.x86_64
glibc-2.17-326.el7_9.x86_64

需要下载 glibc-2.17 的安装包
1
2
3
4
5
6
7
8
9

3 创建 MySQL 用户指定默认组

groupadd mysql
useradd -g mysql mysql -s /usr/sbin/nologin
1
2

4 将下载好的 MySQL 安装包移动支 /opt 下， 并解压、更改文件夹权限

cp mysql-8.1.0-linux-glibc2.28-x86_64.tar /opt
cd /opt
tar -xf mysql-8.1.0-linux-glibc2.28-x86_64.tar 
tar -xf mysql-8.1.0-linux-glibc2.28-x86_64.tar.xz

mv mysql-8.1.0-linux-glibc2.28-x86_64/ mysql
mkdir /opt/mysql/data
mkdir /opt/mysql/logs
chown -R mysql:mysql /opt/mysql
# tree -d -L 1 mysql
1
2
3
4
5
6
7
8
9
10

5 创建配置文件my.cnf 并保存

cat >> /etc/my.cnf << EOF
[mysqld]
datadir=/opt/mysql/data
basedir=/opt/mysql
pid-file=/opt/mysql/mysql.pid
socket=/tmp/mysql.sock
log-error=/opt/mysql/logs/error.log

character_set_server=utf8mb4
collation-server=utf8mb4_general_ci

default-storage-engine=INNODB
lower_case_table_names=1
table_open_cache=128
max_connections=2000
max_connect_errors=6000
innodb_file_per_table=1
innodb_buffer_pool_size=1G
max_allowed_packet=64M
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=1800
innodb_flush_log_at_trx_commit=0
sync_binlog=0
group_concat_max_len=1024000
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
skip-name-resolve
#单机部署需删除下段
server-id=101
log-bin=mysql-bin
auto_increment_increment=2 #主节点数量
auto_increment_offset=1 #第二台写2
expire_logs_days=30
##################
[mysql]
default-character-set=utf8

[mysql.server]
default-character-set=utf8

[client]
default-character-set=utf8mb4
EOF
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

6 更新 MySQL 路径

sudo vim /etc/profile    //全局
export PATH=$PATH:/opt/mysql/bin

sudo vim .bashrc        // 当前用户
export PATH=$PATH:/opt/mysql/bin
1
2
3
4
5

7 初始化 MySQL

/opt/mysql/bin/mysqld --initialize --user mysql --basedir=/opt/mysql --datadir=/opt/mysql/data
# 由于在 my.cnf 中已经指定了 log error 的路径，因此初始化的过程中所有的输出包括密码都保存在了指定的路径和log文件中
# /usr/local/mysql/logs/error.log

Ja*p3mQC3Qv;  LS/VOn*lt4G
1
2
3
4
5

8 准备启动脚本

cp /opt/mysql/support-files/mysql.server /etc/init.d/
# 可查看 /etc/init.d/mysql.server 中的配置内容 datadir和basedir 是否和实际匹配

/etc/init.d/mysql.server status
/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
1
2
3
4
5
6

9 启动 MySQL 并修改初始密码

mysql -uroot -p     ＃ 初始密码可在 /usr/local/mysql/logs/error.log 中找到， 如果在my.cnf中没指定error.log 路径则在初始化的过程中会出现

ALTER USER USER() IDENTIFIED BY 'Password123@mysql';

update mysql.user set Host='%' where User='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
flush privileges;

# 创建DataEase库
CREATE DATABASE `dataease` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

# 验证密码
mysql -uroot -pPassword123@mysql -h192.168.1.142
1
2
3
4
5
6
7
8
9
10
11
12
13

10 设置开机自启

update-rc.d mysql.server defaults
# centos7
echo "/etc/init.d/mysql.server start" >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local

1
2
3
4
5

11 双主

A:28.15
B:28.16
---
A B:
CREATE USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
CREATE USER 'slave_sync_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'slave_sync_user'@'%';

A:
show master status;
B:
CHANGE MASTER TO MASTER_HOST='192.168.28.15', MASTER_USER='slave_sync_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=952;
start slave;
show slave status\G;

show master status;

A:
CHANGE MASTER TO MASTER_HOST='192.168.28.16', MASTER_USER='slave_sync_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1199;
start slave;
show slave status\G;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23