Since you have Master/Slave, you may want to implement the use of two things
- Circular Replication
- Database Virtual IP (aka DBVIP)
Circular Replication
Circular Replication is nothing more than first setting up Master/Slave then performing the same steps using the Slave as the Master's Master and the Master as the Slave's Slave. It just entails
- explicitly user a different server_id on each DB Server
- enabling binary logging on both DB servers
- making sure the replication user is defined on both DB servers
Database Virtual IP (aka DBVIP)
There are products you can download and install to setup a DBVIP. One such product I use is ucarp
. Another product is Linux Heartbeat. I normally do not use such things with MySQL Circular Replication or Master/Slave. Why?
Since those products can perform automatic failover, you do not want to do that in the event a Slave is some number of seconds behind in replication lag.
You should perform manual failovers.
Here is a poor man's approach to implementing DBVIP management.
Suppose you have this setup
- DB Server1 has IP 10.1.2.30
- DB Server2 has IP 10.1.2.40
- You want to use DBVIP 10.1.2.50
Create the Script called /usr/local/sbin/MyAppDBVIP like this
echo echo 10.1.2.50 > /usr/local/sbin/MyAppDBVIP
Create the Script called /usr/local/sbin/dbvip-up
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr add ${DBVIP}/24 dev eth1
Create the Script called /usr/local/sbin/dbvip-down
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr del ${DBVIP}/24 dev eth1
Make sure all scripts are executable
chmod +x /usr/local/sbin/MyAppDBVIP
chmod +x /usr/local/sbin/dbvip-up
chmod +x /usr/local/sbin/dbvip-down
Make sure these script exist on both DB Servers
Simply run dbvip on whichever server you choose. .
So the failover process and protocol are the following:
- Run
dbvip-down
on the DB Server that has the DBVIP. If you cannot
- Run
dbvip-up
on the DB Server that you want to have the DBVIP
- Just remember you should not run
dbvip-up
on both machines
- After running
dbvip-up
, restart apache, JBoss, or any other app server contacting MySQL via the old Master
Yes, it will overwrite the data on the Slave. However, you can setup replication to pick up from the point-in-time of the dump and make it roll all changes since the dump was loaded on the Slave
For this Example, let's assume
- IP of the Master is 10.1.1.20
- IP of the Slave is 10.1.1.30
Here is what you do
STEP01 : Activate Binary Logging on the Old Server
Step01-a) Add this to /etc/my.cnf on the Master
[mysqld]
server-id=101120
log-bin=mysql-bin
Step01-b) # service mysql restart
After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql
STEP02 : Perform mysqldump
On the Master, you can mysqldump the data and record from what point in time it happened.
# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart
What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:
# head -22 MySQLData.sql | tail -1
STEP03 : Load the mysqldump into the Slave
Execute the mysql client loading the mysqldump into the Slave's mysql instance
# mysql -h10.1.1.30 -uroot -p < MySQLData.sql
STEP04 : Create MySQL Replication User on the Master
# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"
STEP05 : Setup the Slave with a Separate Server ID
Add this to /etc/my.cnf on the Slave
[mysqld]
server-id=101130
and # service mysql restart
STEP06 : Setup the Replication on the Slave
Goto the mysql client and run the following command
mysql> CHANGE MASTER TO
MASTER_HOST='10.1.1.20',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1;
STEP07 : Setup the point-in-time Master Log and Position
Back in STEP02, I mentioned viewing the point-in-time position using
# head -22 MySQLData.sql | tail -1
You should see something like this:
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Run it as command in the mysql client on the Slave
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Afterwards, run this command
mysql> SHOW SLAVE STATUS\G
You should see something like this:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.113.232
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 122957100
Relay_Log_File: relay-bin.003666
Relay_Log_Pos: 122957100
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: No
Slave_SQL_Running: No
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: 106
Relay_Log_Space: 106
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
STEP08 : Launch Replication
Start up replication with this:
mysql> START SLAVE;
Afterwards, run this command again
mysql> SHOW SLAVE STATUS\G
If you see this
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
CONGRATULATIONS, MySQL Replication is Working !!!
Best Answer
I use nightly lvm snapshots of production databases that are made static by removing the files needed for replication (master.info, relay-logs), and making that snapshot writable. You'll need to shut mysql down for a few minutes to make the snapshot and have room on the machine to hold that snapshot, but it's an easy and elegant solution.