Database Replication

     Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated. It allows you to make an exact copy of a database to another server, and set one of them (the slave) to take all its updates from the other (the master). It is use to create a read-only database of the master database. It can also be use as a backup of the master database.

SETTING UP REPLICATION
     To set up a replication you will need a database for the master and a database for the slave. You will need to change the configuration file(my.ini / my.cnf) of both servers. 

     In the master you will need to enable the binary logging because the slave reads the binary log of the master in order to replicate the database of the master. For the configuration file of the master you will need to put these:

  log-bin = binary_log_name

  server-id=1

     The server-id is used to act as an ip address for the databases. The commands to use in the mysql of the master are the following:

  CREATE USER bogs IDENTIFIED BY '12345';

  GRANT REPLICATION SLAVE ON *.* TO bogs IDENTIFIED BY '12345';

  GRANT RELOAD ON *.* TO bogs IDENTIFIED BY '12345';

  GRANT SUPER ON *.* TO bogs IDENTIFIED BY '12345';

  FLUSH PRIVILEGES;

  SHOW MASTER STATUS;

  QUIT;

     The bogs is the user created and the 12345 is the password that is also created together with the user.  The FLUSH PRIVELEGES statement is called to refresh the changes made in the privileges granted to the user bogs. The SHOW MASTER STATUS is used to know the current position and what log file is currently being used by the master. The information output from SHOW MASTER STATUS is to be used for the CHANGE statement for the slave.

     For the slave configuration file you will need to put these:
  server-id=2
  master-host=192.168.1.181
  master-user=bogs
  master-password=12345
  master-connect-retry=60

     The server id must be different from the server id of the master. The master host is the ip address of the master server. The master user is the user created by the master for the slaves. The master password is the password created by the master for the slaves. The master connect retry is the number of seconds the slave will wait before it cancels its request for connecting to the master.
     The commands to use in the mysql of the master are the following:
  LOAD DATA FROM MASTER;
  SLAVE STOP;
  CHANGE MASTER TO MASTER_HOST='192.168.1.182',
  MASTER_USER='bogs',MASTER_PASSWORD='12345',
  MASTER_LOG_FILE='binary_log_name.000001', 
  MASTER_LOG_POS=660;
  SLAVE START;

MASTER AND SLAVE CONFIGURATION
     There are different configuration files available for the master and slave. These are the configuration for the master configuration file(my.ini/my.cnf for the master):

      Here are the configuration files for the slave: