Replication

Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated.

Replication allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master).

Replication is the frequent electronic copying data from a database in one computer or server to a database in another so that all users share the same level of information.

Replication allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master).

Setting Up/Configuring Master:

In your my.ini file put these lines:

            [mysqld]

            log-bin = mcvin_binary

            binlog-do-db=exampledb

            server-id=1 

log-bin indicates that the master will use binary update logging, binlog-do-db indicates which database we will use and server-id is a unique number to differentiate each of the master and slave machines. By convention, the master is usually set to 1, and the slaves from 2 upward:

•  Now in the mysql shell, we will grant a permission to a slave to replicate. The slave user will be replication_user, with a password of replication_pwd:
• GRANT REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';
• GRANT RELOAD ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';
• GRANT SUPER ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';
• 
  
• You can also type this 

mysql>FLUSH privileges

Once you have given the desired privileges for your user, you will need to FLUSH privileges in order to complete the setup and to make the new settings work.

Then use the database you want to replicate

mysql>use replicationdb;

mysql>show master status;

• This command will show something like this:

o   show master status;

• This information will be needed later on the slave.
• Then leave the MySQL shell:

mysql>quit;

Setting Up/Configuring Slave:

·         master-host = 192.168.4.100

·         master-user = replication_user

·         master_password = replication_pwd

·         server-id = 3

·         master-connect-retry=60

·         replicate-do-db = replication_db

There are two possibilities to get existing tables and data from replicationdb from master to slave

·         make a database dump

mysqldump -u root -proot  -- opt replicationdb > C:replicationdb.sql

Transfer this file to your slave server

·         load data from master

If you want to go the LOAD DATA FROM MASTER; 

way then there is nothing you must do right now.

Slave Replication Commands

¨  start and stop the replication process, respectively.

returns information about the slave, including the important fact whether the slave is connected to the master (Slave_IO_Running), replication is running (Slave_SQL_Running), what binary log is being used (Master_Log_File and Relay_Master_Log_ File), and what position is current in the binary log (Read_Master_Log_Pos and Exec_ master_log_pos).