Create MySql replication on CentOS

CentOS 5.7  - Mysql 5.5

• Install MySql
• Configure Master and Slave

Install Mysql

1. Remove previously installed MySQL (if applicable)

# yum erase mysql

2. Install additional repos

Navigate to temporary directory

# cd /tmp

Install EPEL repo

# wget http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

# rpm -Uvh epel-release-5-4.noarch.rpm

Install remi repo

# wget http://rpms.famillecollet.com/enterprise/remi-release-5.rpm

# rpm -Uvh remi-release-5.rpm

3. Ensure the repo contains MySQL 5.5

# yum --enablerepo=remi list mysql mysql-server 

4. Install MySQL 5.5

# yum install --enablerepo=remi mysql*

5. Start MySQL and configure to start on boot

# service mysqld start

# chkconfig mysqld on 

6. Run mysql upgrade script

# mysql_upgrade -u root

7. Change Mysql default Password

# mysqladmin -u root password "XXXXX"

8. Check to ensure mysql 5.5 is installed. 

# mysql -uroot -p

SELECT version();

Configure Master and Slave

Master

1. Edit master configuration

# vi /etc/my.cnf

Comment out those lines if present

#skip-networking
#bind-address            = 127.0.0.1
 
Add those lines in [mysqld] section

log-bin = /var/logs/mysql/mysql-bin.log
binlog-do-db=my_database
server-id=1

2. restart MySQL

# service mysqld restart

3. Connect to mysql and create a replication user

mysql -u root -p
Enter password:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';

FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Write down this information, i will need it later on the slave

 File                        | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------+-------------------+
| mysql-bin.000001 |      107  | employees        |                               |


quit

4. take a backup of db


# mysqldump -u root -p<password> --opt exampledb > exampledb.sql

5. Finally we have to unlock the tables in exampledb:

# mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;


Slave

1. Create db

# mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;

2. Import data from master

mysql -u root -p<password> exampledb < /path/to/exampledb.sql

3. Edit slave config

 # vi /etc/my.cnf

For mysql 5.1
[mysqld]
server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb

[mysqld_safe]
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index

For mysql 5.5

[mysqld]
server-id=2
replicate-do-db=exampledb

[mysqld_safe]
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index

4. From mysql shell

$ change master to master_host='192.168.0.11', master_port=3306, master_user='slave_user', master_password='slave_user', master_log_file='mysql-bin.000001', master_log_pos=107, master_connect_retry=10;


START SLAVE;

SHOW SLAVE STATUS \G;


DONE