CHANGE MASTER TO
Sintassi
CHANGE MASTER TO def_master [, def_master] ... master_def: MASTER_BIND = 'nome_interfaccia' | MASTER_HOST = 'nome_host' | MASTER_USER = 'nome_utente' | MASTER_PASSWORD = 'password' | MASTER_PORT = num_porta | MASTER_CONNECT_RETRY = intervallo | MASTER_HEARTBEAT_PERIOD = intervallo | MASTER_LOG_FILE = 'nome_log_master' | MASTER_LOG_POS = pos_log_master | RELAY_LOG_FILE = 'nome_relay_log' | RELAY_LOG_POS = pos_relay_log | MASTER_SSL = {0|1} | MASTER_SSL_CA = 'nome_file_ca' | MASTER_SSL_CAPATH = 'nome_dir_ca' | MASTER_SSL_CERT = 'nome_file_cert' | MASTER_SSL_KEY = 'nome_file_chiave' | MASTER_SSL_CIPHER = 'lista_cifrature' | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
Spiegazione
CHANGE MASTER
TO modifica i parametri utilizzati dallo slave per connettersi e comunicare con il master. Inoltre aggiorna i contenuti dei file master.info e relay-log.info.
MASTER_USER
, MASTER_PASSWORD
,
MASTER_SSL
, MASTER_SSL_CA
,
MASTER_SSL_CAPATH
, MASTER_SSL_CERT
,
MASTER_SSL_KEY
, MASTER_SSL_CIPHER
e
MASTER_SSL_VERIFY_SERVER_CERT
forniscono allo slave informazioni su come connettersi al master.
MASTER_SSL_VERIFY_SERVER_CERT
è stato aggiunto in MySQL 5.1.18. Si usa come spiegato per l'opzione --ssl-verify-server-cert
alla pagina: http://dev.mysql.com/doc/refman/5.1/en/ssl-options.html
MASTER_CONNECT_RETRY
specifica quanti secondi deve attendere prima di riprovare a connettersi. Il default è 60. Il numero massimo di tentativi di riconnessione è determinato dall'opzione server --master-retry-count
; per ulteriori informazioni, si veda http://dev.mysql.com/doc/refman/5.1/en/replication-options.html.
Le opzioni SSL (MASTER_SSL
,
MASTER_SSL_CA
, MASTER_SSL_CAPATH
,
MASTER_SSL_CERT
, MASTER_SSL_KEY
,
MASTER_SSL_CIPHER
) e
MASTER_SSL_VERIFY_SERVER_CERT
possono essere impostate anche sugli slave compilati senza il supporto a SSL. Esse vengono salvate nel file master.info file, ma ignorate se non si utilizza un server con il supporto SSL abilitato.
Se non si specifica un certo parametro, esso mantiene il vecchio valore, eccetto dove indicato nella seguente spiegazione. Per esempio, se la password per connettersi al master viene modificata, per comunicare la nuova password allo slave è sufficiente usare le seguenti istruzioni:
STOP SLAVE; -- se la replica è in funzione CHANGE MASTER TO MASTER_PASSWORD='new3cret'; START SLAVE; -- se si vuole riavviare la replica
Non c'è bisogno di specificare i parametri che non si desidera modificare (host, porta, utente e così via).
MASTER_HOST
e MASTER_PORT
sono il nome (o l'indirizzo IP) dell'host del master e la sua porta TCP/IP.
The next two options are available only in MySQL Cluster NDB 6.3 and 6.4; they are not supported in mainline MySQL 5.1:
MASTER_BIND
is for use on replication slaves having multiple network interfaces, and determines which of the slave's network interfaces is chosen for connecting to the master. It is also possible to determine which network interface is to be used in such cases by starting the slave mysqld process with the--master-bind option
.
The ability to bind a replication slave to specific network interface was added in MySQL Cluster NDB 6.3.4.MASTER_HEARTBEAT_PERIOD
is used to set the interval in seconds between replication heartbeats. Whenever the master's binlog is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution to hundredths of a second; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binlog file for a period longer than interval.
Setting interval to 0 disables heartbeats altogether. The default value for interval is equal to the value of slave_net_timeout divided by 2.
Note: Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued.
IssuingRESET SLAVE
resets the heartbeat interval to the default.MASTER_HEARTBEAT_PERIOD
was added in MySQL Cluster NDB 6.3.4.
Note: Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.
If you specify MASTER_HOST
or
MASTER_PORT
, the slave assumes that the master server is
different from before (even if you specify a host or port value that is the
same as the current value.) In this case, the old values for the master binary
log name and position are considered no longer applicable, so if you do not
specify MASTER_LOG_FILE
and
MASTER_LOG_POS
in the statement,
MASTER_LOG_FILE=''
and MASTER_LOG_POS=4
are silently appended to it.
Setting MASTER_HOST=''
— that is,
setting its value explicitly to an empty string — is
not the same as not setting it at all. Setting this option to an empty string
causes START SLAVE
subsequently to fail. This issue is
addressed in MySQL 6.0. (MySQL Bug #28796)
MASTER_LOG_FILE
and MASTER_LOG_POS
are
the coordinates at which the slave I/O thread should begin reading from the
master the next time the thread starts. If you specify either of them, you
cannot specify RELAY_LOG_FILE
or
RELAY_LOG_POS
. If neither of
MASTER_LOG_FILE
or MASTER_LOG_POS
are
specified, the slave uses the last coordinates of the slave SQL thread before
CHANGE MASTER TO
was issued. This ensures that there is no
discontinuity in replication, even if the slave SQL thread was late compared to
the slave I/O thread, when you merely want to change, say, the password to use.
CHANGE MASTER TO
deletes all relay log files and starts a
new one, unless you specify RELAY_LOG_FILE
or
RELAY_LOG_POS
. In that case, relay logs are kept; the
relay_log_purge global variable is set silently to 0.
CHANGE MASTER TO
is useful for setting up a slave when you
have the snapshot of the master and have recorded the log and the offset
corresponding to it. After loading the snapshot into the slave, you can run
CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master'
,
MASTER_LOG_POS=log_offset_on_master
on the slave.
The following example changes the master and master's binary log coordinates. This is used when you want to set up the slave to replicate the master:
CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
The next example shows an operation that is less frequently employed. It is
used when the slave has relay logs that you want it to execute again for some
reason. To do this, the master need not be reachable. You need only use
CHANGE MASTER TO
and start the SQL thread
(START SLAVE SQL_THREAD
):
CHANGE MASTER TO RELAY_LOG_FILE='slave-relay-bin.006', RELAY_LOG_POS=4025;