Home > HOWTOs > 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
Categories: HOWTOs
  1. lance
    September 2, 2016 at 10:46 am

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘USING ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9” at line 1

    can you please help me with this

  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: