Home > How To > Configure Master / Slave Replication MySQL XAMPP

Configure Master / Slave Replication MySQL XAMPP

Mysql Master Server Configuration

First Lets go to Replication panel on XAMPP.

Screenshot at 2012-04-24 02_13_51

Next Select Configure as Master

Screenshot at 2012-04-24 02_14_03

You can configure Replication as ALL or Selective. I have done selective where I select few DBs to Replicate.

Once you are done you need to add the lines below in the my.cnf file.

# ***** Added Lines here ****
server-id=4745869
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=saggezza_cats,saggezza_catstesting
# ***** Till Here ****

Here are Ignoring all the DBs for Replication except the two above.

Screenshot at 2012-04-24 02_21_40

Go to /opt/lampp/etc/my.cnf

Screenshot at 2012-04-24 02_22_11

Added the lines as I mentioned above above. Also Make sure “max_allowed_packet = 16M”

Screenshot at 2012-04-24 02_23_07

Screenshot at 2012-04-24 02_23_16

Make sure to comment out he existing server-id as we have already given above.

Screenshot at 2012-04-24 02_23_39

Restart XAMPP

$ sudo ./opt/lampp/lampp restart

Screenshot at 2012-04-24 02_24_02

Once you restart XAMPP, you can see that Mysql Server is started as Master.

Screenshot at 2012-04-24 02_24_15

Next Lets Create a Replication user and give it privileges as the slave needs this information to connect to the Master.

Screenshot at 2012-04-24 02_24_40

I have given root but you can give any username you want.

Screenshot at 2012-04-24 02_25_13

MySQL Slave Configuration 

First lets add Server-id to our slave as mentioned.

server-id=9384593

Screenshot at 2012-04-24 02_28_29

Screenshot at 2012-04-24 02_29_29

Now lets connect to Master.

Screenshot at 2012-04-24 02_30_08

You will get a confirmation about he successful connection to Master.

Screenshot at 2012-04-24 02_30_20

Now we need to start SLAVE I/O and Sql Threads.

Screenshot at 2012-04-24 02_31_07

They are right under “Control Slave” as below

Screenshot at 2012-04-24 02_31_28

Now you can see the threads running.

NOTE: If you face any issues getting the threads started then please check logs. In my case IO Thread was not starting. When I checked the log I found that I had two server-id’s in the my.cnf file. Also sometimes you will get IO error due to max_allowed_packet as well, as this is by default set to 1MB, which needs to be around 16MB.

/opt/lampp/var/mysql/mysql-bin

Screenshot at 2012-04-24 02_31_37

Now we can see all the DBs in Replication Mode. Now any Updates to Master will reflect on the slave.

Screenshot at 2012-04-24 03_24_28

This will have the MASTER-SLAVE for Mysql Server is Ready.

Enjoy.

Advertisements
  1. jandson
    July 30, 2013 at 8:06 pm

    MySQL must be accessible via network.

    If you ran the command “/opt/lamp/lamp security” when you installed the Xampp, probably the MySQL isn’t accessible via network. You must be comment skip-networking line in /opt/lampp/etc/my.cnf and restart xampp.

  2. August 15, 2013 at 7:03 am

    I have one confusion.We have to write server-id for slave in same my.cnf file as master.Is it not possible to synchronize local server database to web server database

  3. alex
    October 9, 2013 at 5:50 pm

    is it possible to create master-master replication on xampp??

  4. Moumita
    June 22, 2015 at 11:38 am

    Sorry, but I did not find the replication option in phpmyadmin. Can you suggest me from where I will get it?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: