Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two servers both running on MySQL 5.5.20 and I want to setup replication, so every update/insert on Master would reflect immediately on Slave machine.

I tried following this tutorial (I used instructions given on this website.) but it is not working. I found that after doing all this I couldn't ... (((Please update this part with your experiences)))

Could you please suggest me a tested and an efficient way to do this in MySQL 5.5.20?

share|improve this question
    
Hi Vinny. Can you outline what you tried from the mysql docs, and expound on what exactly didn't work for you? –  Derek Downey Feb 6 '12 at 18:12
    
I used instructions given on this website. howtoforge.com/mysql_database_replication. –  vinny Feb 6 '12 at 18:37
    
Hi Vinny, I think this is too broad of a scope for this Site. If you adjusted the question to be specifically what went wrong with the guide you followed, we could better help you. –  Derek Downey Feb 15 '12 at 16:40
    
Hi Vinny, can you update the question by clicking "edit" above and adding detail where I put this phrase (((Please update this part with your experiences)))? Otherwise we're going to have to close this as too localized, because you won't be able to help others that had the same problems as you did. –  jcolebrand Feb 15 '12 at 17:12
add comment

1 Answer

up vote 5 down vote accepted

Please create this script

Fron the Linux prompt, open up an editor like vi

vi /root/CreateMySQLSlave.sh

and add these lines to it

MYSQL_MASTER_HOST=10.1.2.20
MYSQL_SLAVE_HOST=10.1.2.30
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"

MYSQLDUMP_OPTIONS="--master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"

CREATE_REPL_USER="GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"
mysql ${MYSQL_MASTER_CONN} -AN -e"${CREATE_REPL_USER}"

RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='repluser'," >> ${RELOAD_FILE}
echo "master_password='replpass'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}

mysql ${MYSQL_SLAVE_CONN} -A < ${RELOAD_FILE}

Next, make the file executable and run it

chmod +x /root/CreateMySQLSlave.sh
/root/CreateMySQLSlave.sh

That's it !!!

Make sure you set the IP Address for MYSQL_MASTER_HOST and MYSQL_SLAVE_HOST

By the way, don't worry about the dummy-file in the master_log_file parameter for CHANGE MASTER TO. Line 29 of the dump file should have the correct setting there due to the --master-data=1. You can see it by doing this to the dump file

head -29 /root/MySQLData.sql | tail -1

Make sure binary logging is already enabled on the master. Here is how you can tell: run SHOW MASTER STATUS;. If you get nothing back, you need to enable it like this:

Add this to /etc/my.cnf

[mysqld]
log-bin=mysql-bin

then restart mysql

OK that takes care of binary logging for the Master.

You also need to make sure the Master and the Slave have different server IDs. Here is how you can tell:

Run SHOW VARIABLES LIKE 'server_id'; If the result comes up as 1, you must set it explicitly.

Goto the Master and add this to /etc/my.cnf

[mysqld]
server-id=1

Goto the Slave and add this to /etc/my.cnf

[mysqld]
server-id=2

The main thing here is to make sure server_id is explicitly set in my.cnf on both Master and Slave. You must also make sure the server_id values are different from all other servers involved in the replication topology.

If you are new to this, have a sysadmin join you to do this.

share|improve this answer
    
What exactly do you mean by run this script?? Where should i run it? (sorry i am a very novice user) –  vinny Feb 6 '12 at 18:40
    
I gave the lines for a script in my answer to create a slave on another machine running mysql. You need to create a file on the DB server with these lines, make the script executable (chmod +x) and run the script as given. By the way, what OS are you using, Windows or Linux ??? –  RolandoMySQLDBA Feb 6 '12 at 18:43
    
CentOS 6 and MySQL5.5.20 –  vinny Feb 6 '12 at 19:40
    
Thanks a lot for such an explanatory answer.But, the problem is, The data has already been transferred from DB1 to DB2. I only need a way to set up auto-replication from Master to Slave. Please tell me what changes to be done in "my.cnf" –  vinny Feb 7 '12 at 20:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.