setting up replication with mysql – slave / master

Whilst the mysql cluster option is useful for a HA / scalable solution, sometimes the “old” mysql config of having a master node, with one or more slave nodes is actually the most idea option.

Using a mysql cluster configuration allows you to add nodes simply and increase the power available to you simply, its quite a involved solution with multiple nodes required as well as controller stations, added with the fact that you need the cluster nodes to be on a low latency link means it might not fit the requirement.

Using the master slave option is great if you dont need a massive amount of writes, if you want to replicate between sites, or if you just want a quick solution to provide you with a resilient platform (albeit not instant)

So to setting up the solution.

You need at least 2 nodes running ideally the same version of mysql and the master node needs port 3306 access to the slave / slaves.

Once you have set that up, you need to configure the master to push updates out to the slave, you also need to ensure that from a starting point, both the slave and the master have the same db’s etc..

So below are the steps you need to take to do this:

Edit the my.cnf on your master under the mysqld section with the follow (assuming you have a standard install and things are in the default location, adjust paths if required) :

server-id=1

relay-log=/usr/local/mysql/var/mysql-relay-bin

relay-log-index=/usr/local/mysql/var/mysql-relay-bin.index

log-error=/usr/local/mysql/var/mysql.err

master-info-file=/usr/local/mysql/var/mysql-master.info

relay-log-info-file=/usr/local/mysql/var/mysql-relay-log.info

datadir=/usr/local/mysql/var

log-bin=/usr/local/myql/var/mysql-bin

Now put the following in your slaves my.cnf

server-id=2

relay-log=/usr/local/mysql/var/mysql-relay-bin

relay-log-index=/usr/local/mysql/var/mysql-relay-bin.index

log-error=/usr/local/mysql/var/mysql.err

master-info-file=/usr/local/mysql/var/mysql-master.info

relay-log-info-file=/usr/local/mysql/var/mysql-relay-log.info

datadir=/usr/local/mysql/var

Now the configs are done, you need to sort out the users and permissions.

On the master you need to create a user that has permissions on the slave, do this as follows:

mysql> grant replication slave on *.* to useronslave@’slaves.ip.add.ress’ identified by ‘useronslavepassword’;

Obviously substituting useronslave for your actual username on the slave, slaves.ip.add.ress for your slaves ip, i.e 192.168.0.3 and your useronslavepassword for your slave users password.

Now export the database from your master to your slave, to initially create the sync, as below:

mysqldump -u root -p –all-databases –single-transaction –master-data=1 > masterdump.sql

Next copy this .sql file over to the slave and import it so the slave is now in sync, as below run this on the slave:

mysql -u root -p < masterdump.sql

Once the above dump import has finished you will need to tell the slave which master to connect to, do the following, again substituting users and passwords as appropriate (again done on the slave).

mysql> CHANGE MASTER TO MASTER_HOST=’ip.address.of.master’, MASTER_USER=’useronslave’, MASTER_PASSWORD=’useronslavepassword’;

once that is done start up the slave

mysql> start slave;

if you want to see the status of the slave, type

mysql> show slave status\G

That should be everything done, you should now have your master and slave in sync and updating themselves as required.

You can follow any responses to this entry through the RSS 2.0 feed.

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This blog is kept spam free by WP-SpamFree.