Posted by admin on March 22nd, 2010 |
0 commentsWhilst 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.
