How to create a named database host

Question

I am trying to set up a multi master mysql replication on two systems on a network. The two separate database servers are running mysql at localhost, How do I create a replication user for the hosts since they share the same name.

I could do this, but it doesn't seem like the right thing to do.

CREATE USER 'repl_user1'@'localhost' IDENTIFIED BY 'password1';  // Server1
CREATE USER 'repl_user2'@'localhost' IDENTIFIED BY 'password2';  // Server2

I think something like this should do

CREATE USER 'repl_user1'@'server1.com' IDENTIFIED BY 'password1';  // Server1
CREATE USER 'repl_user2'@'server2.com' IDENTIFIED BY 'password2';  // Server2

but I don't know how to give the host a name. Thanks for any help.

Why would you not use the same replication user on both instances? — Jonathan Amend, Nov 29 '12 at 21:23
I need a user with grant on replication on each instance with. My problem is how to reference the host, since they use the same name 'localhost'. — Chibuzo, Nov 29 '12 at 21:41

score 1 · Accepted Answer · answered Nov 29 '12 at 21:55

You can use IPs, ex:

CREATE USER 'repl_user1'@'192.168.1.101' IDENTIFIED BY 'password1';  // Server1
CREATE USER 'repl_user2'@'192.168.1.102' IDENTIFIED BY 'password2';  // Server2

If you really want to use hostnames (and you don't already have some set up for your servers using DNS), just add them to your hosts file and reference those. Ex.:

/etc/hosts (server1)

127.0.0.1         localhost server1.local
192.168.1.101     server1.local
192.168.1.102     server2.local

/etc/hosts (server2)

127.0.0.1         localhost server2.local
192.168.1.101     server1.local
192.168.1.102     server2.local

Then set up the users:

CREATE USER 'repl_user1'@'server1.local' IDENTIFIED BY 'password1';  // Server1
CREATE USER 'repl_user2'@'server2.local' IDENTIFIED BY 'password2';  // Server2

That should work so both servers receive connections from their own host and the other as well and map it to the correct users.

How to create a named database host

1 Answers1