MySQL 5.5 Replication Bash Script
So the old way (worked in 5.1) of automating the replication setup doesn’t work anymore, luckily the below works just as well.
The below script is taken from some Stackoverflow answer, I had some problems with it though so I modified it a bit.
First of all the script assumes that you’ve setup the same user on both the master and the slave machine and that they can both access each other through the 3306 port, check your ip tables if you have to.
To setup the repadmin user on both slave and master run the following on both machines:
GRANT ALL PRIVILEGES ON *.* TO repadmin@'%' IDENTIFIED BY '1234';
FLUSH PRIVILEGES;Now you’re ready to run the below on your master machine after making the necessary substitutions of course (don’t miss the dbname replacements):
MYSQL_MASTER_HOST=master ip
MYSQL_SLAVE_HOST=slave ip
MYSQL_USER=repadmin
MYSQL_PASS=1234
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="dbname --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --skip-tz-utc"
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='${MYSQL_USER}'," >> ${RELOAD_FILE}
echo "master_password='${MYSQL_PASS}'," >> ${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} --default_character_set utf8 dbname < ${RELOAD_FILE}Don’t get tripped up by the master_log_file and master_log_pos values, just leave them like they are, I don’t know why the original poster even put them there in the first place, could be because they’re needed to avoid initial errors. The correct values are however automatically added to the SQL file through the –master-data flag.
Related Posts
Tags: mysql, replication