Introduction

In a database driven enviroment, it is important to ensure that the database has a zero downtime. Typically, end-users rate their service providers on uptime and disaster rsponse. In this howto, you will configure a MySQL Master and Master enviroment which allows database information to be replicated to more than one source.

The Servers:

• Server1(Adama) 192.168.0.100
• Server2(Starbuck) 192.168.0.103

Basic Preparation:

On both of the servers run in the terminal the following:

yum install mysql mysql-server

On the Master and Slave servers start the mysqld daemon and do the following on both.

yum install mysql mysql-server

Back on the Master server (adama) create a basic database and a couple tables for dummy data. Run the below commands:

mysqladmin -u root password passwordGoesHere mysql -u root -p Enter password:

On the Master, create a database and a couple tables:  

create database testdatabase; use testdatabase; create table galactica (a INT NOT NULL AUTO_INCREMENT, b INT, PRIMARY KEY (a), KEY(b)) ENGINE=MyISAM; create table toaster (a INT NOT NULL AUTO_INCREMENT, b INT, PRIMARY KEY (a), KEY(b)) ENGINE=MyISAM;

With some testtables and a dummy database we are ready to begin. Just one more step, enter the following and where %mysql_slaveuser_password% insert your password inside the single quotes.  

GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY '%mysql_slaveuser_password%'; FLUSH PRIVILEGES; quit;

On the Slave, do the following:

mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY '%mysql_slaveuser_password%'; FLUSH PRIVILEGES; CREATE DATABASE testdatabase; quit;

Since we want the two machines to make a connection make sure TCP port 3306 is allowed. If just following this howto just turn off iptables with service iptables stop. On both machines do the following so the MySQL server is able to create log-files we have to create a directory and pass the ownership to MySQL.  

mkdir /var/log/mysql/ chown mysql:mysql /var/log/mysql/

On Master1 (Adama) in /etc/my.cnf add the following lines and modify appropriately under the [mysqld] context:

server-id = 1

replicate-same-server-id = 0

auto-increment-increment = 2

auto-increment-offset = 1

master-host = 192.168.0.103

master-user = slave1_user

master-password = YourPasswordHere

master-connect-retry = 60

replicate-do-db = testdatabase

log-bin = /var/log/mysql/mysql-bin.log

binlog-do-db = testdatabase

relay-log = /var/lib/mysql/slave-relay.log

relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10

max_binlog_size = 500M

Restart mysqld with:

service mysqld restart

On Server2 (starbuck)in /etc/my.cnf  

server-id = 2

replicate-same-server-id = 0

auto-increment-increment = 2

auto-increment-offset = 2

master-host = 192.168.0.100

master-user = slave2_user

master-password = YourPasswordHere%

master-connect-retry = 60

replicate-do-db = testdatabase

log-bin= /var/log/mysql/mysql-bin.log

binlog-do-db = testdatabase

relay-log = /var/lib/mysql/slave-relay.log

relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10

max_binlog_size = 500M

Restart mysqld with:

service mysqld restart

Back on Server1(Adama)

mysql -u root -p USE testdatabase; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; SHOW MASTER STATUS;

will output the following:

+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 98 | testdatabase | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Quickly open a 2nd terminal to Server1 and do the following:

cd /tmp

mysqldump -u root -pYourpasswordHere(DontremovetheP) --opt testdatabase > sqldump.sql

scp sqldump.sql [email protected]:/tmp/

The authenticity of host '192.168.0.103 (192.168.0.103)' can't be established. RSA key fingerprint is 6a:57:dd:9e:5f:04:d0:5e:3c:e4:41:a1:e3:62:fb:dd. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.103' (RSA) to the list of known hosts. [email protected]'s password: sqldump.sql 100% 2060 2.0KB/s 00:00

Now close the 2nd terminal for Server1(Adama)

In the 1st terminal for Server1(Adama) we need to unlock the tables and then quit.

UNLOCK TABLES; quit;

Switch back to Server2(Starbuck)'s terminal and enter the following:  

mysqladmin --user=root --password=YourPasswordHere

stop-slave Slave stopped

cd /tmp

mysql -u root -pYourPasswordDontForgetThep testdatabase < sqldump.sql

Now we need to do some modification to the database still on Server2.

mysql -u root -p USE testdatabase;

FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1292 | testdatabase | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Keep note of the File and the position. Still on Server 2:  

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave2_user', MASTER_PASSWORD='YourPasswordHere', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

SHOW SLAVE STATUS;

QUIT;

Open a terminal to Server1(Adama)

mysql -u root -p

STOP SLAVE CHANGE MASTER TO MASTER_HOST='192.168.0.103', MASTER_USER='slave1_user', MASTER_PASSWORD='YourPasswordHere', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1292;

Now start the slave ...

START SLAVE;

Look at the slave status. It's very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they aren't, you have a problem.

Check the logs. SHOW SLAVE STATUS; QUIT;

Now you have a Master to Master MySQL replication. Any problems, just refer to the logs.