We help IT Professionals succeed at work.

Simple guide to MySQL Replication...

Last Modified: 2008-03-10

I have a series of applications that save data to a MySQL Server.
I wish to keep a second MySQL server on standby, being continuously updated from the Master, so as to keep in sync with it.
If the 'Master' server were to fail, the clients would be informed and would begin to store there data on the backup server.
Once the Master was repaired, this would again take over and would automatically be updated with the additional/updated data taken during its downtime.

Is this a posible scenario using REPLICATION services provided with MySQL 4.0.14 servers ?

If so, could someome give me a simple example of how to configure the two machines...

Many thanks,

Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)



I have run through the tutorial, and things are ALMOST working...

In stage 2, it states the following...
"Be sure to change the ownership and group of the directories recursively to the MySQL user, and change the file mode to 660 (read-write for owner and group only) on the files, and the directories themselves to 770 (read-write-execute for owner and group only)."

Please excuse my ignorance in this area, but how do I do this???  
I assume that it is done through the mysql.exe commandline, but what are the instructions...

Thanks for any help you can offer me..

Changing directory permissions is done using chmod in the shell prompt. Assuming you know how to login to your server via SSH, you need to do the following:

Depending on what directory they are talking about, you will need to navigate to that directory. I will precede all shell prompts with $

$ pwd (This will tell you your current directory)
$ cd /mysqldir (Navigate to the directory they mention in the tutorial)
$ chown -R mysql . (Change ownership to mysql user recursively)
$ chgrp -R mysql . (Assign to mysql group recursively)
$ chmod -v 660 filename (for the files. Use the wildcard * to match any character)
$ chmod -v 770 directory (for the directories. Use the wildcard * to match any character)

Let me know if this gets you on the right track. It's hard to walk you through exactly what commands you need to run and how, since it changes according to your setup, but hopefully this will give you a start.


My mistake, sorry!

Both servers are running under Windows NT4 - I must have mistyped something along the way, as recalling the "GRANT . . . " command fixed the issue...

It's Friday I guess!!!

Just making the SLAVE -> MASTER reverse update changes....


Ok, got there - I think - but I did need to edit the 'replication' user in the mySQL database on BOTH machines and grant them both SLAVE and MASTER privileges. Should I have needed to do this?

I also have one more question - more involved I believe...

If there are several clients connected to each of the servers and they are all inserting data, as all my Tables have Unique Autoincrementing ID fields, is it likely to get a little confused!!!  i.e. If two users add data to the same table at the same time (one on each server) they will both get the SAME autoincrementing value in the ID field - am I right in assuming this - and if so, the synchronisation will not happen as this field already exists?

Thanks again for any pointers...


It won't hurt to give both users these privileges, but is only necessary if you want the replication user to have additional privileges:

From: http://www.mysql.com/doc/en/Replication_HOWTO.html


Set up an account on the master server that the slave server can use to connnect. This account must be given the REPLICATION SLAVE privilege. (If MySQL versions older than 4.0.2, give the account the FILE privilege instead.) If the account is only for replication (which is recommended), you don't need to grant any additional privileges. The hostname in the account name should be such that each of the slave servers can use the account to connect to the master. For example, to create a user named repl which can access your master from any host, you might use this command:

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '<password>';

For MySQL versions older than 4.0.2, use this command instead:

mysql> GRANT FILE ON *.* TO repl@'%' IDENTIFIED BY '<password>';

If you plan to use the LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER statements from the slave host, you will need to grant this account additional privileges:

    * Grant to the account the SUPER and RELOAD global privileges.
    * Grant the SELECT privilege for all tables that you want to load. Any master tables from which the account cannot SELECT will be ignored by LOAD DATA FROM MASTER.


Your AUTO_INCREMENT values will be kept intact when replication occurs:

"Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values."

From: http://www.mysql.com/doc/en/Replication_Features.html

The only way to get definitive answers is to test it with your setup. If your system is complex and under high load (which I assume it is if it requires replication) you should be tesing it extensively before release anyway. Let us know if you run into any problems.




Thanks for all your help with this; well deserved points...

You're welcome. Glad you got it working with a minimum of fuss :)


Just a quick question (I hope).

Since the servers have been set up - and working - they both report the following error every hour...

040227 14:34:38  Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)
040227 14:34:38  Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysvr1-bin.005' position 79

Replication appears to still be working though!
Have I got a timeout set wrongly somewhere???

Thanks again,


I'd say this is a problem with the network and not with replication itself. If you are getting a lot of network traffic, then you might get this error. As long as your server is reconnecting successfully and your data is preserved, then it could just be momentary.

Maybe the default query timeout needs to be set to something longer.

I did find this in the changelog for MySQL 4.0.16:

"Extended the default timeout value for Windows clients from 30 seconds to 1 year. (The timeout that was added in MySQL 4.0.15 was way too short). This fixes a bug that caused ERROR 2013: Lost connection to MySQL server during query for queries that lasted longer than 30 seconds, if the client didn't specify a limit with mysql_options(). Users of 4.0.15 on Windows should upgrade to avoid this problem."



What entries can I add in my.cnf to implement a change to the default ?


I've added...

   set-variable = net_read_timeout=3600
   set-variable = net_write_timeout=3600

in the my.cnf files on both machines but I still get a similar response...

on MYSVR2...
   040301 12:55:11  Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)
   040301 12:55:11  Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysvr1-bin.008' position 148

on MYSVR1...
   040301 12:57:47  Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)
   040301 12:57:47  Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysvr2-bin.005' position 4

Have I changed the correct setting???

Am I ok to continue to try to resolve this as part of this question or should I submit a new one?

Since I have never run across this error, I can't give you further recommendations other than I can find on Google. I'd be happy to help you further in this Q, but I'm afraid that I couldn't come up with anything :(

If you post a new Q, there might be others who could help.

Sorry I couldn't be handy with this one :(


No problem - You've helped me get the system up & running..

Thanks for the help...

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.