Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
RESET MASTER [TO #]Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file with a suffix of .000001.
If TO # is given, then the first new binary log file will start from number #.
This statement is for use only when the master is started for the first time and should never be used if any slaves are actively replicating from the binary log.
The statement is intended for use in active replication.
This page is licensed: GPLv2, originally from
Control replication topologies. Learn statements like CHANGE MASTER TO and START SLAVE to configure primaries and replicas.
CHANGE MASTER is used on a replica to set up or change settings for connecting to the primary.
The FOR CHANNEL keyword was added for MySQL compatibility. This is identical to using the channel_name directly after CHANGE MASTER.
FOR CHANNEL is not available.
If you are using , then you need to specify a connection name when you execute CHANGE MASTER. There are two ways to do this:
Setting the system variable prior to executing CHANGE MASTER.
Setting the connection_name parameter when executing CHANGE MASTER.
The MASTER_USER option for CHANGE MASTER defines the user account that the will use to connect to the .
This user account will need the privilege on the primary.
This user account will need the privilege on the primary.
For example:
The maximum length of the MASTER_USER string is 128 characters.
The maximum length of the MASTER_USER string is 96 characters.
The MASTER_PASSWORD option for CHANGE MASTER defines the password that the will use to connect to the as the user account defined by the option.
For example:
The maximum length of the MASTER_PASSWORD string is 32 characters. The effective maximum length of the string depends on how many bytes are used per character and can be up to 96 characters.
Due to , the password can be silently truncated to 41 characters when MariaDB is restarted. For this reason, it is recommended to use a password that is shorter than this.
The MASTER_HOST option for CHANGE MASTER defines the hostname or IP address of the .
If you set the value of the MASTER_HOST option to the empty string, then that is not the same as not setting the option's value at all. If you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command will fail with an error.
If you set the value of the MASTER_HOST option to the empty string, then that is not the same as not setting the option's value at all. If you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command will fail with an error. In MariaDB 5.3 and before, if you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command would succeed, but the subsequent command would fail.
For example:
The maximum length of the MASTER_HOST string is 255 characters.
The maximum length of the MASTER_HOST string is 60 characters.
The MASTER_PORT option for CHANGE MASTER defines the TCP/IP port of the .
For example:
The MASTER_CONNECT_RETRY option for CHANGE MASTER defines how many seconds that the replica will wait between connection retries. The default is 60.
The MASTER_RETRY_COUNT option limits the number of connection attempts (i.e., Connects_Tried in ). For example:
Setting this option resets the Connects_Tried statistic in to 0.
The default is the option, which be set either on the command-line or in a server in an prior to starting up the server. For example:
The MASTER_RETRY_COUNT option for CHANGE MASTER is only supported by MariaDB 12.0.1 and later and by MySQL. Please use the option instead, which be set either on the command-line or in a server in an prior to starting up the server. For example:
The MASTER_BIND option for CHANGE MASTER is only supported by MySQL 5.6.2 and later and by MySQL NDB Cluster 7.3.1 and later. This option is not supported by MariaDB. See for more information.
The MASTER_BIND option for CHANGE MASTER can be used on replicas that have multiple network interfaces to choose which network interface the replica will use to connect to the primary.
The MASTER_HEARTBEAT_PERIOD option for CHANGE MASTER can be used to set the interval in seconds between replication heartbeats. Whenever the primary's is updated with an event, the waiting period for the next heartbeat is reset.
This option's interval argument has the following characteristics:
It is a decimal value with a range of 0 to 4294967 seconds.
It has a resolution of hundredths of a second.
Its smallest valid non-zero value is 0.001.
Heartbeats are sent by the primary only if there are no unsent events in the binary log file for a period longer than the interval.
If the statement is executed, then the heartbeat interval is reset to the default.
The TLS options are used for providing information about . The options can be set even on replicas that are compiled without TLS support. The TLS options are saved to either the default master.info file or the file that is configured by the option, but these TLS options are ignored unless the replica supports TLS.
See for more information.
The MASTER_SSL option for CHANGE MASTER tells the replica whether to force for the connection. The valid values are 0 or 1. Required to be set to 1 for the other MASTER_SSL* options to have any effect.
For example:
The MASTER_SSL_CA option for CHANGE MASTER defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for . This option requires that you use the absolute path, not a relative path.
For example:
See for more information.
The maximum length of MASTER_SSL_CA string is 511 characters.
The MASTER_SSL_CAPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for . This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the command.
For example:
See for more information.
The maximum length of MASTER_SSL_CA_PATH string is 511 characters.
The MASTER_SSL_CERT option for CHANGE MASTER defines a path to the X509 certificate file to use for . This option requires that you use the absolute path, not a relative path.
For example:
The maximum length of MASTER_SSL_CERT string is 511 characters.
The MASTER_SSL_CRL option for CHANGE MASTER defines a path to a PEM file that should contain one or more revoked X509 certificates to use for . This option requires that you use the absolute path, not a relative path.
This option is only supported if the server was built with OpenSSL. If the server was built with yaSSL, then this option is not supported. See for more information about which libraries are used on which platforms.
For example:
See for more information.
The maximum length of MASTER_SSL_CRL string is 511 characters.
The MASTER_SSL_CRLPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for . This option requires that you use the absolute path, not a relative path. The directory specified by this variable needs to be run through the command.
This option is only supported if the server was built with OpenSSL. If the server was built with yaSSL, then this option is not supported. See for more information about which libraries are used on which platforms.
For example:
See for more information.
The maximum length of MASTER_SSL_CRL_PATH string is 511 characters.
The MASTER_SSL_KEY option for CHANGE MASTER defines a path to a private key file to use for . This option requires that you use the absolute path, not a relative path.
For example:
The maximum length of MASTER_SSL_KEY string is 511 characters.
The MASTER_SSL_CIPHER option for CHANGE MASTER defines the list of permitted ciphers or cipher suites to use for . Besides cipher names, if MariaDB was compiled with OpenSSL, this option could be set to "SSLv3" or "TLSv1.2" to allow all SSLv3 or all TLSv1.2 ciphers. Note that the TLSv1.3 ciphers cannot be excluded when using OpenSSL, even by using this option. See for details.
For example:
The maximum length of MASTER_SSL_CIPHER string is 511 characters.
The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables . This option is enabled by default.
The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables . This option is disabled by default.
For example:
See for more information.
These options are related to the position on the primary.
The MASTER_LOG_FILE option for CHANGE MASTER can be used along with MASTER_LOG_POS to specify the coordinates at which the should begin reading from the primary's the next time the thread starts.
For example:
The MASTER_LOG_POS option for CHANGE MASTER can be used along with MASTER_LOG_FILE to specify the coordinates at which the should begin reading from the primary's the next time the thread starts.
For example:
These options are related to the position on the replica.
The RELAY_LOG_FILE option for CHANGE MASTER can be used along with the option to specify the coordinates at which the should begin reading from the the next time the thread starts.
The CHANGE MASTER statement usually deletes all files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing files are kept.
When you want to change the position, you only need to stop the . The can continue running. The and statements support the SQL_THREAD option for this scenario. For example:
When the value of this option is changed, the metadata about the position in the will also be changed in the relay-log.info file or the file that is configured by the system variable.
The RELAY_LOG_POS option for CHANGE MASTER can be used along with the option to specify the coordinates at which the should begin reading from the the next time the thread starts.
The CHANGE MASTER statement usually deletes all files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing files are kept.
When you want to change the position, you only need to stop the . The can continue running. The and statements support the SQL_THREAD option for this scenario. For example:
When the value of this option is changed, the metadata about the position in the will also be changed in the relay-log.info file or the file that is configured by the system variable.
The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the when connecting to a primary. The possible values are:
current_pos - Replicate in mode and use as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, option instead.
replica_pos - Replicate in
The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the when connecting to a primary. The possible values are:
current_pos - Replicate in mode and use as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, option instead.
slave_pos - Replicate in mode and use as the position to start downloading transactions from the primary. From , replica_pos is an alias for slave_pos.
For example:
Or:
Used to transition a primary to become a replica. Replaces the old with a safe alternative by forcing users to set Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos once at CHANGE MASTER TO time. If gtid_slave_pos is morerecent than gtid_binlog_pos (as in the case of chain replication), the replication state should be preserved.
For example:
MASTER_DEMOTE_TO_SLAVE is not available.
Also see .
The IGNORE_SERVER_IDS option for CHANGE MASTER can be used to configure a to ignore events that originated from certain servers. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.
The option's value can be specified by providing a comma-separated list of values. For example:
If you would like to clear a previously set list, then you can set the value to an empty list. For example:
The DO_DOMAIN_IDS option for CHANGE MASTER can be used to configure a to only apply events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.
The option's value can be specified by providing a comma-separated list of values. Duplicate values are automatically ignored. For example:
If you would like to clear a previously set list, then you can set the value to an empty list. For example:
The IGNORE_DOMAIN_IDS option for CHANGE MASTER can be used to configure a to ignore events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.
The option's value can be specified by providing a comma-separated list of values. Duplicate values are automatically ignored. For example:
If you would like to clear a previously set list, then you can set the value to an empty list. For example:
The MASTER_DELAY option for CHANGE MASTER can be used to enable . This option specifies the time in seconds (at least) that a replica should lag behind the primary up to a maximum value of 2147483647, or about 68 years. Before executing an event, the replica will first wait, if necessary, until the given time has passed since the event was created on the primary. The result is that the replica will reflect the state of the primary some time back in the past. The default is zero, no delay.
If you don't specify a given option when executing the CHANGE MASTER statement, then the option keeps its old value in most cases. Most of the time, there is no need to specify the options that do not need to change. For example, if the password for the user account that the replica uses to connect to its primary has changed, but no other options need to change, then you can just change the option by executing the following commands:
There are some cases where options are implicitly reset, such as when the and options are changed.
The values of the and options (i.e. the position on the primary) and most other options are written to either the default master.info file or the file that is configured by the option. The keeps this position updated as it downloads events only when optionis set to NO. Otherwise the file is not updated on a per event basis.
The option can be set either on the command-line or in a server in an prior to starting up the server. For example:
The values of the and options (i.e. the position) are written to either the default relay-log.info file or the file that is configured by the system variable. The keeps this position updated as it applies events.
The system variable can be set either on the command-line or in a server in an prior to starting up the server. For example:
If the replica is replicating events that contain , then the will write every GTID that it applies to the table. This GTID can be inspected and modified through the system variable.
If the replica has the system variable enabled and if the replica has the enabled, then every write by the will also go into the replica's . This means that of replicated transactions would be reflected in the value of the system variable.
The CHANGE MASTER statement is useful for setting up a replica when you have a backup of the primary and you also have the position or position corresponding to the backup.
After restoring the backup on the replica, you could execute something like this to use the position:
Or you could execute something like this to use the position:
See for more information on how to do this with .
The following example changes the primary and primary's binary log coordinates. This is used when you want to set up the replica to replicate the primary:
. Removes a connection created with CHANGE MASTER TO.
This page is licensed: GPLv2, originally from
CHANGE MASTER ['connection_name'] TO master_def [, master_def] ...
[FOR CHANNEL 'channel_name']
master_def:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_DELAY = interval
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_CRL = 'crl_file_name'
| MASTER_SSL_CRLPATH = 'crl_directory_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| MASTER_USE_GTID = {current_pos|slave_pos|no}
| MASTER_DEMOTE_TO_SLAVE = bool
| IGNORE_SERVER_IDS = (server_id_list)
| DO_DOMAIN_IDS = ([N,..])
| IGNORE_DOMAIN_IDS = ([N,..])
| MASTER_RETRY_COUNT = longCategory for old replication statements
If it's set to 0, then heartbeats are disabled.
no - Don't replicate in GTID mode.
The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:
current_pos - Replicate in GTID mode and use gtid_current_pos as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, MASTER_DEMOTE_TO_SLAVE= option instead.
slave_pos - Replicate in GTID mode and use as the position to start downloading transactions from the primary.
no - Don't replicate in mode.
no - Don't replicate in GTID mode.
Old name for
This page is licensed: CC BY-SA / Gnu FDL
Old name for
This page is licensed: CC BY-SA / Gnu FDL
Old name for
This page is licensed: CC BY-SA / Gnu FDL
This page is licensed: CC BY-SA / Gnu FDL
Old name for
This page is licensed: CC BY-SA / Gnu FDL
SET default_master_connection = 'gandalf';
STOP SLAVE;
CHANGE MASTER TO
MASTER_PASSWORD='new3cret';
START SLAVE;STOP SLAVE 'gandalf';
CHANGE MASTER 'gandalf' TO
MASTER_PASSWORD='new3cret';
START SLAVE 'gandalf';STOP SLAVE;
CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='new3cret';
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_PASSWORD='new3cret';
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='dbserver1.example.com',
MASTER_USER='repl',
MASTER_PASSWORD='new3cret',
MASTER_USE_GTID=slave_pos;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='dbserver1.example.com',
MASTER_PORT=3307,
MASTER_USER='repl',
MASTER_PASSWORD='new3cret',
MASTER_USE_GTID=slave_pos;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_CONNECT_RETRY=20;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_RETRY_COUNT=1; # attempt only once; do not retry if it fails
START SLAVE;[mariadb]
...
master_retry_count=4294967295STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL=1;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CAPATH='/etc/my.cnf.d/certificates/ca/',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1,
MASTER_SSL_CRL='/etc/my.cnf.d/certificates/crl.pem';
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1,
MASTER_SSL_CRLPATH='/etc/my.cnf.d/certificates/crl/';
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1,
MASTER_SSL_CIPHER='TLSv1.2';
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4;
START SLAVE;STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
RELAY_LOG_FILE='slave-relay-bin.006',
RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
RELAY_LOG_FILE='slave-relay-bin.006',
RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;STOP SLAVE;
CHANGE MASTER TO
MASTER_USE_GTID = current_pos;
START SLAVE;STOP SLAVE;
SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
MASTER_USE_GTID = slave_pos;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_DEMOTE_TO_SLAVE = 1;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
IGNORE_SERVER_IDS = (3,5);
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
IGNORE_SERVER_IDS = ();
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
DO_DOMAIN_IDS = (1,2);
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
DO_DOMAIN_IDS = ();
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
IGNORE_DOMAIN_IDS = (),
DO_DOMAIN_IDS = (1,2);
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
IGNORE_DOMAIN_IDS = ();
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
DO_DOMAIN_IDS = (),
IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_DELAY=3600;
START SLAVE;STOP SLAVE;
CHANGE MASTER TO
MASTER_PASSWORD='new3cret';
START SLAVE;[mariadb]
...
master_info_file=/mariadb/myserver1-master.info[mariadb]
...
relay_log_info_file=/mariadb/myserver1-relay-log.infoCHANGE MASTER TO
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4;
START SLAVE;SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
MASTER_USE_GTID=slave_pos;
START SLAVE;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;
START SLAVE;Old name for SHOW REPLICA STATUS
This page is licensed: CC BY-SA / Gnu FDL
If multi-source replication is used, this statement applies to the default connection. It could be necessary to change the value of the default_master_connection system variable.
Note that, if the event is a transaction, the whole transaction will be skipped. With non-transactional engines, an event is always a single statement.
This statement is valid only when the replica threads are not running. Otherwise, it produces an error.
The statement does not automatically restart the replica threads.
Multi-source replication:
sql_slave_skip_counter can't be used to skip transactions on a replica if GTID replication is in use and if gtid_slave_pos contains multiple gtid_domain_id values. In that case, you'll get an error like the following:
In order to skip transactions in cases like this, you will have to manually change gtid_slave_pos.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET GLOBAL sql_slave_skip_counter = NSHOW SLAVE STATUS \G
...
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;SET @@default_master_connection = 'master_01';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;ERROR 1966 (HY000): When using parallel replication and GTID with multiple
replication domains, @@sql_slave_skip_counter can not be used. Instead,
setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID
position.RESET REPLICA makes the replica forget its position in the master's . This statement is meant to be used for a clean start. It deletes the master.info and relay-log.info files, all the files, and starts a new relay log file. To use RESET REPLICA, the replica threads must be stopped (use if necessary).
Note: All relay log files are deleted, even if they have not been completely executed by the replica SQL thread. (This is a condition likely to exist on a replication replica if you have issued a STOP REPLICA statement or if the replica is highly loaded.)
Note: RESET REPLICA does not reset the globalgtid_slave_pos variable. This means that a replica server configured with CHANGE MASTER TO MASTER_USE_GTID=slave_pos will not receive events with GTIDs occurring before the state saved ingtid_slave_pos. If the intent is to reprocess these events,gtid_slave_pos must be manually reset, e.g., by executing set global gtid_slave_pos="".
Connection information stored in the master.info file is immediately reset using any values specified in the corresponding startup options. This information includes values such as master host, master port, master user, and master password. If the replica SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET REPLICA is issued, these replicated temporary tables are deleted on the replica.
The ALL also resets the PORT, HOST, USER, and PASSWORD parameters for the replica. If you are using a connection name, it will be permanently deleted it and it will not show up anymore in .
The connection_name option is used for .
If there is only one nameless primary, or the default primary (as specified by the system variable) is intended, connection_name can be omitted. If provided, the RESET REPLICA statement will apply to the specified primary. connection_name is case-insensitive.
MariaDB starting with
The FOR CHANNEL keyword was added for MySQL compatibility. This is identical to using the channel_name directly after RESET REPLICA.
FOR CHANNEL is not available.
The FOR CHANNEL keyword was added for MySQL compatibility. This is identical as using the channel_name directly after RESET REPLICA.
MariaDB starting with
RESET REPLICA resets the Master/Slave_last_event_time and Connects_Tried values (see ).
RESET REPLICA resets the Master/Slave_last_event_time values (see ).
RESET REPLICA does not reset the Master
stops the replica, but it can be restarted with or after next MariaDB server restart.
This page is licensed: GPLv2, originally from
START REPLICA is a synonym for START SLAVE, which is considered deprecated.
Only START SLAVE can be used.
START SLAVE or START REPLICA with no thread_type options starts both of the replica threads (see ) needed to connect with a master setup with The I/O thread reads events from the primary server and stores them in the . The SQL thread reads events from the relay log and executes them.
START REPLICA requires the privilege.
START REPLICA requires the privilege.
If START REPLICA succeeds in starting the replica threads, it returns without any error. However, even in that case, it might be that the replica threads start and then later stop (for example, because they do not manage to connect to the primary or read its , or some other problem). START REPLICA does not warn you about this. You must check the replica's for error messages generated by the replica threads or check that they are running satisfactorily with ().
START REPLICA UNTIL refers to the SQL_THREAD replica position at which the SQL_THREAD replication will halt. If SQL_THREAD isn't specified, both threads are started.
START REPLICA UNTIL master_gtid_pos=xxx is also supported. See for more details.
MariaDB starting with
SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS
The START REPLICA UNTIL statement contains the options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS to allow control of whether the replica stops before or after a provided GTID state. Its syntax is:
See for details.
SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS are not available.
If there is only one nameless primary, or the default primary (as specified by the system variable) is intended, connection_name can be omitted. If provided, the START REPLICA statement will apply to the specified primary. connection_name is case-insensitive.
The FOR CHANNEL keyword is available for MySQL compatibility. This is identical to using the channel_name directly after START REPLICA.
The FOR CHANNEL keyword is not available.
START ALL REPLICAS starts all configured replicas (replicas with master_host not empty) that were not started before. It will give a note for all started connections. You can check the notes with .
.
is used to create and change connections.
is used to stop a running connection.
is used to reset parameters for a connection and also to permanently delete a primary connection.
This page is licensed: GPLv2, originally from
Stops the replica threads. STOP REPLICA requires the privilege, or, from , the privilege.
Like , this statement may be used with the IO_THREAD andSQL_THREAD options to name the thread or threads to be stopped. In almost all cases, one never need to use the thread_type options.
STOP REPLICA waits until any current replication event group affecting one or more non-transactional tables has finished executing (if there is any such replication group), or until the user issues a or statement.
Note that STOP REPLICA doesn't delete the connection permanently. Next time you execute or the MariaDB server restarts, the replica connection is restored with it's . If you want to delete a connection, you should execute .
STOP ALL REPLICAS stops all your running replicas. It will give you a note for every stopped connection. You can check the notes with .
The connection_name option is used for .
If there is only one nameless master, or the default master (as specified by the system variable) is intended, connection_name can be omitted. If provided, the STOP REPLICA statement will apply to the specified master. connection_name is case-insensitive.
MariaDB starting with
The FOR CHANNEL keyword is available for MySQL compatibility. This is identical as using the channel_name directly after STOP REPLICA.
The FOR CHANNEL keyword is not available.
is used to create and change connections.
is used to start a predefined connection.
is used to reset parameters for a connection and also to permanently delete a master connection.
This page is licensed: GPLv2, originally from
RESET { SLAVE | REPLICA } ["connection_name"] [ALL] [FOR CHANNEL "connection_name"].START { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ]
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
START { SLAVE | REPLICA } ["connection_name"] [SQL_THREAD] UNTIL
MASTER_GTID_POS = <GTID position>
START ALL { SLAVES | REPLICAS } [thread_type [, thread_type]]
thread_type: IO_THREAD | SQL_THREADSTOP { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ]
[FOR CHANNEL "connection_name"]
STOP ALL { SLAVES | REPLICAS } [thread_type [, thread_type]]
STOP { SLAVE | REPLICA } ["connection_name"] [thread_type [, thread_type] ... ]
STOP ALL { SLAVES | REPLICAS } [thread_type [, thread_type]]
thread_type: IO_THREAD | SQL_THREADSlave_last_event_timeSTART REPLICA UNTIL (SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS)="<gtid_list>"