..........mysql master slave replication configuration on LINUX.......- Shirish Shukla
Some Basics:
Port No: 3306 (TCP/UDP)
IPADDRESS_Set as per your requirement
SYATEM 1 >> master : 192.168.8.10
SYATEM 2 >> Slave : 192.168.8.20
1> Installation on both server
# yum install mysql*
# /etc/init.d/mysqld restart
# /etc/init.d/mysqld restart
### Set root password
# mysqladmin -u root password x ### replace x is ur password
Login in mysql:
# mysql -u root -px ### replace x is as ur password aviod direct
### passing of password for security reason
# mysql -u root -p <Enter>
Enter password: <Enter Password>
mysql> exit
2> ON MASTER--192.168.8.10
# cp /etc/my.cfg /etc/my.cfg_Org ### Keep a copy
# vi /etc/my.cnf
[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
server-id=1
binlog-do-db=SHUKLA ### databases which you want to replicate
binlog-do-db=SHIRISH # create these databases before SEE
# at END BASIC MYSQL COMMANDS
# use, to add in 1 line or add seperately
binlog-ignore-db=mysql ### Input the database that should be ignored
binlog-ignore-db=test # for replication
log-bin=/var/lib/mysql/mysqld-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
relay-log=/var/log/mysql-relay-bin
# mysql -u root -p
mysql> grant replication slave on *.* to replication@192.168.8.20 identified by 'slave';
mysql> GRANT ALL PRIVILEGES ON *.* TO replication;
Cross check user replication
======================
mysql> use mysql;
mysql> select * from user where User='replication'; ### must show 'Y' in most fields specially in "Super_priv"
#### Now Craete some database and tables
mysql> CREATE DATABASE SHIRISH;
mysql> use SHIRISH;
mysql> create table members(name char(20), age varchar(20));
mysql> show tables;
mysql> insert into members(name, age) values ('Shirish Shukla', 23);
mysql> insert into members(name, age) values ('Shirish Shukla-2', 23);
mysql> insert into members(name, age) values ('Shirish Shukla-3', 23);
mysql> select * from members;
+------------------+------+
| name | age |
+------------------+------+
| Shirish Shukla | 23 |
| Shirish Shukla-2 | 23 |
| Shirish Shukla-3 | 23 |
+------------------+------+
3 rows in set (0.00 sec)
mysql> exit
3> ON SLAVE--192.168.8.20
# cp /etc/my.cfg /etc/my.cfg_Org ### Keep a copy
# vi /etc/my.cnf
[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
server-id=2
master-host = 192.168.8.10
master-user = replication
master-password = slave
master-port = 3306
log-bin=/var/lib/mysql/mysqld-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
relay-log=/var/log/mysql-relay-bin
# mysql -u root -p
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.10',MASTER_USER='replication',MASTER_PASSWORD='slave';
mysql> GRANT ALL PRIVILEGES on *.* TO replication
Cross check user replication
======================
mysql> use mysql;
mysql> select * from user where User='replication'; ### must show 'Y' in most fields specially in "Super_priv"
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event <1>
Master_Host: 192.168.8.10 <2>
Master_User: replication <3>
Master_Port: 3306 <4>
Connect_Retry: 60
Master_Log_File: mysqld-bin.000008 <5>
Read_Master_Log_Pos: 223 <6>
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysqld-bin.000008
Slave_IO_Running: Yes <7>
Slave_SQL_Running: Yes <8>
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: 223 <9>
Relay_Log_Space: 361
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: 0 <10>
1 row in set (0.01 sec)
ERROR:
No query specified
#### CROSS check some things in above;
<1>Always "Waiting for master to send event" if unable to connect then report or restrat
<2>Muyst show master IP
<3>Show user as set above
<4>PORT 3306
<5>must match with below (show master status;) FILE
<6>must match with below (show master status;) Position
On master RUN
mysql> show master status;
+-------------------+-----------+-------------------------------+------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+-------------------------------+------------------------+
| mysqld-bin.000008 | 223 | SHUKLA,SHIRISH,SHUKLA,SHIRISH | mysql,test,mysql,test |
+-------------------+-----------+-------------------------------+------------------------+
<7>Must show Yes
<8>Must show Yes
<9> SAME As <6>
<10>Must show limited sec diff < 2 or 0 sec but not 'NULL'
3> TESTING MASTER SLAVE
#### Now cross check is replacition done properly or not !!!
mysql> show databases;
mysql> use SHIRISH;
mysql> show tables; <--- Compare with master
mysql> select * from members; <--- Compare with master
### Now create a new table as LINUX at MASTER;
mysql> SHOW TABLES;
mysql> create table linux(name char(20), age varchar(20));
mysql> insert into linux(name, age) values ('Shirish Shukla', 23);
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_SHIRISH |
+--------------------------+
| linux |
| members |
+--------------------------+
2 rows in set (0.00 sec)
## now at slave
mysql> use SHIRISH;
mysql> show tables;
mysql> select * from linux; << MUST SHOW SAME AS MASTER JUST UPDATED>>
Some Tuning.....add below in /etc/my.conf of both master and slave........
==========================================================================
|| MASTER SLAVE <--> MASTER SLAVE ||
==========================================================================
Continue above configuration and add BELOW....
192.168.8.10 192.168.8.20
SYSTEM-1 SYSTEM-2
Now Above AS: Master <------> SLAVE
SLAVE <------> MASTER2
ON SYSTEM-1
# vi /etc/my.cnf <<--Add below lines
master-host = 192.168.8.20
master-user = replication
master-password = slave
master-port = 3306
# /etc/init.d/mysqld restart
# mysql -u root -px
mysql> stop slave;
mysql> grant replication slave on *.* TO ayam@'192.168.8.10' identified by 'x';
mysql> grant replication slave on *.* TO ayam@'192.168.8.20' identified by 'x';
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.10;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.20;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.20',MASTER_USER='replication',MASTER_PASSWORD='slave';
mysql> start slave;
ON SYSTEM-2
# vi /etc/my.cnf <<--confirm below line we had already added in Part -1
## Add below lines
binlog-do-db=SHUKLA #Databases that you want to replicate
binlog-do-db=SHIRISH
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=test
## Cross varify below to exist we had already added
master-host = 192.168.8.10
master-user = replication
master-password = slave
master-port = 3306
# mysql -u root -px
mysql> stop slave;
mysql> grant replication slave on *.* TO ayam@'192.168.8.10' identified by 'x';
mysql> grant replication slave on *.* TO ayam@'192.168.8.20' identified by 'x';
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.10;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.20;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.10',MASTER_USER='replication',MASTER_PASSWORD='slave';
mysql> start slave;
ON SYSTEM-1
mysql> show slave status\G; < and cross varifie with as we done in PART-1
ON SYSTEM-2
mysql> show slave status\G; < and cross varifie with as we done in PART-1
CROSS Checking:
###################################### BASIC MYSQL COMMANDS ###############################
##Create database SHIRISH
mysql> create database SHIRISH;
##Create Tables into database SHIRISH
mysql> use SHIRISH;
mysql> create table LINUX_USERS(name char(20), age varchar(20));
##Display created table;
mysql> show tables;
##Insert data into table LINUX_USERS
mysql> insert into LINUX_USERS(name,age) values ('Shirish Shukla', 23)
##display created entry into table LINUX_USERS
mysql> select * from shukla;
# Faced any problem Feedback in above contact me
#===============================Scratch===============================#
# AND Many More .....................Linux is Endless #
#========================== Hope you Liked IT ========================#
# MYSQL -- BY Shirish Shukla #
# RHC Engineer 2010 #
# shirish.linux@gmail.com #
# shirishlinux.blogspot.com #
# "Give Respect To Time One Day At Right Time, Time Will Respect You" #
#=====================================================================#
# TRy Hard theres nothing that are un-achievable by HARDdd-WORKkk #
#=====================================================================#
Some Basics:
Port No: 3306 (TCP/UDP)
IPADDRESS_Set as per your requirement
SYATEM 1 >> master : 192.168.8.10
SYATEM 2 >> Slave : 192.168.8.20
1> Installation on both server
# yum install mysql*
# /etc/init.d/mysqld restart
# /etc/init.d/mysqld restart
### Set root password
# mysqladmin -u root password x ### replace x is ur password
Login in mysql:
# mysql -u root -px ### replace x is as ur password aviod direct
### passing of password for security reason
# mysql -u root -p <Enter>
Enter password: <Enter Password>
mysql> exit
2> ON MASTER--192.168.8.10
# cp /etc/my.cfg /etc/my.cfg_Org ### Keep a copy
# vi /etc/my.cnf
[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
server-id=1
binlog-do-db=SHUKLA ### databases which you want to replicate
binlog-do-db=SHIRISH # create these databases before SEE
# at END BASIC MYSQL COMMANDS
# use, to add in 1 line or add seperately
binlog-ignore-db=mysql ### Input the database that should be ignored
binlog-ignore-db=test # for replication
log-bin=/var/lib/mysql/mysqld-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
relay-log=/var/log/mysql-relay-bin
# mysql -u root -p
mysql> grant replication slave on *.* to replication@192.168.8.20 identified by 'slave';
mysql> GRANT ALL PRIVILEGES ON *.* TO replication;
Cross check user replication
======================
mysql> use mysql;
mysql> select * from user where User='replication'; ### must show 'Y' in most fields specially in "Super_priv"
#### Now Craete some database and tables
mysql> CREATE DATABASE SHIRISH;
mysql> use SHIRISH;
mysql> create table members(name char(20), age varchar(20));
mysql> show tables;
mysql> insert into members(name, age) values ('Shirish Shukla', 23);
mysql> insert into members(name, age) values ('Shirish Shukla-2', 23);
mysql> insert into members(name, age) values ('Shirish Shukla-3', 23);
mysql> select * from members;
+------------------+------+
| name | age |
+------------------+------+
| Shirish Shukla | 23 |
| Shirish Shukla-2 | 23 |
| Shirish Shukla-3 | 23 |
+------------------+------+
3 rows in set (0.00 sec)
mysql> exit
3> ON SLAVE--192.168.8.20
# cp /etc/my.cfg /etc/my.cfg_Org ### Keep a copy
# vi /etc/my.cnf
[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
server-id=2
master-host = 192.168.8.10
master-user = replication
master-password = slave
master-port = 3306
log-bin=/var/lib/mysql/mysqld-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
relay-log=/var/log/mysql-relay-bin
# mysql -u root -p
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.10',MASTER_USER='replication',MASTER_PASSWORD='slave';
mysql> GRANT ALL PRIVILEGES on *.* TO replication
Cross check user replication
======================
mysql> use mysql;
mysql> select * from user where User='replication'; ### must show 'Y' in most fields specially in "Super_priv"
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event <1>
Master_Host: 192.168.8.10 <2>
Master_User: replication <3>
Master_Port: 3306 <4>
Connect_Retry: 60
Master_Log_File: mysqld-bin.000008 <5>
Read_Master_Log_Pos: 223 <6>
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysqld-bin.000008
Slave_IO_Running: Yes <7>
Slave_SQL_Running: Yes <8>
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: 223 <9>
Relay_Log_Space: 361
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: 0 <10>
1 row in set (0.01 sec)
ERROR:
No query specified
#### CROSS check some things in above;
<1>Always "Waiting for master to send event" if unable to connect then report or restrat
<2>Muyst show master IP
<3>Show user as set above
<4>PORT 3306
<5>must match with below (show master status;) FILE
<6>must match with below (show master status;) Position
On master RUN
mysql> show master status;
+-------------------+-----------+-------------------------------+------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+-------------------------------+------------------------+
| mysqld-bin.000008 | 223 | SHUKLA,SHIRISH,SHUKLA,SHIRISH | mysql,test,mysql,test |
+-------------------+-----------+-------------------------------+------------------------+
<7>Must show Yes
<8>Must show Yes
<9> SAME As <6>
<10>Must show limited sec diff < 2 or 0 sec but not 'NULL'
3> TESTING MASTER SLAVE
#### Now cross check is replacition done properly or not !!!
mysql> show databases;
mysql> use SHIRISH;
mysql> show tables; <--- Compare with master
mysql> select * from members; <--- Compare with master
### Now create a new table as LINUX at MASTER;
mysql> SHOW TABLES;
mysql> create table linux(name char(20), age varchar(20));
mysql> insert into linux(name, age) values ('Shirish Shukla', 23);
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_SHIRISH |
+--------------------------+
| linux |
| members |
+--------------------------+
2 rows in set (0.00 sec)
## now at slave
mysql> use SHIRISH;
mysql> show tables;
mysql> select * from linux; << MUST SHOW SAME AS MASTER JUST UPDATED>>
Some Tuning.....add below in /etc/my.conf of both master and slave........
==========================================================================
|| MASTER SLAVE <--> MASTER SLAVE ||
==========================================================================
Continue above configuration and add BELOW....
192.168.8.10 192.168.8.20
SYSTEM-1 SYSTEM-2
Now Above AS: Master <------> SLAVE
SLAVE <------> MASTER2
ON SYSTEM-1
# vi /etc/my.cnf <<--Add below lines
master-host = 192.168.8.20
master-user = replication
master-password = slave
master-port = 3306
# /etc/init.d/mysqld restart
# mysql -u root -px
mysql> stop slave;
mysql> grant replication slave on *.* TO ayam@'192.168.8.10' identified by 'x';
mysql> grant replication slave on *.* TO ayam@'192.168.8.20' identified by 'x';
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.10;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.20;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.20',MASTER_USER='replication',MASTER_PASSWORD='slave';
mysql> start slave;
ON SYSTEM-2
# vi /etc/my.cnf <<--confirm below line we had already added in Part -1
## Add below lines
binlog-do-db=SHUKLA #Databases that you want to replicate
binlog-do-db=SHIRISH
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=test
## Cross varify below to exist we had already added
master-host = 192.168.8.10
master-user = replication
master-password = slave
master-port = 3306
# mysql -u root -px
mysql> stop slave;
mysql> grant replication slave on *.* TO ayam@'192.168.8.10' identified by 'x';
mysql> grant replication slave on *.* TO ayam@'192.168.8.20' identified by 'x';
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.10;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication@192.168.8.20;
mysql> GRANT ALL PRIVILEGES ON *.* TO replication;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.10',MASTER_USER='replication',MASTER_PASSWORD='slave';
mysql> start slave;
ON SYSTEM-1
mysql> show slave status\G; < and cross varifie with as we done in PART-1
ON SYSTEM-2
mysql> show slave status\G; < and cross varifie with as we done in PART-1
CROSS Checking:
###################################### BASIC MYSQL COMMANDS ###############################
##Create database SHIRISH
mysql> create database SHIRISH;
##Create Tables into database SHIRISH
mysql> use SHIRISH;
mysql> create table LINUX_USERS(name char(20), age varchar(20));
##Display created table;
mysql> show tables;
##Insert data into table LINUX_USERS
mysql> insert into LINUX_USERS(name,age) values ('Shirish Shukla', 23)
##display created entry into table LINUX_USERS
mysql> select * from shukla;
# Faced any problem Feedback in above contact me
#===============================Scratch===============================#
# AND Many More .....................Linux is Endless #
#========================== Hope you Liked IT ========================#
# MYSQL -- BY Shirish Shukla #
# RHC Engineer 2010 #
# shirish.linux@gmail.com #
# shirishlinux.blogspot.com #
# "Give Respect To Time One Day At Right Time, Time Will Respect You" #
#=====================================================================#
# TRy Hard theres nothing that are un-achievable by HARDdd-WORKkk #
#=====================================================================#
