I’d like to share a quick guide on how to set up master/slave replication for the MySQL 5.5 server. The procedure below should be used for development/testing only. If you want to create a production-ready setup, you should follow instructions from MySQL official documentation or use MySQL server packaged by your favorite Linux distribution.
1. Download latest MySQL 5.5 from http://dev.mysql.com/downloads/mysql.
2. Follow the installation instructions on http://dev.mysql.com/doc/refman/5.5/en/binary-installation.html or instructions below if you don’t care about the secure setup (e.g. you are only using this MySQL installation for testing). You should also follow my instructions if you want to avoid conflicts with the MySQL you may have installed from your Linux distribution package. Do not follow them for the production setup.
- switch to root
- on ubuntu/debian run
sudo apt-get install libaio1
- download mysql tarball
- unpack it
tar -zxf mysql-5.5.3-m3-linux2.6-x86_64-icc.tar.gz
ln -s mysql-5.5.3-m3-linux2.6-x86_64-icc mysql
scripts/mysql_install_db --basedir=/opt/mysql --datadir=/opt/mysql/data
bin/mysqld_safe --defaults-file=support-files/my-medium.cnf --user=root --basedir=/opt/mysql --datadir=/opt/mysql/data
You should be able to connect as a root without the password
To shut down your MySQL use
Now, repeat the procedure on the second machine and we can move on.
3. Set up the replication.
Master will already be configured for the replication, as the whole necessary setup (log-bin,server-id) is already included in support-files/my-medium.cnf. You will still need to create a replication user on the slave:
Go to your slave and set in the /opt/mysql/support-files/my-medium.cnf:
server-id=2 (replace the default id 1 with 2)
Make sure you can connect from the machine where slave is installed to the master MySQL server, using ‘repl’ user.
That’s it! Let’s test it, on your master issue:
Corresponding table should be created on slave and should contain one record. Now, let’s test temporary disabling replication log on master:
You table on master should contain 5 rows but the one on the slave only 3 – the values 2 and 3 should be missing.