Mysql8.0主从复制安装

Mysql8.0主从复制安装

𝓓𝓸𝓷 Lv6
  2024-07-16 11:30:08 2024-07-16 11:30:08 Created   2025-01-26 09:01:34 2025-01-26 09:01:34 Updated    

Mysql8.0配置主从复制

一、环境

服务器IP 角色
192.168.1.171 Master
192.168.1.172 Slave01

二、配置yum

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
# cp -r /dev/cdrom /opt/yum
# ll /opt/yum/
total 328
lrwxrwxrwx 1 root root      3 Jan 13 02:39 cdrom -> sr0
-rw-r--r-- 1 root root     14 Jan 13 02:18 CentOS_BuildTag
drwxr-xr-x 3 root root     35 Jan 13 02:18 EFI
-rw-r--r-- 1 root root    227 Jan 13 02:18 EULA
-rw-r--r-- 1 root root  18009 Jan 13 02:18 GPL
drwxr-xr-x 3 root root     57 Jan 13 02:18 images
drwxr-xr-x 2 root root    198 Jan 13 02:18 isolinux
drwxr-xr-x 2 root root     43 Jan 13 02:18 LiveOS
drwxr-xr-x 2 root root 225280 Jan 13 02:19 Packages
drwxr-xr-x 2 root root   4096 Jan 13 02:19 repodata
-rw-r--r-- 1 root root   1690 Jan 13 02:19 RPM-GPG-KEY-CentOS-7
-rw-r--r-- 1 root root   1690 Jan 13 02:19 RPM-GPG-KEY-CentOS-Testing-7
-r--r--r-- 1 root root   2883 Jan 13 02:19 TRANS.TBL

# cat > /etc/yum.repos.d/CentOS-Base.repo <<EOF
[base]
name=CentOS-\$releasever - Base
baseurl=file:///opt/yum
enable=1
gpgcheck=0

EOF



# yum clean all && yum makecache

三、卸载旧版mysql

1
2
3
4
5
6
7
for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done  
  
rm -rf /var/lib/mysql  
rm -rf /etc/my.cnf  
rm -rf /usr/share/mysql   
rm -rf /etc/my.cnf.d  
rm -rf /etc/mysql

四、创建用户

1
2
3
groupadd -g 10000 mysql  
useradd -r -g mysql -u 10000 -s /bin/false 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
(1)下载mysql
[root@primary ~]# cd /opt/soft

[root@primary soft]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz

(2)解压
[root@primary soft]# tar xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz -C /usr/local

(3)创建软链接
[root@primary soft]# cd /usr/local/
[root@primary local]# ln -s mysql-8.0.28-linux-glibc2.12-x86_64/ mysql

[root@primary local]# ll
total 0
drwxr-xr-x. 2 root root   6 Apr 11  2018 bin
drwxr-xr-x. 2 root root   6 Apr 11  2018 etc
drwxr-xr-x. 2 root root   6 Apr 11  2018 games
drwxr-xr-x. 2 root root   6 Apr 11  2018 include
drwxr-xr-x. 2 root root   6 Apr 11  2018 lib
drwxr-xr-x. 2 root root   6 Apr 11  2018 lib64
drwxr-xr-x. 2 root root   6 Apr 11  2018 libexec
lrwxrwxrwx  1 root root  36 Jan 13 02:34 mysql -> mysql-8.0.28-linux-glibc2.12-x86_64/
drwxr-xr-x  9 root root 129 Jan 13 02:32 mysql-8.0.28-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Apr 11  2018 sbin
drwxr-xr-x. 5 root root  49 Nov 29 00:56 share
drwxr-xr-x. 2 root root   6 Apr 11  2018 src

(4)初始化数据库
# mkdir -p /data/mysql
# cd /usr/local/mysql/bin
# ./mysqld --initialize --user=mysql --datadir=/data/mysql

或

# cd /usr/local/mysql/bin
# ./mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
2025-01-12T21:20:18.159298Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.28-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 2231
2025-01-12T21:20:18.171485Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-01-12T21:20:18.798138Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-01-12T21:20:20.385516Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.


(5)配置my.cnf
[mysqld]

basedir=/usr/local/mysql
datadir=/data/mysql
character-set-server=utf8mb4
socket=/tmp/mysql.sock 

log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid


[mysql]  
default-character-set=utf8mb4  
socket=/tmp/mysql.sock  



---设置权限  
# chmod 664 /etc/my.cnf


(6)创建mysql服务
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
# chkconfig mysql on
# systemctl enable mysql


(7)配置环境变量
# cat >> /etc/profile <<EOF

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

EOF


# source /etc/profile

五、主库配置

1.配置主库参数
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
# vi /etc/my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=row
skip_name_resolve=ON

basedir=/usr/local/mysql
datadir=/data/mysql
character-set-server=utf8mb4
socket=/tmp/mysql.sock 

log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid

[mysql]  
default-character-set=utf8mb4  
socket=/tmp/mysql.sock  


----------------------------------------------
参数详解:
# 主从复制-主机配置
# 主服务器唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 设置需要复制的数据库(可设置多个)
binlog-do-db=test
# 设置logbin格式
binlog_format=STATEMENT

# 设置一个 binlog 文件的最大字节# 设置最大 100MB
max_binlog_size=104857600
# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7 
----------------------------------------------

2.创建数据库复制帐号
1
2
3
4
5
6
7
8
9
10

mysql> create user repl@'192.168.1.172' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to repl@'192.168.1.172';
Query OK, 0 rows affected (0.00 sec)


这里的IP:192.168.1.172，为从库IP

3.重启主库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@primary ~]# systemctl restart mysql

[root@primary ~]# systemctl status mysql
● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
   Active: active (running) since Thu 2025-01-16 04:58:36 CST; 2s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2849 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 2874 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS)
    Tasks: 39
   CGroup: /system.slice/mysql.service
           ├─2887 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysqld.pid
           └─3099 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysqld.log --pid-file=/data...

Jan 16 04:58:35 primary systemd[1]: Starting LSB: start and stop MySQL...
Jan 16 04:58:36 primary mysql[2874]: Starting MySQL. SUCCESS!
Jan 16 04:58:36 primary systemd[1]: Started LSB: start and stop MySQL.
4.查看主库状态
1
2
3
4
5
6
7
8
9
10
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


记录下File和Position**的信息，配置从库时需要使用到

六、从库配置

1.配置从库参数
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
# vi /etc/my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin
binlog_format=row
skip_name_resolve=ON
read_only=1

basedir=/usr/local/mysql
datadir=/data/mysql
character-set-server=utf8mb4
socket=/tmp/mysql.sock 

log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid

[mysql]  
default-character-set=utf8mb4  
socket=/tmp/mysql.sock  


从库添加read_only=1参数，限制普通用户修改从库数据，但不限制root用户
从库添加relay-log=mysql-relay参数可以设置生成relay log日志的名称

2.重启从库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@slave01 ~]# systemctl restart mysql

[root@slave01 ~]# systemctl status mysql
● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
   Active: active (running) since Thu 2025-01-16 05:12:58 CST; 14s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2527 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 2554 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS)
    Tasks: 39
   CGroup: /system.slice/mysql.service
           ├─2567 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysqld.pid
           └─2779 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysqld.log --pid-file=/data...

Jan 16 05:12:57 slave01 systemd[1]: Starting LSB: start and stop MySQL...
Jan 16 05:12:58 slave01 mysql[2554]: Starting MySQL. SUCCESS!
Jan 16 05:12:58 slave01 systemd[1]: Started LSB: start and stop MySQL.
3.配置主从同步
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

change master to 
master_host='192.168.1.171',
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000003',
master_log_pos=157;


[root@slave01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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> change master to 
    -> master_host='192.168.1.171',
    -> master_user='repl',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=157;
Query OK, 0 rows affected, 8 warnings (0.01 sec)


192.168.1.171为主库IP地址

4.开启主从同步
1
2
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
5.检查同步状态
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
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.171
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 157
               Relay_Log_File: slave01-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 157
              Relay_Log_Space: 538
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 05ba0194-d12b-11ef-b4a4-000c29ff3820
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)



Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
SQL进程状态表明主从配置成功

七、主从同步测试

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
---主库创建数据库
[root@primary ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb
Database changed

mysql> create table t(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

---从库已接收到新创建的数据库mydb及t表数据
[root@slave01 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@slave01 ~]# mysql -e 'select * from mydb.t'
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

  • Title: Mysql8.0主从复制安装
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-07-16 11:30:08
  • Updated at : 2025-01-26 09:01:34
  • Link: https://www.zhangdong.me/mysql8.0-replication-installation.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
mysql8.0 gtid主从安装 mysql8.0 gtid主从安装 Mysql5.6主从复制安装 Mysql5.6主从复制安装 Linux安装Mysql5.6 Linux安装Mysql5.6
推荐文章
mysql8.0 gtid主从安装 mysql8.0 gtid主从安装 Mysql5.6主从复制安装 Mysql5.6主从复制安装
评论
  1. 一、环境
  2. 二、配置yum
  3. 三、卸载旧版mysql
  4. 四、创建用户
  5. 四、安装数据库(主从)
  6. 五、主库配置
    1. 1.配置主库参数
    2. 2.创建数据库复制帐号
    3. 3.重启主库
    4. 4.查看主库状态
  7. 六、从库配置
    1. 1.配置从库参数
    2. 2.重启从库
    3. 3.配置主从同步
    4. 4.开启主从同步
    5. 5.检查同步状态
  8. 七、主从同步测试