Replication
Master Options and Variables
server-idFor 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
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:
Post a Comment