Replication

Replication means copying the files into other files. Having a backup is better because when your database has crash, you can use your backup to have the files again in your database. You need to have a server, a master and a slave. The master is the main source of the database. The slave needs to connect to the master so that you can have the access in the shared database. Usually the server id of the master is 1, while the server id of the slave is 2 upwards. Any number of slave can connects to the master that writes to the binary log and any update read this binary log. It is useful form of backup. The relationship between the binary log on the master and the master.info file on the slave is the key to keeping replication in synchronize. Replication will fail, if the master binary logs are removed before the slaves have used them.

Setting up the Replication

Step 1: configuring the master

We need to create user.

                CREATE USER hannah IDENTIFIED BY '12345';

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

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

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

                FLUSH PRIVILEGES;

                SHOW MASTER STATUS;

                QUIT;

Now we need to edit the my.ini file. Write this code:

                log-bin =hannah-bin.log  -  it is where to start writing a log
                binlog-do-db=my_database  -  this is the syntax of the creating e replication of a specific database
                server-id=1 – this is the id of a master, always 1                

Then start again the mysql.

Step 2: configuring the slave

Edit the my.ini file and put this code:

                server-id=2
                master-host=128.0.0.1
                master-connect-retry=60
                master-user=slave_user
                master-password=slave_password
                replicate-do-db=my_database

And then run this on mysql:

                load data from master; - to load the database from the master

                slave stop;

                change master to master_host='192.168.1.182',

                 master_user=‘hannah',master_password='12345',            

                master_log_file=‘hannah-bin.000001',

                master_log_pos=660;

                slave start;

Master Configuration File Options

·         log-bin=filename

®     Activates binary logging. This option must be present on the master for replication to occur. The filename is optional.

·         log-bin-index=filename

®     Specifies the name of the binary log index file.

·         sql-bin-update-same

®     if set, setting SQL_LOG_BIN to either 1 or 0 will automatically set SQL_ LOG_UPDATE to the same value, and vice versa. SQL_LOG_UPDATE should not be needed any more, so this option is unlikely to be used.

·         binlog-do-db=database_name

®     Only logs updates to the binary log from the database_name database. Allother databases are ignored. You can restrict databases from the slave as well.

·         binlog-ignore-db= database_name

®     Logs all updates to the binary log except from the database_name database. You can also set the database to ignore on the slave.

Slave Configuration File Options

·         master-host=host

®     Specifies the hostname or IP address of the master to which to connect. Has to be set for replication to begin. Once replication has begun, the master.info data will determine this, and you'll need to use a CHANGE MASTER TO statement to change it.

·         master-user=username

®     Specifies the username the slave will connect to the master with. The user should have REPLICATION SLAVE permission on the master.

·         master-password=password

®     Specifies the password with which the slave will connect to the master.

·         master-port=portnumber

®     Specifies the port the master listens on (defaults to the value of MYSQL_ PORT, usually 3306).

·         master-connect-retry= seconds

®     If the connection between the master and slave goes down, MySQL will wait this many seconds before trying to reconnect (default 60).

·         master-ssl

®     Specifies that replication take place using Secure Sockets Layer (SSL).

·         master-ssl-key=key_name

®     If SSL is set to be used (the master-ssl option), this specifies the master SSL key filename.

·         master-ssl-cert= certificate_name

®     If SSL is set to be used (the master-ssl option), this specifies the master SSL certificate name.

·         master-info-file=filename

®     Specifies the master information file (default master.info in the data directory), which keeps track of the point in the binary logs that the slave is at in the replication process.

·         report-host

®     Specifies the hostname or IP address that the slave will announce itself as to the master (for use during a SHOW SLAVE HOSTS statement).

·         report-port

®     Specifies the port for connecting to the slave reported to the master.

·         replicate-do-table= db_name.table_name

®     Ensures that the slave only replicates the specified table name, from the specified database.

·         replicate-ignore-table= db_name.table_name

®     Tells the slave not to replicate a statement that updates the specified table.

·         replicate-wild-do-table= db_name.table_name

®     Tells the slave to replicate statements only where they match the specified table.

·         replicate-wild-ignore-table=db_ name.table_name

®     Tells the slave not to replicate a statement that updates the specified table, even if other tables are also updated by the same statement, similar to the replicate-ignore-table option, except that wildcards are taken into account.

·         replicate-ignore-db= database_name

®     Tells the slave not to replicate any statement when the current database is database_name.

·         replicate-do-db= database_name

®     Tells the slave thread to replicate a statement only when the database is database_name.

·         log-slave-updates

®     Tells the slave to log replicated updates to the binary log.

·         replicate-rewrite-db= master_database-> slave_database

®     If the database on your slave has a different name to that on the master, you'll need to map the relationship with this option.

·         slave-skip-errors= [err_code1,err_ code2,... | all]

®     This option tells MySQL to continue replicating if the error is one of the listed errors.

·         skip-slave-start

®     With this option set, replication will not begin when the server starts.

·         slave_compressed_protocol=#

®     If set to 1, then MySQL uses compression to transfer the data between slave and master if both servers support this.

·         slave_net_timeout=#

®     Determines the number of seconds to wait for more data from the master before a read is aborted.

Replication Commands

·         Slave Commands

                                LOAD DATA FROM MASTER;

                                SLAVE STOP;

                                CHANGE MASTER TO MASTER_HOST='192.168.1.182',  

                                MASTER_USER=‘hannah',MASTER_PASSWORD='12345',

                                MASTER_LOG_FILE=‘hannah_binary.000001',

                                MASTER_LOG_POS=660;

                                SLAVE START;

·         Master Command

             CREATE USER hannah IDENTIFIED BY '12345';

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

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

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

                FLUSH PRIVILEGES;

                SHOW MASTER STATUS;

                QUIT;

• SLAVE START and SLAVE STOP start and stop the replication process.
• SHOW SLAVE STATUS returns information about the slave
• The RESET SLAVE statement causes the slave to forget its position in the master logs.
• LOAD DATA FROM MASTER takes a copy of the data on the master and brings it onto the slave.

Master replication commands:

• The SET SQL_LOG_BIN = n statement either deactivates binary update logging (if set to 0) or reactivates it (if set to 1). You need the SUPER privilege to run this statement.
• RESET MASTER removes all binary logs and starts numbering again at 001.
• SHOW MASTER STATUS shows the current binary log, the position in the binary log, and whether any databases are being ignored for binary logging.
• PURGE MASTER LOGS TO binary_log_filename removes all logs prior to the specified binary log. Be sure that no slaves still need the binary logs you're about to remove. See the example later in the "Removing Old Binary Logs from the Master and Starting" section.
• SHOW MASTER LOGS shows the list of binary log files available. You'd usually use this before purging the logs.
• SHOW SLAVE HOSTS returns a list of slaves registered with the master (note that by default a slave does not register itself but requires the report-host configuration option to be set).
• The SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ] statement reads statements from the binary logs.