基本环境

  • 系统环境：debian12
  • mysql版本：mysql-5.7.44

mysql基本安装

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
## 下载二进制包
root@master-100:~# wget -c https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

## 解压移动到部署目录
root@master-100:~# tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
root@master-100:~# mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql

## 创建其他目录
root@master-100:/usr/local/mysql# mkdir run log etc data mysql-files
## 基本配置文件path/etc/my.cnf
[mysqld]
skip-host-cache
skip-name-resolve
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/run/mysqld.sock
secure-file-priv = /usr/local/mysql/mysql-files
user = mysql
port = 3306
symbolic-links = 0
bind-address = 0.0.0.0
log-error = /usr/local/mysql/log/mysqld.log
pid-file = /usr/local/mysql/run/mysqld.pid
default-time_zone = '+8:00'
max_connections = 3000
binlog_cache_size = 50M
max_binlog_cache_size = 100M 
max_binlog_size = 5M 
max_allowed_packet = 100M
default-storage-engine = InnoDB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
socket=/usr/local/mysql/run/mysqld.sock

## 安装依赖包
apt update && apt upgrade -y && apt install libncurses5 libaio1

## 创建mysql用户
useradd -M -s /sbin/nologin -r mysql

## 目录权限
chown -R mysql:mysql /usr/local/mysql

## 确保环境变量包含/usr/local/mysql/bin
root@master-100:/usr/local/mysql# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/elastic/jdk/bin:/usr/local/node/bin:/usr/local/mysql/bin:/usr/local/mongo/bin:/usr/local/redis/bin

## 初始化
root@master-100:/usr/local/mysql# mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --initialize

## 查看日志，保存生成的密码，首次连接时使用
root@master-100:/usr/local/mysql# tail -5 log/mysqld.log 
2023-12-24T07:24:07.461223Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-12-24T07:24:08.628106Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-12-24T07:24:08.628121Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-12-24T07:24:08.630096Z 0 [Warning] CA certificate ca.pem is self signed.
2023-12-24T07:24:09.099864Z 1 [Note] A temporary password is generated for root@localhost: aBu&f1h(yTLy

## 启动
root@master-100:/usr/local/mysql# cp -a support-files/mysql.server /etc/init.d/mysqld
root@master-100:/usr/local/mysql# /etc/init.d/mysqld start
Starting mysqld (via systemctl): mysqld.service.
root@master-100:/usr/local/mysql# /etc/init.d/mysqld status
● mysqld.service - LSB: start and stop MySQL
     Loaded: loaded (/etc/init.d/mysqld; generated)
     Active: active (running) since Sun 2023-12-24 15:30:08 CST; 3s ago

## 登录验证
root@master-100:/usr/local/mysql# mysql -uroot -p'aBu&f1h(yTLy'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44

Copyright (c) 2000, 2023, 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> 

## 尝试操作，要求更改密码
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

## 之后在另一台从服务器上进行同样安装操作

配置主从异步复制

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
## 数据同步，主服务器备份
root@master-100:~# mysqldump -uroot -p123456  --single-transaction --opt --all-databases > ./bak.sql

## 从服武器导入
root@slave-101:~# mysql -uroot -p123456 < ./bak.sql 

## 主服务器创建复制用户
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)


## 主服务器配置文件追加
### 异步
server-id = 1
log-bin = /usr/local/mysql/log/mysql-bin
binlog_format = mixed

## 从服务器配置文件追加
### 异步
server-id=2
relay-log=/usr/local/mysql/log/relay-bin
relay-log-index=/usr/local/mysql/log/relay-bin.index

## 重启后登录主服务器查看
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      590 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

## 重启后登陆从服务器配置
mysql> change master to
    ->   master_host='192.168.250.100',
    ->   master_port=3306,
    ->   master_user='slave',
    ->   master_password='123456',
    ->   master_log_file='mysql-bin.000001',
    ->   master_log_pos=590;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

## 开启同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.250.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 590
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

## 主服务器创建
mysql> create database testdata;
Query OK, 1 row affected (0.02 sec)
mysql> use testdata
Database changed
mysql> create table `test` (
    ->   `id` int(10) not null auto_increment,
    ->   `time` varchar(255) collate utf8mb4_unicode_ci not null,
    ->   primary key (`id`)
    -> ) engine=innodb auto_increment=12 default charset=utf8mb4 collate=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test values(1,12),(2,13),(3,15),(4,20);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

## 从服务器检查，同步生效
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdata           |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from testdata.test;
+----+------+
| id | time |
+----+------+
|  1 | 12   |
|  2 | 13   |
|  3 | 15   |
|  4 | 20   |
+----+------+
4 rows in set (0.00 sec)

半同步配置

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
44
### 半同步，主服务器
plugin-load = rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_wait_no_slave = 1
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 2000

## 半同步，从服务器
plugin-load = rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

## 重启验证
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 2000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+

mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)