Configuring MySQL Replication 5.5 in 6 steps

Posted on by Jordansphere

Scenario: You want to create a MySQL replication between an exisiting master and a brand new slave. Both machines running FreeBSD 8.x with MySQL 5.5. The master server currently has a mixture of InnoDB and MyISAM engines. A small amount of downtime is available.

Master server:    10.1.1.1
Slave server:     10.1.1.2

 

 

(1) ON MASTER

Edit my.cnf 

[mysqld] 


log-bin=mysql-bin
server-id=1
binlog-format=mixed

 

Restart MySQL server (/usr/local/etc/rc.d/mysql restart)

(2) ON SLAVE

Edit my.cnf

[mysqld]
  

server-id=2
binlog-format=mixed

 

(3) ON MASTER

mysql> CREATE USER 'replicationuser'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'%';

 

(4) ON MASTER

mysql> FLUSH TABLES WITH READ LOCK;

In different SSH session: 

mysql> SHOW MASTER STATUS;


+————–+———-+————–+——————+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————–+———-+————–+——————+
| mysql.000002 |  449494  |              |                  |
+————–+———-+————–+——————+

Take a note of the 'File' and 'Position'. This will be required later.

As some of the databases are InnoDB we will need to shut down the MySQL server and copy the raw data via rsync to the slave server. If you can't afford any downtime then you'll need to take a snapshot of the databases using mysqldump and then import it into the slave. The following is uses the rsync method. 

# mysqladmin -u root -p shutdown

 

(5) ON SLAVE

 

Sync data from /data/mysql directory on master to slave. For this I used this command: 

# /usr/local/bin/rsync -avpro --delete 10.1.1.1::alldbs /data/mysql/

I actually did a sync when the data was live before stopping the master mysql server and doing it again when it wasnt running. This minimised downtime as the sync was a lot quicker.

Note: alldbs is an rysnc module on the master server which just points to /data/mysql locally.

(When complete make sure to start the mysql server on the MASTER)

 

(6) ON SLAVE

Edit my.cnf and add skip-slave-start

Start the mysql server ( /usr/local/etc/rc.d/mysql-server start ) 


mysql> CHANGE MASTER TO
             MASTER_HOST='10.1.1.1',
             MASTER_USER='replicationuser',
             MASTER_PASSWORD='PASSWORD',
             MASTER_LOG_FILE='mysql.000002',
             MASTER_LOG_POS=449494;

  

mysql> START SLAVE;

Note: Dont forget to uncomment the "skip-slave-start" from my.cnf

 

You can get the status of the replication by logging into the slave server 

mysql> SHOW SLAVE STATUS;

 

As of MySQL 5.5 any new databases added will automatically get pushed over to the slave afterwards.