Mysql Replication & Load Balancing

What is replication and what should it is and what it isnt…

Master-Master – synchronous replication where each server updates one another.

Pros: Used for hot standby situation. Write to one master only to avoid primary key collisions.
Cons: Network intensive.

Master-Slave – Primary used to share the load. Writes go to master from client,reads go to slave.

Pros: Asyncronous updates (one way) Simple, inexpensive. Hot cache from slave.
Cons: Not good for heavy write loads.

source: http://www.mysqlperformanceblog.com/2009/11/13/finding-your-mysql-high-availability-solution-%E2%80%93-replication/

http://dev.mysql.com/doc/refman/5.1/en/replication.htmlwatch film Bone Tomahawk now

How to setup mysql replication

Create new replication user on master.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to slave@'SLAVEIPADDRESS' IDENTIFIED BY 'PASSHERE';
Query OK, 0 rows affected (0.07 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

IMPORTANT!!! – make sure /etc/mysql/my.cnf 127.0.0.1 for bind-address is commented out or cannot connect from the slave!

#bind-address = 127.0.0.1

Now verify can connect from slave:


root@slave:/var/log# mysql -h MASTERIP -P 3306 -u slave -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 60
Server version: 5.1.37-1ubuntu5-log (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

Configure master.

edit /etc/my.cnf

Add the following.

[mysqld]
...
log-bin=mysql-bin
server-id=1 #master id
...

Make backup of Master.


root@donkey [~]# mysqldump --all-databases -u root -p > mysql-dump-master.sql
Enter password:

Write down the position of the master status for later…

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Shudown mysql


root@donkey [~]# sudo /etc/init.d/mysql stop Shutting down MySQL.

Transfter file to Slave.

scp mysql-dump-all-10-15-09.sql root@slave:~

Import Data

root@titan:~# mysql -u root -p < ~/mysql-dump-all-10-15-09.sql
Enter password

Slave /etc/mysql/my.cnf

[mysqld]
server-id = 2
log_bin = mysql-bin.log

Now set master from slave.

mysql> CHANGE MASTER TO MASTER_HOST='205.234.235.89',MASTER_USER='replication_user', MASTER_PASSWORD='PASSHERE',MASTER_LOG_POS=106,MASTER_LOG_FILE='mysql-bin.000001';
Query OK, 0 rows affected (0.00 sec)

*log file and position should be the output from master ‘show master status’; command.
*in need to reset the above then do ‘stop slave’, ‘reset slave’ and issue the CHANGE MASTER TO cmd again.

mysql> start slave
-> ;
Query OK, 0 rows affected (0.00 sec)

Verify Master/Slave are replicating. ‘Slave IO Running’ and ‘Slave SQL Running’ should both be set to “YES”

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.139.68.174
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 43981
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 33858
Relay_Master_Log_File: mysql-bin.000002
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: 43981
Relay_Log_Space: 33858
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
1 row in set (0.00 sec)

 

Check master to see if positon of slave ans master are in sync!

Done!

Leave a Reply