OverView
Step by Step Approach to MySQL Replication
- Make sure the database in each server (A, B, C, D) is in same state.
§ Make a dump of database on any one of the server A and copy that into rest of the entire server B, C, D.
§ Server A command line> mysqldump -u <mysql user> -p<mysql password> -c <database name> > <filename of dump>
§ Copy the dump file created in Server A to Server B, C and D.
§ Repeat the following command in Server B, C and D.
§ Server B, C, D command line> mysql -u <mysql user> -p<mysql password> -D <database name> < <filename of dump>
§ Make sure that the databases in all four servers look alike.
- Create a "slave user" on each server. These users are used by MySQL for the slave to master connection and need to be given specific privileges.
§ Repeat the following in all four servers MySQL command line.
§ Server MySQL command line> USE mysql;
§ Server MySQL command line> INSERT INTO user (Host, User, Password, Select_priv, Reload_priv, Super_priv, Repl_slave_priv, ssl_cipher, x509_issuer, x509_subject) VALUES ('<Hostname/IP>', '<slave_user>', password('<slave_password>'), 'Y', 'Y', 'Y', 'Y', ‘ ‘, ‘ ‘, ‘ ‘);
§ Server MySQL command line> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘slave_password’;
§ Server MySQL command line> FLUSH ALL PRIVILEGES;
· Delete the following files in all the servers, inside the MySQL data folder. Usually it is C:\ProgramData\MySQL\MySQL Server 5.5\data.
§ Stop MySQL service
§ *.err, *.pid, master.info, relay-log.info, *-relay-bin.*
§ Start MySQL service
· Open and edit my.ini in all servers for Slave Configuration.
§ Stop MySQL services.
§ Comment “skip-innodb”
§ Uncomment “innodb_flush_log_at_trx_commit=1”
§ Add the following…
#Replication Configuration Server A
sync_binlog=1
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1
log-slave-updates
replicate-do-db = <database1>
replicate-do-db = <database2> relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
log-bin = C:\mysql\log\log-bin.log # change this to a path/name appropriate to your system
binlog-do-db = <database1>
binlog-do-db = <database2>
#Replication Configuration Server B
sync_binlog=1
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2
log-slave-updates
replicate-do-db = <database name>
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
log-bin = C:\mysql\log\log-bin.log # change this to a path/name appropriate to your system
binlog-do-db = <database1>
binlog-do-db = <database2>
#Replication Configuration Server C
sync_binlog=1
server-id = 3
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 3
log-slave-updates
replicate-do-db = <database1>
replicate-do-db = <database2>
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
log-bin = C:\mysql\log\log-bin.log # change this to a path/name appropriate to your system
binlog-do-db = <database1>
binlog-do-db = <database2>
#Replication Configuration Server D
sync_binlog=1
server-id = 4
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 4
log-slave-updates
replicate-do-db = <database1>
replicate-do-db = <database2>
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
log-bin = C:\mysql\log\log-bin.log # change this to a path/name appropriate to your system
binlog-do-db = <database1>
binlog-do-db = <database2>
§ Note: The two MySQL configuration variables that prevent key collisions are auto-increment-increment and auto-increment-offset. The value of auto-increment-increment should be at least set to N, where N is equal to the number of servers in the replication setup (in this case two). The auto-increment-offset and server-id configuration variables should be set as consecutive integers (in this case 1, 2, 3, and 4). Server-id should be any unique integer assigned to each server.
§ The path given in log-bin (in next step) should be a valid path. Create the folders if doesn’t exist.
§ After adding the server-id, the server has to be restarted.
§ Start MySQL service.
· Set the Master Configuration from the MySQL command line on each server.
§ Server MySQL command line> STOP SLAVE;
§ Server MySQL command line> SHOW MASTER STATUS;
Eg:
File - log-bin.000002
Position - 107
Binlog_Do_DB - <database name>
Binlog_Ignore_DB -
§ Change Master Configuration.
Server B MySQL command line> CHANGE MASTER TO MASTER_HOST='<Server A’s IP>', MASTER_USER='<A’s slave_user>', MASTER_PASSWORD='<A’s slave_password>', MASTER_PORT=3306, MASTER_LOG_FILE='<A’s Master Filename
MASTER_LOG_POS=<A’s Master Position>, MASTER_CONNECT_RETRY=60;
Server C MySQL command line> CHANGE MASTER TO MASTER_HOST='<Server B’s IP>', MASTER_USER='<B’s slave_user>', MASTER_PASSWORD='<B’s slave_password>', MASTER_PORT=3306, MASTER_LOG_FILE='<B’s Master Filename>',
MASTER_LOG_POS=<B’s Master Position>, MASTER_CONNECT_RETRY=60;
Server D MySQL command line> CHANGE MASTER TO MASTER_HOST='<Server C’s IP>', MASTER_USER='<C’s slave_user>', MASTER_PASSWORD='<C’s slave_password>', MASTER_PORT=3306, MASTER_LOG_FILE='<C’s Master Filename>',
MASTER_LOG_POS=<C’s Master Position>, MASTER_CONNECT_RETRY=60;
Server A MySQL command line> CHANGE MASTER TO MASTER_HOST='<Server D’s IP>', MASTER_USER='<D’s slave_user>', MASTER_PASSWORD='<D’s slave_password>', MASTER_PORT=3306, MASTER_LOG_FILE='<D’s Master Filename>',
MASTER_LOG_POS=<D’s Master Position>, MASTER_CONNECT_RETRY=60;
§ Server MySQL command line> START SLAVE;
§ Server MySQL command line> SHOW SLAVE STATUS;
Look for the following fields in the row
slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_IO_Errno: 0
Last_SQL_Errno: 0
Master_Server_Id: <Master’s server-id>
Now test by making some changes to the database. It should be replicated on all 4 Servers.
