how to set up master slave mysql replication in mysql linux, centos

how to set up master slave mysql replication in mysql (linux, centos, ubunto, fedora, redhat) ..

how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). there are following reason to set up master-slave replication using MySQL.

1) Offload some of the queries from one server to another and spread the load: 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. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That 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, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

there are following steps to configure master slave replication in mysql ...

--------------------------

Step :1
1. Install mysql on master and slave.. configure network services on both system, like Here are some assumptions:
- Master server ip: 11.0.0.20
- Slave server ip: 11.0.0.21
- Slave username: slaveusername
- Slave pw: slavepassword
- Your data directory is: /usr/local/mysql/var/

Step :2
2. Put the following changes in your MASTER my.cnf file :-
# changes made to do master
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin=mysql-bin
log-bin
binlog-do-db=database_name_1 # input the database which should be replicated
binlog-do-db=database_name_2
binlog-do-db=database_name_3
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
3. Create replication slave user account on master :-
mysql> grant replication slave on *.* to slaveusername@'11.0.0.21' identified by 'slavepassword';

if master not allow to connect slave .. use following to connect slave to master ..

mysql> CREATE USER 'slaveusername'@'11.0.0.21' IDENTIFIED BY 'slavepassword';
mysql> GRANT ALL ON *.* TO 'slaveusername'@'11.0.0.21' IDENTIFIED BY 'slavepassword';
mysql> flush privileges;

Step :4
4. Put the following changes in your SLAVE my.cnf file :-
# changes made to do slave
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3

server-id=2

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

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

Step :5
5. On Mater Do a dump of mysql data to move to slave ..
i . mysql> FLUSH TABLES WITH READ LOCK;
Note :- wait 5-10 sec

ii . mysqldump -u masteruser -p masterpassword --single-transaction --flush-logs --master-data --databases database_name_1 database_name_2 database_name_3 > databasename.`date +%y-%m-%d-%H-%M`.sql

NOTE :- mysql> show master status;
+------------------+----------+------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------------------------+------------------+
| mysql-bin.000163 | 98 | database_name_1 database_name_2 database_name_3 | mysql,test |
+------------------+----------+------------------------------------+------------------+

iii . mysql> UNLOCK TABLES;

Step :6
6. send master databasedump to slave server using ftp OR sftp ..

Step :7
7. import master databasedump on slave
mysql -u slaveusername -p slavepassword < masterdatabasedumpname.sql

Step :8
8. 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:
mysql> CHANGE MASTER TO MASTER_HOST='11.0.0.20', MASTER_USER='masteruser', MASTER_PASSWORD='masterpassword';
-------------------------------
if probem than use
1. check master status at the time of masterbackup ..
mysql> show master status;
+------------------+----------+------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------------------------+------------------+
| mysql-bin.000163 | 98 | database_name_1 database_name_2 database_name_3 | mysql,test |
+------------------+----------+------------------------------------+------------------+
2. same position and bin file use in below command ...
CHANGE MASTER TO MASTER_HOST='11.0.0.20', MASTER_USER='masteruser', MASTER_PASSWORD='masterpassword', MASTER_LOG_FILE='mysql-bin.000163', MASTER_LOG_POS=98;
-------------------------------

Step :9
9. Let us start the slave:
mysql> start slave;

Step :10
10. You can check the status of the slave by typing
mysql> show slave status\G

if set up is proper than it show
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 11.0.0.20
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
Click here to find out more!

1 row in set (0.00 sec)



Note: --

Master my.cnf file

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

#innodb_force_recovery = 4

#added to fix timeout issues - next 2 lines
innodb_lock_wait_timeout=200
innodb_buffer_pool_size=1024M

#packet size so that the dump restore works
max_allowed_packet=1500M
max_binlog_size=512M
max_connections=2000
#max_connections=250

#fix for creating index on large table
innodb_lock_wait_timeout=2000

#performance related - mysql wouldnt flush at every commit
innodb_flush_log_at_trx_commit=0

#we have enough memory, and very repeated selects , hence use the query cache
query_cache_type=1
query_cache_size=200M

#required for mysql master slave
log-bin=mysql-bin
log-bin
binlog-do-db=database_name_1 # input the database which should be replicated
binlog-do-db=database_name_2
binlog-do-db=database_name_3
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=test
server-id=1

# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log

long_query_time=1
log-slow-queries=/var/log/mysql/mysql-slow.log

#cache some threads
thread_cache = 40

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

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

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


Slave my.cnf file :-

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

#added to fix timeout issues - next 2 lines
innodb_lock_wait_timeout=200
innodb_buffer_pool_size=500M
##innodb_buffer_pool_size=1024M

#packet size so that the dump restore works
max_allowed_packet=1024M
##max_connections=2000
max_connections=300

#fix for creating index on large table
##innodb_lock_wait_timeout=2000

#performance related - mysql wouldnt flush at every commit
innodb_flush_log_at_trx_commit=0

#we have enough memory, and very repeated selects , hence use the query cache
query_cache_type=1
query_cache_size=200M
query_cache_limit = 2M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M

#required for mysql master slave
#log-bin=mysql-bin
replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3
server-id=2

#master-host = 11.0.0.20
#master-user = masteruser
#master-password = masterpassword
#master-port = 3306

#relay-log=mysqld-relay-bin

# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log

long_query_time=1
log-slow-queries=/var/log/mysql/mysql-slow.log

#cache some threads
thread_cache = 40

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

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

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