Setup MySQL Replication in 11 Easy Steps

Well, Fasten Your Seat-belts

1. Install a MySQL instance that will serve as a Master
2. Install a MySQL instance that will serve as a Slave
3. Configure the Master my.cnf file (located at /etc/ at CentOS) with the server id and the log file name: 
1. [mysqld]
2. server­-id = 1
3. log­-bin   = master­-bin.log
4. Configure the Slave my.cnf with the server id, reply logs and key configuration databases;
1. server­-id = 2 
2. relay­-log-­index = slave-­relay-­bin.index
3. relay­-log = slave­-relay­-bin
4. replicate-wild-ignore-table=mysql.%
5. replicate-wild-ignore-table=information_schema.%
6. replicate-wild-ignore-table=performance_schema.%
5. Restart the MySQL daemons on both servers to apply the my.cnf changes.
6. If both servers were just installed there is no need to sync their data files (since they  should have the same data files). O/w you should either stop the slave and master and copy the data files using SCP or perform just perform a mysqldump. Notice! Before copying the files, get the Master location describe below.
7. Get the master location, so we can sync the slave to it:
1. master> FLUSH TABLES WITH READ LOCK;
2. master> SHOW MASTER STATUS;
3. +-------------------+----------+--------------+------------------+
4. | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
5. +-------------------+----------+--------------+------------------+
6. | master-bin.000013 |  1233453 |              |                  |
7. +-------------------+----------+--------------+------------------+
9. master> UNLOCK TABLES;
8. Provide the slave with replication permissions:
1. master> GRANT REPLICATION SLAVE ON *.* to `repl`@`%` IDENTIFIED BY 'slavepass';
2. master> FLUSH PRIVILEGES;
9. Setup the slave using the chosen user/password and the master location we found before:
1. slave> CHANGE MASTER TO
2.     ->     MASTER_HOST='10.17.16.253',
3.     ->     MASTER_PORT=3306,
4.     ->     MASTER_USER='repl',
5.     ->     MASTER_PASSWORD='slavepass',
6.     ->     MASTER_LOG_FILE='master-bin.000013',
7.     ->     MASTER_LOG_POS=1233453;
10. Now, start the slave and verify it's running: 
1. slave> start slave;show slave status\G
11. If everything is Okay, verify the replication really works by:
1. Creating a table and inserting values to it in the master:
1. master> CREATE DATABASE a;
2. master> CREATE TABLE a.b (c int);
3. master> INSERT INTO a.b (c) VALUES (1);
2. Verifying that SLAVE/MASTER> SELECT * FROM a.b; return the same values in the master and slave;
3. Dropping the database in the master: master> DROP DATABASE IF EXISTS a;

Creating a MySQL slave from a running master

 Creating a MySQL slave from a running master

First, start by reading the online documentation. The MySQL documentation is well written and accurate. It goes through all of the steps in a good level of detail except for one. Unfortunately, that one step is critical.
To start MySQL replication, you need your new slave server to have an exact snapshot of the master at a point in time. MySQL recommends shutting down the master to take a backup. That sounds nice, but I can't afford several hours of downtime. The naive approach is just to use mysqldump to export a copy of the master and load it on the slave server. This works if you only have one database. With multiple database, you'll end up with inconsistent data. Mysqldump will dump data from each database on the server in a different transaction. That means that your export will have data from a different point in time for each database.
Instead of dumping all of the data at once, I dump my databases one at a time. Once the first dump completes, I then dump the next database. All databases are dumped using

mysqldump -u root -e -q --single-transaction --master-data database_name

Once I have dumped all of the databases on the server, I load the slave by importing the first database . 

I configure replication to only process replication events for this database by setting



 

replicate-wild-do-table= database_name.%




Next, I find out what the master log position is for my next import.NIt's in the first 25 lines of the MySQL 

dmp. Once I have that, I start the slave and tell mysql to replicate until until this position. You can


do this by passing the until argument to start slave. For example:







START SLAVE UNTIL MASTER_LOG_FILE='bin.000029', MASTER_LOG_POS=651322976;

Now, the database will be in the same state as when I dumped the next database. I then load that export. Once the load is done, I shutdown mysql and add a record for the newly import database to the replication setup. It will now look like:

replicate-wild-do-table= database_name.%
replicate-wild-do-table= another_db.%

I then start mysql and immediately stop the slave. I can the repeat as necessary, running replication until the same time as the previous export, exporting the data then adding the new database to replication.

This process allows me to easily build a new slave server from an existing MySQL master with no downtime.




Reference

Creating a MySQL slave from a running master 

How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication

Mysql Replication Broken issue

Mysql Server1 is running as MASTER.
Mysql Server2 is running as SLAVE.
Now DB replication is happening from MASTER to SLAVE.
Server2 is removed from network and re-connet it back after 1 day. After this there is mismatch in database in master and slave.
How to re-sync the DB again as after restoring DB taken from Master to Slave also doesn't solve the problem ?

This is the full step-by-step procedure to resync a master-slave replication from scratch:
At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.
Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:
mysqldump -uroot -p --all-database > /a/path/mysqldump.sql Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client:
UNLOCK TABLES; Now copy the dump file to the slave using scp or your preferred tool.

At the slave:
Open a connection to mysql and type:
STOP SLAVE; Load master's data dump with this console command:
mysql -uroot -p < mysqldump.sql Sync slave and master logs:
RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; Where the values of the above fields are the ones you copied before.
Finally type
START SLAVE; And to check that everything is working again, if you type
SHOW SLAVE STATUS; you should see:
Slave_IO_Running: Yes Slave_SQL_Running: Yes

Reference Link
http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o

MySQL Replicaiton Slave Monitor Script

MySQL Replicaiton Slave Monitor Script

Slave_IO_Running=`mysql  -u root -pB0Mb@cu7$ -e "show slave status\G" | grep Slave_IO_Running | awk '{ print $2 }'`
Slave_SQL_Running=`mysql  -u root -pB0Mb@cu7$ -e "show slave status\G" | grep Slave_SQL_Running | awk '{ print $2 }'`

if [ $Slave_IO_Running == 'Yes' ] && [ $Slave_SQL_Running == 'Yes' ] ; then
        echo "Replication slave is running"

else

   echo "Replication slave is down"

fi

how to configure mysql master slave replication

how to configure mysql master slave replication 

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.

 Step 1:

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

Master  ip: 192.168.16.4
Slave  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;

Step 6:

On master 1:
mysql> show master status;

how to for mysql master master replication

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

# for auto increment fields
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 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;

Step 6:

On master 1:
mysql> show master status;

Note: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
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 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;

Reference url:Mysql master master replication