Setting up MySQL Database Replication

I.Configure Master

1. edit /etc/mysql/my.cnf. We have to enable networking for MySQL,

I.Configure Master

1. edit /etc/mysql/my.cnf. We have to enable networking for MySQL,

#skip-networking

2. Add the following line to the my.cnf

server-id = 1
log-bin=/storage/lun0/db/mysql/FLTVM01-bin

3. restart mysql
#service mysqld restart

4.     log into the MySQL database as root and create a user with replication privileges:

#mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser’@’%’ IDENTIFIED BY ‘<some_password>’; (Replace <some_password> with a real password!)
mysql>FLUSH PRIVILEGES;

5. To accomplish identical data on both master and slave  server, prevent all writes on the master via Lock Tables & Show Log Position
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

NOTE: Keep this prompt running  in order for lock to be ACTIVE

e.g.

mysql> SHOW MASTER STATUS;
+——————–+———–+————–+——————+
| File               | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+——————–+———–+————–+——————+
| FLTVM01-bin.000051 | 444132144 |              |                  |
+——————–+———–+————–+——————+
1 row in set (0.00 sec)

mysql> exit

6. Dump Database

#mysqldump -u root -p<password> –lock-all-tables –all-databases > alldb.sql

7. Unlock Tables on the master after dumping

mysql> UNLOCK TABLES;

II. Configure Slave

1.Create Database

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

2. Restore Database

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

3. Configure Slave configuration, assuming master is 192.168.1.2,

server-id=2
master-host=192.168.1.2
master-user=slave_user
master-password=secret
master-connect-retry=60

4. Restart MySQL service
#service mysql restart

5. Issue command Slave Stop and Change master

#mysql -u root -p
Enter password:
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.1.2′, MASTER_USER=’slaveuser’, MASTER_PASSWORD=’slavepass!’, MASTER_LOG_FILE=’FLTVM01-bin.000051′, MASTER_LOG_POS=444132144;

6. Start Slave

mysql> START SLAVE;
mysql>quit

7. Check for errors
#tail -f /var/log/mysqld.log

III. CHECKING & TESTING

status of the master can be known from mysql prompt:

mysql> SHOW MASTER STATUS;

and slave can be known by:

mysql> SHOW SLAVE STATUS;

To test:

1. On sample database on master, create table

Example:

mysql>use testdb;
mysql>CREATE TABLE example (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100)       );

–> insert data on table

mysql> INSERT INTO example (data)  VALUES (‘Hello world’);

2. Check the Slave database and see if the following changes are replicated.
mysql>use testdb;
mysql> select * from example;