How to set up a Slave replication server on mysql 4.1 (win)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

How to set up a Slave replication server on mysql 4.1 (win)

Post by mhouldridge »

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
Post Reply