Replicates mysql realtime from many to many databases

replication.sh

 git clone  https://github.com/mysqlDBReplication/replication
    
   - Replicates mysql realtime from many to many databases
     https://github.com/mysqlDBReplication/replication/blob/master/src/Master-Master_configuration

SETTING UP MASTER MASTER CONFIGURATION OF MYSQL ON UBUNTU

STEP 1
	Install the mysql-server and mysql-client in both the servers

	command : sudo apt-get install mysql-server mysql-client

STEP 2

 	get the ip address of both the servers

	For example the ipaddress of servers are
		MASTER1 : 10.176.15.66
		MASTER2 : 10.176.15.85

STEP 3

	Once the mysql is installed in both the servers, in the MASTER1 server go to the directory /etc/mysql/

	Open the file my.cnf
	under the section [mysqld] please update the following variables(if not present please write them up)
	
	server-id		= 	1
	log_bin			= 	/var/log/mysql/mysql-bin.log
	binlog_do_db		= 	asterisk                                         #this is a database name
	log-slave-updates	= 	ON
	binlog_format		= 	MIXED
	slave-skip-errors	=	1054,1051

	Please comment the below line (if uncommented the mysql server will not allow any external machines to connect to this server)

	# !!! ALLOW TO CONNECT EXTERNAL
	#bind-address		= 127.0.0.1  

	the first line is the server-id of the MASTER1(which we can fix arbitarily)
	the second line is the binary logfiles path and file name(binary logs will be stored in this file)
	the third line binlog_do_db is the database for which binary logging should be done(can give multiple database names i have used asterisk as my database name)(we can also filter 

the databases using "Binlog_Ignore_DB" variable which will ignore these db but will log all other databases)
	the fourth line is log-slave-updates option which will ask the slave server to log the transactions that were done in masterserver as well. 
	the fifthe line "binlog_format" is the format in which logging should be happened. The possinble values are "ROW","STATEMENT" and "MIXED". if either ROW or STATEMENT is 

selected , database might give error when opposite(like ROW based when STATEMENT is selected) based transaction happens. It is always safe to give MIXED option
	
	the sixth line slave-skip-errors is not necessary in master-master replication setup.( it is used in master -slave setup) these 2 errors are happen when some one created the tables 

directly in slave and inserted the data in slave. when the same transactions comes from master the slave gives an error and stops replicating. To restart the replication we need to give this 

statement and restart the mysql server.(please note that if the table structure is different in slave than in master this might create a different and error and replication issues. hence make 

sure not to add anything directly in slave server when the configuration is done as master-slave)

	
STEP 4
	save the above configuration file and restart the server

STEP 5

	create an user by which the other server connects this server

	example :
		create user asterisk_master@'%' identified by '<password>'

STEP 6
	once the user is created grant the replication permissions to this user
	
	example
		grant replication slave on *.* to asterisk_master

STEP 7
	Get the master status of the MASTER1 server using the following command
	
	show master status;

	show master status;
	+------------------+----------+--------------+------------------+
	| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
	+------------------+----------+--------------+------------------+
	| mysql-bin.000002 |      299 | asterisk     |                  |
	+------------------+----------+--------------+------------------+

	make a note of this which we will use to configure master 2

STEP 8

	Repeat Step 3 in MASTER2 server. give serverid as 2 in this instance

STEP 9
	save the above configuration file and restart the server

STEP 10

	create an user by which the other server connects this server

	example :
		create user asterisk_slave@'%' identified by '<password>';

STEP 11
	once the user is created grant the replication permissions to this user
	
	example
		grant replication slave on *.* to asterisk_slave;

STEP 12
	Get the master status of the MASTER1 server using the following command
	
	show master status;

	show master status;
	+------------------+----------+--------------+------------------+
	| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
	+------------------+----------+--------------+------------------+
	| mysql-bin.000001 |      346 | asterisk     |                  |
	+------------------+----------+--------------+------------------+

	make a note of this which we will use to configure master 1

STEP 13
	on MASTER1 server run the following commands

	slave stop;
	CHANGE MASTER TO MASTER_HOST = '<master2 ip address>', MASTER_USER ='<replicator user of MASTER2>',MASTER_PASSWORD = 'password of MASTER2 user', 

MASTER_LOG_FILE = 'logfile of MASTER2', MASTER_LOG_POS = <log position in the logfile>;
	slave start;

	
	example:
	slave stop;
	CHANGE MASTER TO MASTER_HOST = '10.176.15.66', MASTER_USER ='asterisk_slave',MASTER_PASSWORD = 'asterisk', MASTER_LOG_FILE = 'mysql-bin.000001', 

MASTER_LOG_POS = 346;
	slave start;

STEP 14

	repeat the STEP 13 in MASTER2


Thats it the setup is done
create anything on anyserver and check if it is reflected on other server



MISTAKES WE GENERALLY MAKE : 
	1) Not commenting the bind-address in my.cnf file(this will not allow other servers to connect)
	2) not placing the correct database name in  "binlog_do_db" variable.

Here’s the overview of how we’ll accomplish this:

Enable binary logging for both databases
Export data from database A
Import data into database B
Setup an SSH tunnel between database A and B
Setup replication permissions
Make B a slave of A
Make A a slave of B
Quick note: In the steps below, I’ve used a$ to indicate a shell command to be issued on the host for database A. Likewise, b$ is for shell commands on database B. a> is a SQL command on database A and b> is a SQL command for database B.

Step 1: Enable Binary Logging
We need to adjust the database configuration (/etc/mysql/my.cnf) for databases A and B to enable what’s called binary logging. The database’s binary log can be thought of as a log of all the SQL statements it has received.

Here’s what we want in my.cnf for database A:

server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
And here’s what we want for database B:

server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
Note that they each have a unique server_id. That’s important because when these replication logs are flying all over the place, MySQL needs to know from which database the statement originated (to avoid duplication and things like that).

The auto-increment-offset and auto-increment-increment are also very important. If we’re going to allow writes to each database, we don’t want collisions for auto-incrementing ID fields. To avoid this, we set each server to increment by 2 instead of 1, and set database A to only use odd IDs and database B to use even ones.

Changes to my.cnf require a database restart to take effect. So go ahead and restart your databases.

Step 2: Export data from database A
You’ve probably used mysqldump before to export data from MySQL. This is no different, except we want to record the position in the binary log when we do it.

The reason this is important is that when we slave up database B to database A, we need to tell it where in database A’s binary log to start working from.

To do this we will lock the database, dump the data, record the position in the binary log, and release the lock. The lock is necessary so that we’re assured binary log position doesn’t change while we’re exporting the data. Without this lock, pesky users could be inserting and updating rows during our data dump!

Ok, here we go. Login to mysql on database A and issue these commands to lock the database:

a> FLUSH TABLES WITH READ LOCK;
a> SHOW MASTER STATUS;
Record the output of the last statement. It’s the binary log position! It will look something like this:

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
Now in another terminal on database A, dump the data:

a$ mysqldump -u root -p database > ~/my_database.sql
As soon as that dump is complete, release the read lock:

a> UNLOCK TABLES;
Great. Now we have the export of the database and the exact position in the binary log to which it corresponds.

Step 3: Import data into database B
If the dataset is reasonably large and we’re sending it over the public internet, we should probably compress it first so it doesn’t take forever to transport.

a$ gzip my_database.sql
Now let’s copy it to the host database B lives on:

a$ scp my_database.sql.gz user@database-b:~/my_database.sql.gz
Now we switch over to database B for the import. Let’s first unzip the file:

b$ gunzip database.sql.gz
Now let’s make the database and import the data:

b$ mysqladmin -uroot -p create my_database
b$ pv my_database.sql | mysql -uroot -p my_database
If you haven’t used pv before, I highly recommend it. It will allow you to see the speed and progress of the import.

Ok, so now we’ve got the snapshot of database A imported into database B. Next, we need to setup an SSH tunnel between hosts A and B so the two databases can talk.

Step 4: Setup the SSH tunnel
If your two database machines are both on a private network that you trust, you can skip this step and just have them talk directly over that network. If your databases are only connected via the public internet, you’ll want to setup an SSH tunnel so people can’t snoop on your information and database passwords.

We want to setup the SSH tunnel such that on database A we can connect to localhost:13306 and it will forward to port 3306 on database B, and vice versa.

In this way, if we’re on database A, we can connect to database B via localhost:13306 and if we’re on database B we can connect to database A via localhost:13306.

Setting this up is actually really easy. On the database A host, do this:

a$ sudo ssh -N -f -L13306:127.0.0.1:3306 -R13306:127.0.0.1:3306 user@database-b
Note that you’ll want to change 127.0.0.1 to the IP that each of your MySQL servers are listening on. So if database A isn’t listening on localhost, but instead listening on 10.0.0.10 and database B is listening on 192.168.1.10, the command will look like this:

a$ sudo ssh -N -f -L13306:192.168.1.10:3306 -R13306:10.0.0.10:3306 user@database-b
As part of the next step, we’ll be setting up a special database user for replication and we can use those users to test the tunnel.

Step 5: Setup replication permissions
In order to slave up each database, we’ll need to create a replication user on each database and give it the proper permissions.

First, on database A, create a replication user so database B will have access:

a> GRANT REPLICATION SLAVE ON . TO 'replication'@'127.0.0.1' IDENTIFIED BY 'replication_password';
Second, on database B, create a replication user so A will have access:

b> GRANT REPLICATION SLAVE ON . TO 'replication'@'127.0.0.1' IDENTIFIED BY 'replication_password';
Note, just like in the last step with the SSH tunnel, you’ll need to change 127.0.0.1 to the IP address that each of the databases are listening on.

Now that this is setup, we can test our SSH tunnel and replication users. Let’s first connect from database A to database B:

a$ mysql -ureplication -p -h 127.0.0.1 -P 13306
Now let’s try from B to A:

b$ mysql -ureplication -p -h 127.0.0.1 -P 13306
Hopefully that worked like a charm. If it didn’t, double check that your SSH tunnel is setup correctly and that you have all your IPs correct.

Step 6: Slave database B to A
Now we’re ready to instruct database B to replicate database A. Let’s show database B who its master is with the following SQL command. You’ll want to make sure you use the binary log position that we recorded in Step 2.

b> CHANGE MASTER TO master_host='127.0.0.1', master_port=13306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000001', master_log_pos=106;
Now let’s start slaving!

b> START SLAVE;
b> SHOW SLAVE STATUS\G
The output of the show slave status command will tell you what it’s up to. If your database doesn’t get tons of traffic, it should sync up almost instantly. Hooray!

Step 7: Slave database A to B
We’re almost there. This is the final step.

Let’s get a position in database B’s binary log, so we can tell A to start replicating from there. On database B in MySQL:

b> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 480764 | | |
+------------------+----------+--------------+------------------+
Now on database A, we issue these commands to slave it to database B. Substitute the binary log position we just recorded as appropriate.

a> CHANGE MASTER TO master_host='127.0.0.1', master_port=13306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000001', master_log_pos=480764;
a> START SLAVE;
a> SHOW SLAVE STATUS\G

a@:~$ sudo service jira stop

Server A: 192.168.1.137

server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1

192.168.1.126
And here’s what we want for database B:

server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

mysqldump -u root -p jira2 > ~/my_database.sql

UNLOCK TABLES;

gzip my_database.sql

Now let’s copy it to the host database B lives on:

a$ scp my_database.sql ckp@192.168.1.126:~/my_database.sql

print SET foreign_key_checks = 0; >my_database.sql

b$ mysqladmin -uroot -p create my_database
b$ pv my_database.sql | mysql -uroot -p

a> GRANT REPLICATION SLAVE ON . TO 'replication'@'127.0.0.1' IDENTIFIED BY 'replication_password';

Second, on database B, create a replication user so A will have access:

b> GRANT REPLICATION SLAVE ON . TO 'replication'@'127.0.0.1' IDENTIFIED BY 'replication_password';