简单学习MySQL集群

主从复制

单点故障

MySQL可以往主库写，也可以往从库写。但是人为的不要往从库写，不然会数据不一致。

1，主从复制架构

一主多从

主库 -> 从库

        -> 从库

        -> 从库

主库写数据,增删改，建库建表等

从库读数据。

主库崩溃，不能写。

双主双从

主库3307   ->    从库3309

  ^       V

主库3308   ->  从库3310

3307与3308相互同步，互为主从

3307同步给3309，3308同步给3310

2，主从复制流程

1）从库不断试探主库二进制日志文件，如果有更新则发送请求来获取主库新内容；

2）用户向主库写数据；

3）主库将写命令记录到二进制文件，并更新该文件偏移量；

4）从库试探二进制文件发现偏移量与从库记录不同，启动IO线程向主库请求从偏移量到结束内容；

5）主库根据从库请求偏移量位置推送到从库，从库接收后更新从库并更新偏移位置。

6）从库获取主库数据后，将命令写入中继文件，然后唤醒SQL线程同时让IO线程挂起；

7）SQL线程根据中继文件偏亮读取中继文件命令；

8）SQL拿到命令在本地数据库进行回放（从库执行主库SQL），完成后挂起当前SQL线程。

3，多实例

一台Linux服务器上同时启动多个数据库实例。（只适用学习）

实际线上一般每台服务器都分别安装MySQL。（适用实际线上生产环境）

3，CentOS安装MySQL

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

1）下载5.7.37、Linux Generic

配图1

竟然要635M。。。。

https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

2）安装依赖

[root@localhost senrsl]# yum install libaio

3）创建mysql用户，用于启动MySQL服务

[root@localhost senrsl]# groupadd mysql
[root@localhost senrsl]# useradd -r -g mysql -s /bin/false mysql
[root@localhost senrsl]#

4）解压并创建链接

[senrsl@localhost mysql]$ tar zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

[senrsl@localhost mysql]$ sudo ln -s mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql  //这是错的

[root@localhost local]# ln -s /home/senrsl/mysql/mysql-5.7.37-linux-glibc2.12-x86_64/ mysql //这才是对的

[root@localhost local]# ll /usr/local/
。。。
lrwxrwxrwx.  1 root root  55 6月  29 17:41 mysql -> /home/senrsl/mysql/mysql-5.7.37-linux-glibc2.12-x86_64/
。。。
[root@localhost local]#

5）初始化

[senrsl@localhost mysql-5.7.37-linux-glibc2.12-x86_64]$ bin/mysqld --intialize --user=mysql
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2 - No such file or directory)
2022-06-29T09:10:28.440171Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2022-06-29T09:10:28.440385Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2022-06-29T09:10:28.640660Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-29T09:10:28.641099Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-06-29T09:10:28.641140Z 0 [Note] bin/mysqld (mysqld 5.7.37) starting as process 18795 ...
2022-06-29T09:10:28.641172Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-06-29T09:10:28.641896Z 0 [Warning] Can't create test file /var/lib/mysql/localhost.lower-test
2022-06-29T09:10:28.641908Z 0 [Warning] Can't create test file /var/lib/mysql/localhost.lower-test
2022-06-29T09:10:28.642244Z 0 [Warning] One can only use the --user switch if running as root

2022-06-29T09:10:28.642287Z 0 [ERROR] failed to set datadir to /var/lib/mysql/
2022-06-29T09:10:28.642301Z 0 [ERROR] Aborting

2022-06-29T09:10:28.642308Z 0 [Note] Binlog end
2022-06-29T09:10:28.642354Z 0 [Note]
[senrsl@localhost mysql-5.7.37-linux-glibc2.12-x86_64]$

[senrsl@localhost mysql-5.7.37-linux-glibc2.12-x86_64]$ yum install numactl.x86_64

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# bin/mysqld --initialize --user=mysql
2022-06-29T09:12:34.502044Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-29T09:12:34.502217Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-06-29T09:12:34.656944Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-29T09:12:34.681196Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-29T09:12:34.768872Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9cf87e91-f78b-11ec-8045-000c297900bb.
2022-06-29T09:12:34.769510Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-29T09:12:35.201089Z 0 [Warning]
2022-06-29T09:12:35.201105Z 0 [Warning]
2022-06-29T09:12:35.201720Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-29T09:12:35.485133Z 1 [Note] A temporary password is generated for root@localhost: mo?ihDkpX0:t  //这个是初始密码
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]#

6）启动

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# bin/mysqld_safe --user=mysql &
[1] 18873
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# 2022-06-29T09:17:09.931894Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.

[1]+  退出 1                bin/mysqld_safe --user=mysql
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]#

修复没权限

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# mkdir /var/log/mariadb
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# touch /var/log/mariadb/mariadb.log
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# chown -R mysql:mysql /var/log/mariadb/
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]#

再启动报错

2022-06-29T09:21:17.176775Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-06-29T09:21:17.179616Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-29T09:21:17.179670Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2022-06-29T09:21:17.180048Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-06-29T09:21:17.182168Z 0 [Note] InnoDB: Buffer pool(s) load completed at 220629 17:21:17
2022-06-29T09:21:17.182476Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2022-06-29T09:21:17.182531Z 0 [Note] IPv6 is available.
2022-06-29T09:21:17.182542Z 0 [Note]   - '::' resolves to '::';
2022-06-29T09:21:17.182561Z 0 [Note] Server socket created on IP: '::'.
2022-06-29T09:21:17.184013Z 0 [ERROR] Can't start server: can't check PID filepath: No such file or directory

跟上面那个一样

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# vi /etc/my.cnf   //查看pid-file路径

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# mkdir /var/run/mariadb/

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# touch /var/run/mariadb/mariadb.pid

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# chown -R mysql:mysql /var/run/mariadb/

终于启动成功

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# bin/mysqld_safe --user=mysql &
[1] 19298
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# 2022-06-29T09:28:20.063294Z mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
2022-06-29T09:28:20.087755Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]#
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# ps -ef | grep mysql
root      19298  18809  0 17:28 pts/3    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql     19437  19298  2 17:28 pts/3    00:00:00 /home/senrsl/mysql/mysql-5.7.37-linux-glibc2.12-x86_64/bin/mysqld --basedir=/home/senrsl/mysqmysql-5.7.37-linux-glibc2.12-x86_64 --datadir=/var/lib/mysql --plugin-dir=/home/senrsl/mysql/mysql-5.7.37-linux-glibc2.12-x86_64/lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      19468  18809  0 17:28 pts/3    00:00:00 grep --color=auto mysql
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]#

7）设置环境变量

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# vi /etc/profile

增加

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

8）验证

[root@localhost local]# mysqld --version
mysqld  Ver 5.7.37 for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL))
[root@localhost local]# mysql --version
mysql  Ver 14.14 Distrib 5.7.37, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[root@localhost local]#

9）设置为服务

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# cp support-files/mysql.server /etc/init.d/mysqld

[root@localhost senrsl]# service mysqld status
 ERROR! MySQL is running but PID file could not be found
[root@localhost senrsl]#

10）设置开机启动

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# chkconfig --add mysqld
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# chkconfig --list mysqld

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关
[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]#

11）登录

[root@localhost mysql]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@localhost mysql]#

[root@localhost mysql]# chown -R  mysql:mysql /var/lib/mysql/
[root@localhost mysql]# service mysqld stop
 ERROR! MySQL server PID file could not be found!
[root@localhost mysql]#

[root@localhost mysql]# ps -ef | grep mysqld
[root@localhost mysql]# kill -9 19298
[root@localhost mysql]# ps -ef | grep mysqld
[root@localhost mysql]# kill -9 19437

kill之后不报错MySQL server PID file could not be found!了

[root@localhost mysql]# service mysqld status
 ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists
[root@localhost mysql]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost mysql]# service mysqld status
 SUCCESS! MySQL running (20421)
[root@localhost mysql]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost mysql]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost mysql]#

但账号密码登录依然不行

mo?ihDkpX0:t

[root@localhost mysql]# ll /var/lib/mysql/
srwxrwxrwx. 1 mysql mysql        0 6月  29 18:02 mysql.sock

[root@localhost mysql]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

[root@localhost mysql]# mysql -uroot -p

一定要确定mysql服务启动了。。。。

4，修改默认密码

[root@localhost mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37

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> ALTER USER 'root'@'localhost' IDENTIFIED BY '0123456';
Query OK, 0 rows affected (0.00 sec)

mysql> quit;

5，root远程访问

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '012345' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

6，设置编码

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+------------------------------------------------------------------------+
| Variable_name            | Value                                                                  |
+--------------------------+------------------------------------------------------------------------+
| character_set_client     | utf8                                                                   |
| character_set_connection | utf8                                                                   |
| character_set_database   | latin1                                                                 |
| character_set_filesystem | binary                                                                 |
| character_set_results    | utf8                                                                   |
| character_set_server     | latin1                                                                 |
| character_set_system     | utf8                                                                   |
| character_sets_dir       | /home/senrsl/mysql/mysql-5.7.37-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+------------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> quit
Bye
[root@localhost mysql]# vi /etc/my.cnf
[root@localhost mysql]#

在mysqld下增加编码设置

[mysqld]
character_set_server=utf8
init-connect='SET NAMES utf8'
datadir=/var/lib/mysql

7，伪集群创建

一个实例，改4个端口。。。。

1）创建4个data目录

[root@localhost mysql-5.7.37-linux-glibc2.12-x86_64]# cd data/
[root@localhost data]# mkdir 3307
[root@localhost data]# mkdir 3308
[root@localhost data]# mkdir 3309
[root@localhost data]# mkdir 3310

2）依次初始化

[root@localhost data]# cd ../bin/
[root@localhost bin]# ./mysqld --initalize-insecure //表示不生成root密码 --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3307 --user=mysql
mysqld: Can't change dir to '/usr/local/mysql/data/3307/' (Errcode: 13 - Permission denied)
2022-06-29T10:30:51.123252Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-29T10:30:51.123461Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-06-29T10:30:51.123493Z 0 [Note] ./mysqld (mysqld 5.7.37) starting as process 21332 ...
2022-06-29T10:30:51.135557Z 0 [ERROR] failed to set datadir to /usr/local/mysql/data/3307/
2022-06-29T10:30:51.135567Z 0 [ERROR] Aborting

2022-06-29T10:30:51.135616Z 0 [Note] Binlog end
2022-06-29T10:30:51.135722Z 0 [Note] ./mysqld: Shutdown complete

[root@localhost bin]#

非常烦躁，直接改成root权限，要不就复制四个虚拟机。。。。

[root@localhost bin]# ./mysqld --initalize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3307 --user=root

依次修改四个端口路径，创建。

3)依次创建my.cnf

[client]
port = 3307
socket = /usr/local/mysql/data/3307/mysql.sock
default-character-set=utf8

[mysqld]
port = 3307
socket = /usr/local/mysql/data/3307/mysql.sock
datadir = /usr/local/mysql/data/3307
log-error = /usr/local/mysql/data/3307/error.log
pid-file = /usr/local/mysql/data/3307/mysql.pid
character-set-server=utf8
lower_case_table_names=1
autocommit = 1

放到四个端口目录

vi全局替换 :%s/3307/3308/g

%表示从第一行到最后一行

s表示替换

3307表示搜索文本

3308表示替换文本

g表示每一行的每个结果进行替换

4）好吧，还是开四个虚拟机。。。。

vm 链接克隆

192.168.0.121 -> 192.168.0.142

                       -> 192.168.0.125

                       -> 1192.168.0.112

哎，这么弄两分钟就好了。。。。

8，一主多从配置

设置121为主机，142、125、112为从机。

1）逐个修改my.cnf

主库增加

[mysqld]
character_set_server=utf8
init-connect='SET NAMES utf8'

log-bin=mysql-bin  #二进制日志
server-id=121        #server编号，要唯一

从库增加

server-id=142 #其他也是一样，编号唯一

2）依次重启

service mysqld restart

3）设置121为主服务器

主库查看当前偏移量

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

偏移 154

创建复制数据的账号并授权

grant replication slave on *.* to 'copy'@'%' identified by '012345';

恢复偏移量，因为这个创建账号不需要同步

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      437 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

4）设置其他三台为从库

依次登录

[root@localhost senrsl]# mysql -uroot -p

查看当前丛属状态

mysql> show slave status;

依次重置，以解决服务器不是初始状态

mysql> stop slave;
mysql> reset slave;

依次设置从服务器的master

change master to master_host='192.168.0.121',master_user='copy',
master_port=3306,master_password='012345',
master_log_file='mysql-bin.000001',master_log_pos=154;

启动从库

三台从库依次执行

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>

5）验证

从节点执行

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.121
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        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: 154
              Relay_Log_Space: 531
              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: 121
                  Master_UUID: 9cf87e91-f78b-11ec-8055-000c297900bb
             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 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:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show slave status\G;

需要确认

            Slave_IO_Running: No
            Slave_SQL_Running: No

这俩都是Yes。

可以看到格式化的数据。

错误1：

error connecting to master 'copy@192.168.0.121:3308' - retry-time: 60  retries: 2

端口写错，找不到3308，默认是3306

错误2：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

UUID不能一样。

mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 9cf87e91-f78b-11ec-8045-000c297900bb |
+---------------+--------------------------------------+
1 row in set (0.01 sec)

mysql>

依次修改三个从库uuid文件，重启mysql.

vi /var/lib/mysql/auto.cnf

service mysqld restart;

错误3：

直接不显示错误了，只显示Error。。。。

然后发现主库多次启动后，bin file变成了003

change master to master_host='192.168.0.121',master_user='copy',
master_port=3306,master_password='012345',
master_log_file='mysql-bin.000003',master_log_pos=154;

重新stop slave；指向上面这个，然后start slave。终于显示yes了。。。。

6）同步修改测试

主库修改会自动同步到从库，从库修改不会同步主库。

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

mysql> use test2022;
Database changed
mysql> create table `users`(`user_id` INT AUTO_INCREMENT,`name` VARCHAR(100),PRIMARY KEY(`user_id`));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into users(name) values("张三");
Query OK, 1 row affected (0.01 sec)

mysql>

9，双主双从

一主多从，主机down,就不能写，但是可以从库读。

设置121、142为主机。

125为121的从机，112为142的从机。

121和142互为主从。

1）设置121配置log-bin及server-id

[root@localhost senrsl]# cat /etc/my.cnf
[mysqld]
。。。

log-bin=mysql-bin
server-id=121

。。。

2）设置142配置log-bin及server-id

[root@localhost senrsl]# cat /etc/my.cnf
[mysqld]
。。。。

log-bin=mysql-bin
server-id=142

。。。。

3）125、112只配置server-id

[root@localhost senrsl]# cat /etc/my.cnf
[mysqld]
。。。

server-id=125

。。。

[root@localhost senrsl]# cat /etc/my.cnf
[mysqld]
。。。

server-id=112

。。。

4）121继续增加配置

[root@localhost senrsl]# cat /etc/my.cnf
[mysqld]
。。。

log-bin=mysql-bin
server-id=121

//增加下面这四个
auto_increment_increment=2  //主键自增步长，避免master间冲突，一般为主服务器数量
auto_increment_offset=1    //自增起始值，不同master不一样。与上面那个配合避免冲突
log-slave-updates  //其他master的slave也更新
sync_binlog=1  //每1次事务提交，会把binlog缓存刷进日志

。。。

5）142也增加配置

auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1

6）依次启动mysql

7）主库创建同步账号

121、142执行

[root@localhost senrsl]# mysql -uroot -p

mysql> reset master;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

reset后，file会变回000001,pos会变回154。

如果show master是空，就确认cnf文件并重启mysql。

如果之前是从，需要停止从。

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql>

8）121/112停止复制，重置从关系

mysql> stop slave;

mysql> reset slave;

9）设置142、125为121的从

change master to master_host='192.168.0.121',master_user='copy',
master_port=3306,master_password='012345',
master_log_file='mysql-bin.000001',master_log_pos=154;

10）设置121、112为142的从

change master to master_host='192.168.0.142',master_user='copy',
master_port=3306,master_password='012345',
master_log_file='mysql-bin.000001',master_log_pos=154;

11）依次启动从

mysql> start slave;

11）验证

操作一个验证一个，每次都是

            Slave_IO_Running: No
            Slave_SQL_Running: No

也不显示Error信息。。。。

原来是没启动。。。。

启动后正常。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.122
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 154
              Relay_Log_Space: 531
              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: 121
                  Master_UUID: 9cf87e91-f78b-11ec-8055-000c297900bb
             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 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:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

12）测试

create database test202201;

use database test202201;

insert into users(name) values("张三");

从121写入就是奇数ID、从142写入就是偶数ID。

10，使用SpringBoot+MyBatiys连接多数据源

略

--
senRsl
2022年07月01日14:31:06