配置master-master mysql cluster复制

1. 准备工作,修改配置
Master side:
set binlog_format:mysqld=mixed,sync_binlog:mysqld=1 c01_test;
set server_id:mysqld:51=51,log_bin:mysqld:51=/data/mcm_data/clusters/c01_test/51/data/binlog c01_test;
set server_id:mysqld:52=52,log_bin:mysqld:52=/data/mcm_data/clusters/c01_test/52/data/binlog c01_test;

mcm> get server_id:mysqld c01_test;
+-----------+-------+----------+---------+----------+---------+-------+---------+
| Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment |
+-----------+-------+----------+---------+----------+---------+-------+---------+
| server_id | 51 | mysqld | 51 | | | | |
| server_id | 52 | mysqld | 52 | | | | |
+-----------+-------+----------+---------+----------+---------+-------+---------+
2 rows in set (0.07 sec)

验证：两边都执行下面的操作
show master status;
show slave status\G
select * FROM mysql.ndb_binlog_index;
select * from mysql.ndb_apply_status;


2. Master, create user
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';

3. Slave设置master的信息
CHANGE MASTER TO
MASTER_HOST='192.168.1.14',
MASTER_PORT=3306,
MASTER_USER='slave_user',
MASTER_PASSWORD='slave_password';

对等：
CHANGE MASTER TO
MASTER_HOST='192.168.1.13',
MASTER_PORT=3306,
MASTER_USER='slave_user',
MASTER_PASSWORD='slave_password';

4. master:备份
ndb_mgm> START BACKUP WAIT COMPLETED;
Connected to Management Server at: localhost:1186
Waiting for completed, this may take several minutes
Node 1: Backup 4 started from node 50
ndb_mgm> Node 1: Backup 4 started from node 50 completed
StartGCP: 389082 StopGCP: 389085
#Records: 2156 #LogRecords: 0
Data: 63636 bytes Log: 0 bytes

5. slave:恢复
把每个datanode上的备份拷贝到slave的cluster上,再本地恢复
或在master上远程恢复到slave,我用的是远程恢复
ndb_restore -c 192.168.1.13:1186 -n 2 -b 4 -r -m ./2/data/BACKUP/BACKUP-4
ndb_restore -c 192.168.1.13:1186 -n 1 -b 4 -r -e ./1/data/BACKUP/BACKUP-4
第一个恢复-m参数恢复metadata是必需的，只在第一个恢复的时候使用
最后一个恢复-e (or --restore-epoch)是必须的，这样才能更新mysql.ndb_apply_status得内容

slave:查看-e参数更新的数据
mysql> select * from mysql.ndb_apply_status;
+-----------+------------------+----------+-----------+---------+
| server_id | epoch | log_name | start_pos | end_pos |
+-----------+------------------+----------+-----------+---------+
| 0 | 1671111645331455 | | 0 | 0 |
+-----------+------------------+----------+-----------+---------+
1 row in set (0.00 sec)

master:取得LSN号
SELECT SUBSTRING_INDEX(File, '/', -1), Position
FROM mysql.ndb_binlog_index
WHERE epoch > 1671111645331455;

如果为空，则用show master status取得LSN号：
SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 | 112 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

7 slave:启动slave:
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=112;
start slave;
show slave status\G

对等:
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=268;
start slave;
show slave status\G

8. 验证
两边执行更新，查看是否复制成功
下面的查询应该有数据了
select * FROM mysql.ndb_binlog_index;
select * from mysql.ndb_apply_status;