Error 13117 while setting up master-slave replication with both DB servers being on the same Mac M1 machine
0
votes
0
answers
655
views
I am trying to create a master-slave replication with both the Mysql Servers being on the same machine.
The first mysql server, which I intend to keep as the master was installed using the Mysql Native Package Installer mentioned here
Following is the
my.cnf
configuration file used for the above master server.
Location of the file: /etc/my.cnf
[mysqld]
server-id=1 #was confused which one is the correct server-id key, thus added both of them.
server_id=1
log-bin=mysql-bin-master
default_authentication_plugin=mysql_native_password
Once the above was done, I started the master Mysql Server using the Mysql Preference Pane in MacOS (the last tab in System Preferences on Mac)
Following is the my.ini
configuration for the Slave.
Location of the file: /etc/my.ini
[mysqld]
server-id=2
server_id=2
log-bin=mysql-bin-slave
relay-log=relay-log-bin
log-slave-updates=1
read-only=1
replicate-ignore-db=mysql
replicate-do-db=mydatabase
replicate-ignore-table=mydatabase.logs
replicate-wild-ignore-table=mydatabase.temp_%
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate-same-server-id=1
Thereafter, I tried to setup the slave mysql server instance using the following command:
sudo mysqld --user=mysql --datadir=/usr/local/mysql/replica/data --port=3307 --log-error=/usr/local/mysql/replica/data/error.log --pid-file=/usr/local/mysql/replica/data/mysql.pid --defaults-file=/etc/my.ini
I had created a replica folder inside /usr/local/mysql/
where all of the data files for replica could be kept.
I created a user account on the Master with the following command:
CREATE USER 'replica'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
and granted replication access via GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
Thereafter, tried logging in to the SLAVE Mysql console and start replication via:
CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin-master.000001', MASTER_LOG_POS=157;
START SLAVE;
But when I do, SHOW SLAVE STATUS
, I get the following error:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000001
Read_Master_Log_Pos: 157
Relay_Log_File: Karans-Air-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
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: 123456
Relay_Log_Space: 697
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: 13117
Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
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: 230423 18:02:08
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)
Also, checked the MASTER_LOG_POS already and MASTER_LOG_FILE. Both of them are correct!
Asked by karan kapoor
(1 rep)
Apr 23, 2023, 01:20 PM