/var/net/sys/admin/blog
| More

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;

Share

1 Response to “Setting up MySQL Database Replication”

  1. Mohan

    on August 14 2015

    My Slave is 100000 seconds behind primary db, how can i fix this issue

Comment RSS · TrackBack URI

Leave a comment

Name: (Required)

E-mail: (Required)

Website:

Comment:

 

About FLT

This site is dedicated to everyone who likes to learn and explore the beautiful world of Linux. If you have comments and suggestions, please feel free to email at freelinuxtutorials@gmail.com. I am happy to serve and share things esp. that is free and enjoyable as Linux.