Replication Master Options and Variablesserver-id
For each server there is a unique replication ID. Its range is from 1 to 232 – 1. Server id should not be same on servers, they must be different. It is used for both master and slave.
it controls the interval between successive column values. The range for setting the value is from 1 – 65,535. when set to 10 it increases the columns value in which auto increment is set to 10 times starting from the offset value.
Syntax: auto_increment_increment = N (where N is the range).
It determines the starting value of auto increment column value. Its range is also from 1 – 65,535.
Syntax: auto_increment_offset = N (where N is the range).
Replication Slave Options and Variables
The master.info file contains all the details of the master server.
The variables are,
It is the host_name or the IP address of the server .
The user name of the master on which nthe slave takes for replication.The username should be provided with GRANT REPLICATION SLAVE privilege.
The password provided for the master server.
The binary log file of the master which tells the slave the information of the updates in master.
The number of seconds that the slave sleeps before trying to reconnect to the master server in case if master server goes down or connection losses. The default time is 60 secs
The postion of binary log file it can be seen through 'show master status'.
The port number of the master server.
By general the slave doesn't update the changes made to it .This option will tell the slave to log the updates performed by its sql thread to its own binary log file. This is used generally foe a chain replication servers ie. Master to slave -> slave.
This option enables logging for queries that have taken more than long_query_time seconds to execute the slave.
This option tells the server to print more messages to the error log about what it is doing if it is enabled. By default it is enabled the value will be 1,for disabling set the value to 0.
the name to use for the file in which slave records the details about the master. The default name is master.info.
It shows the number of times the slave tries to connect with the master before giving up.
This option sets maximum packet size in bytes for the SQL and I/O threads.
The size at which the server rotates the relay log files automatically.
It makes the slave to only get the updates from the master server or from users having super privileges and not from its clients.
By default the server writes the file in data directory,if the basename is given it writes the data in that particular basename. Default basename is host_name-relay-bin
The name to use for the index file. The default name is host_name-relay-log.index
The name to use for the file in which the the slave records about the information of relay log. The default name is relay-log.info .
disable or enable automatic purging of relay log as soon as they are no longer needed. If it is 0 it is disabled ,1 means enabled. It is a global variable.
This options sets size for the relay log to store the updates ,if the size exceeds I/O thread will stop reading binary updates from the master until the SQL thread has caught up and deleted some unused relay logs.
When enabled it automatically recover the log files from the server, which means that replication slave discards all unprocessed relay logs and takes it from the master replication.
Tells the slave to replicate statements only to the specified database. Many databases can be added by using this option multiple times.
Tells the slave not to replicate any statement to the specified database. Many databases can be added by using this option multiple times.
Tells the slave to replicate statements only to the specified table. Many table can be added by using this option multiple times
tells the slave not to replicate any statement to the specified table. Many table can be added by using this option multiple times.
replicate-rewrite-db=from_name -> to_name
Tells the slave to translate the default database to to_name if it was from_name.
Tells the slave to replicate statements only to the specified table under the specified database. Many table can be added by using this option multiple times.
Tells the slave to not to replicate statements to the specified table under the specified database. Many table can be added by using this option multiple times.
The host name or IP address of the slave to be reported to the master during slave registration. It can be seen under SHOW MASTER STATUS.
The password of the slave to be reported to the master during slave registration.
The account username of the slave to be reported to the master during slave registration
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
Display slaves user names and passwords in the o/p of SHOW SLAVE STATUS in the master server for the slaves started with report-user and report-passwords
Tells the slave server not to start the threads when server starts. It can be started by giving start slave command in the server after starting the server.
The name of the directory where slave creates temporary files.when a SQL thread replicates a LOAD DATA LOCAL INFILE statement it extracts the file to be loaded from the relay log into the temporary directory and load these to the table.
The number of seconds to wait for more data from master to the slave before slave considers that connection is broken out and retries to connect.
Generally replication stops if an error occurs on slave. By using this we can tell the slave server to skip errors which are mentioned and continue replication.