Wednesday, June 20, 2012

Mysql Replication With Phpmyadmin

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.

10 comments:

  1. nicely done sir great help
    but how to check weather replication is working or not

    ReplyDelete
  2. Thank you very much! For some reason I couldn't get this to work, but following your directions helped and now it's fully functional :D

    ReplyDelete
  3. Hi, I was able to complete the process and all table struture were copied but not the data inside each table. Do u have any suggestion of how to fix it?

    ReplyDelete
  4. 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.

    >>> Where are its logs?

    ReplyDelete
  5. copied

    http://ahmedzubayr.wordpress.com/2012/04/24/configure-master-slave-replication-mysql-xampp/

    ReplyDelete
  6. nice Santosh Sarkar but if i tried in on cpanel then there is no option about replication

    ReplyDelete
  7. how do i undo my confeguration (master to normal)

    ReplyDelete
  8. I want replication on same server as master and slave...
    Is it possible then reply me on mail parsaniyaparth@gmail.com

    ReplyDelete
  9. Sir,
    Thank you so much. You saved me not to be run by the day :)

    ReplyDelete