Sr. Web Developer
mediabistro.com
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume

Using MySQL's Built-In Replication To Maximize Availability
Step Two: Configure The Slave.
Go ahead and stop the MySQL server on the slave, and move the database directories you copied above to the data directory on the slave server. Be sure to change the ownership and group of the directories recursively to the MySQL user, and change the file mode to 660 (read-write for owner and group only) on the files, and the directories themselves to 770 (read-write-execute for owner and group only).
Now go ahead and start the MySQL server on the slave to ensure everything is working fine. Run a few select queries (no updates or inserts) to make sure the data snapshot you took in step one was successful. Go ahead and shutdown the server after successful testing.
The slave needs to be configured to look to a specific master to receive its updates, so we need to edit the 'my.cnf' file on the slave, adding the following lines to the[mysqld]portion.
master-host=10.1.1.1
master-user=replicate
master-password=password
After starting the slave server, it will automatically look the master specified in the 'my.cnf' file for any updates and incorporate those changes into its databases. The slave server keeps track of what updates it has received from its master in the 'master.info' file. The status of the slave thread can be seen through the sql command 'SHOW SLAVE STATUS'. Any errors in processing the binary logs on the slave will cause the slave thread to exit, and generate a message in the *.err log. The errors can then be corrected, and the sql statement 'SLAVE START' can be used to restart the slave thread, where it will pick up where it left off in the binary log of the master.
By now the changes made to the data on the master should have replicated to the slave, and you can test this by inserting or updating a record on the master, and then selecting it on the slave.
Now we have a master -> slave relationship from A -> B, which would allow us to redirect all of our queries to B if A should be down, but we have no way of getting any updates to the databases back to A when it is brought back up. To solve that problem, we create a master -> slave relationship from B -> A.
[ Next Page ]

[Page 1]  [Page 2]  


Comments:
master slave mysql replication on windowsreetish02/16/09 02:54
Two-way (dual master) replicationMick Sear05/03/05 09:19
mysql replication = syncBobby03/17/05 22:14
RE: How to make the slave database read only ?Ramon Sosa01/31/05 12:56
RE: Too many connections...Max03/18/04 06:39
RE: Error reading PacketDimple04/22/03 03:04
Mysql database ReplicationDimple04/22/03 02:36
Re: replicate 1 dbAntoine Adams01/22/03 18:20
RE: where is FIRST.log?booting10/16/02 17:28
RE: too many connections...someone10/15/02 18:49
Conversion of Oracle to MySQL Senthil kumar10/10/02 03:28
RE: replicate 1 databasecheng10/02/02 04:01
Snapshot.....jason09/17/02 23:26
update log statements with freezepuneet09/12/02 06:39
server-idSteve09/04/02 11:20
Mysql Replication QuestionVicky08/20/02 14:59
replicate 1 databaseJoshua Dickerson08/16/02 19:24
Replication in MySqlsushant08/12/02 04:18
RE: MySQL connectionRay da Costa08/01/02 12:10
Helloneelima06/29/02 01:00
Oracle to MySqlBruno Carlos Caetano Santos06/11/02 09:14
RE: Linux to NTRicky Palandeng06/11/02 09:00
RE: Replication of single DBmayonaise05/24/02 04:26
possible to balance the load?Fredrik Jeanson05/07/02 18:03
How to make the slave database read only ?Parminder05/06/02 10:54
RE: Connect Function to balance the loadjohn signorello05/02/02 17:09
use of TYPEAmine Hadji04/30/02 08:03
Connect Function to balance the loadWaaX04/28/02 20:43
Error install librerias de mysqlDimas Ayala04/09/02 17:45
where is FIRST.log?linccg03/25/02 01:30
RE: Replication of single DBRadek Burkat03/05/02 17:45
mysqlguillaume b02/17/02 18:01
sync of local box and internet boxsachin rase02/01/02 21:24
corrupted table on one corrupts the otherCampbell02/01/02 17:20
Replication of single DBCharitha01/28/02 04:23
Connect Function - my versionJacob C01/24/02 22:51
Manual MYSQL replicationEdouard Lauer01/21/02 09:42
Primary Key's and Pull Down menuesGeorge Van Tuyl01/08/02 18:24
uploadarti12/25/01 10:46
my.cnf changed to my_cnf.bakbhuwan tiwari12/25/01 03:31
A and the B... errors - URGENT !!!!!Rony Cesar11/20/01 08:32
Just a different way :)AdStar10/28/01 03:00
Data Availability - MysqlCleber10/22/01 11:21
RE: Bi-Directional Replicationram10/22/01 06:22
Error in two-end replicationTen10/15/01 14:57
NEWBY: problems in replication, please help!Fabrizio Fioravanti10/12/01 08:19
too many connections...mario palomares10/11/01 18:28
synchronisation of MYSQL databasephani09/24/01 06:25
Mysql errorcode 13G Smith08/31/01 08:41
RE: If only one database to be replicatedJeremy Hilton08/27/01 14:51
error in replication at position 73Tilmann Großmann08/23/01 09:18
replication of mysql datadaseVinod Nambiar08/21/01 06:59
Linux to NTKevin08/15/01 01:25
Automated Replicationhas08/07/01 07:42
Procedures,functions, triggersguilherme08/04/01 18:24
ask for recover procedureTony07/30/01 14:44
Bi-Directional ReplicationBeccles07/25/01 05:56
howto database files in mysqlalfin07/17/01 12:18
RE: error connecting to masterBeccles07/06/01 05:47
replication/ several server on one systemMauel06/18/01 22:49
RE: MySQL connectionapocryia06/12/01 08:54
Slaves with many masters?Stephen Davis06/07/01 23:08
RE: A MySQL cluster: is it possible?Myke06/07/01 15:49
RE: Security of connection?Myke06/07/01 15:45
RE: MySQL connectionGeorge06/05/01 01:53
RE: MS SQL to MYSQL replication Help !Richard Lynch05/29/01 16:13
Replicate Mysql to DB2Didier05/22/01 10:10
RE: How Often is it updated?A/C05/19/01 03:56
MySQL connectionSarah05/13/01 05:24
If only one database to be replicatedAravind05/09/01 01:10
Iterartive updates ??Mustiantono04/27/01 01:12
Help : MySQL Replication with Windows NTMulder04/25/01 05:58
RE: error connecting to masterAndrew03/30/01 02:10
RE: error connecting to masterbrucelai03/17/01 14:02
2-way replication from mysql's site:Alex03/15/01 11:45
MS SQL to MYSQL replication Help !David Wilson02/22/01 10:16
Any good monitoring tool?Guess02/19/01 16:27
RE: error in mysql replicationGuess02/12/01 21:42
RE: error connecting to masterGuess02/12/01 21:41
Deleting a fieldHelzy02/01/01 16:31
error connecting to masterAndrew Cuthbert01/29/01 10:57
RE: Security of connection?Poncki01/26/01 13:47
A MySQL cluster: is it possible?Roy Nasser01/15/01 10:34
Client Fail-Over code for JDBCNash Weber01/08/01 13:50
Security of connection?Nick Rab01/03/01 16:08
error in mysql replicationjasvinder12/29/00 01:26
MS SQL to MYSQL replicationtodd12/14/00 19:35
Mysql Replication and Transactionstom12/04/00 20:20
RE: Can replication be automated ?Markus10/27/00 07:55
Error from masterThomas Schletter10/27/00 03:25
RE: Can replication be automated ?karthikeyan10/21/00 16:16
replication only one waymel10/18/00 04:09
error reading PacketAndré martinelli Agunzi10/10/00 13:37
What use can have the Msql ProgrammeMartin Giuraq10/08/00 17:03
Can replication be automated ?Sam10/07/00 00:27
Error reading PacketAndré Amrtinelli Agunzi10/06/00 16:35
client fail-over codeJames Hickie10/06/00 09:40
RE: Error reading PacketBrian Austin10/04/00 15:58
Error reading packetRafael Mateo10/04/00 12:43
Error reading PacketBrian Austin10/03/00 19:05
RE: Snapshot?Michael Tanoviceanu10/01/00 00:11
Snapshot?Paul09/28/00 23:42
RE: mysql tableMelvyn Sopacua09/25/00 15:30
INSERT DELAYEDConstantin Deribin09/25/00 11:51
RE: mysql tableMelvyn Sopacua09/23/00 21:10
RE: Load balancing queryAndrei09/23/00 18:24
mysql tableMelvyn Sopacua09/23/00 15:49
RE: Load balancing querySteven Fletcher09/21/00 10:15
RE: Load balancing queryMichael Tanoviceanu09/20/00 13:45
Load balancing querySteven Fletcher09/19/00 17:18
Timeout patch for PHP4 MySQL connectionsMichael Tanoviceanu09/18/00 14:24
RE: why no timeout in mysql_connect() ?Larry09/16/00 01:12
RE: why no timeout in mysql_connect() ?Michael Tanoviceanu09/16/00 00:25
RE: why no timeout in mysql_connect() ?Larry09/15/00 22:32
RE: Two CorrectionsMichael Tanoviceanu09/15/00 13:52
Two CorrectionsSasha Pachev09/15/00 13:35
UpdateMichael Tanoviceanu09/15/00 13:25
why no timeout in mysql_connect() ?Larry09/15/00 12:38
RE: small correctionMichael Tanoviceanu09/15/00 12:31
RE: Bandwidth requirementsMichael Tanoviceanu09/15/00 12:30
Bandwidth requirementsPhil09/15/00 11:36
RE: mySQL upgradeashish09/13/00 13:45
RE: mySQL upgradeashish09/13/00 13:44
mySQL upgradeashish09/13/00 13:43
small correctionJustin Grant09/13/00 12:31
Nice one.Richard Heyes09/13/00 09:37
 

If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly.

Add A Comment:

Name:

Email:

Subject:

Message:

To reduce spam posts, messages are now manually approved

You are not [logged in]. That means your account will not get credit for this post.