Hello, please sign in or register
You are here: Home

Replication and Load balancing with master and slave servers

Preface

I want to be able to duplicate data between two servers. One server is additionally a webserver and the other additionaly updates the data. The idea is that i hope to be able to reduce the proccessing overhead on the webserver.

I could recreate the data by periodically deleting and copying the whole database across, but, for what i'm working on, cant spare the 5gb going over the network and the grind on the webserver : ( - So data replication seems to be a very good alternative as it only transferrs the changes, and executes quickly at the other end.

Setup master

Grant access on the master server, called 'A', to be accessed by slave server, called 'B', (the slave is the webserver)

GRANT REPLICATION SLAVE, SELECT, SUPER ON *.* TO 'repl'@'B' IDENTIFIED BY 'youllnevergetthispassword'  # FLUSH PRIVILIGES, is unnecessary if rebooting the server)  FLUSH PRIVILEGES  

NOTE: the password can be only 16 characters long

DONT: try setting this in mysql 4.0.23-nt. 'REPLICATION SLAVE' is not recognised, and the exercise will fall, flattening its face. I used MySQL 4.1.10a-nt on both master and slave servers

TEST: priviliges with SHOW GRANT 'repl'@'B'

In the master server 'A' my.ini(Win) or my.cnf(Linux) file. Write under [mysqld] section...

# INITIATE BINARY LOGGING  set-variable=log-bin=D:mysqldata  # IDENTIFY THIS SERVER (id must be unique on the network)  server-id=1  

NOTE: If you restart the master server 'A' now, and make changes to the records, you will see that a file data.00# will be created and update each time a change is made. But dont do this yet as you'll need to ensure that you have a replicated dataset on another machine in which to make updates.

Setup slave

You will need to ensure that you have an exact replica of the inital data on the slave. To do this. Stop the master server 'B'. Copy all database files from the master server 'A' to the slave server 'B'. Zip'em preferably

In the slave server 'A' my.ini(Win) or my.cnf(Linux) file, write under [mysqld]...

# MASTER SETTINGS  master-host= A  master-user= repl  master-password= youllnevergetthispassword  # IDENTIFY THIS SERVER  server-id=2  

You can override these settings with the mysql command... (This'll override anything you put in my.cnf). E.G.

  CHANGE MASTER TO MASTER_HOST=A, MASTER_USER=repl, MASTER_PASSWORD=youllnevergetthispassword  #MASTER_LOG_FILE='master-bin.004' MASTER_LOG_POS=420  

Run


mysql> SHOW SLAVE STATUS;

+----------------------------------+-------------+-------------+-------------+---------------+-----------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+

| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |

+----------------------------------+-------------+-------------+-------------+---------------+-----------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+

| Waiting for master to send event | igweb04 | repl | 3306 | 60 | data.000001 | 1880 | IGITWRK01-relay-bin.000003 | 1202 | data.000001 | Yes | Yes | books | | | | | | 0 | | 0 | 1880 | 1202 | None | | 0 | No | | | | | | 0 |

+----------------------------------+-------------+-------------+-------------+---------------+-----------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+

1 row in set (0.00 sec)



mysql>

Comments

WyJafpyLUncvGxTaO
These topics are so confsunig but this helped me get the job done.
Created 07/08/11
dXUaikZmskjd
QLAaXW fwvatwwtxetw
Created 08/08/11
hrtmZvCXrNKW
jGHkt5 , [url=http://murqigkqdgse.com/]murqigkqdgse[/url], [link=http://nrpwdqeylmor.com/]nrpwdqeylmor[/link], http://yusmvksqjmuz.com/
Created 09/08/11
byOOvwFdCgUcD
tngZsc amolvvollcib
Created 10/08/11
CtfBzODNOl
r0IVq6 , [url=http://fgnlxopcwyaa.com/]fgnlxopcwyaa[/url], [link=http://thgsbcmijxlw.com/]thgsbcmijxlw[/link], http://loigjhvusivk.com/
Created 14/08/11
chenlina
chenlina20160103 ugg boots on sale louis vuitton outlet polo ralph laure...
Created 03/01/16
clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share