|
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 ]
| Comments: | ||
| master slave mysql replication on windows | reetish | 02/16/09 02:54 |
| Two-way (dual master) replication | Mick Sear | 05/03/05 09:19 |
| mysql replication = sync | Bobby | 03/17/05 22:14 |
| RE: How to make the slave database read only ? | Ramon Sosa | 01/31/05 12:56 |
| RE: Too many connections... | Max | 03/18/04 06:39 |
| RE: Error reading Packet | Dimple | 04/22/03 03:04 |
| Mysql database Replication | Dimple | 04/22/03 02:36 |
| Re: replicate 1 db | Antoine Adams | 01/22/03 18:20 |
| RE: where is FIRST.log? | booting | 10/16/02 17:28 |
| RE: too many connections... | someone | 10/15/02 18:49 |
| Conversion of Oracle to MySQL | Senthil kumar | 10/10/02 03:28 |
| RE: replicate 1 database | cheng | 10/02/02 04:01 |
| Snapshot..... | jason | 09/17/02 23:26 |
| update log statements with freeze | puneet | 09/12/02 06:39 |
| server-id | Steve | 09/04/02 11:20 |
| Mysql Replication Question | Vicky | 08/20/02 14:59 |
| replicate 1 database | Joshua Dickerson | 08/16/02 19:24 |
| Replication in MySql | sushant | 08/12/02 04:18 |
| RE: MySQL connection | Ray da Costa | 08/01/02 12:10 |
| Hello | neelima | 06/29/02 01:00 |
| Oracle to MySql | Bruno Carlos Caetano Santos | 06/11/02 09:14 |
| RE: Linux to NT | Ricky Palandeng | 06/11/02 09:00 |
| RE: Replication of single DB | mayonaise | 05/24/02 04:26 |
| possible to balance the load? | Fredrik Jeanson | 05/07/02 18:03 |
| How to make the slave database read only ? | Parminder | 05/06/02 10:54 |
| RE: Connect Function to balance the load | john signorello | 05/02/02 17:09 |
| use of TYPE | Amine Hadji | 04/30/02 08:03 |
| Connect Function to balance the load | WaaX | 04/28/02 20:43 |
| Error install librerias de mysql | Dimas Ayala | 04/09/02 17:45 |
| where is FIRST.log? | linccg | 03/25/02 01:30 |
| RE: Replication of single DB | Radek Burkat | 03/05/02 17:45 |
| mysql | guillaume b | 02/17/02 18:01 |
| sync of local box and internet box | sachin rase | 02/01/02 21:24 |
| corrupted table on one corrupts the other | Campbell | 02/01/02 17:20 |
| Replication of single DB | Charitha | 01/28/02 04:23 |
| Connect Function - my version | Jacob C | 01/24/02 22:51 |
| Manual MYSQL replication | Edouard Lauer | 01/21/02 09:42 |
| Primary Key's and Pull Down menues | George Van Tuyl | 01/08/02 18:24 |
| upload | arti | 12/25/01 10:46 |
| my.cnf changed to my_cnf.bak | bhuwan tiwari | 12/25/01 03:31 |
| A and the B... errors - URGENT !!!!! | Rony Cesar | 11/20/01 08:32 |
| Just a different way :) | AdStar | 10/28/01 03:00 |
| Data Availability - Mysql | Cleber | 10/22/01 11:21 |
| RE: Bi-Directional Replication | ram | 10/22/01 06:22 |
| Error in two-end replication | Ten | 10/15/01 14:57 |
| NEWBY: problems in replication, please help! | Fabrizio Fioravanti | 10/12/01 08:19 |
| too many connections... | mario palomares | 10/11/01 18:28 |
| synchronisation of MYSQL database | phani | 09/24/01 06:25 |
| Mysql errorcode 13 | G Smith | 08/31/01 08:41 |
| RE: If only one database to be replicated | Jeremy Hilton | 08/27/01 14:51 |
| error in replication at position 73 | Tilmann Großmann | 08/23/01 09:18 |
| replication of mysql datadase | Vinod Nambiar | 08/21/01 06:59 |
| Linux to NT | Kevin | 08/15/01 01:25 |
| Automated Replication | has | 08/07/01 07:42 |
| Procedures,functions, triggers | guilherme | 08/04/01 18:24 |
| ask for recover procedure | Tony | 07/30/01 14:44 |
| Bi-Directional Replication | Beccles | 07/25/01 05:56 |
| howto database files in mysql | alfin | 07/17/01 12:18 |
| RE: error connecting to master | Beccles | 07/06/01 05:47 |
| replication/ several server on one system | Mauel | 06/18/01 22:49 |
| RE: MySQL connection | apocryia | 06/12/01 08:54 |
| Slaves with many masters? | Stephen Davis | 06/07/01 23:08 |
| RE: A MySQL cluster: is it possible? | Myke | 06/07/01 15:49 |
| RE: Security of connection? | Myke | 06/07/01 15:45 |
| RE: MySQL connection | George | 06/05/01 01:53 |
| RE: MS SQL to MYSQL replication Help ! | Richard Lynch | 05/29/01 16:13 |
| Replicate Mysql to DB2 | Didier | 05/22/01 10:10 |
| RE: How Often is it updated? | A/C | 05/19/01 03:56 |
| MySQL connection | Sarah | 05/13/01 05:24 |
| If only one database to be replicated | Aravind | 05/09/01 01:10 |
| Iterartive updates ?? | Mustiantono | 04/27/01 01:12 |
| Help : MySQL Replication with Windows NT | Mulder | 04/25/01 05:58 |
| RE: error connecting to master | Andrew | 03/30/01 02:10 |
| RE: error connecting to master | brucelai | 03/17/01 14:02 |
| 2-way replication from mysql's site: | Alex | 03/15/01 11:45 |
| MS SQL to MYSQL replication Help ! | David Wilson | 02/22/01 10:16 |
| Any good monitoring tool? | Guess | 02/19/01 16:27 |
| RE: error in mysql replication | Guess | 02/12/01 21:42 |
| RE: error connecting to master | Guess | 02/12/01 21:41 |
| Deleting a field | Helzy | 02/01/01 16:31 |
| error connecting to master | Andrew Cuthbert | 01/29/01 10:57 |
| RE: Security of connection? | Poncki | 01/26/01 13:47 |
| A MySQL cluster: is it possible? | Roy Nasser | 01/15/01 10:34 |
| Client Fail-Over code for JDBC | Nash Weber | 01/08/01 13:50 |
| Security of connection? | Nick Rab | 01/03/01 16:08 |
| error in mysql replication | jasvinder | 12/29/00 01:26 |
| MS SQL to MYSQL replication | todd | 12/14/00 19:35 |
| Mysql Replication and Transactions | tom | 12/04/00 20:20 |
| RE: Can replication be automated ? | Markus | 10/27/00 07:55 |
| Error from master | Thomas Schletter | 10/27/00 03:25 |
| RE: Can replication be automated ? | karthikeyan | 10/21/00 16:16 |
| replication only one way | mel | 10/18/00 04:09 |
| error reading Packet | André martinelli Agunzi | 10/10/00 13:37 |
| What use can have the Msql Programme | Martin Giuraq | 10/08/00 17:03 |
| Can replication be automated ? | Sam | 10/07/00 00:27 |
| Error reading Packet | André Amrtinelli Agunzi | 10/06/00 16:35 |
| client fail-over code | James Hickie | 10/06/00 09:40 |
| RE: Error reading Packet | Brian Austin | 10/04/00 15:58 |
| Error reading packet | Rafael Mateo | 10/04/00 12:43 |
| Error reading Packet | Brian Austin | 10/03/00 19:05 |
| RE: Snapshot? | Michael Tanoviceanu | 10/01/00 00:11 |
| Snapshot? | Paul | 09/28/00 23:42 |
| RE: mysql table | Melvyn Sopacua | 09/25/00 15:30 |
| INSERT DELAYED | Constantin Deribin | 09/25/00 11:51 |
| RE: mysql table | Melvyn Sopacua | 09/23/00 21:10 |
| RE: Load balancing query | Andrei | 09/23/00 18:24 |
| mysql table | Melvyn Sopacua | 09/23/00 15:49 |
| RE: Load balancing query | Steven Fletcher | 09/21/00 10:15 |
| RE: Load balancing query | Michael Tanoviceanu | 09/20/00 13:45 |
| Load balancing query | Steven Fletcher | 09/19/00 17:18 |
| Timeout patch for PHP4 MySQL connections | Michael Tanoviceanu | 09/18/00 14:24 |
| RE: why no timeout in mysql_connect() ? | Larry | 09/16/00 01:12 |
| RE: why no timeout in mysql_connect() ? | Michael Tanoviceanu | 09/16/00 00:25 |
| RE: why no timeout in mysql_connect() ? | Larry | 09/15/00 22:32 |
| RE: Two Corrections | Michael Tanoviceanu | 09/15/00 13:52 |
| Two Corrections | Sasha Pachev | 09/15/00 13:35 |
| Update | Michael Tanoviceanu | 09/15/00 13:25 |
| why no timeout in mysql_connect() ? | Larry | 09/15/00 12:38 |
| RE: small correction | Michael Tanoviceanu | 09/15/00 12:31 |
| RE: Bandwidth requirements | Michael Tanoviceanu | 09/15/00 12:30 |
| Bandwidth requirements | Phil | 09/15/00 11:36 |
| RE: mySQL upgrade | ashish | 09/13/00 13:45 |
| RE: mySQL upgrade | ashish | 09/13/00 13:44 |
| mySQL upgrade | ashish | 09/13/00 13:43 |
| small correction | Justin Grant | 09/13/00 12:31 |
| Nice one. | Richard Heyes | 09/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. | ||


