Wednesday, 29 May 2013

Master Slave Configuration in Mysql

CONTENT TAKEN BY GIVEN URL:-

http://erlycoder.com/43/mysql-master-slave-and-master-master-replication-step-by-step-configuration-instructions-

http://studioshorts.com/blog/2010/03/mysql-master-slave-replication-on-centos-rhel/






MySQL master-slave and master-master replication. Step by step configuration instructions.

If you are looking for the options to csale your MySQL installation you may be also interested in MySQL partitioning and subpartitioning. It may improve each node speed and capacity parameters.
One may say that there are a lot of MySQL replication manuals, but latest versions of MySQL server have changed the way how configuration should be applied. Most of the manuals do not reflect these changes. I want to describe some other aspects of configurations also. As far as there are a lot of good manuals about replication, I think there is no need to dove into details what is the replication. Just want to mention that this technique is usually used for load balancing on database servers. If you have a lot of read requests (most common for web applications) master-slave replication should suit your needs well. In this case you will do write transactions on master host and read requests on slave hosts, because data is populated from master to slave much faster than from slaves to master and to other slaves.
mysql master-slave replication
But sometimes you might have more write requests or may have other (application related) reasons to start another type of replication. You can see it on the next fugure and that is so called  master-master replication.
mysql master-master replication
In this article I will describe simple master-slave architecture with 2 hosts and simple master-master replication with the same 2 hosts. Our final goal is to configure master-master replication, what includes several sub-steps, so lets  start. Sure you should configure network services on both systems. For example:
Master 1/Slave 2 ip: 192.168.16.4
Master 2/Slave 1 ip : 192.168.16.5

Iptables rules for MySQL replication

It will be good practice to allow connections only from other nodes envolved into the replication and deny from other. By the way this will work good for some other services that are allowed to communicate nly with the known hosts. You can define port range like 1025:3306 (I am going to write more about iptables soon, so follow my blog on twitter).
    

  1. iptables -A INPUT -p tcp -s 192.168.16.4 --sport 3306 -d 192.168.16.5 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
    2. 

  2. iptables -A OUTPUT -p tcp -s 192.168.16.5 --sport 3306 -d 192.168.16.4 --dport 3306 -m state --state ESTABLISHED -j ACCEPT
    3.

MySQL master-slave replication

Basically master-master replication consists of two master-slave replications. Now we will configure master-slave replication from the first server to the second one.
Create relication user on Master 1:
    

  1. mysql> grant replication slave on *.* to 'replication'@192.168.16.5 identified by 'slave';
    2.
And start master:
    

  1. mysql> start master;
    2.
Master 1 changes to /etc/my.cnf:
    

  1. [mysqld]
    2. 

  2. datadir=/var/lib/mysql
    3. 

  3. socket=/var/lib/mysql/mysql.sock
    4. 

  4. old_passwords=1
    5. 

  5. 

    6. 

  6. log-bin
    7. 

  7. binlog-do-db=<database name>  # input the database which should be replicated
    8. 

  8. binlog-ignore-db=mysql            # input the database that should be ignored for replication
    9. 

  9. binlog-ignore-db=test
    10. 

  10. 

    11. 

  11. server-id=1
    12. 

  12. 

    13. 

  13. [mysql.server]
    14. 

  14. user=mysql
    15. 

  15. basedir=/var/lib
    16. 

  16. 

    17. 

  17. 

    18. 

  18. [mysqld_safe]
    19. 

  19. err-log=/var/log/mysqld.log
    20. 

  20. pid-file=/var/run/mysqld/mysqld.pid
    21.
Slave 1 changes to /etc/my.cnf:
    

  1. [mysqld]
    2. 

  2. datadir=/var/lib/mysql
    3. 

  3. socket=/var/lib/mysql/mysql.sock
    4. 

  4. old_passwords=1
    5. 

  5. 

    6. 

  6. server-id=2
    7. 

  7. 

    8. 

  8. [mysql.server]
    9. 

  9. user=mysql
    10. 

  10. basedir=/var/lib
    11. 

  11. 

    12. 

  12. [mysqld_safe]
    13. 

  13. err-log=/var/log/mysqld.log
    14. 

  14. pid-file=/var/run/mysqld/mysqld.pid
    15.
Important! Pay attention that you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.
On Master 1:
    

  1. mysql> show master status;
    2. 

  2. +------------------------+----------+--------------+------------------+
    3. 

  3. | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    4. 

  4. +------------------------+----------+--------------+------------------+
    5. 

  5. |mysqld-bin.000012       |      106 | adam         |                  |
    6. 

  6. +------------------------+----------+--------------+------------------+
    7. 

  7. 1 row in set (0.00 sec)
    8.
On Slave 1:
    

  1. mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.4', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;
    2.
Attention! This will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.
Start slave on Slave 1:
    

  1. mysql> start slave;
    2. 

  2. mysql> show slave statusG;
    3. 

  3. *************************** 1. row ***************************
    4. 

  4.                Slave_IO_State: Waiting for master to send event
    5. 

  5.                   Master_Host: 192.168.16.5
    6. 

  6.                   Master_User: slave
    7. 

  7.                   Master_Port: 3306
    8. 

  8.                 Connect_Retry: 10
    9. 

  9.               Master_Log_File: mysqld-bin.000012
    10. 

  10.           Read_Master_Log_Pos: 1368129
    11. 

  11.                Relay_Log_File: mysqld-relay-bin.000005
    12. 

  12.                 Relay_Log_Pos: 605530
    13. 

  13.         Relay_Master_Log_File: mysqld-bin.000012
    14. 

  14.              Slave_IO_Running: Yes
    15. 

  15.             Slave_SQL_Running: Yes
    16. 

  16.               Replicate_Do_DB: 
    17. 

  17.           Replicate_Ignore_DB: 
    18. 

  18.            Replicate_Do_Table: 
    19. 

  19.        Replicate_Ignore_Table: 
    20. 

  20.       Replicate_Wild_Do_Table: 
    21. 

  21.   Replicate_Wild_Ignore_Table: 
    22. 

  22.                    Last_Errno: 0
    23. 

  23.                    Last_Error: 
    24. 

  24.                  Skip_Counter: 0
    25. 

  25.           Exec_Master_Log_Pos: 1368129
    26. 

  26.               Relay_Log_Space: 1367083
    27. 

  27.               Until_Condition: None
    28. 

  28.                Until_Log_File: 
    29. 

  29.                 Until_Log_Pos: 0
    30. 

  30.            Master_SSL_Allowed: No
    31. 

  31.            Master_SSL_CA_File: 
    32. 

  32.            Master_SSL_CA_Path: 
    33. 

  33.               Master_SSL_Cert: 
    34. 

  34.             Master_SSL_Cipher: 
    35. 

  35.                Master_SSL_Key: 
    36. 

  36.         Seconds_Behind_Master: 0
    37. 

  37. Master_SSL_Verify_Server_Cert: No
    38. 

  38.                 Last_IO_Errno: 0
    39. 

  39.                 Last_IO_Error: 
    40. 

  40.                Last_SQL_Errno: 0
    41. 

  41.                Last_SQL_Error: 
    42. 

  42.   Replicate_Ignore_Server_Ids: 
    43. 

  43.              Master_Server_Id: 1
    44. 

  44. 1 row in set (0.02 sec)
    45.
Above highlighted rows must be indicate related log files and  Slave_IO_Running and   Slave_SQL_Running: must be to YES.

MySQL master-master replication

Master-master replication is actually two master-slave replications. This allows to make read and write transactions on both servers, as data propagation from master to slave goes very fast oposit to data propagation from slave to master which requires much more time. So, to create master-master replication we should now configure Master 2 - Slave 2 replication.
Create a replication slave account on Master 2 for Master 1/Slave 2:
    

  1. mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave';
    2.
And start master:
    

  1. mysql> start master;
    2.
Master 2 changes to /etc/my.cnf:
    

  1. [mysqld]
    2. 

  2. datadir=/var/lib/mysql
    3. 

  3. socket=/var/lib/mysql/mysql.sock
    4. 

  4. old_passwords=1
    5. 

  5. 

    6. 

  6. log-bin
    7. 

  7. binlog-do-db=<database name>  # input the database which should be replicated
    8. 

  8. binlog-ignore-db=mysql            # input the database that should be ignored for replication
    9. 

  9. binlog-ignore-db=test
    10. 

  10. 

    11. 

  11. server-id=2
    12. 

  12. 

    13. 

  13. [mysql.server]
    14. 

  14. user=mysql
    15. 

  15. basedir=/var/lib
    16. 

  16. 

    17. 

  17. 

    18. 

  18. [mysqld_safe]
    19. 

  19. err-log=/var/log/mysqld.log
    20. 

  20. pid-file=/var/run/mysqld/mysqld.pid
    21.
Slave 2 / Master 1 changes to /etc/my.cnf:
    

  1. [mysqld]
    2. 

  2. datadir=/var/lib/mysql
    3. 

  3. socket=/var/lib/mysql/mysql.sock
    4. 

  4. old_passwords=1
    5. 

  5. 

    6. 

  6. log-bin
    7. 

  7. binlog-do-db=<database name>  # input the database which should be replicated
    8. 

  8. binlog-ignore-db=mysql            # input the database that should be ignored for replication
    9. 

  9. binlog-ignore-db=test
    10. 

  10. 

    11. 

  11. server-id=1
    12. 

  12. 

    13. 

  13. [mysql.server]
    14. 

  14. user=mysql
    15. 

  15. basedir=/var/lib
    16. 

  16. 

    17. 

  17. [mysqld_safe]
    18. 

  18. err-log=/var/log/mysqld.log
    19. 

  19. pid-file=/var/run/mysqld/mysqld.pid
    20.
Important! And again you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.
On Master 2:
    

  1. mysql> show master status;
    2. 

  2. +------------------------+----------+--------------+------------------+
    3. 

  3. | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    4. 

  4. +------------------------+----------+--------------+------------------+
    5. 

  5. |mysqld-bin.000012       |      106 | adam         |                  |
    6. 

  6. +------------------------+----------+--------------+------------------+
    7. 

  7. 1 row in set (0.00 sec)
    8.
On Slave 2:
    

  1. mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.5', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;
    2.
Attention! As I have already mentioned in the previous section this will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.

MySQL master-master replication and autoincrement indexes

If you are using master-slave replication, than most likely you will design your application the way to write to master and read from slave or several slaves. But when you are using master-master replication you are going to read and write to any of master servers. So, in this case the problem with autoincremental indexes will raise. When both servers will have to add a record (different one each server simultaneously) to the same table. Each one will assign them the same index and will try to replicate to the salve, this will create a collision. Simple trick will allow to avoid such collisions on MySQL server.
On the Master 1/Slave 2 add to /etc/my.cnf:
    

  1. auto_increment_increment= 2
    2. 

  2. auto_increment_offset   = 1
    3.
On the Master 2/Slave 1 add to /etc/my.cnf:
    

  1. auto_increment_increment= 2
    2. 

  2. auto_increment_offset   = 2
    3. 









THIS CONTENT tAKEN bY gIVEN uRL:-
















    

  1. 
Today I set up some MySQL replication for a server that I set up a few weeks ago. This was my first time doing replication and it was quite a learning experience. This was using MySQL 5.0 and CentOS 5.3, but this should work for most semi-recent versions of both.
In this setup, transactions are mirrored to the slave server as they happen on the master.
    

    
In this tutorial I’ll use the following setup:
    

    Master Server: 10.1.100.1
Slave Server: 10.2.200.2
MySQL Data path: /var/lib/mysql
MySQL slave user named slave_user

    

    
[Master]
    

    
First, edit the master server MySQL config file. Add/Replace the following lines
    

    

    

    
1
    

    
vim /etc/my.cnf
    

    

    

    

    # [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/var/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
# Stop Modification

    

    
Restart MySQL to load the changes
    

    

    

    
1
    

    
service mysqld restart
    

    

    

    

    
[Slave]
    

    
Now we’ll do about the same thing on the slave server
    

    

    

    
1
    

    
vim /etc/my.cnf
    

    

    

    

    # [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/var/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
# Stop Modification

    

    
Restart MySQL to load the changes
    

    

    

    
1
    

    
service mysqld restart
    

    

    

    

    
[Master]
    

    
Now we need to tell MySQL where we are replicating to and what user we will do it with.
    

    

    

    
1
2
3
4
    

    
mysql -u root -p
mysql> STOP SLAVE;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
mysql> FLUSH PRIVILEGES;
    

    

    

    

    
Now we will test that this side of the replication is working and get the location that we will start the replication from.
    

    

    

    
1
2
3
4
5
6
7
8
9
    

    
mysql> USE Any_database_name;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   451228 |              |                  |
+------------------+----------+--------------+------------------+
1 ROW IN SET (0.00 sec)
    

    

    

    

    
Write down the File, Position number, as this is where we will start the replication from.
Here it is a good idea to do a dump of your master database(s) and pipe it into your slave server.
    

    

    

    
1
    

    
mysqldump -u root --all-databases --single-transaction --master-data=1 > /home/MasterSnapshot.sql
    

    

    

    

    
[Slave]
    

    
First grab your SQL dump file from the master server. You can use whatever method you would like to transfer the file. SCP example:
    

    

    

    
1
    

    
scp root@10.1.100.1:/path/to/MasterSnapshot.sql root@10.2.200.2:/home/MasterSnapshot.sql
    

    

    

    

    
Import the SQL file into MySQL
    

    

    

    
1
    

    
mysql -u root -p < /home/MasterSnapshot.sql
    

    

    

    

    
Now we’ll set the slave to read from the master server, starting at the record position we wrote down earlier. Make sure you use the MASTER_LOG_FILE and MASTER_LOG_POS from a few steps back.
    

    

    

    
1
2
3
    

    
mysql> CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
    

    

    

    

    
Make sure that from the resulting output you have the following:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
    

    

    

    

    

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

    

    

    
Master1
    

    
grant replication slave on *.* to 'root'@'172.31.22.57' identified by 'admin123';
    

    
GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.57' IDENTIFIED BY 'admin123';
    

    


    

    
Slave1/master2
    

    
CHANGE MASTER TO MASTER_HOST='172.31.22.52', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000006', MASTER_LOG_POS=66302851, MASTER_CONNECT_RETRY=10;
    

    


    

    


    

    
CHANGE MASTER TO MASTER_HOST='172.31.22.56', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=310;
    

    


    

    


    

    
master2/slave1
    

    
grant replication slave on *.* to 'root'@'172.31.22.56' identified by 'admin123';
    

    


    

    
GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.56' IDENTIFIED BY 'admin123';
    

    


    

    
slave2/master1:
    

    


    

    
CHANGE MASTER TO MASTER_HOST='172.31.22.53', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000020', MASTER_LOG_POS=89253722, MASTER_CONNECT_RETRY=10;
    

    


    

    


    

    


    

    


    

    


    

    


    

    


    

    


    

    
iptables -A INPUT -p tcp -s 172.31.22.56 --sport 3306 -d 172.31.22.57 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
    

    


    

    
iptables -A INPUT -p tcp -s 172.31.22.57 --sport 3306 -d 172.31.22.56 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
    

    


    

    


    

    


    

    

    

    2.
Posted by at No comments:
Subscribe to: Posts (Atom)