9

I've been trying to set up two databases as master & slave.

I followed the famous guide here: https://dev.mysql.com/doc/refman/5.1/en/replication-howto-existingdata.html

But no luck on my slave server, The issue I'm having is setting the server-id variable. No matter where I define it (I looked into all the possible cnf files that might allow me to define the variable)

I tried to define it like so:

[mysqld]
server-id = 2

I also tried setting it by using SET GLOBAL server_id but obviously, it didn't save the setting.

when I do:

SHOW VARIABLES LIKE 'server_id'

It returns

server_id 0

Among the cnf files I've looked into are:

etc/mysql/mysql.conf.d/mysqld.cnf
etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf
etc/mysql/conf.d/mysql.cnf
etc/mysql/debian.cnf
etc/mysql/mysql.cnf
usr/my.cnf
usr/my-new.cnf
usr/etc/my.cnf

My MySQL server is running on Ubuntu. And if it matters, I start it by typing:

service mysql start

I'd love to know where else I could look to fix this issue. Thanks a bunch!

Additional Notes:

MySQL Ignoring the global conf file

I received this warning a couple of times and it disappeared when I returned the chmod to 644 on the etc/mysql folder, although every thing stated above was attempted using both 644 and 777 permissions, with 644 the warning disappears.

RagZ
  • 149
  • 1
  • 1
  • 6
  • Dont know much about linx but try restart instead of start in the command line – Mihai Oct 25 '15 at 10:39
  • @Mihai Thanks for the response but I do use restart, as well as stop, just for some reason it seems like my setting is being ignored. – RagZ Oct 25 '15 at 10:44

3 Answers3

12

I know it's to late, but for those who will make the mistake. It's server_id and not server-id:

[mysqld]
server_id = 2
Groco
  • 1,301
  • 15
  • 19
  • 3
    It's not always `server_id`. Sometimes it's `server-id` and sometimes it's `server_id`. Not sure exactly what it depends on. This answer can be misguiding for some. – Rakmo Mar 22 '21 at 20:47
2

For me it worked by renaming the from /etc/mysql/conf.d/my.cnf to /etc/mysql/conf.d/my.ini

I'm not sure of the exact reason for that (feel free to edit this answer and add to it).

I tried out various combinations:

  1. Keeping the variable as: server-id and server_id
  2. Keeping the group/section as: [mysql] and [mysqld]
  3. Renaming the file to: my.cnf , mysqld.cnf, mysql.cnf, my.ini

You can keep trying various combinations out of the above options, it should definitely work. :)

Rakmo
  • 1,926
  • 3
  • 19
  • 37
0

server-id and server_id are actually the same when you use in conf file.

Refercene: https://dev.mysql.com/doc/refman/8.1/en/program-variables.html

Adding those

[mysqld]
server_id = 2

to mysqld (https://dev.mysql.com/doc/refman/8.0/en/mysqld.html) config file locate at /etc/mysql/mysql.conf.d/mysqld.cnf

Then restart mysql service

systemctl restart mysql

Check variable

mysql> SHOW GLOBAL VARIABLES LIKE "server_id";
Minh Quang Nguyen
  • 165
  • 1
  • 10