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 you can upgrade the Master's my.cnf file to handle the new environment.
I have an additional suggestion.
InnoDB prior to MySQL 5.1.38 cannot use multiple CPUs.
You can do one of two things that will not affect the Slave
- Upgrade to Percona Server 5.1
- MySQL 5.1.38 has InnoDB Plugin (dsiabled by default) which acceses mulitple CPUs
- If you have MySQL 5.1.37 or prior, upgrade to the latest MySQL 5.1 and install InnoDB Plugin
- If you have MySQL 5.1.38+, install InnoDB Plugin
Once you do, one of the above, you can configure the necessary options for accessing multiple CPUs
Best Answer
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
Here is what you do
STEP01 : Activate Binary Logging on the Old Server
Step01-a) Add this to /etc/my.cnf on the Master
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.
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:
STEP03 : Load the mysqldump into the Slave
Execute the mysql client loading the mysqldump into the Slave's mysql instance
STEP04 : Create MySQL Replication User on the Master
STEP05 : Setup the Slave with a Separate Server ID
Add this to /etc/my.cnf on the Slave
and
# service mysql restart
STEP06 : Setup the Replication on the Slave
Goto the mysql client and run the following command
STEP07 : Setup the point-in-time Master Log and Position
Back in STEP02, I mentioned viewing the point-in-time position using
You should see something like this:
Run it as command in the mysql client on the Slave
Afterwards, run this command
You should see something like this:
STEP08 : Launch Replication
Start up replication with this:
Afterwards, run this command again
If you see this
CONGRATULATIONS, MySQL Replication is Working !!!