WL#5564: Replication Information: scope, visibility, persistency, mutability
Affects: Server-Prototype Only
—
Status: Assigned
==== Purpose ====
This worklog aims to ensure that the replication interface that we
expose to the user is consistent and future-proof. (Here, the term
"consistent" does not refer to database consistency, but to interface
consistency. I.e., the interface should use unified naming conventions
where possible, map well to a structured mental view of the data,
avoid unnecessary duplication, etc.)
We will analyze all pieces of information and determine a unified way
to organize it. This may affect the design of the worklogs listed in
B2.
This worklog will not have any associated code. It will only serve as
a base to design other worklogs.
==== Background ====
B1. Replication-related information is currently user-accessible in the
following ways:
- As server system variables.
- As server status variables.
- As server command-line options.
- In SHOW SLAVE STATUS.
- In SHOW MASTER STATUS.
- In CHANGE MASTER.
- In arguments to START SLAVE.
- In SHOW SLAVE HOSTS.
B2. In the current implementation, it is easy to distinguish several
independent scopes of replication-related information: information may
be associated with the IO thread, the SQL thread, the sequence of relay
logs, a dump thread, the sequence of binary logs, a client, or be global.
However, currently, commands like CHANGE MASTER, SHOW SLAVE STATUS,
SHOW MASTER STATUS, and SHOW SLAVE HOSTS unnecessarily couple information
from different scopes in a way that assumes there is one IO thread, one SQL
thread, one sequence of relay logs, and one sequence of binlogs.
It would be better if the different scopes were separated entirely. This
would have several advantages:
- It would make clear which scope each field belongs to. For example,
it cannot currently be deduced from the way the field is exposed if
Replicate_Ignore_Server_Ids affects the IO thread or the SQL thread
(both are conceivable). If this could be deduced from the scope, the
user interface would be more easy to understand.
- The current schema design of SHOW SLAVE STATUS has insertion anomalities
(http://en.wikipedia.org/wiki/Database_normalization#Free_the_database_of_modification_anomalies)
For example, we currently expose fields of the IO thread when no IO
thread is configured. When we separate scopes, we don't have to expose
dummy fields with default values.
- In the future, we may change the model for replication so that there
can be more than one SQL thread, more than one IO thread, more than one
sequence of relay logs, and more than one binary log. Existing plans
include WL#1697, which will allow more than one IO thread and more than
one sequence of relay logs; and http://lists.mysql.com/internals/38079,
which suggests more than one sequence of binary logs. When we separate
scopes, we also open the door for other similar ideas; for instance,
if we want to allow similar filtering as suggested at
http://lists.mysql.com/internals/38079, but on the slave side, it means
we need more relay logs and SQL threads than IO threads.
B3. There is ongoing work that changes how fields are made persistent or
visible:
- WL#3656 exposes information that is currently visible in
SHOW SLAVE STATUS in a PERFORMANCE_SCHEMA table.
- WL#3657 exposes information that is currently visible in
SHOW MASTER STATUS in a PERFORMANCE_SCHEMA table.
- WL#5177 makes replication filters persistent and exposes them in a
new way, using system tables in the mysql database.
- WL#2775 makes information that is currently stored in master.info
or relay-log.info persistent and visible, using system tables in
the mysql database.
B4. There is no clear rule for things like:
- What is made persistent and not.
For example, Replicate_Ignore_Server_Ids and --replicate-same-server-id
both filter events by server id, but only the former is persistent.
- What is mutable and not.
For example, both --slave-skip-errors and slave_exec_mode affect how
the SQL thread handles various kinds of errors, but only the latter is
settable after startup.
- What is mutable where.
For example, CHANGE MASTER TO RELAY_LOG_POS,
SET @@global.sql_skip_counter, and START SLAVE UNTIL RELAY_LOG_POS all
specify where the SQL thread will start to execute, but they use
three different types of syntax.
- What is exposed and not.
For example, both the slave's and the master's coordinates for both the
IO thread and the SQL thread are visible, *except* the slaves's
coordinates for the IO thread.
- What is exposed where.
For example, both MASTER_HEARTBEAT_PERIOD and MASTER_CONNECT_RETRY are
parameters of the IO thread (and both are settable with CHANGE MASTER),
but the former is readable as a status variable and the latter is
readable in SHOW SLAVE STATUS.
- What names do we use.
For example, the position of the SQL thread in master coordinates is
expressed by the two fields Relay_Master_Log_File and
Exec_Master_Log_Pos, each of which seems to use its own naming
convention.
These are just examples, and there are more examples. The point here is that
we need to make up clear rules in order to create a consistent interface.
B5. The naming of some replication options are ambiguous. For example the
following SSL related options in CHANGE MASTER TO:
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
The phrase "MASTER" is badly misleading. If fact most of those
variables are for the *slave* side of the replication channel, not
the master side (as the naming suggests) (quote from BUG#11759446).
==== Definitions and Scope of this Worklog ====
We use the term "field" to denote one piece of information with a defined
semantics. For example, the file name of the relay log that the SQL thread is
currently reading from is one field; the slave_exec_mode is another field.
This worklog deals with fields that relate to the replication master and to the
replication slave but not to the contents of the binary logs or relay logs
(i.e., not the output of SHOW BINLOG EVENTS or SHOW RELAY_LOG EVENTS). Fields
are found in the following places:
- SHOW SLAVE STATUS
http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html
- Slave command line options
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html
- Slave system variables
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html
- Server status variables
http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html
TODO: not yet accounted for in table
- Fields settable with CHANGE MASTER
http://dev.mysql.com/doc/refman/5.5/en/change-master-to.html
- Fields settable when executing START SLAVE
http://dev.mysql.com/doc/refman/5.5/en/start-slave.html
- SHOW MASTER STATUS
http://dev.mysql.com/doc/refman/5.5/en/show-master-status.html
- SHOW SLAVE HOSTS
http://dev.mysql.com/doc/refman/5.5/en/show-slave-hosts.html
- Master command line options and system variables
http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html
- Binlog command line options and variables
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html
- Fields currently not exposed
Some fields appear in more than one place.
==== Step-by-step Plan ====
We will do this WL in the following steps:
S1. List all fields that are currently exposed to end users, or that we
plan to expose in the near future.
S2. For each field, determine:
S2.1 The logical scope.
Is the field relevant for each IO thread, for each SQL thread, for
each sequence of relay logs, for each sequence of binary logs, for
each dump thread, for each client, or globally?
S2.2 The current visibility.
Where is the field currently exposed?
S2.3 The current persistency.
Is the field currently made persistent?
S2.4 The current mutability.
Is it currently possible to change the field? When and under what
conditions can it be changed?
S3. For each field, determine:
S3.1 If the field should be made persistent in the future.
S3.2 If and when the field should be made mutable in the future.
S3.3 If the field should be made visible in the future.
S3.4 What is an appropriate name for the field.
S4. Determine how fields should be grouped together and how to expose them to
users. For example, some fields may be exposed in system tables, some
fields may be exposed as variables, some fields may be exposed in
INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables, and we may propose new
ways to modify fields so that their logical scope is taken into account.
S5. Ensure that all relevant worklogs are updated to conform with the design
in S4.
S6. Possibly file new worklogs if the existing ones are not enough.
==== Notes on deprecated fields ====
Fields that are deprecated and completely removed do not need to be analyzed.
The following worklogs list deprecated features:
WL#5154
WL#5182
WL#5185
WL#5265
WL#5533
However, after analyzing these worklogs I found that none of the fields has been
deprecated and completely removed. A small number fields were exposed in two
places (like --master-retry-count / CHANGE MASTER TO MASTER_RETRY_COUNT), one of
which has been removed; but all fields remain in the server.
==== Data Model and Field Scopes ==== We assume a model where the following scopes are possible: - One server with global-scope fields. - Zero or more sequences of binlogs with binlog-scope fields. In the future, we may want to have multiple parallel binlogs; e.g., one binlog for each database. That would allow easy migration of one database out of many from one server to another. See http://lists.mysql.com/internals/38079 - Zero or more dump threads with dump-scope fields. - Zero or more clients with client-scope fields. - Zero or more sequences of relay logs with relay log-scope fields. In the future, we may want to be able to replicate from multiple masters or using multiple connections to the same master. - Zero or more IO threads with IO thread scope, each writing to one or more sequences of relay logs. In the future, there may be more than one IO thread (because we want to support replication from more than one master, WL#1697), and possibly each IO thread may write to more than one sequence of relay logs (e.g., to split the relay log by database to allow more efficient parallel execution of relay logs), and possibly more than one IO thread writes to each relay log. - Each sequence of relay logs is associated with zero or more SQL threads with SQL thread scope. In the future, there may be multiple parallel threads that apply events from the relay logs. See WL#5563. This model is more general than what the server currently supports. In particular, the server is currently limited to one binlog, one sequence of relay logs, one IO thread, and one SQL thread. ==== Interfaces for Storing, Modifying, and Exposing Fields ==== Fields that are persistent need to be stored somewhere. Fields that can be modified need a way to be updated. Fields that are visible need a way to access them. We will separate the concepts of storage, modification, and visibility of fields into three different interfaces. Moreover, global fields that merely serve as default values for other fields are in a fourth interface (namely, they are exposed as server variables). This separation allows, for instance, all mutable fields to be modified with similar interfaces, independently of whether the fields are persistent or not. We will define the following types of interfaces: - Persistent fields are stored in tables in the mysql database. - Mutable fields have a dedicated statement syntax to modify them. - Visible fields are listed in a view in a PERFORMANCE_SCHEMA table. - If a field is specific to one scope but has a global default value, then the global default value is settable and gettable using a @@global.variable. The following tables are used for persistent storage: mysql.io_thread - persistent storage of fields with IO thread scope mysql.sql_thread - persistent storage of fields with SQL thread scope mysql.relay_log - persistent storage of fields with relay log scope mysql.dump_thread - persistent storage of fields with dump thread scope mysql.binlog - persistent storage of fields with binlog scope The following views are used to access fields that should be readable (here, P_S is an abbreviation for PERFORMANCE_SCHEMA): P_S.IO_THREAD - read-only view of fields with IO thread scope P_S.SQL_THREAD - read-only view of fields with SQL thread scope P_S.RELAY_LOG - read-only view of fields with relay log scope P_S.DUMP_THREAD - read-only view of fields with dump thread scope P_S.BINLOG - read-only view of fields with binlog scope The following commands are used to set fields that should be settable. CHANGE IO_THREADTO = Set a new value for a field of a named IO thread CHANGE SQL_THREAD TO = Set a new value for a field of a named SQL thread CHANGE RELAY_LOG TO = Set a new value for a field of a named sequence of relay logs CHANGE DUMP_THREAD TO = Set a new value for a field of a named dump thread CHANGE BINLOG TO = Set a new value for a field of a named sequence of binary logs Here, identifies the object among all objects (e.g., identifies one out of many IO threads). The form of (string, number, etc) can be determined in later worklogs. In the present worklog, we only determine which fields that should be settable by which command. ==== Names ==== When fields are added to new places, we should use consistent naming schemes. We should ensure the following: - Fields should use the same name in all places where they are added. This is relevant for fields that are added in two or more of "the mysql database", "the P_S database", and "the CHANGE command". For example, in the current interface, the read position of the IO thread in the master's coordinates is called Read_Master_Log_Pos in SHOW SLAVE STATUS and Master_Log_Pos in CHANGE MASTER. We should take the opportunity to fix such inconsistencies in the new interface. - Fields should not use unnecessary prefixes. For example, if a field has SQL thread scope, then it will normally be accessed through a table or command that contains the word SQL_THREAD, so there is no need to prefix the field with SQL_. When fields have default values that are stored in global or session variables, then those variables need to be prefixed with the scope. For example, when we add the field that is currently called SQL_Delay as a column in P_S.SQL_THREAD, it should be called Delay, and the CHANGE command should be CHANGE SQL_THREAD TO DELAY = VALUE. However, the global variable that holds the default delay for new SQL threads should be called @@global.sql_thread_delay. Similarly, the words Master, Slave, and Replicate are superfluous in most places. - Similar fields should have similar naming conventions. For example, most fields that are limitations are prefixed with the word max. So instead of relay-log-space-limit, we should use a name that begins with max, and in of binlog-row-event-max-size, we should move the word max to the beginning. - Names that are known to cause confusion should be clarified. For example, binlog_format causes a lot of confusion because it specifies a policy, not a format. (There are two formats: row and statement. There are three policies: use only row format (that's the row policy), use only statement (that's the statement policy), or use whatever is most appropriate (that's the mixed policy)). So we should rename it to Policy. - Non-standard or ambiguous abbreviations should be avoided. For example, we should use Position instead of Pos, and Report_Authentication_Info instead of Report_Auth_Info (Auth can be confused with the related term Authorization). More standard abbreviations like db, id, init, max, SSL, sync, and UUID should of course be used. - Use standardized schemas for Capitalization. It seems that column names for SHOW statements have all sorts of capitalization (all of Leading_capital_with_underscores, Camel_Case_With_Underscores, Leading capital with spaces, Camel Case With Spaces), Leading_capital_with_underscores is most common for column names in the mysql database (lower_case_with_underscores appears too), UPPER_CASE_WITH_UNDERSCORE is most common for column names in the PERFORMANCE_SCHEMA and INFORMATION_SCHEMA databases (lower_case_with_underscores appear too in INFORMATION_SCHEMA), --lower-case-with-dashes is the most common for command line arguments, lower_case_with_underscores is most common for variable names, UPPER_CASE_WITH_UNDERSCORES is most common for sql statements, Leading_capital_with_underscores is most common for status variables, and UPPER_CASE_WITH_UNDERSCORES is most common for table names in the PERFORMANCE_SCHEMA database. So we use Leading_capital_with_underscores for column names in SHOW statements and in the mysql database, UPPER_CASE_WITH_UNDERSCORE for column names in INFORMATION_SCHEMA and PERFORMANCE_SCHEMA, --lower-case-with-dashes for command line arguments, lower_case_with_underscores for variables names, UPPER_CASE_WITH_UNDERSCORES for SQL statements, Leading_capital_with_underscores for status variables. - Question: the words "IO thread" and "SQL thread" are a bit vague (e.g., most other threads do some IO; most client threads do some SQL, and the SQL thread executes row events that are not SQL). Perhaps "copy thread" and "apply thread" are more descriptive. Can we use these names (or something better) or should we stick to existing names? ==== Backward compatibility ==== There are no backward compatibility issues because this WL only defines new interfaces, it does not change existing interfaces. In the future (in other worklogs) we may make e.g. SHOW SLAVE STATUS and CHANGE MASTER throw an error whenever there is more than one SQL thread, more than one IO thread, or more than one sequence of relay logs. This again does not break backward compatibility because it has not been possible to use more than one of each object before. ==== Classification of Fields ==== In the following table, we analyze all fields. ---- Legend ---- FIELD: The current name of the field. Names are grouped by the place they are found. Fields are found in the following places: SHOW SLAVE STATUS Slave command line options Slave variables Fields settable with CHANGE MASTER Fields settable when executing START SLAVE SHOW MASTER STATUS SHOW SLAVE HOSTS Master command line options and variables Binlog command line options and variables Fields currently not exposed To make the analysis clear, fields that appear on two places are only analyzed in one place. The other place contains a reference to the first place (for an example, see the line Master_Host in the SHOW SLAVE STATUS section of the table). SCP: The logical scope of the field. For purposes of classification, we assume a more general model of replication slaves and masters than is currently implemented. Currently, there is one sequence of relay logs, one IO thread and one SQL thread. In the future, as a consequence of implementing multi-threaded slave and multi-master replication, we may have several sequences of relay logs, each of which is associated with several IO threads and several SQL threads. Moreover, currently there is only one binlog, but in the future we may use several binlogs (and, e.g., filter events into different binlogs depending on database etc). Hence, fields can have the following logical scopes: I - the field is relevant for each IO thread S - the field is relevant for each SQL thread R - the field is relevant for each sequence of relay logs D - the field is relevant for each dump thread B - the field is relevant for each binlog G - the field is relevant globally IG - the field is relevant for each IO thread but can have a global default SG - the field is relevant for each SQL thread but can have a global default RG - the field is relevant for each sequence of relay logs but can have a global default DG - the field is relevant for each dump but can have a global default BG - the field is relevant for each binlog but can have a global default CG - the field is relevant for each client thread but can have a global default BCG - the field is relevant for each binlog, but each client can have its own set of values and there can be a global default set of values CURRENT/PER: the persistence of the field, currently. Y - The field is currently persistent. - - The field is not currently persistsent. CURRENT/SET: when can a user set the field's value, currently. Y - The field can be set at any time !I - The field can be set when the IO thread is stopped !S - The field can be set when the SQL thread is stopped !R - The field can be set when all IO threads that read from the relevant relay log are stopped. !IS - The field can be set when both the IO and the SQL thread is stopped !T - The field can be set globally always, but in the client only when there is no ongoing transaction X - The field can be set by exiting and restarting the server - - The field is not currently mutable. CURRENT/VIS: where the field is currently visible. S - The field exists in SHOW SLAVE STATUS. M - The field exists in SHOW MASTER STATUS. H - The field exists in SHOW SLAVE HOSTS. V - The field exists as a global variable. t - The field exists as a global sTatus variable - - The field is currently not exposed. WANTED/PER: the persistence of the field, as we would like it to be. Y - The field should be persistent: i.e., it should be stored in a table in the mysql database. The SCP column determines which table. - - The field shouuld not be persistsent. WANTED/SET: when can the user set the field's value, as we would like it to be. Y - The field can be set at any time. The scope determines how the field can be set: using a variable if the scope contains 'G'; using CHANGE [IO_THREAD|SQL_THREAD|DUMP_THREAD|RELAY_LOG|BINLOG] if the scope contains I/S/D/R/B. !I - The field can be set when the IO thread is stopped, using CHANGE IO_THREAD. !S - The field can be set when the SQL thread is stopped, using CHANGE SQL_THREAD. !R - The field can be set when all IO threads that read from the relevant relay log are stopped, using CHANGE RELAY_LOG. !T - The field can be set globally always, but in the client only when there is no ongoing transaction, using CHANGE BINLOG X - The field can only be set by exiting and restarting the server, using --field-name-in-lowercase-with-underscores-replaced-by-dashes=value - - The field is not currently mutable. WANTED/VIS: places where we may want to add the field in the future (for backward compatibility, we cannot remove existing fields). Can be one or more of: Y - The field should be visible - - The field should not be visible WANTED/NAME: the name we would like to use when adding the field to new places (listed in WANTED/VIS), or empty if we want to keep the old name. ---- Classification ---- ================================================================================ | || CURRENT || WANTED | FIELD |SCP||PER|SET|VIS||PER|SET|VIS|NAME | ================================================================================ SHOW SLAVE STATUS ----------+---++---+---+---++---+---+---+--------------------+ Slave_IO_State | I || - | - | S || - | - | Y |Run_State | Master_Host = CHANGE MASTER TO MASTER_HOST Master_User = CHANGE MASTER TO MASTER_USER Master_Port = CHANGE MASTER TO MASTER_PORT Connect_Retry = CHANGE MASTER TO MASTER_CONNECT_RETRY Master_Log_File | I || Y | - | S || Y |!I | Y |File_Master | Read_Master_Log_Pos | I || Y | - | S || Y |!I | Y |Position_Master | Relay_Log_File | S || Y |!IS| S || Y |!S | Y |File_Slave | Relay_Log_Pos | S || Y |!IS| S || Y |!S | Y |Position_Slave | Relay_Master_Log_File | S || Y |!IS| S || Y |!S | Y |File_Master | Slave_IO_Running | I || - | - | S || Y | - | Y |Thread_Running (1) | Slave_SQL_Running | S || - | - | S || Y | - | Y |Thread_Running (1) | Replicate_Do_DB = replicate-do-db Replicate_Ignore_DB = replicate-ignore-db Replicate_Do_Table = replicate-do-table Replicate_Ignore_Table = replicate-ignore-table Replicate_Wild_Do_Table = replicate-wild-do-table Replicate_Wild_Ignore_Table = replicate-wild-ignore-table Last_Errno = SHOW SLAVE STATUS/Last_SQL_Errno Last_Error = SHOW SLAVE STATUS/Last_SQL_Error Skip_Counter = sql_slave_skip_counter Exec_Master_Log_Pos | S || Y |!IS| S || Y |!S | Y |Position_Master | Relay_Log_Space | R || - | - | S || - | - | Y |Disk_Usage | Until_Condition | S || - |!IS| S || - | - | - | | Until_Log_File = START SLAVE/UNTIL [MASTER|RELAY]_LOG_FILE Until_Log_Pos = START SLAVE/UNTIL [MASTER|RELAY]_LOG_POS Master_SSL_Allowed | IG|| Y |!IS| S || Y |!I | Y |SSL_Allowed | Master_SSL_CA_File | IG|| Y |!IS| S || Y |!I | Y |SSL_CA_File | Master_SSL_CA_Path | IG|| Y |!IS| S || Y |!I | Y |SSL_CA_Path | Master_SSL_Cert | IG|| Y |!IS| S || Y |!I | Y |SSL_Certificate | Master_SSL_Cipher | IG|| Y |!IS| S || Y |!I | Y |SSL_Cipher | Master_SSL_Key | IG|| Y |!IS| S || Y |!I | Y |SSL_Key | Seconds_Behind_Master | I || - | - | S || - | - | Y |(2) | Master_SSL_Verify_Server_Cer| IG|| Y |!IS| S || Y |!I | Y |SSL_Verify_Server_C-| | || | | || | | |ertificate | Last_IO_Errno | I || - |!IS| S || Y | Y | Y |Last_Errno | Last_IO_Error | I || - |!IS| S || Y | Y | Y |Last_Error | Last_SQL_Errno | S || - |!IS| S || Y | Y | Y |Last_Errno | Last_SQL_Error | S || - |!IS| S || Y | Y | Y |Last_Error | Replicate_Ignore_Server_Ids | IG|| Y |!IS| S || Y |!I | Y |Ignore_Server_Ids | Master_Server_Id | I || - | - | S || - | - | Y | | Master_UUID | I || Y | - | S || Y | - | Y | | Master_Info_File = Slave command line options/master-info-file SQL_Delay | SG|| Y |!IS| S || Y |!S | Y |Delay | SQL_Remaining_Delay | S || - | - | S || - | - | Y |Remaining_Delay | Slave_SQL_Running_State | S || - | - | S || - | - | Y |Run_State | Slave command line options -+---++---+---+---++---+---+---+--------------------+ abort-slave-event-count | SG|| - | X | - || - |!S | Y |Abort_Event_Count | disconnect-slave-event-count| IG|| - | X | - || - |!I | Y |Disconnect_Event_Co-| | || | | || | | |unt | log-slave-updates | SG|| - | X | - || Y |!S | Y |Log_Updates | log-slow-slave-statements | SG|| - | X | - || Y |!S | Y |Log_Slow_Statements | log-warnings | Not specific to replication master-info-file | I || - | X | S || - | - | - | | master-retry-count = CHANGE MASTER TO MASTER_CONNECT_RETRY max-relay-log-size | RG|| - | X | - || Y |!R | Y |Max_File_Size | read-only | G || - | X | - || Y | Y | Y | | relay-log | R || - | X | - || Y |!R | Y |File_Basename | relay-log-index | R || - | X | - || Y |!R | Y |Index_File | relay-log-info-file | S || - | X | - || - | - | - | | relay-log-purge | RG|| - | X | - || Y | Y | Y |Automatic_Purge | relay-log-recovery | RG|| - | X | - || - | X | Y |Recovery | relay-log-space-limit | RG|| - | X | S || Y |!R | Y |Max_Disk_Usage | replicate-do-db | SG|| - | X | S || Y |!S | Y |Do_DB | replicate-ignore-db | SG|| - | X | S || Y |!S | Y |Ignore_DB | replicate-do-table | SG|| - | X | S || Y |!S | Y |Do_Table | replicate-ignore-table | SG|| - | X | S || Y |!S | Y |Ignore_Table | replicate-rewrite-db | SG|| - | X | - || Y |!S | Y |Rewrite_DB | replicate-same-server-id | IG|| - | X | - || Y |!I | Y |Do_Same_Server_Id | replicate-wild-do-table | SG|| - | X | S || Y |!S | Y |Wildcard_Do_Table | replicate-wild-ignore-table | SG|| - | X | S || Y |!S | Y |Wildcard_Ignore_Tab-| | || | | || | | |le | report-host | IG|| - | X | - || Y |!I | Y | | report-password | IG|| - | X | - || Y |!I | Y | | report-port | IG|| - | X | - || Y |!I | Y | | report-user | IG|| - | X | - || Y |!I | Y | | show-slave-auth-info | IG|| - | X | - || Y |!I | Y |Report_Authenticati-| | || | | || | | |on_Info | skip-slave-start | G || - | X | - || - | X | Y |(1) | slave-compressed-protocol | IG|| - | X | - || Y |!I | Y |Compressed_Protocol | slave-load-tmpdir | SG|| - | X | - || Y |!S | Y |Load_Tmpdir | slave-net-timeout | IG|| - | X | - || Y |!I | Y |Net_Timeout | slave-skip-errors | SG|| - | X | - || Y |!S | Y |Skip_Errors | Slave variables ------------+---++---+---+---++---+---+---+--------------------+ init_slave | SG|| - | G | V || Y |!S | Y |Init_Statement | relay_log_recovery = relay-log-recovery slave_compressed_protocol = slave-compressed-protocol slave_exec_mode | SG|| - | Y | V || Y |!S | Y |Execution_Mode | slave_load_tmpdir = slave-load-tmpdir slave_net_timeout = slave-net-timeout slave_skip_errors = slave-skip-errors slave_transaction_retries | SG|| - | Y | V || Y |!S | Y |Transaction_Retry_C-| | || | | || | | |ount | slave_type_conversions | SG|| - | Y | V || Y |!S | Y |Type_Conversions | sql_slave_skip_counter | S || - |!S | SV|| Y |!S | Y |Skip_Count (1) | sync_master_info | IG|| - | Y | V || - | - | - | | sync_relay_log | RG|| - | Y | V || Y |!I | Y |Sync_Frequency | sync_relay_log_info | SG|| - | Y | V || - | - | - | | CHANGE MASTER fields -------+---++---+---+---++---+---+---+--------------------+ MASTER_BIND | I || Y |!IS| - || Y |!I | Y |Network_Interface | MASTER_HOST | I || Y |!IS| - || Y |!I | Y |Host | MASTER_USER | I || Y |!IS| - || Y |!I | Y |User | MASTER_PASSWORD | I || Y |!IS| - || Y |!I | Y |Password | MASTER_PORT | I || Y |!IS| - || Y |!I | Y |Port | MASTER_CONNECT_RETRY | IG|| Y |!IS| - || Y |!I | Y |Connection_Retry_Co-| | || | | || | | |unt | MASTER_HEARTBEAT_PERIOD | IG|| Y |!IS| t || Y |!I | Y |Heartbeat_Period | MASTER_LOG_FILE = SHOW SLAVE STATUS/Master_Log_File MASTER_LOG_POS = SHOW SLAVE STATUS/Master_Log_Pos RELAY_LOG_FILE = SHOW SLAVE STATUS/Relay_Log_File RELAY_LOG_POS = SHOW SLAVE STATUS/Relay_Log_Pos MASTER_SSL = SHOW SLAVE STATUS/MASTER_SSL_ALLOWED MASTER_SSL_CA = SHOW SLAVE STATUS/MASTER_SSL_CA MASTER_SSL_CAPATH = SHOW SLAVE STATUS/MASTER_SSL_CAPATH MASTER_SSL_CERT = SHOW SLAVE STATUS/MASTER_SSL_CERT MASTER_SSL_KEY = SHOW SLAVE STATUS/MASTER_SSL_KEY MASTER_SSL_CIPHER = SHOW SLAVE STATUS/MASTER_SSL_CIPHER MASTER_SSL_VERIFY_SERVER_CER= SHOW SLAVE STATUS/MASTER_SSL_VERIFY_SERVER_CERT IGNORE_SERVER_IDS = SHOW SLAVE STATUS/Replicate_Ignore_Server_Ids START SLAVE fields ---------+---++---+---+---++---+---+---+--------------------+ UNTIL MASTER_LOG_FILE | S || - |!S | S || Y |!S | Y |Until_File_Master | UNTIL MASTER_LOG_POS | S || - |!S | S || Y |!S | Y |Until_Position_Mast-| | || | | || | | |er | UNTIL RELAY_LOG_FILE | S || - |!S | S || Y |!S | Y |Until_File_Slave | UNTIL RELAY_LOG_POS | S || - |!S | S || Y |!S | Y |Until_Position_Slave| SHOW MASTER STATUS ---------+---++---+---+---++---+---+---+--------------------+ File | D || - | - | M || - | - | Y | | Position | D || - | - | M || - | - | Y | | Binlog_Do_DB = binlog-do-db Binlog_Ignore_DB = binlog-ignore-db SHOW SLAVE HOSTS -----------+---++---+---+---++---+---+---+--------------------+ Server_id | D || - | - | H || - | - | Y |Server_Id | Host | D || - | - | H || - | - | Y | | Port | D || - | - | H || - | - | Y | | Master_id = Master variables/server_id Slave_UUID | D || - | - | H || - | - | Y |UUID | Master variables and options+---++---+---+---++---+---+---+--------------------+ auto_increment_increment | CG|| - | Y | V || Y | Y | Y | | auto_increment_offset | CG|| - | Y | V || Y | Y | Y | | server_id | CG|| - | Y | V || Y | - | Y | | Binlog options and variables+---++---+---+---++---+---+---+--------------------+ binlog-row-event-max-size |BCG|| - | X | - || Y | Y | Y |Max_Row_Event_Size | log-bin | B || - | X | - || Y | Y | Y |File_Basename | log-bin-index | B || - | X | - || Y | Y | Y |Index_File | log-bin-trust-function-creat= log_bin_trust_function_creators binlog-do-db |BCG|| - | X | M || Y | Y | Y |Do_DB | binlog-ignore-db |BCG|| - | X | M || Y | Y | Y |Ignore_DB | max-binlog-dump-events | DG|| - | X | - || - | Y | Y |Max_Events | sporadic-binlog-dump-fail | DG|| - | X | - || - | Y | Y |Sporadic_Failures | binlog-cache-size = binlog_cache_size binlog_cache_size | BG|| - | Y | V || Y | Y | Y |Default_Cache_Size | binlog-direct-non-transactio= binlog_direct_non_transactional_updates binlog_direct_non_transactio|BCG|| - |!T | V || Y |!T | Y |Write_Non_Transacti-| | || | | || | | |onal_Updates_Direct-| | || | | || | | |ly | binlog-format = binlog-format binlog_format |BCG|| - |!T | V || Y |!T | Y |Policy | max-binlog-cache-size = max_binlog_cache_size max_binlog_cache_size | BG|| - | Y | V || Y | Y | Y |Max_Cache_Size | max-binlog-size = max_binlog_size max_binlog_size | BG|| - | Y | V || Y | Y | Y |Max_File_Size | log_bin = sql_log_bin sql_log_bin |BCG|| - |!T | V || Y |!T | Y |Enabled | log_bin_trust_function_creat| G || - | Y | V || Y | Y | Y |Binlog_Trust_Functi-| | || | | || | | |on_Creators | expire_log_days | B || - | Y | V || Y | Y | Y |Automatic_Purge_Del-| | || | | || | | |ay | Server status variables ----+---++---+---+---++---+---+---+--------------------+ (here, "RSSM/RSSS" abbreviates "Rpl_semi_sync_master"/"Rpl_semi_sync_slave") Binlog_cache_disk_use | C || - | - | t || - | - | Y |Transaction_Cache_D-| | || | | || | | |isk_Use_Count | Binlog_cache_use | C || - | - | t || - | - | Y |Transaction_Cache_U-| | || | | || | | |se_Count | RSSM_clients | G || - | - | t || - | - | Y |RSSM_Client_Count | RSSM_net_avg_wait_time | D || - | - | t || - | - | Y |RSSM_Avg_Net_Wait_T-| | || | | || | | |ime | RSSM_net_wait_time | D || - | - | t || - | - | Y |RSSM_Total_Net_Wait-| | || | | || | | |_Time | RSSM_net_waits | D || - | - | t || - | - | Y |RSSM_Net_Wait_Count | RSSM_no_times | G || - | - | t || - | - | Y |RSSM_Off_Count | RSSM_no_tx | D || - | - | t || - | - | Y |RSSM_Ack_Miss_Count | RSSM_status | D || - | - | t || - | - | Y | | RSSM_timefunc_failures | D || - | - | t || - | - | Y |RSSM_Time_Function_-| | || | | || | | |Failure_Count | RSSM_tx_avg_wait_time | D || - | - | t || - | - | Y |RSSM_Avg_Transactio-| | || | | || | | |n_Wait_Time | RSSM_tx_wait_time | D || - | - | t || - | - | Y |RSSM_Total_Transact-| | || | | || | | |ion_Wait_Time | RSSM_tx_waits | D || - | - | t || - | - | Y |RSSM_Transaction_Wa-| | || | | || | | |it_Count | RSSM_wait_pos_backtraver| D || - | - | t || - | - | Y |RSSM_Back_Traversal-| | || | | || | | |_Count | RSSM_wait_sessions | G || - | - | t || - | - | Y |RSSM_Wait_Session_C-| | || | | || | | |ount | RSSM_yes_tx | D || - | - | t || - | - | Y |RSSM_Ack_Count | RSSS_status | I || - | - | t || - | - | Y | | Slave_heartbeat_period = CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD Slave_open_temp_tables | S || - | - | t || - | - | Y |Open_Tables | Slave_received_heartbeats | I || - | - | t || - | - | Y |Received_Heartbeats | Slave_retried_transactions | S || - | - | t || - | - | Y |Retried_Transactions| Slave_running | G || - | - | t || - | - | - | | Unexposed fields -----------+---++---+---+---++---+---+---+--------------------+ relay log where IOthd writes| I || - | - | - || Y |!I | Y |File_Slave | position where IOthd writes | I || - | - | - || Y |!I | Y |Position_Slave | dump thd's heartbeat period | D || - | - | - || - | - | Y |Heartbeat_Period | timestamp of last_io_error | I || - | - | - || Y | Y | Y |Last_Error_Time | timestamp of last_sql_error | S || - | - | - || Y | Y | Y |Last_Error_Time | ================================================================================ Footnotes: (1) Slave_IO_Running and Slave_SQL_Running determine the current state of the SQL and IO thread. We could make these persistent, so that after server restart, the same threads are started again as were running when the server was shut down. The --skip-slave-start would then suppress starting any slave. We then need to make the following fields persistent: sql_slave_skip_counter, the UNTIL fields, Last_*_Error, etc. (2) In the most generic definition, Seconds_Behind_Master measures how long a particular SQL thread is behind a particular master, so the scope depends on *both* an IO thread and an SQL thread. To avoid adding a new scope for only this field, we can define Seconds_Behind_Master to measure the maximal time, over all SQL threads, we are behind a particular master. This is in any case a meaningful measurement because it shows the oldest un-applied event from each master. So we define the scope to be IO thread.
The following table schemas, CHANGE commands, and system variables were
generated using the attached script get_schemas_version_2.
For now, all fields are ordered alphabetically. It may be possible to order
fields in a more logical way (similar fields in adjacent columns). If there is a
sufficient demand to order fields logically, we can do that. However, note when
new fields are added in the future, they have to be added to the end. So a
consistent ordering cannot be maintained.
Columns in mysql.io_thread:
Compressed_Protocol
Connection_Retry_Count
Do_Same_Server_Id
File_Master
File_Slave
Heartbeat_Period
Host
Ignore_Server_Ids
Last_Errno
Last_Error
Last_Error_Time
Master_UUID
Net_Timeout
Network_Interface
Password
Port
Position_Master
Position_Slave
Report_Authentication_Info
Report_Host
Report_Password
Report_Port
Report_User
SSL_Allowed
SSL_CA_File
SSL_CA_Path
SSL_Certificate
SSL_Cipher
SSL_Key
SSL_Verify_Server_Certificate
Thread_Running
User
Columns in mysql.sql_thread:
Delay
Do_DB
Do_Table
Execution_Mode
File_Master
File_Slave
Ignore_DB
Ignore_Table
Init_Statement
Last_Errno
Last_Error
Last_Error_Time
Load_Tmpdir
Log_Slow_Statements
Log_Updates
Position_Master
Position_Slave
Rewrite_DB
Skip_Counter
Skip_Errors
Thread_Running
Transaction_Retries
Type_Conversions
Until_File_Master
Until_File_Slave
Until_Position_Master
Until_Position_Slave
Wildcard_Do_Table
Wildcard_Ignore_Table
Columns in mysql.relay_log:
Automatic_Purge
File_Basename
Index_File
Max_Disk_Usage
Max_File_Size
Sync_Frequency
Columns in mysql.dump_thread:
Columns in mysql.binlog:
Default_Cache_Size
Do_DB
Enabled
File_Basename
Ignore_DB
Index_File
Max_Cache_Size
Max_File_Size
Max_Row_Event_Size
Policy
Write_Non_Transactional_Updates_Directly
Columns in PERFORMANCE_SCHEMA.IO_THREAD:
Compressed_Protocol
Connection_Retry_Count
Disconnect_Event_Count
Do_Same_Server_Id
File_Master
File_Slave
Heartbeat_Period
Host
Ignore_Server_Ids
Last_Errno
Last_Error
Last_Error_Time
Master_Server_Id
Master_UUID
Net_Timeout
Network_Interface
Password
Port
Position_Master
Position_Slave
Report_Authentication_Info
Report_Host
Report_Password
Report_Port
Report_User
Run_State
SSL_Allowed
SSL_CA_File
SSL_CA_Path
SSL_Certificate
SSL_Cipher
SSL_Key
SSL_Verify_Server_Certificate
Seconds_Behind_Master
Thread_Running
User
Columns in PERFORMANCE_SCHEMA.SQL_THREAD:
Abort_Event_Count
Delay
Do_DB
Do_Table
Execution_Mode
File_Master
File_Slave
Ignore_DB
Ignore_Table
Init_Statement
Last_Errno
Last_Error
Last_Error_Time
Load_Tmpdir
Log_Slow_Statements
Log_Updates
Position_Master
Position_Slave
Remaining_Delay
Rewrite_DB
Run_State
Skip_Counter
Skip_Errors
Thread_Running
Transaction_Retries
Type_Conversions
Until_File_Master
Until_File_Slave
Until_Position_Master
Until_Position_Slave
Wildcard_Do_Table
Wildcard_Ignore_Table
Columns in PERFORMANCE_SCHEMA.RELAY_LOG:
Automatic_Purge
Disk_Usage
File_Basename
Index_File
Max_Disk_Usage
Max_File_Size
Recovery
Sync_Frequency
Columns in PERFORMANCE_SCHEMA.DUMP_THREAD:
File
Heartbeat_Period
Host
Max_Events
Port
Position
Server_Id
Sporadic_Failures
UUID
Columns in PERFORMANCE_SCHEMA.BINLOG:
Default_Cache_Size
Do_DB
Enabled
File_Basename
Ignore_DB
Index_File
Max_Cache_Size
Max_File_Size
Max_Row_Event_Size
Policy
Write_Non_Transactional_Updates_Directly
Commands on the form CHANGE IO_THREAD TO ... = :
COMPRESSED_PROTOCOL
CONNECTION_RETRY_COUNT
DISCONNECT_EVENT_COUNT
DO_SAME_SERVER_ID
FILE_MASTER
FILE_SLAVE
HEARTBEAT_PERIOD
HOST
IGNORE_SERVER_IDS
LAST_ERRNO
LAST_ERROR
LAST_ERROR_TIME
NETWORK_INTERFACE
NET_TIMEOUT
PASSWORD
PORT
POSITION_MASTER
POSITION_SLAVE
REPORT_AUTHENTICATION_INFO
REPORT_HOST
REPORT_PASSWORD
REPORT_PORT
REPORT_USER
SSL_ALLOWED
SSL_CA_FILE
SSL_CA_PATH
SSL_CERTIFICATE
SSL_CIPHER
SSL_KEY
SSL_VERIFY_SERVER_CERTIFICATE
USER
Commands on the form CHANGE SQL_THREAD TO ... = :
ABORT_EVENT_COUNT
DELAY
DO_DB
DO_TABLE
EXECUTION_MODE
FILE_MASTER
FILE_SLAVE
IGNORE_DB
IGNORE_TABLE
INIT_STATEMENT
LAST_ERRNO
LAST_ERROR
LAST_ERROR_TIME
LOAD_TMPDIR
LOG_SLOW_STATEMENTS
LOG_UPDATES
POSITION_MASTER
POSITION_SLAVE
REWRITE_DB
SKIP_COUNTER
SKIP_ERRORS
TRANSACTION_RETRIES
TYPE_CONVERSIONS
UNTIL_FILE_MASTER
UNTIL_FILE_SLAVE
UNTIL_POSITION_MASTER
UNTIL_POSITION_SLAVE
WILDCARD_DO_TABLE
WILDCARD_IGNORE_TABLE
Commands on the form CHANGE RELAY_LOG TO ... = :
AUTOMATIC_PURGE
FILE_BASENAME
INDEX_FILE
MAX_DISK_USAGE
MAX_FILE_SIZE
RECOVERY
SYNC_FREQUENCY
Commands on the form CHANGE DUMP_THREAD TO ... = :
MAX_EVENTS
SPORADIC_FAILURES
Commands on the form CHANGE BINLOG TO ... = :
DEFAULT_CACHE_SIZE
DO_DB
ENABLED
FILE_BASENAME
IGNORE_DB
INDEX_FILE
MAX_CACHE_SIZE
MAX_FILE_SIZE
MAX_ROW_EVENT_SIZE
POLICY
WRITE_NON_TRANSACTIONAL_UPDATES_DIRECTLY
Session io_thread variables:
Session sql_thread variables:
Session relay_log variables:
Session dump_thread variables:
Session binlog variables:
default_binlog_do_db
default_binlog_enabled
default_binlog_ignore_db
default_binlog_max_row_event_size
default_binlog_policy
default_binlog_write_non_transactional_updates_directly
Session sql_thread variables:
Global io_thread variables:
default_io_thread_compressed_protocol
default_io_thread_connection_retry_count
default_io_thread_disconnect_event_count
default_io_thread_do_same_server_id
default_io_thread_heartbeat_period
default_io_thread_ignore_server_ids
default_io_thread_net_timeout
default_io_thread_report_authentication_info
default_io_thread_report_host
default_io_thread_report_password
default_io_thread_report_port
default_io_thread_report_user
default_io_thread_ssl_allowed
default_io_thread_ssl_ca_file
default_io_thread_ssl_ca_path
default_io_thread_ssl_certificate
default_io_thread_ssl_cipher
default_io_thread_ssl_key
default_io_thread_ssl_verify_server_certificate
default_io_thread_sync_master_info
Global sql_thread variables:
default_sql_thread_abort_event_count
default_sql_thread_delay
default_sql_thread_do_db
default_sql_thread_do_table
default_sql_thread_execution_mode
default_sql_thread_ignore_db
default_sql_thread_ignore_table
default_sql_thread_init_statement
default_sql_thread_load_tmpdir
default_sql_thread_log_slow_statements
default_sql_thread_log_updates
default_sql_thread_rewrite_db
default_sql_thread_skip_errors
default_sql_thread_sync_relay_log_info
default_sql_thread_transaction_retries
default_sql_thread_type_conversions
default_sql_thread_wildcard_do_table
default_sql_thread_wildcard_ignore_table
Global relay_log variables:
default_relay_log_automatic_purge
default_relay_log_max_disk_usage
default_relay_log_max_file_size
default_relay_log_recovery
default_relay_log_sync_frequency
Global dump_thread variables:
default_dump_thread_max_events
default_dump_thread_sporadic_failures
Global binlog variables:
default_binlog_default_cache_size
default_binlog_do_db
default_binlog_enabled
default_binlog_ignore_db
default_binlog_max_cache_size
default_binlog_max_file_size
default_binlog_max_row_event_size
default_binlog_policy
default_binlog_write_non_transactional_updates_directly
Global variables:
auto_increment_increment
auto_increment_offset
binlog_trust_function_creators
read_only
server_id
skip_slave_start
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.