As long as you do not perform any INSERT/UPDATE/DELETE statements directly on the Slave, your Slave should be just fine. Otherwise, MySQL Replication could break if you INSERT an new row in mydb.mytable on the Slave and, via Replication, the Slave later detects an INSERT of a row to mydb.mytable with the same PRIMARY KEY. This produces error 1062 (Duplicate Key).
The only way you could write to Slave without breaking MySQL Replication is this:
- Master has databases db1,db2,db3
- Slave is replicating databases db1,db2,db3 from the Master
- You run
CREATE DATABASE db4;
on the Slave
- You do INSERTs/UPDATEs/DELETEs only to db4 on the Slave
- You do not do INSERTs/UPDATEs/DELETEs only to db1,db2,db3 on the Slave
To detect a Master and Slave being out of sync without downloading any tools, pick any table and run CHECKSUM TABLE against a table on the Master and the Slave's copy of the same table.
EXAMPLE
If you have a table mydb.mytable
, run the command against it:
mysql> CHECKSUM TABLE mydb.mytable;
If the values do not come back the same, then something is out-of-sync.
If you want to examine a bunch of table in bulk, you can down Percona's MAATKIT. You will need two specific tools (Percona also has the Percona Toolkit that they themselves forked from MAATKIT which is now being promoted more)
or
pt-table-checksum
will perform a CHECKSUM TABLE against all tables on Master and Slave. You can configure it to do all databases on just specific ones.
pt-table-sync
can be run on a Slave against any table. Using the --print and --sync-to-master options, you can see what SQL statements need to be executed on the Slave to have it perfectly match the Master. This tool does not work with table that lacks a PRIMARY KEY or UNIQUE KEY.
I have used MAATKIT for years. I still do. I have not tried the Percona Toolkit yet, but I am sure it should be of the same quality as MAATKIT.
Forgive me for sounding somewhat biased, but you can learn about setting up Master/Slave and Master/Master Replication right here in the DBA StackExchange.
Here are some of my posts
I also have posts concerning
Click here to see all MySQL Replication posts in the DBA StackExchange (not just mine)
There is only one thing I cannot give advice on : MySQL Cluster
While I have answered some questions, there are three MySQL Cluster Experts here
Feel free to post your MySQL Replication questions or simply search through the already posted questions. You may find that what you are looking for is already answered.
Best Answer
If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.
Here is a Topology
Say you want to Spawn
SLV3
. You could useSLV0
as a CloneSLV3
(Same version as all other Slaves)STOP SLAVE;
service mysql stop
scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
service mysql start
chown -R mysql:mysql /var/lib/mysql
server_id
in/etc/my.cnf
to be unique from all other Slavesservice mysql start
That's it.
This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as
SST
(State Snapshot Transfer).If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.