Master slave replication in MySQL 5.5

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
  • cd /opt
  • 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
  • cd 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

% mysql -u root -h 127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.3-m3 MySQL Community Server (GPL)

To shut down your MySQL use

mysqladmin -h 127.0.0.1 -u root shutdown

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:

CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

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.

CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='repl', MASTER_PASSWORD='slavepass';
START SLAVE;

That’s it! Let’s test it, on your master issue:

CREATE DATABASE symfony2;
USE symfony2;
CREATE TABLE test_table (a INT);
INSERT INTO test_table VALUES (1);

Corresponding table should be created on slave and should contain one record. Now, let’s test temporary disabling replication log on master:

SET sql_log_bin=0;
INSERT INTO test_table VALUES (2),(3);
SET sql_log_bin=1;
INSERT INTO test_table VALUES (4),(5);

You table on master should contain 5 rows but the one on the slave only 3 – the values 2 and 3 should be missing.

Leave a Comment


*


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">