Replication

What is Replication?

Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated.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).  Electronic copying data from a database in one computer or server  to a database in another.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 Replication

In my.ini , search for this line:

#skip-networking

#bind-address = 127.0.0.1

Remove the # to add this on the list.

Setting up Replication :

Configuring Master:

·         In your my.ini file, add this:

            [mysqld]

            log-bin = richard-PC_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:

·         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';

FLUSH privileges. Type this command to complete the setup or update.

·         Then use the database you want to replicate

>use replicationdb;

>show master status;

>quit;

·         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:

·         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.

*

·         unlock the tables in replicationdb:

·         mysql -u root –p

·         Enter password:

·         UNLOCK TABLES;

·         quit;

Now the configuration on the master is finished. On to the slave...

Now we will configure the slave:

First create database replicationdb then quit mysql by “quit” command

In the my.ini or my.cfg file, put these lines

Replace the master-host setting with the actual IP address of the master server, Master-user is the user created on the master for replication as well as the master-password. The server_id can be any number, as long as it's not the same as the server_id on the master: 

·         Then quit and copy the sql

·         Then on the mysql shell copy the replicationdb data from the master to the slave

>mysql -u root replicationdb < C:replicationdb.sql

·         Then we restart MySQL

LOAD DATA FROM MASTER

>mysql -u root

>load data from master;

>quit;

You can now use phpMyadmin to see the tables master and slave replicationdb.

·         Next step is to stop the slave to finish up the Slave configuration.

mysql -u root -p
Enter password:
SLAVE STOP;

·         replace the values appropriately:

>CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER=‘replication_user', MASTER_PASSWORD='replication_pwd', MASTER_LOG_FILE=‘allen_binary.000001', MASTER_LOG_POS=106;

·         MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).

·         MASTER_USER is the user we granted replication privileges on the master.

·         MASTER_PASSWORD is the actual password gave to the user on the master.

·         MASTER_LOG_FILE is the file MySQL gave, back when you ran SHOW MASTER STATUS; on the master.

·         MASTER_LOG_POS is the position MySQL gave back when you also ran SHOW MASTER STATUS; on the master.

·         Then start the slave.

>START SLAVE;

And now, be aware that if your master server has already been using binary logging, you'll need to reset the binary log with RESET MASTER so that the slave can start updating from the beginning of the first binary log. Now start the slave server and connect. Once you've connected, check the slave status to see if replication has begun properly:

Master Configuration File Options

·         log-bin=filenameActivates binary logging

log-bin-index=filename

Specifies the name of the binary log index file

Master Configuration File Options

·         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. All other databases are   ignored.

·         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.

-master-user=username

-Specifies the username the slave will connect to the master with.

-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

-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

-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. You can use this option multiple times to replicate multiple tables.

·         replicate-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). You can specify multiple instances of this option.

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

-Tells the slave to replicate statements only where they match the specified table (similar to the replicate_do_table option), but where the matchtakes into account wildcards. For example, where the table name isdb%.tb%, the match will apply to any database beginning with the letters db, and any table beginning with the letters tb.

·         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. For example, where the table name is db%.tb% replication will not be performed where the database begins with the letters db, and the table begins with the letters tb). You can specify multiple instances of this option.

·         replicate-ignore-db= database_name

-Tells the slave not to replicate any statement when the current database is database_name. You can use this option multiple times to specify multiple databases to ignore.

·         replicate-do-db= database_name

-Tells the slave thread to replicate a statement only when the database is database_name. You can use this option multiple times to replicate multiple databases.

·         log-slave-updates

Tells the slave to log replicated updates to the binary log. Not set by default. If you plan to use the slave as a master to another slave, you'll need to set this option.

·         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]

When replication encounters an error, it will stop (since an error usually means the data is inconsistent, and manual steps are needed). This option tells MySQL to continue replicating if the error is one of the listed errors. Error codes are supplied as a number (the same number given in the error log) and separated by a comma. You can also use the all option to cater for any possible errors. You should not normally use this option, as mistaken use of it can lead to your data getting out of sync with the master, with no realistic way of getting it back in sync besides recopying the master data.

·         skip-slave-start

With this option set, replication will not begin when the server starts. You can manually begin it with the SLAVE START command.

·         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.

Slave and Master Commands on mysql

SLAVE START and SLAVE STOP

SHOW SLAVE STATUS

-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).

CHANGE MASTER TO

-This statement is an important one for keeping the replication in sync or starting it off at the right place. The MASTER_LOG_FILE refers to the binary log on the master from which the slave must start replicating, and the MASTER_LOG_POS is the position in that file. (You'll see examples of this later in the chapter.) This statement is also used when the master fails, and you need to change the master to which the slave connects. The full set of CHANGE MASTER TO options is:

CHANGE MASTER TO MASTER_HOST = 'master_hostname', MASTER_USER='replication_username', MASTER_PASSWORD=''replication_user_password', MASTER_PORT='master_port', MASTER_LOG_FILE='master_binary_logfile', MASTER_LOG_POS='master_binary_log_position'

RESET SLAVE

The 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. Currently, this is not useful for large datasets or for situations where the master cannot be unavailable for long, as it acquires a global read lock when copying the data. It also updates the value of MASTER_LOG_FILE and MASTER_LOG_POS. Currently it only works with MyISAM tables. This statement is likely to become the standard way of preparing the slave in future, so be sure to read your latest documentation.

Master Replication Commands

The following slides are the master replication commands:

SET SQL_LOG_BIN

RESET MASTER

SHOW MASTER STATUS

PURGE MASTER LOGS TO

SHOW MASTER LOGS

SHOW SLAVE HOSTS

SHOW BINLOG EVENTS