Jump to content

mysql replication

Recommended Posts

So I did this once before but then my webserver sort of died.. ok I just left it at my old office cause it was on a p2 and I didn't want to move it, so i'll redo this.


This little tutorial thinks you have a basic mysql setup and have never tried to setup replication. If you have you generally have to remove all the binary logs and the settings in the my.cnf


Master Server


So these are the settings on the master server you need. For me I edit the /etc/my.cnf


server-id = 1


Now restart the mysql server


Now log into the mysql client and you need to setup a replication user.




When creating users I always like to use ip's you can use hostnames and wildcards and such.. for more information on that visit the mysql doc page for the new user section.


Ok now the next step requires you to stop your mysql server for a bit cause you want to make sure the db is locked when you do a dump and no hidden data gets inserted or such.




Now that its locked you can run the following command




You will get something like this


| File					| Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql_binary_log.000004 |	   79 |			  |				  |


You want to make sure you note the file and the Position.. you will need these values for the slave.


Ok now exit out of the mysql client and you need to get a dump of all your databases.


mysqldump -u root -p --extended-insert --all-databases > /tmp/backup.sql


now move the /tmp/backup.sql file to the slave server


You can now unlock the db's now


unlock tables



Slave Server


Now on to the slave server. So this takes into account that it is a new install or such of mysql and you have no tables.. so lets import the backup from the master server


mysql < backup.sql


You want to add -u root -p if you setup a root password already on your slave


Now we want to add the following into /etc/my.cnf. The server id muse be unique




now restart the mysql server


Now login to the mysql client and issue the following query


CHANGE MASTER TO MASTER_HOST='<master host name>',


So you can see the last 2 lines are where i told you to note the filename and position and the others are from the grant.. now if everything worked you should be able to issue the following command in the mysql client


start slave


and replication should be working.. you can test this by creating a new DB on the master and it should appear on the slave.. if it doesn't work remember to check those logs

  • Like 1
Link to post
Share on other sites
  • 1 year later...
I am starting a wiki for my own use on tips/tricks/howto's I have done in the past. I am basically taking all my notes I have and putting the good ones online for others.


The first major one I have done is mysql replication. This is a kickass feature in mysql that a lot of people use oracle just for. It basically allows you to keep multiple backups in sync with a master server as soon as any changes are done on the server they are on the slave servers. I have decided to do a howto since its an area not very covered i've found.


Plus i've needed to do it since it looks like out database server is about to die.. both raid strips look like they aren't gonna last very long.


here is the link




The Url you provided does not work anymore.

Link to post
Share on other sites
  • 2 months later...
  • 2 years later...
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...