From: Marcus Bointon
Date: April 7 2008 10:24am
Subject: Re: Master - Slave MySQL Database Server
List-Archive: http://lists.mysql.com/replication/1202
Message-Id:
MIME-Version: 1.0 (Apple Message framework v919.2)
Content-Type: multipart/signed; boundary=Apple-Mail-7--524082273; micalg=sha1; protocol="application/pkcs7-signature"
--Apple-Mail-7--524082273
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit
On 7 Apr 2008, at 11:09, Kaushal Shriyan wrote:
> is this a correct documentation
> http://howtoforge.com/mysql_master_master_replication for Master Slave
> Replication
Well, it's for master-master, not master-slave. Also see http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
> My requirement was if the Master goes down, the slave should take
> care and
> act as Master. the Master should acts as slave and reversing of this
> role.
> is this possible
Yes. With master-master, neither machine is considered a slave
(actually they are both slaves too). However, there are good practical
reasons to treat one as a master, for example it may be on superior
hardware, or you need to ensure transactional integrity which you
can't do spread across servers.
When you have master-master up and running, you need to deal with
failover at the application level - so your app needs to know about
both servers and know to try the other one if its default connection
fails.
> Also how can i test this setup from the MySQL client. Any test cases
> available would be of great help to test out this MySQL Database
> Master and
> Slave Server
It's easy to test - log into each instance. Do an insert on one and it
should be available pretty much immediately from the other, then test
it in the other direction. Then try the same thing for deletes. After
that, you just need to monitor the 'seconds_behind_master' field in
the output from "show slave status" on each.
Most guides neglect to tell you about:
mysqldump -u root -p --opt --quick --single-transaction --master-data
mydb > mydb.sql
If you're using InnoDB everywhere, this will get you a data dump in a
consistent state without having to lock tables or otherwise block
access to your DB while you're setting up the replication.
Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/
--Apple-Mail-7--524082273
Content-Disposition: attachment;
filename=smime.p7s
Content-Type: application/pkcs7-signature;
name=smime.p7s
Content-Transfer-Encoding: base64
MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIILLzCCBN0w
ggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAwezELMAkGA1UEBhMCR0Ix
GzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9yZDEaMBgGA1UECgwR
Q29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0aWZpY2F0ZSBTZXJ2aWNlczAeFw0w
NDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQx
FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsx
ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJz
dC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A
MIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVNNRm5pELlzkniii8efNIx
B8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQylbsMTzC9mKALi+VuG6JG+ni8
om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXqvgvOdjp6Dpvq/NonWz1zHyLmSGHG
TPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7Nl
yP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IBJzCCASMwHwYDVR0j
BBgwFoAUoBEKIz6W8Qfs4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZwAEu0UEh83j2uBG59
MA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQGCCsGAQUFBwMCBggr
BgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYyaHR0cDovL2NybC5j
b21vZG9jYS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKGMGh0dHA6Ly9jcmwu
Y29tb2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgBhvhCAQEEBAMCAQYw
DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMngq4RdiVTgZTvzbRx8
NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp1Er57D9Wyd12jMOCAU9s
APMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0zykX2OBKnxogL5dMUbtGB8SK
N04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYyyDlWjDoRUtNBpEMD9O3vMyfbOeAU
TibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+RMTQw16Hc6mYHK9/FX8wggZKMIIFMqAD
AgECAhABjmOx1E79nPKh12HwFZY8MA0GCSqGSIb3DQEBBQUAMIGuMQswCQYDVQQGEwJVUzELMAkG
A1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNU
IE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMB4XDTA3MTEyODAwMDAw
MFoXDTA4MTEyNzIzNTk1OVowgeMxNTAzBgNVBAsTLENvbW9kbyBUcnVzdCBOZXR3b3JrIC0gUEVS
U09OQSBOT1QgVkFMSURBVEVEMUYwRAYDVQQLEz1UZXJtcyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6
IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8wHQYDVQQLExYoYykyMDAzIENvbW9k
byBMaW1pdGVkMRcwFQYDVQQDEw5NYXJjdXMgQm9pbnRvbjEoMCYGCSqGSIb3DQEJARYZbWFyY3Vz
QHN5bmNocm9tZWRpYS5jby51azCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMfdkFu7
ZFntxqysPCX4ZELsCGET2maGRji8pFDF0+kqR5yGUrI+de7BheAIy0IL7h89UeMSnJfYtnxb901y
LBFwIRC0R77i9ShXg4W3UC0hy3IfM0OTUZyjDWpdv3K7lRHDjEQy2iqF3fJSRchxQE6XfQiALWxF
foeU3NdTmjfqGcmPOiyv6ngDeEtx2766btbQMWaB8gp/kesA79jBfkip34bK7SE5r7K33iyCKQFB
nJkBydGk/MMRMPev3HX879YpBKthhbfvHNrDalpiRf6R78CV6L9DfpcyUvfbTYTf+fIEb3kxP3Q8
nShXggq1scwfWVtfwBQKORmbVO5rlaMCAwEAAaOCAiswggInMB8GA1UdIwQYMBaAFImCZ33EnSZw
AEu0UEh83j2uBG59MB0GA1UdDgQWBBRqnns+AZrtY4U86tNs3z+p9BTPYzAOBgNVHQ8BAf8EBAMC
BaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGyMQEDBQIwEQYJYIZI
AYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCswKQYIKwYBBQUHAgEWHWh0
dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0wgZowTKBKoEiGRmh0dHA6Ly9j
cmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50QXV0aGVudGljYXRpb25hbmRFbWFp
bC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5ldC9VVE4tVVNFUkZpcnN0LUNsaWVudEF1
dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMHwGCCsGAQUFBwEBBHAwbjA2BggrBgEFBQcwAoYqaHR0
cDovL2NydC5jb21vZG9jYS5jb20vVVROQUFBQ2xpZW50Q0EuY3J0MDQGCCsGAQUFBzAChihodHRw
Oi8vY3J0LmNvbW9kby5uZXQvVVROQUFBQ2xpZW50Q0EuY3J0MCQGA1UdEQQdMBuBGW1hcmN1c0Bz
eW5jaHJvbWVkaWEuY28udWswDQYJKoZIhvcNAQEFBQADggEBAITHsTuM4xG7XfdPBrS0Vtqy81ch
waexQ8BBWlWY0SA50eELvK+6gk89y5q6/zxOgUlTyXDRpVwwZGt96KBfyxpd20WHVRBsGtYSCTAP
Vt0ahdVNIrm2hWzPnAF9Vb8rczhaiLN7dgz43q9XgfOsBbXYL5erkZppsJS/v18etsFzeWkZ4Xkz
kZ1JTp7qCRMPswKsPV7JrtToP0eA+Meuhb2NaoZZwZwwoTJnuN845yzLx6TF/qHmUQFVfT6rF9/6
Qv2rjp2ZmQAj18IrhsgFFL/abTK7fTGfwyMa8RJgShpYRLF1doTrbJL300T+upnQ/ceNkGtdDNJW
eynQjSNka3cxggP8MIID+AIBATCBwzCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYD
VQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYD
VQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1VU0VSRmlyc3QtQ2xp
ZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbAIQAY5jsdRO/Zzyoddh8BWWPDAJBgUrDgMCGgUA
oIICDTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wODA0MDcxMDI0
MjhaMCMGCSqGSIb3DQEJBDEWBBRq6orA94eMyCi1/8BJI/8T635j+DCB1AYJKwYBBAGCNxAEMYHG
MIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5
MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNl
cnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24g
YW5kIEVtYWlsAhABjmOx1E79nPKh12HwFZY8MIHWBgsqhkiG9w0BCRACCzGBxqCBwzCBrjELMAkG
A1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMV
VGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20x
NjA0BgNVBAMTLVVUTi1VU0VSRmlyc3QtQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbAIQ
AY5jsdRO/Zzyoddh8BWWPDANBgkqhkiG9w0BAQEFAASCAQBBvd67aF7Qa29Kavh78Z/vwfGPSLmI
Ls2NXrGj9OO4O3B/1i1F9GGicU8I7dVz2umQ1Kj+WoX9Ng8GvDg3h0z8tMicjbJfEy6qffmUSan3
xNfgW6g32bQxWqZHpeCzsjBC1ulLa8RCyqF5Vlja3E5k7tATVIptDxvwYkkqqBktbSTiCP9d22mv
JpwdysK5LU2Gbfiet3BT4lsg2hDrvWtE2OvNZwzMHs8tt5Fomj/ujMlU7/x1db4E1JdfBnCwqp6o
AJiqHvgPbHMitja9yxq81yxjVHDAqz8m5gu8iKA5xp7kkO1dOv1nw4mXXTtkByVQKefH4aRw9lYG
JEhQXt+EAAAAAAAA
--Apple-Mail-7--524082273--