Thursday 26 December 2013

Replication Master Slave - Options & Variables


Replication Master Options and Variables
server-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.



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


auto_increment_offset
     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
master.info
      The master.info file contains all the details of the master server. 
      master-host
      master-user
      master-password
      master-log-file
      master-connect-retry
      master-log-pos
      master-port
      etc


The variables are,

master-host='host_name'
      It is the host_name or the IP address of the server .


master-user='user_name
      The user name of the master on which nthe slave takes for replication.The username should be provided with GRANT REPLICATION SLAVE privilege.


master-password='passwd'
      The password provided for the master server.


master-log-file='file_name'
      The binary log file of the master which tells the slave the information of the updates in master.


master-connect-retry=seconds
      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


master-log-pos=position
      The postion of binary log file it can be seen through  'show master status'.


master-port=port_no
     The port number of the master server.


log-slave-updates
      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.


log-slow-slave-statements
      This option enables logging for queries that have taken more than long_query_time seconds to execute the slave.


log-warnings=level1
      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.


master-info-file=filename
     the name to use for the file in which slave records the details about the master. The default name is master.info.


master-retry-count=count
      It shows the number of times the slave tries to connect with the master before giving up.


slave-max-allowed-packet=bytes
      This option sets maximum packet size in bytes for the SQL and I/O threads.


max-relay-log-size=size
      The size at which the server rotates the relay log files automatically.


read-only
      It makes the slave to only get the updates from the master server or from users having super privileges and not from its clients.


relay-log=file_name
      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


relay-log-index=file_name
      The name to use for the index file. The default name is host_name-relay-log.index


relay-log-info-file=file_name
      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 .


relay-log-purge=0|1
      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.


relay-log-space-limit=size
      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.


relay-log-recovery=0|1
      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.


replicate_do_db=db_name
      Tells the slave to replicate statements only to the specified database. Many databases can be added by using this option multiple times.


replicate_ignore_db=db_name
      Tells the slave not to replicate any statement to the specified database. Many databases can be added by using this option multiple times.


replicate_do_table=db_name.table_name
      Tells the slave to replicate statements only to the specified table. Many table can be added by using this option multiple times


replicate_ignore_table=db_name.table_name
      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.

replicate-wild-do-table=db_name.table_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.


replicate-wild-ignore-table=db_name.table_name
      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.


report-host=host_name
      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.


  report-password=password
      The password of the slave to be reported to the master during slave registration.
 
report-user=username
      The account  username of the slave to be reported to the master during slave registration


report-port=slave_port_no
      The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.


show-slave-auth-info
      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


skip-slave-start
      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.


slave-load-tmpdir=file_name
      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.


slave-net-timeout=seconds
      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.

slave-skip-errors=err_code1,err_code2,....,all
      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.

No comments: