📋

Description

Error 1794 indicates that a MySQL server, likely intended for replication (as a master or slave), is not properly configured or has failed to initialize its replication capabilities. This typically occurs when the mandatory `server-id` parameter is missing or incorrectly set in the server's configuration, preventing the server from establishing its unique identity within a replication topology.

💬

Error Message

Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

🔍

Known Causes

3 known causes

⚠️

Missing Server ID

The `server-id` parameter, essential for uniquely identifying a MySQL instance in a replication setup, has not been defined in the server's configuration file (my.cnf or my.ini).

⚠️

Invalid Server ID Value

The assigned `server-id` value is either zero (0), which is reserved and invalid for replication, or a duplicate of another server's ID within the same replication topology, leading to conflicts.

⚠️

Incomplete Replication Setup

Although `server-id` might be present, other necessary replication parameters, such as `log_bin` or `relay_log`, are missing, preventing proper initialization of the replication role.

🛠️

Solutions

3 solutions available

1. Configure server-id in MySQL Configuration File easy

Add the 'server-id' directive to your MySQL configuration file.

1

Locate your MySQL configuration file. This is typically `my.cnf` or `my.ini`.

2

Open the configuration file with a text editor (e.g., `nano`, `vim`, `notepad`).

3

Add or modify the `server-id` directive under the `[mysqld]` section. Ensure each server in a replication topology has a unique ID.

[mysqld]
server-id = 1

4

Save the configuration file and restart the MySQL server.

sudo systemctl restart mysql

2. Set server-id Dynamically (Requires Restart) easy

Set the 'server-id' dynamically using `SET GLOBAL` which requires a server restart to take effect.

1

Connect to your MySQL server as a user with sufficient privileges (e.g., root).

2

Execute the `SET GLOBAL` command to set the `server-id`. This change is temporary and will be lost on restart unless also configured in the `my.cnf` file.

SET GLOBAL server_id = 1;

3

Restart the MySQL server for the `server-id` to be recognized and persist.

sudo systemctl restart mysql

3. Verify Replication Configuration on Slave medium

Ensure the slave is correctly configured to connect to the master, including its own unique server ID.

1

Connect to the slave MySQL server.

2

Check the current `server-id` setting.

SHOW VARIABLES LIKE 'server_id';

3

If `server-id` is not set or is incorrect, follow Solution 1 or Solution 2 to configure it.

4

Ensure the slave is configured to connect to the master using `CHANGE MASTER TO` or `CHANGE REPLICATION SOURCE TO` (MySQL 8.0.23+). This includes `MASTER_HOST`, `MASTER_USER`, `MASTER_PASSWORD`, and importantly, that the master's `server-id` is also correctly set.

CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='replication_user', MASTER_PASSWORD='your_password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345;

5

Start the slave replication threads.

START SLAVE;

6

Check the slave status for any errors.

SHOW SLAVE STATUS\G

🔗

Related Errors

5 related errors

MySQL Error 1794: Missing Server ID for Replication