Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
MySQL Master Slave

MySQL Master Slave

MySQL Master Master Repliction Tutorial

This tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.

Hereツ is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.

Notions:we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.

Step 1:

Install mysql on master 1 and slave 1. configure network services on both system, likeツ

ツ ツMaster 1/Slave 2 ip: 192.168.16.4

ツ ツMaster 2/Slave 1 ip : 192.168.16.5ツ

Step 2:

ツ ツOn Master 1, make changes in my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>ツ # input the database which should be replicated
binlog-ignore-db=mysqlツツツツツツツツツツツ # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 3:

On master 1, create a replication slave account in mysql.

mysql> grant replication slave on *.* to 'replication'@192.168.16.5 \
identified by 'slave';

and restart the mysql master1.ツ

Step 4:

Now edit my.cnf on Slave1 or Master2 :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

ツStep 5:

Restart mysql slave 1 and at

mysql> start slave;
mysql> show slave status\G;


*************************** 1. row ***************************

ツツツツツツツツツツツツ Slave_IO_State: Waiting for master to send event
ツツツツツツツツツツツツツツツ Master_Host: 192.168.16.4
ツツツツツツツツツツツツツツツ Master_User: replica
ツツツツツツツツツツツツツツツ Master_Port: 3306
ツツツツツツツツツツツツツ Connect_Retry: 60
ツツツツツツツツツツツ Master_Log_File: MASTERMYSQL01-bin.000009
ツツツツツツツ Read_Master_Log_Pos: 4
ツツツツツツツツツツツツ Relay_Log_File: MASTERMYSQL02-relay-bin.000015
ツツツツツツツツツツツツツ Relay_Log_Pos: 3630
ツツツツツ Relay_Master_Log_File: MASTERMYSQL01-bin.000009
ツツツツツツツツツツ Slave_IO_Running: Yes
ツツツツツツツツツ Slave_SQL_Running: Yes
ツツツツツツツツツツツ Replicate_Do_DB:
ツツツツツツツ Replicate_Ignore_DB:
ツツツツツツツツ Replicate_Do_Table:
ツツツツ Replicate_Ignore_Table:
ツツツ Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
ツツツツツツツツツツツツツツツツ Last_Errno: 0
ツツツツツツツツツツツツツツツツ Last_Error:
ツツツツツツツツツツツツツツ Skip_Counter: 0
ツツツツツツツ Exec_Master_Log_Pos: 4
ツツツツツツツツツツツ Relay_Log_Space: 3630
ツツツツツツツツツツツ Until_Condition: None
ツツツツツツツツツツツツ Until_Log_File:
ツツツツツツツツツツツツツ Until_Log_Pos: 0
ツツツツツツツツ Master_SSL_Allowed: No
ツツツツツツツツ Master_SSL_CA_File:
ツツツツツツツツ Master_SSL_CA_Path:
ツツツツツツツツツツツ Master_SSL_Cert:
ツツツツツツツツツ Master_SSL_Cipher:
ツツツツツツツツツツツツ Master_SSL_Key:
ツツツツツ Seconds_Behind_Master: 1519187

1 row in set (0.00 sec)

Above highlighted rows must be indicate related log files andツ Slave_IO_Running and ツ Slave_SQL_Running: must be to YES.

Step 6:

On master 1:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| Fileツツツツツツツツツツツツツツツツツツ | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 |ツツツツツ 410 | adamツツツツツツツツ |ツツツツツツツツツツツツツツツツツ |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.

Step 7:

On Master2/Slave 1, edit my.cnf and master entries into it:

ツ[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-binツツツツツツツツツツツツツツツツツツツツ #information for becoming master added
binlog-do-db=adamツ

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 8:

Create a replication slave account on master2 for master1:

mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave2';

Step 9:

Edit my.cnf on master1 for information of its master.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

[mysql.server]user=mysqlbasedir=/var/libツ

Step 10:

Restart both mysql master1 and master2.

On mysql master1:

mysql> start slave;

On mysql master2:ツ

mysql > show master status;

On mysql master 1:

mysql> show slave status\G;


*************************** 1. row ***************************
ツツツツツツツツツツツツ Slave_IO_State: Waiting for master to send event
ツツツツツツツツツツツツツツツ Master_Host: 192.168.16.5
ツツツツツツツツツツツツツツツ Master_User: replica
ツツツツツツツツツツツツツツツ Master_Port: 3306
ツツツツツツツツツツツツツ Connect_Retry: 60
ツツツツツツツツツツツ Master_Log_File: Mysql1MYSQL02-bin.000008
ツツツツツツツ Read_Master_Log_Pos: 410
ツツツツツツツツツツツツ Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
ツツツツツツツツツツツツツ Relay_Log_Pos: 445
ツツツツツ Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
ツツツツツツツツツツ Slave_IO_Running: Yes
ツツツツツツツツツ Slave_SQL_Running: Yes
ツツツツツツツツツツツ Replicate_Do_DB:
ツツツツツツツ Replicate_Ignore_DB:
ツツツツツツツツ Replicate_Do_Table:
ツツツツ Replicate_Ignore_Table:
ツツツ Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
ツツツツツツツツツツツツツツツツ Last_Errno: 0
ツツツツツツツツツツツツツツツツ Last_Error:
ツツツツツツツツツツツツツツ Skip_Counter: 0
ツツツツツツツ Exec_Master_Log_Pos: 410
ツツツツツツツツツツツ Relay_Log_Space: 445
ツツツツツツツツツツツ Until_Condition: None
ツツツツツツツツツツツツ Until_Log_File:
ツツツツツツツツツツツツツ Until_Log_Pos: 0
ツツツツツツツツ Master_SSL_Allowed: No
ツツツツツツツツ Master_SSL_CA_File:
ツツツツツツツツ Master_SSL_CA_Path:
ツツツツツツツツツツツ Master_SSL_Cert:
ツツツツツツツツツ Master_SSL_Cipher:
ツツツツツツツツツツツツ Master_SSL_Key:
ツツツツツ Seconds_Behind_Master: 103799
1 row in set (0.00 sec)

ERROR:
No query specified

Check for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave.ツEnjoy!!

Comments   

 
0 #1 Guest 2016-10-13 23:54
_________________________
_____________________________
_______________3________
____________WEB_______________
__________________________
__________________________

Here is my webpage: _____________
Quote
 

Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email