Friday, August 19, 2016

MySQL Database Master To Slave Replication

I followed this guide but it's for Linux https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
But recorded all the steps i did to get it to work Windows. Tested twice.
DATABASE REPLICATION (Master to Slave replication) (On Windows)
-----------------------------------------------------------------------------------------------------------
If you don't already have slave server (used to replicate database from a master server) follow these steps (labelled by letters).
If you already have a slave server, make sure you have a server-uuid values in Data/auto.cnf on your master different from Data/auto.cnf on your slave.
If they are the same, delete auto.cnf on your slave server so that it can generate a new one. The move on to steps that are numbered (below these lettered steps).
-----------------------------------------------------------------------------------------------------------
Step A. Make a copy of master and use as slave. From command prompt. Change directory just outside of master's MySQL folder. Mine is c:/wamp/bin/mysql/.
        and my master's server is in c:/wamp/bin/mysql/mysql5.6.17/.
        so I cd to c:/wamp/bin/mysql/, then xcopy master's folder into a different folder at the same level called mysqlslave.
xcopy /s mysql5.6.17 mysqlslave

Step B. Delete mysqlslave/data/auto.cnf file. So that it can generate a different server-uuid from the master next time server starts.

Step C. Copied mysqlslave/my-default.ini overtop of mysqlslave/my.ini and changed/added these values so that i can my mysql server running on port 3311 and uses its own data
    my master was running on port 3310 so in mysqlslave/my.ini file i changed all the port to 3311.
basedir=c:/wamp/bin/mysql/mysqlslave
datadir=c:/wamp/bin/mysql/mysqlslave/data
port=3311
server_id=2
      
Step D. Now slave server can be run by using mysqld pointing to mysqlslave/my.ini file.
example:
    mysqld --defaults-file="c:/wamp/bin/mysql/mysqlslave/my.ini"

------------------------------------------------
We'll configure the master database/server first
------------------------------------------------
Step 1. On master server. If it's running shut it down from command line. MAKE SURE to all commands in command prompts as Adminstrator to prevent running into trouble.

mysqladmin -u root -p shutdown
    example: (this example specifies Port number and host as well, root user and will prompt for password)
    mysqladmin -P 3307 -h localhost -u root -p shutdown

Step 2. On master server. Find my.ini file (On my default install on windows it's located in c:/wamp/bin/mysql/mysql5.6.17/my.ini).
Change these values in my.ini. Notice server-id=1, log_bin=[a file name and path],
and binlog_do_db=[a database we want to replicate, ie. taxcollection, for multiple databases repeat this line for all databases that we want to replicate]
[mysqld]
port=3310
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit = 1
sync_binlog=1
log_bin=c:/wamp/bin/mysql/mysql5.6.17/mysql-bin.log
binlog_do_db=taxcollection

Step 3. Start master server pointing to the my.ini file.
  
mysqld --defaults-file="c:/wamp/bin/mysql/mysql5.6.17/my.ini"

Step 4. a) Open up MySQL from command line

mysql -u root -p
    example: (this example specifies Port number and host as well)
    mysql -u root -p -P 3310 -h localhost
  
   b) run below in mysql to allow a slave user name identified by a password to log in to replicate, you maybe change 'slave_user' and 'password'
CREATE USER 'slave_user'@'localhost' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
   c) do below steps but leave window open active after these steps to perform step 5, This will lock tables to prevent any changes.
USE taxcollection;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
   d) you'll see something like this
mysql> SHOW MASTER STATUS;
+------------------+----------+---------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+------------------+-------------------+
| mysql-bin.000001 |     4020 | taxcollection |                  |                   |
+------------------+----------+---------------+------------------+-------------------+
1 row in set (0.03 sec)

   e) Leave this window/command prompt open to perform step 5, then we'll continue with this prompt in step 6.
 
Step 5. Open a new command prompt, and use command, dump a snapshot of taxcollection database to file.

mysqldump -u root -p --opt taxcollection > taxcollection.sql
    example: (this example specifies Port number and host as well)
    mysqldump -u root -p -h localhost -P 3310 --opt taxcollection > taxcollection.sql
  
Step 6. Go back to mysql prompt that we left opened in step 4 do these to unlock tables.
    UNLOCK TABLES;
    QUIT;
  
----------------------------------------------------------------------
Now we are all done with the configuration of the the master database.
----------------------------------------------------------------------

Step 7. Log into slave server. create the new database that you will be replicating from the master (then exit).
CREATE DATABASE taxcollection;
EXIT;

Step 8. Import database that we previously exported from the master database (file that was exported in step 5)

mysql -u root -p taxcollection < taxcollection.sql
    example: (this example specifies Port number and host as well, on my machine i use port 3308 for slave server)
    mysql -u root -p -h localhost -P 3311 taxcollection < taxcollection.sql
  
Step 9. Change these attributes in my.ini file of the slave server. (relay-log is not there by default, add it)
[mysqld]
port=3311
server-id=2
relay-log=c:/wamp/bin/mysql/mysqlslave/mysql-relay-bin.log
log_bin=c:/wamp/bin/mysql/mysqlslave/mysql-bin.log
binlog_do_db=taxcollection

Step 10. shut down slave server and restart slave server using mysqladmin and mysqld
    example:
    mysqladmin -P 3311 -h localhost -u root -p shutdown
    mysqld --defaults-file="c:/wamp/bin/mysql/mysqlslave/my.ini"

Step 11. Open up MySQL on slave server.
    example:
    mysql -u root -p -P 3311 -h localhost
  
Step 12. run this command to change Master using username and password from step 4b, and log file and log position from step 4d
CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_PORT=3310,
MASTER_USER='slave_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4020;

------------------------------------------------------------------------------
We have configured master and slave server.  Now to activate the slave server.
------------------------------------------------------------------------------

Step 13. While in MySQL on slave server.
START SLAVE;
SHOW SLAVE STATUS\G

NOTE: If run into trouble, with START SLAVE.
Use RESET SLAVE ALL; followed by a restart my mysql. Then it's possible to use CHANGE MASTER TO to re-initialise the replication.

(I ran into problems because i forgot to delete auto.cnf in slave's Data folder after making a copy of master as slave. So i deleted auto.cnf file and restart mysql
then START SLAVE).