mysql主从同步集群搭建从0到1随笔_mysql搭建集群和主从同步框架

一、安装mysql，注意不是mariadb（以下为linux-centos7.6环境下）

主从节点都这么装也不麻烦，mysql8.0+才能用clone并且子节点克隆的mysql还需要重新生成uuid文件

[root@VM-0-15-centos etc]# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
[root@VM-0-15-centos etc]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
[root@VM-0-15-centos etc]# yum install mysql-community-server
1
2
3

二、启动mysql服务，进行基本设置

[root@VM-0-15-centos etc]# service mysqld restart
1

初次安装mysql，root账户没有密码，登陆mysql设置密码。

1、初次登陆

[root@VM-0-15-centos etc]# mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.51 MySQL Community Server (GPL)

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

2、设置密码重新登陆

mysql> set password for 'root'@'localhost' = password('111'); # 设置root用户密码
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@VM-0-15-centos etc]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3、设置字符集utf8mb4

采用utf8mb4编码的原因是：存储与获取数据的时候，不用再考虑表情字符的编码与解码问题。
utf8mb4的最低mysql版本支持版本为5.5.3+，最低mysql驱动版本5.1.13+。
修改mysql配置文件my.cnf(windows为my.ini)
my.cnf一般在etc/my.cnf位置。找到后请在以下三部分里添加如下内容：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
1
2
3
4
5
6
7
8
9
10
11

重启数据库检查设置是否生效

[root@VM-0-15-centos etc]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@VM-0-15-centos etc]# mysql -uroot -p111
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 4
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'; # 查看数据库字符集
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_unicode_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+
11 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

如上显示则配置成功。

4、远程连接设置

mysql> grant all privileges on *.* to root@'%'identified by '111';
Query OK, 0 rows affected (0.00 sec)
1
2

PS: 正式标准的服务配置，应该有专门的数据库同步用户仅赋予同步权限，抱着先跑起来的想法，此处小小偷懒一波

三、集群主从节点配置

1、/etc/my.cnf 添加如下配置（必须在（# Recommended in standard MySQL setup）注释下，不然不会生效）

# Recommended in standard MySQL setup
server_id=64 # 配置服务唯一id，建议为ip地址最后一部分，主从不可相同
log-bin=mysql-bin # 开启binlog日志，从节点可不开启
# 指定不同步的数据库（原理是不生成指定库的binlog日志，主节点设置即可，可不配置）
binlog-ignore-db=performance_schema 
binlog-ignore-db=information_schema
binlog-ignore-db=sys
1
2
3
4
5
6
7

2、查看主节点binlog日志状态信息，待会儿配置从节点要用到

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

3、从节点同步配置

# 每一项都可单独修改，但需要停止同步再操作（stop slave）
CHANGE MASTER TO # 修改配置命令
MASTER_HOST='xxx.x.x.x', # 主节点ip地址 
MASTER_PORT=3306, # 主节点mysql实例端口
MASTER_USER='root', # 主节点mysql同步用的账号
MASTER_PASSWORD='111', # 主节点mysql同步用的账号密码
MASTER_LOG_FILE='mysql-bin.000002', # 主节点开始同步的文件（上一步操作获得的信息‘File’）
MASTER_LOG_POS=120;  # 主节点开始同步的文件日志位置（上一步操作获得的信息‘Position’）
1
2
3
4
5
6
7
8

配置完成后启动同步

mysql> CHANGE MASTER TO
    -> MASTER_HOST='xxx.x.x.x',
    -> MASTER_PORT=3306,
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='111',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.05 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: xxx.x.x.x
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 453
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 616
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes # 取主节点日志的线程, Yes 为正在运行
            Slave_SQL_Running: Yes # 从日志恢复数据的线程, 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: 453
              Relay_Log_Space: 790
              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: 64
                  Master_UUID: 9b5df4cc-3e2b-11ec-8842-5254004d6c1c
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row 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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

上面标注的三个参数为关键参数如上显示则同步状态正常，随笔到此结束，接下来就是手动体验数据库主从同步的功能啦。