[email protected]

learn – code – share

Mysql db replication process – Master to Slave

Dec
11

Master to Slave Replication:

Master server configuration:

open my.cnf location in etc/

and add this lines

[mysqld]
server-id=1
log-bin=mysql-bin

now restart mysql server

and dump the databases which one you want to replicate and copy to slave server

to dump all databases use

shell> mysqldump --all-databases > dump.sql

to dump only specified databases use

shell> mysqldump --databases db1 db2 db3 > dump.sql

once done we now create a user and grant permissions to replicate the db.

connect to mysql

and grant the access using.

mysql> grant replication slave on *.* to 'slaveusername'@'180.xxx.xxx.xxx' identified by 'slaveuserpassword';

once its done, to see the master status type

mysql> show master status;


+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

you will see the output like this save this file and position id for next steps.

now flush the tables and set the lock to read;

mysql > flush tables with read lock;

[incase if you want to unlock use this command ]

mysql > unlock tables;

 

Now, Slave configuration:

open my.cnf located in /etc/

and add this line

serverid = 2 

#this id needs to be higher than what we setup in master.

replicate-wild-do-table=mytestdb.%

# this line will replicate database mytestdb and all its tables.

save this file and restart your mysql server

once its restarted login to mysql and change the master using this query.

mysql > change master to

master_host = '170.xxx.xxx.xxx',    #this is your master host ip

master_user='slaveusername',  #this is the slave username we used in master

master_password='slaveusername_password', 

master_log_file='mysql-bin.000001',  # this the file we get when we did master status 

master_log_pos=120; # this is the position we get when we did master status

now all set, dump the databases we copied in master to slave, using mysqldump

shell> mysqldump test.sql < testdb

now start the slave

mysql > start slave;

all done, try changing some records in master and you will see immeditely changes in slave database.

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *