第13章: SQL语句语法 / 13.6. 复制语句 / 13.6.2. 用于控制从服务器的SQL语句
13.6.2.1. CHANGE MASTER TO语法
13.6.2.2. LOAD DATA FROM MASTER语法
13.6.2.3. LOAD TABLE tbl_name FROM MASTER语法
13.6.2.4. MASTER_POS_WAIT()语法
13.6.2.5. RESET SLAVE语法
13.6.2.6. SET GLOBAL SQL_SLAVE_SKIP_COUNTER语法
13.6.2.7. SHOW SLAVE STATUS语法
13.6.2.8. START SLAVE语法
13.6.2.9. STOP SLAVE语法
 

复制操作可以通过SQL界面控制。本节讨论了用于管理从属复制服务器的语句。13.6.1节,“用于控制主服务器的SQL语句”讨论了用于管理主服务器的语句。

13.6.2.1. CHANGE MASTER TO语法

  CHANGE MASTER TO master_def [, master_def] ...
 
master_def:
      MASTER_HOST = 'host_name'
    | MASTER_USER = 'user_name'
    | MASTER_PASSWORD = 'password'
    | MASTER_PORT = port_num
    | MASTER_CONNECT_RETRY = count
    | 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_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_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEYMASTER_SSL_CIPHER用于向从属服务器提供有关如何与主服务器连接的信息。

即使对于在编译时没有SSL支持的从属服务器,SSL选项(MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEYMASTER_SSL_CIPHER)也可以被更改。它们被保存到master.info文件中,但是会被忽略,直到您使用一个SSL支持已启用的服务器。

如果您不指定一个给定的参数,则它会保持其原有的值。例外情况在后面的讨论中进行了说明。举例说明,如果用于连接到您的MySQL主服务器的 密码被更改了,您只需发布这些语句,就可以告知从属服务器新的密码:

mysql> STOP SLAVE; -- if replication was running
mysql> CHANGE MASTER TO MASTER_PASSWORD='new3cret';
mysql> START SLAVE; -- if you want to restart replication

没有必要指定没有改变的参数(主机、接口、用户等)。

MASTER_HOSTMASTER_PORT是主服务器主机和其TCP/IP接口的主机名(或IP地址)。注意,如果MASTER_HOSTlocalhost相等,那么,和MySQL的其它部分一样,接口可以被忽略(例如,如果可以使用Unix插槽文件)。

如果您指定了MASTER_HOSTMASTER_PORT,则从属服务器会假定主服务器与以前不一样(即使您指定的主机或接口值与当前值是一样的。)在此情况下,主服务器二进制日志的名称和位置的原有值不再适用,因此,如果您不指定语句中的MASTER_LOG_FILEMASTER_LOG_POSMASTER_LOG_FILE=''MASTER_LOG_POS=4会被静默地添加。

MASTER_LOG_FILEMASTER_LOG_POS坐标点,从属服务器I/O线程在启动之后从主服务器读取。如果您只指定了其中一个,则从属服务器不能指定RELAY_LOG_FILERELAY_LOG_POS。如果MSATER_LOG_FILEMASTER_LOG_POS都没有被指定,则从属服务器会使用在CHANGE MASTER被发布前的最后一个slave SQL thread坐标。当您只想改变要使用的 密码时,这可以确保复制的连续性。即使从属服务器SQL线程落后于从属服务器I/O线程,也可以确保复制的连续性。

CHANGE MASTER会删除所有的中继日志文件并启动一个新的日志,除非您指定了RELAY_LOG_FILERELAY_LOG_POS。在此情况下,中继日志被保持;relay_log_purge全局变量被静默地设置为0

CHANGE MASTER TO可以更新master.inforelay-log.info文件的内容。

当您拥有主服务器快照并拥有日志和对应的偏移量时,CHANGE MASTER对于设置从属服务器是有用的。在把快照载入从属服务器之后,您可以在从属服务器上运行CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master

举例说明:

mysql> 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;
 
mysql> CHANGE MASTER TO
    ->     RELAY_LOG_FILE='slave-relay-bin.006',
    ->     RELAY_LOG_POS=4025;

第一个例子可以更改主服务器及其二进制日志坐标。当想要设置从属服务器来复制主服务器时使用。

第二个例子显示了较少被使用的一个操作。当从属服务器含有中继日志,并且您出于某种原因想要执行此日志时使用。要这么做时,不需要连接主服务器。您只需要使用CHANGE MASTER TO并启动SQL线程(START SLAVE SQL_THREAD)。

您甚至可以在一个用于独立非从属服务器的非复制型设置中使用第二种操作,在崩溃之后进行复原。假设您的服务器已崩溃,同时您已恢复了备份。您想要重新播放服务器自己的二进制日志(不是中继日志,而是正规的二进制文件),例如名为myhost-bin.*。首先,应在安全的地方制作这些二进制日志的备份,以防您没有完全遵守以下步骤,意外地让服务器清理了二进制文件。使用SET GLOBAL relay_log_purge=0,进一步增加安全性。然后启动不含--log-bin选项的服务器。使用--replicate-same-server-id, --relay-log=myhost-bin(让服务器相信,这些正规的二进制日志是中继日志)和--skip-slave-start options选项。当服务器启动后,发布以下语句:

mysql> CHANGE MASTER TO
    ->     RELAY_LOG_FILE='myhost-bin.153',
    ->     RELAY_LOG_POS=410,
    ->     MASTER_HOST='some_dummy_string';
mysql> START SLAVE SQL_THREAD;

服务器会读取并执行自己的二进制日志,完成崩溃复原。当复原完成后,运行STOP SLAVE,关闭服务器,删除master.inforelay-log.info,并使用原来的选项重新启动服务器。

要让服务器认为它是一个从属服务器,需要指定MASTER_HOST(甚至使用假值)。

13.6.2.2. LOAD DATA FROM MASTER语法

LOAD DATA FROM MASTER

本命令用于对主服务器进行快照,并拷贝到从属服务器上。它可以更新MASTER_LOG_FILEMASTER_LOG_POS的值,这样,从属服务器就可以从正确的位置开始进行复制。使用--replicate-*-do-*--replicate-*-ignore-*选项指定的表和数据库排除规则均被兑现。--replicate-rewrite-db没有被考虑。这是因为使用本选项,用户就可以设置一个例如--replicate-rewrite-db=db1->db3--replicate-rewrite-db=db2->db3的非唯一映射。当从主服务器载入表时,该映射会使从属服务器发生混淆。

本语句的使用受以下条件的制约:

·         只对MyISAM表起作用。如果试图载入一个非MyISAM表,会导致以下错误:

·                ERROR 1189 (08S01): Net error reading from master

·         当拍摄快照时,会获得对主服务器的全局读取锁定。在载入操作期间,该锁定会阻止对主服务器的更新。

如果您正在载入大表,您可能必须对主服务器和从属服务器均增加net_read_timeoutnet_write_timeout值。请参见5.3.3节,“服务器系统变量”

注意,LOAD DATA FROM MASTER不从mysql数据库拷贝任何表。这可以更容易地让主服务器和从属服务器拥有不同的用户和权限。

LOAD DATA FROM MASTER语句要求用于连接主服务器的复制帐户,以便让主服务器拥有RELOADSUPER权限,并让所有您想要载入的主服务器表拥有SELECT权限。所有的用户不拥有SELECT权限的主服务器表均被LOAD DATA FROM MASTER忽略。这是因为主服务器会对用户隐藏它们:LOAD DATA FROM MASTER会调用SHOW DATABASES以了解要载入的主服务器数据库,但是SHOW DATABASES只会返回用户有部分权限的数据库。请参见13.5.4.6节,“SHOW DATABASES语法”。在从属服务器方面,发布LOAD DATA FROM MASTER的用户应拥有授权,以取消或创建被复制的数据库和表。

13.6.2.3. LOAD TABLE tbl_name FROM MASTER语法

LOAD TABLE tbl_name FROM MASTER

用于把表的拷贝从主服务器转移到从属服务器。本语句的主要作用是调试LOAD DATA FROM MASTER。它要求用于连接主服务器的帐户拥有对主服务器的RELOADSUPER权限,并拥有对要载入的主服务器表的SELECT权限。在从属服务器方面,发布LOAD TABLE FROM MASTER的用户应拥有取消和创建表的权限。

用于LOAD DATA FROM MASTER的条件也适用于这里。举例说明,LOAD TABLE FROM MASTER仅对于MyISAM表起作用。对LOAD DATA FROM MASTER的暂停注意也适用。

13.6.2.4. MASTER_POS_WAIT()语法

SELECT MASTER_POS_WAIT('master_log_file', master_log_pos)
这实际上是一个函数,而不是一个语句。它被用于确认,从属服务器已读取并执行了到达主服务器二进制日志的给定位置。要了解完整的描述,请参见12.9.4节,“其他函数”

13.6.2.5. RESET SLAVE语法

RESET SLAVE

用于让从属服务器忘记其在主服务器的二进制日志中的复制位置。本语句被用于进行一个明确的启动:它会删除master.inforelay-log.info文件,以及所有的中继日志,并启动一个新的中继日志。

注释:所有的中继日志被删除,即使它们没有被从属服务器SQL线程完全的执行。(如果您已经发布了一个SLAVE语句或如果从属服务器的载入量很大,则这对于一个复制从属服务器是一个很可能出现的情况。)

存储在master.info文件中的连接信息通过使用在对应的启动选项中指定的值,被立即重新设置了。此信息包括主服务器主机、主服务器接口、主服务器用户和主服务器 密码等值。当从属服务器SQL线程被中止时,它位于正在复制的临时表的中间,并且发布了RESET SLAVE,则已被复制的临时表在从属服务器中被删除。

13.6.2.6. SET GLOBAL SQL_SLAVE_SKIP_COUNTER语法

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n

从主服务器中跳过后面的n个事件。要复原由语句导致的复制中止,这是有用的。

仅当从属线程没有正在运行时,本语句时有效的。否则,会产生一个错误。

13.6.2.7. SHOW SLAVE STATUS语法

SHOW SLAVE STATUS

用于提供有关从属服务器线程的关键参数的信息。如果您使用mysql客户端发布此语句,则您可以使用一个\G语句终止符来获得更便于阅读的竖向版面,而不是使用分号:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
          Master_Host: localhost
          Master_User: root
          Master_Port: 3306
        Connect_Retry: 3
      Master_Log_File: gbichot-bin.005
  Read_Master_Log_Pos: 79
       Relay_Log_File: gbichot-relay-bin.005
        Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB:
  Replicate_Ignore_DB:
           Last_Errno: 0
           Last_Error:
         Skip_Counter: 0
  Exec_Master_Log_Pos: 79
      Relay_Log_Space: 552
      Until_Condition: None
       Until_Log_File:
        Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
      Master_SSL_Cert:
    Master_SSL_Cipher:
       Master_SSL_Key:
Seconds_Behind_Master: 8

SHOW SLAVE STATUS会返回以下字段:

·         Slave_IO_State

SHOW PROCESSLIST输出的State字段的拷贝。SHOW PROCESSLIST用于从属I/O线程。如果线程正在试图连接到主服务器,正在等待来自主服务器的时间或正在连接到主服务器等,本语句会通知您。在6.3节,“复制实施细节”中列出了可能的状态。旧版本的MySQL在连接主服务器不成功时,允许线程继续运行。对于旧版本的MySQL,观看此字段是必须的。如果它正在运行,则无问题;如果它没有运行,则您会在Last_Error字段中发现错误(后面有说明)。

·         Master_Host

当前的主服务器主机。

·         Master_User

被用于连接主服务器的当前用户。

·         Master_Port

当前的主服务器接口。

·         Connect_Retry

--master-connect-retry选项的当前值

·         Master_Log_File

I/O线程当前正在读取的主服务器二进制日志文件的名称。

·         Read_Master_Log_Pos

在当前的主服务器二进制日志中,I/O线程已经读取的位置。

·         Relay_Log_File

SQL线程当前正在读取和执行的中继日志文件的名称。

·         Relay_Log_Pos

在当前的中继日志中,SQL线程已读取和执行的位置。

·         Relay_Master_Log_File

SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。

·         Slave_IO_Running

I/O线程是否被启动并成功地连接到主服务器上。对于旧版本的MySQL(在4.1.145.0.12之前),如果I/O线程已被启动,即使从属服务器仍没有连接到主服务器上,Slave_IO_Running也将被设置到YES

·         Slave_SQL_Running

SQL线程是否被启动。

·         Replicate_Do_DB, Replicate_Ignore_DB

使用--replicate-do-db--replicate-ignore-db选项指定的数据库清单。

·         Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table

使用--replicate-do-table, --replicate-ignore-table, --replicate-wild-do-table--replicate-wild-ignore_table选项指定的表清单。

·         Last_Errno, Last_Error

被多数最近被执行的查询返回的错误数量和错误消息。错误数量为0并且消息为空字符串意味着“没有错误”。如果Last_Error值不是空值,它也会在从属服务器的错误日志中作为消息显示。

举例说明:

Last_Errno: 1051
Last_Error: error 'Unknown table 'z'' on query 'drop table z'

该消息指示,表z曾经存在于在主服务器中并已被取消了,但是它没有在从属服务器中存在过,因此对于从属服务器,DROP TABLE失败。(举例说明,在设置复制时,如果您忘记了把此表拷贝到从属服务器中,则这有可能发生。)

·         Skip_Counter

最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值。

·         Exec_Master_Log_Pos

来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置(Relay_Master_Log_File)。在主服务器的二进制日志中的(Relay_Master_Log_File, Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File, Relay_Log_Pos)

·         Relay_Log_Space

所有原有的中继日志结合起来的总大小。

·         Until_Condition, Until_Log_File, Until_Log_Pos

START SLAVE语句的UNTIL子句中指定的值。

Until_Condition具有以下值:

o        如果没有指定UNTIL子句,则没有值

o        如果从属服务器正在读取,直到达到主服务器的二进制日志的给定位置为止,则值为Master

o        如果从属服务器正在读取,直到达到其中继日志的给定位置为止,则值为Relay

Until_Log_FileUntil_Log_Pos用于指示日志文件名和位置值。日志文件名和位置值定义了SQL线程在哪个点中止执行。

·         Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, Master_SSL_Cipher, Master_SSL_Key

这些字段显示了被从属服务器使用的参数。这些参数用于连接主服务器。

Master_SSL_Allowed具有以下值:

o        如果允许对主服务器进行SSL连接,则值为Yes

o        如果不允许对主服务器进行SSL连接,则值为No

o        如果允许SSL连接,但是从属服务器没有让SSL支持被启用,则值为Ignored

SSL有关的字段的值对应于--master-ca, --master-capath, --master-cert, --master-cipher--master-key选项的值。

·         Seconds_Behind_Master

本字段是从属服务器“落后”多少的一个指示。当从属SQL线程正在运行时(处理更新),本字段为在主服务器上由此线程执行的最近的一个事件的时间标记开始,已经过的秒数。当此线程被从属服务器I/O线程赶上,并进入闲置状态,等待来自I/O线程的更多的事件时,本字段为零。总之,本字段测量从属服务器SQL线程和从属服务器I/O线程之间的时间差距,单位以秒计。

如果主服务器和从属服务器之间的网络连接较快,则从属服务器I/O线程会非常接近主服务器,所以本字段能够十分近似地指示,从属服务器SQL线程比主服务器落后多少。如果网络较慢,则这种指示不准确;从属SQL线程经常会赶上读取速度较慢地从属服务器I/O线程,因此,Seconds_Behind_Master经常显示值为0。即使I/O线程落后于主服务器时,也是如此。换句话说,本列只对速度快的网络有用。

即使主服务器和从属服务器不具有相同的时钟,时间差计算也会起作用(当从属服务器I/O线程启动时,计算时间差。并假定从此时以后,时间差保持不变)。如果从属SQL线程不运行,或者如果从属服务器I/O线程不运行或未与主服务器连接,则Seconds_Behind_MasterNULL(意义为“未知”)。举例说明,如果在重新连接之前,从属服务器I/O线程休眠了master-connect-retry秒,则显示NULL,因为从属服务器不知道主服务器正在做什么,也不能有把握地说落后多少。

本字段有一个限制。时间标记通过复制被保留,这意味着,如果一个主服务器M1本身是一个从属服务器M0,则来自M1binlog的任何事件(通过复制来自M0binlog的事件而产生),与原事件具有相同的时间标记。这可以使MySQL成功地复制TIMESTAMP。但是,Seconds_Behind_Master的缺点是,如果M1也收到来自客户端的直接更新,则值会随机变化,因为有时最近的M1时间来自M0,有时来自直接更新,最近的时间标记也是如此。

13.6.2.8. START SLAVE语法

START SLAVE [thread_type [, thread_type] ... ]
START SLAVE [SQL_THREAD] UNTIL
    MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
    RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
 
thread_type: IO_THREAD | SQL_THREAD

不含选项的START SLAVE会同时启动两个从属服务器线程。I/O线程从主服务器中读取查询,并把它们存储在中继日志中。SQL线程读取中继日志并执行查询。START SLAVE要求SUPER权限。

如果START SLAVE成功地启动了从属服务器线程,则会返回,不会出现错误。但是,即使在此情况下,也有可能出现这样的现象——服务器线程启动了,然后又停止了(例如,因为它们没有成功地连接到主服务器上,或者没有能读取二进制日志,或者出现了其它问题)。START SLAVE对此不会发出警告。您必须检查从属服务器的错误日志,查看是否有由从属服务器线程产生的错误消息,或者使用SHOW SLAVE STATUS检查它们是否运行正常。

您可以把IO_THREADSQL_THREAD选项添加到语句中,指明哪些线程将要启动。

可以添加一个UNTIL子句,指定从属服务器应启动并运行,直到SQL线程达到主服务器二进制日志中的一个给定点为止。当SQL线程达到此点时,它会停止。如果在该语句中指定了SQL_THREAD选项,则它只会启动SQL线程。否则,它会同时启动两个从属服务器线程。如果SQL线程正在运行,则UNTIL子句被忽略,并发布一个警告。

对于一个UNTIL子句,您必须同时指定一个日志文件名和位置。不要把主服务器和中继日志选项混合在一起。

UNTIL条件由一个后续的STOP SLAVE语句,或一个不包括UNTIL子句的START SLAVE语句,或一个服务器重启命令重新设置。

UNTIL子句对于调试复制操作是有用的,或者可用于促使复制操作继续,直到接近一个特定的点时为止,在此点,您想要避免让从属服务器复制一个语句。举例说明,如果在主服务上执行了一个不明智的DROP TABLE语句,您可以使用UNTIL来告知从属服务器,执行到此点就停止,不要再继续了。要查找该事件是什么,需对主服务器日志或从属中继日志使用mysqlbinlog,或通过使用SHOW BINLOG EVENTS语句。

如果您正在使用UNTIL,让从属服务器成段地处理已复制的查询,则建议您使用--skip-slave-start选项来启动从属服务器,以防止当从属服务器启动时,SQL线程运行。最好在一个选项文件中使用此选项,而不是在命令行中使用,这样,如果发生了意料外的服务器重新启动,它也不会被忘记。

SHOW SLAVE STATUS语句包括了输出字段。这些字段显示了UNTIL条件的当前值。

在以前版本的MySQL中,本语句被称为SLAVE START。在MySQL 5.1中仍然接受这种用法,以便与以前版本兼容。但现在不赞成使用。

13.6.2.9. STOP SLAVE语法
 

STOP SLAVE [thread_type [, thread_type] ... ]
 
thread_type: IO_THREAD | SQL_THREAD

用于中止从属服务器线程。STOP SLAVE要求SUPER权限。

START SLAVE相似,本语句在使用时可以加IO_THREADSQL_THREAD选项,指明将被中止的线程。

在以前版本的MySQL中,本语句被称为SLAVE STOP。在MySQL 5.1中仍然接受这种用法,以便与以前版本兼容。但是现在不赞成使用。