{"id":2241,"date":"2013-07-12T15:12:00","date_gmt":"2013-07-12T07:12:00","guid":{"rendered":"http:\/\/rmohan.com\/?p=2241"},"modified":"2013-07-12T15:12:54","modified_gmt":"2013-07-12T07:12:54","slug":"mysql-master-slave-replication","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=2241","title":{"rendered":"Mysql Master Slave Replication"},"content":{"rendered":"

Setting the Replication Master Configuration<\/p>\n

On a replication master, you must enable binary logging and establish a unique server ID.<\/p>\n

Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves.<\/p>\n

If binary logging is not enabled, replication will not be possible.<\/p>\n

Each server within a replication group must be configured with a unique server ID.This ID is used to identify individual servers within the group.<\/p>\n

To configure the binary log and server ID options, you will need to shut down your MySQL server and edit the my.cnf or my.ini file.\u00a0 Add the following options to the configuration file within the [mysqld] section.<\/p>\n

to enable binary logging using a log file name prefix of\u00a0mysql-bin, and configure aserver ID of 1, use these lines:<\/p>\n

[mysqld]
\nlog-bin=mysql-bin
\nserver-id=1<\/p>\n

After making the changes, restart the server.<\/p>\n

If you omit server-id\u00a0(or set it explicitly to its default value of 0),\u00a0a master refuses connections from all slaves.<\/p>\n

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.<\/p>\n

Ensure that the skip-networking\u00a0 option is not enabled on your replication master. If networking has been disabled, your slave will not able to communicate with the master and replication will fail.<\/p>\n

if your master is also a slave (DB1 is the master of DB2, DB2 is the master of DB3) in order for DB2 to log updates from his master DB1 to the binlog (so DB3 can read them) you need to put “log-slave-updates” on\u00a0my.cnf or my.ini.<\/p>\n

Replication Implementation<\/p>\n

\u00a0Replication is based on the master server keeping track of all changes to its databases\u00a0(updates, deletes, and so on)\u00a0in its binary log.The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically,SELECT\u00a0 statements are not recorded because they modify neither database structure nor content.<\/p>\n

Each slave that connects to the master requests a copy of the binary log.\u00a0That is, it pulls the data from the master, rather than the master pushing the data to the slave.\u00a0The slave also executes the events from the binary log that it receives.This has the effect of repeating the original changes just as they were made on the master. Tables are created or their structure modified, and data is inserted, deleted, and updated according to the changes that were originally made on the master.<\/p>\n

Because each slave is independent, the replaying of the changes from the master’s binary log occurs independently on each slave that is connected to the master. In addition, because each slave receives a copy of the binary log only by requesting it from the master, the slave is able to read and update the copy of the database at its own pace and can start and stop the replication process at will without affecting the ability to update to the latest database status on either the master or slave side.<\/p>\n<\/div>\n

\u00a0 http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication-implementation-details.html<\/div>\n
<\/div>\n
Masters and slaves report their status in respect of the replication process regularly\u00a0so that you can monitor them.
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/thread-information.html<\/div>\n
<\/div>\n
The master binary log is written to a local relay log on the slave before it is processed. The slave also records information about the current position with the master’s binary log and the local relay log.<\/div>\n
\u00a0http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/slave-logs.html<\/div>\n
<\/div>\n
Database changes are filtered on the slave according to a set of rules that are applied according to the various configuration options and variables that control event evaluation.
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication-rules.html<\/div>\n
<\/div>\n
Steps For Mysql Master-slave Replication<\/div>\n
<\/div>\n
One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave.\u00a0This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from.\u00a0That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump,\u00a0site lags when table locks happen.You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.<\/p>\n

let us dive into how to setup master-slave replication under MySQL. There are many configuration changes you can do to optimize your MySQL set up.<\/p>\n<\/div>\n

<\/div>\n
Master server ip: 192.168.1.231
\nSlave server ip : 192.168.1.232Slave username : mysqlslave
\nSlave password : slavepwd<\/p>\n

Your data directory is:\u00a0 \/var\/lib\/mysql<\/p>\n<\/div>\n

<\/div>\n
1.\u00a0In Master# yum install mysql mysql-server
\n# service mysqld start
\n# mysqladmin -uroot password ‘master’
\n# service mysqld stop<\/p>\n

Edit the my.cnf file under [mysqld] section of your mysql master<\/p>\n

# vim \/etc\/my.cnf<\/p>\n

[mysqld]
\nserver-id = 1
\nrelay-log = \/var\/lib\/mysql\/mysql-relay-bin
\nrelay-log-index = \/var\/lib\/mysql\/mysql-relay-bin.index
\nlog-error = \/var\/lib\/mysql\/mysql.err
\nmaster-info-file = \/var\/lib\/mysql\/mysql-master.info
\nrelay-log-info-file = \/var\/lib\/mysql\/mysql-relay-log.info
\ndatadir=\/var\/lib\/mysql
\nlog-bin = \/var\/lib\/mysql\/mysql-bin<\/p>\n

# service mysqld restart<\/p>\n<\/div>\n

<\/div>\n
2.\u00a0In Slave# yum install mysql mysql-server
\n# service mysqld start
\n# mysqladmin -uroot password ‘slave’
\n# service mysqld stop<\/p>\n

Add the the following under [mysqld] to the mysql slave by edting my.cnf<\/p>\n

# vim \/etc\/my.cnf<\/p>\n

[mysqld]
\nserver-id = 2
\nrelay-log = \/var\/lib\/mysql\/mysql-relay-bin
\nrelay-log-index = \/var\/lib\/mysql\/mysql-relay-bin.index
\nlog-error = \/var\/lib\/mysql\/mysql.err
\nmaster-info-file = \/var\/lib\/mysql\/mysql-master.info
\nrelay-log-info-file = \/var\/lib\/mysql\/mysql-relay-log.info
\ndatadir=\/var\/lib\/mysql<\/p>\n

# service mysqld restart<\/p>\n

3.\u00a0Then in Mysql Master server create a user with replication privileges<\/p>\n

# mysql -uroot -pmaster<\/p>\n<\/div>\n

mysql>\u00a0 grant replication slave on *.* to mysqlslave@’192.168.1.232′ identified by ‘slavepwd’;
\nmysql> flush privileges;You can see the new user on the master db by<\/p>\n<\/div>\n
mysql> show databases;
\nmysql> use mysql;
\nmysql> show tables;
\nmysql> select * from user;<\/div>\n
<\/div>\n
4.\u00a0Take a dump of data from Mysql Master to move to slave<\/div>\n
<\/div>\n
# mysqldump -uroot -p –all-databases –single-transaction –master-data=1 > masterdump.sql\u00a0(master dump is put into slave inorder to make the master and slave data similar before starting the sync)after taking the dump of master import it in slave server,<\/p>\n

# scp masterdump.sql root@192.168.1.232:<\/p>\n<\/div>\n

# ssh root@192.168.1.232 —–> slave machine# mysql -uroot -p < masterdump.sql<\/p>\n<\/div>\n
<\/div>\n
After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login\/password to use: ( In slave machine )<\/div>\n
<\/div>\n
# mysql -uroot -p<\/div>\n
mysql> change master to master_host=’192.168.1.231′, master_user=’mysqlslave’, master_password=’slavepwd’;<\/div>\n
mysql> flush privileges;<\/div>\n
<\/div>\n
5.\u00a0Let us start the slave<\/div>\n
<\/div>\n
mysql> start slave;You can check the status of the slave by typing
\nmysql> show slave status;
\nmysql> show slave status\\G<\/p>\n<\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0 ****************** 1. row *******************<\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slave_IO_State: Waiting for master to send event
\nMaster_Host: 192.168.1.231
\nMaster_User: mysqlslave
\nMaster_Port: 3306
\nConnect_Retry: 60
\nMaster_Log_File: mysql-bin.000001
\nRead_Master_Log_Pos: 706
\nRelay_Log_File: mysql-relay-bin.000008
\nRelay_Log_Pos: 446
\nRelay_Master_Log_File: mysql-bin.000001
\nSlave_IO_Running: Yes
\nSlave_SQL_Running: Yes
\nReplicate_Do_DB:
\nReplicate_Ignore_DB:
\nReplicate_Do_Table:
\nReplicate_Ignore_Table:
\nReplicate_Wild_Do_Table:
\nReplicate_Wild_Ignore_Table:
\nLast_Errno: 0
\nLast_Error:
\nSkip_Counter: 0
\nExec_Master_Log_Pos: 706
\nRelay_Log_Space: 446
\nUntil_Condition: None
\nUntil_Log_File:
\nUntil_Log_Pos: 0
\nMaster_SSL_Allowed: No
\nMaster_SSL_CA_File:
\nMaster_SSL_CA_Path:
\nMaster_SSL_Cert:
\nMaster_SSL_Cipher:
\nMaster_SSL_Key:
\nSeconds_Behind_Master: 0
\n1 row in set (0.01 sec)<\/div>\n
<\/div>\n
The last row tells you how many seconds its behind the master. Don\u2019t worry if it doesn\u2019t say 0, the number should be going down over time until it catches up with master (at that time it will show Seconds_Behind_Master: 0) If it shows NULL, it could be that slave is not started (you can start by typing: start slave).<\/div>\n
<\/div>\n
6.\u00a0For Checking whether replication is working or not create a db in mysql master server<\/div>\n
<\/div>\n
# create database replication;<\/div>\n
\u00a0\u00a0\u00a0and check in slave whether its replicated or not<\/div>\n
# show databases;
\nthe db replication will be there in slave as well<\/div>\n","protected":false},"excerpt":{"rendered":"

Setting the Replication Master Configuration<\/p>\n

On a replication master, you must enable binary logging and establish a unique server ID.<\/p>\n

Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves.<\/p>\n

If binary logging is not enabled, replication will not be […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2241"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2241"}],"version-history":[{"count":2,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2241\/revisions"}],"predecessor-version":[{"id":2243,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2241\/revisions\/2243"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2241"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2241"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2241"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}