Hi,
I think this posting will benefit those who need to set up MySQL replication database servers.
First things first I created a user within MySQL and granted all permissions to every database.
I named the user repl and allowed access from the replication server, ie. replication.domain.com (This will be the network name of the computer).
I then added "REPLICATION SLAVE" privilege to this user within mysql console as follows;
mysql>GRANT REPLICATION SLAVE ON *.*
mysql>TO "repl"@"replication.domain.com" IDENTIFIED BY "password";
Once this has been done i locked the master database as follows;
mysql> FLUSH TABLES WITH READ LOCK;
I then copied the database folders within the data directories of mysql to the slave server.
I then added the following lines to the bottom of the master server's ini file;
log-bin=masterlog
server-id = 1
Restart mysql service now
This sets the log name for the slave server to masterlog and sets the server id to 1, which has to be unique.
I then added the following lines to the bottom of my slave server's my.ini file;
server-id = 2 (This has to be different to the master)
master-host = The master server's ip address, ie. 195.123.123.123
master-user = repl
master-password = whatever password you have assigned
master-port = 3306 (remember to allow this port on any firewalls!)
Restart mysql service now
Once this is done you can start the master server again by typing at console
mysql>UNLOCK TABLES;
Then go back on to your slave server and go into mysql console and type;
mysql>CHANGE MASTER TO MASTER_HOST"host ip here",
mysql>MASTER_USER="repl",
mysql>MASTER_PASSWORD="whatever your password is",
mysql>MASTER_LOG_FILE="masterlog.000005", (you can find this out using mysql admin replication status on the master box).
mysql>MASTER_LOG_POS=702; (you can find this out as above looking within mysql admin)
mysql>START SLAVE (start the slave)
I think this is everything, if I missed anything let me know. It has been quite a headache but is working perfectly now (TOUCH WOOD!).
Also, I recommend you install same version of mysql and mysql admin - mysq admin comes in really handy!.
Good luck
How to set up a Slave replication server on mysql 4.1 (win)
Moderator: General Moderators
- mhouldridge
- Forum Contributor
- Posts: 267
- Joined: Wed Jan 26, 2005 5:13 am