MySQL/MariaDB数据库的复制监控和维护

      MySQL/MariaDB数据库的复制监控和维护

                       作者:尹正杰 

版权声明:原创作品,谢绝转载!否则将追究法律责任。

 

 

一.清理日志

1>.删除指定日志文件名称之前的日志(也可用基于时间)

MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       351 |
| mysql-bin.000002 |       375 |
| mysql-bin.000003 |      1014 |
| mysql-bin.000004 |      1303 |
| mysql-bin.000005 |      1141 |
+------------------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003';          #删除"mysql-bin.000003"之前的日志
Query OK, 0 rows affected (0.01 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |      1014 |
| mysql-bin.000004 |      1303 |
| mysql-bin.000005 |      1141 |
+------------------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total 16
-rw-rw---- 1 mysql mysql 1014 Nov  4 19:52 mysql-bin.000003
-rw-rw---- 1 mysql mysql 1303 Nov  4 21:53 mysql-bin.000004
-rw-rw---- 1 mysql mysql 1141 Nov  4 22:06 mysql-bin.000005
-rw-rw---- 1 mysql mysql   90 Nov  4 22:21 mysql-bin.index
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index
/data/logbin/mysql-bin.000003
/data/logbin/mysql-bin.000004
/data/logbin/mysql-bin.000005
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003';        #删除"mysql-bin.000003"之前的日志
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total 16
-rw-rw---- 1 mysql mysql 1014 Nov  4 19:52 mysql-bin.000003
-rw-rw---- 1 mysql mysql 1303 Nov  4 21:53 mysql-bin.000004
-rw-rw---- 1 mysql mysql 1141 Nov  4 22:06 mysql-bin.000005
-rw-rw---- 1 mysql mysql   90 Nov  4 22:21 mysql-bin.index
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |      1014 |
| mysql-bin.000004 |      1303 |
| mysql-bin.000005 |      1141 |
+------------------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> PURGE BINARY LOGS BEFORE '2019-11-4 22:00:00';
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000005 |      1141 |
+------------------+-----------+
row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total 8
-rw-rw---- 1 mysql mysql 1141 Nov  4 22:06 mysql-bin.000005
-rw-rw---- 1 mysql mysql   30 Nov  4 22:27 mysql-bin.index
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index
/data/logbin/mysql-bin.000005
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> PURGE BINARY LOGS BEFORE '2019-11-4 22:00:00';     #删除"2019-11-4 22:00:00"之前的日志

2>.删除所有二进制日志文件,并重新生成日志文件

  RESET MASTER [TO #]; 删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从1开始,一般是master主机第一次启动时执行,MariaDB10.1.6开始支持TO #
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000005 |      1188 |
| mysql-bin.000006 |       432 |
| mysql-bin.000007 |       432 |
| mysql-bin.000008 |       432 |
| mysql-bin.000009 |       432 |
| mysql-bin.000010 |       432 |
| mysql-bin.000011 |       385 |
+------------------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total 32
-rw-rw---- 1 mysql mysql 1188 Nov  4 22:30 mysql-bin.000005
-rw-rw---- 1 mysql mysql  432 Nov  4 22:30 mysql-bin.000006
-rw-rw---- 1 mysql mysql  432 Nov  4 22:30 mysql-bin.000007
-rw-rw---- 1 mysql mysql  432 Nov  4 22:30 mysql-bin.000008
-rw-rw---- 1 mysql mysql  432 Nov  4 22:30 mysql-bin.000009
-rw-rw---- 1 mysql mysql  432 Nov  4 22:30 mysql-bin.000010
-rw-rw---- 1 mysql mysql  385 Nov  4 22:30 mysql-bin.000011
-rw-rw---- 1 mysql mysql  210 Nov  4 22:30 mysql-bin.index
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> RESET MASTER;                #删除所有二进制文件,并重新生产日志文件,文件名称从默认从1开始计数。
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total 8
-rw-rw---- 1 mysql mysql 328 Nov  4 22:31 mysql-bin.000001
-rw-rw---- 1 mysql mysql  30 Nov  4 22:31 mysql-bin.index
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> RESET MASTER;           #删除所有二进制文件,并重新生产日志文件,文件名称从默认从1开始计数。
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       375 |
| mysql-bin.000002 |       418 |
| mysql-bin.000003 |       418 |
| mysql-bin.000004 |       418 |
| mysql-bin.000005 |       418 |
| mysql-bin.000006 |       418 |
| mysql-bin.000007 |       371 |
+------------------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total 32
-rw-rw---- 1 mysql mysql 375 Nov  4 22:33 mysql-bin.000001
-rw-rw---- 1 mysql mysql 418 Nov  4 22:33 mysql-bin.000002
-rw-rw---- 1 mysql mysql 418 Nov  4 22:33 mysql-bin.000003
-rw-rw---- 1 mysql mysql 418 Nov  4 22:33 mysql-bin.000004
-rw-rw---- 1 mysql mysql 418 Nov  4 22:33 mysql-bin.000005
-rw-rw---- 1 mysql mysql 418 Nov  4 22:33 mysql-bin.000006
-rw-rw---- 1 mysql mysql 371 Nov  4 22:33 mysql-bin.000007
-rw-rw---- 1 mysql mysql 210 Nov  4 22:33 mysql-bin.index
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> RESET MASTER TO 3;        #删除所有二进制日志并指定起始文件名称数字为3
Query OK, 0 rows affected (0.01 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total 8
-rw-rw---- 1 mysql mysql 328 Nov  4 22:34 mysql-bin.000003
-rw-rw---- 1 mysql mysql  30 Nov  4 22:34 mysql-bin.index
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       328 |
+------------------+-----------+
row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> RESET MASTER TO 3;        #删除所有二进制日志并指定起始文件名称数字为3

3>.重置slave节点的配置信息

[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: sslcopy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-102.000003
          Read_Master_Log_Pos: 1239
               Relay_Log_File: relay-log-103.000002
                Relay_Log_Pos: 1353
        Relay_Master_Log_File: master-102.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1239
              Relay_Log_Space: 1645
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.30.1.102
                  Master_User: sslcopy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-102.000003
          Read_Master_Log_Pos: 1239
               Relay_Log_File: relay-log-103.000002
                Relay_Log_Pos: 1353
        Relay_Master_Log_File: master-102.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1239
              Relay_Log_Space: 1645
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> RESET SLAVE;      #它会自动删除master.info和relay-log.info文件,所有中继日志文件,并启动新的中继日志文件。
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.30.1.102
                  Master_User: sslcopy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-log-103.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 264
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [(none)]>  
MariaDB [(none)]> 
MariaDB [(none)]> HELP RESET SLAVE;
Name: 'RESET SLAVE'
Description:
Syntax:
RESET SLAVE [ALL]

RESET SLAVE makes the slave forget its replication position in the
master's binary log. This statement is meant to be used for a clean
start: It deletes the master.info and relay-log.info files, all the
relay log files, and starts a new relay log file. To use RESET SLAVE,
the slave replication threads must be stopped (use STOP SLAVE if
necessary).

*Note*: All relay log files are deleted, even if they have not been
completely executed by the slave SQL thread. (This is a condition
likely to exist on a replication slave if you have issued a STOP SLAVE
statement or if the slave is highly loaded.)

In MySQL 5.5 (unlike the case in MySQL 5.1 and earlier), RESET SLAVE
does not change any replication connection parameters such as master
host, master port, master user, or master password, which are retained
in memory. This means that START SLAVE can be issued without requiring
a CHANGE MASTER TO statement following RESET SLAVE.

In MySQL 5.5.16 and later, you can use RESET SLAVE ALL to reset these
connection parameters (Bug #11809016). Connection parameters are also
reset if the slave mysqld is shut down.

If the slave SQL thread was in the middle of replicating temporary
tables when it was stopped, and RESET SLAVE is issued, these replicated
temporary tables are deleted on the slave.

URL: http://dev.mysql.com/doc/refman/5.5/en/reset-slave.html


MariaDB [(none)]> 
MariaDB [(none)]> RESET SLAVE;     #它会自动删除master.info和relay-log.info文件,所有中继日志文件,并启动新的中继日志文件。

 

二.复制监控

1>.查看所有可用的二进制日志(要求数据库需要开启二进制日志功能)

[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 144
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-102.000001 |     26813 |
| master-102.000002 |    921736 |
| master-102.000003 |      1239 |
+-------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW MASTER LOGS;
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 152
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-102.000001 |     26813 |
| master-102.000002 |    921736 |
| master-102.000003 |      1239 |
+-------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-102.000001 |     26813 |
| master-102.000002 |    921736 |
| master-102.000003 |      1239 |
+-------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# 
MariaDB [(none)]> SHOW BINARY LOGS;
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 147
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW BINLOG EVENTS\G
*************************** 1. row ***************************
   Log_name: master-102.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 102
End_log_pos: 245
       Info: Server ver: 5.5.64-MariaDB, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: master-102.000001
        Pos: 245
 Event_type: Query
  Server_id: 102
End_log_pos: 321
       Info: use `mysql`; flush tables
*************************** 3. row ***************************
   Log_name: master-102.000001
        Pos: 321
 Event_type: Query
  Server_id: 102
End_log_pos: 2079
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS db (   Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Use
r char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'*************************** 4. row ***************************
   Log_name: master-102.000001
        Pos: 2079
 Event_type: Query
  Server_id: 102
End_log_pos: 3733
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS host (  Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Se
lect_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges;  Merged with database privileges'*************************** 5. row ***************************
   Log_name: master-102.000001
        Pos: 3733
 Event_type: Query
  Server_id: 102
End_log_pos: 6737
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS user (   Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL,
 Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0  NOT NULL, max_updates int(11) unsigned DEFAULT 0  NOT NULL, max_connections int(11) unsigned DEFAULT 0  NOT NULL, max_user_connections int(11) DEFAULT 0  NOT NULL, plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, authentication_string TEXT NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'*************************** 6. row ***************************
   Log_name: master-102.000001
        Pos: 6737
 Event_type: Query
  Server_id: 102
End_log_pos: 7123
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS func (  name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl ch
ar(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='User defined functions'*************************** 7. row ***************************
   Log_name: master-102.000001
        Pos: 7123
 Event_type: Query
  Server_id: 102
End_log_pos: 7399
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS plugin ( name varchar(64) DEFAULT '' NOT NULL, dl varchar(128) DEFAULT '' NOT NULL, PRIMARY 
KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci comment='MySQL plugins'*************************** 8. row ***************************
   Log_name: master-102.000001
        Pos: 7399
 Event_type: Query
  Server_id: 102
End_log_pos: 7916
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db ch
ar(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'*************************** 9. row ***************************
   Log_name: master-102.000001
        Pos: 7916
 Event_type: Query
  Server_id: 102
End_log_pos: 8681
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NU
LL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Table privileges'*************************** 10. row ***************************
   Log_name: master-102.000001
        Pos: 8681
 Event_type: Query
  Server_id: 102
End_log_pos: 9262
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT N
ULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Column privileges'*************************** 11. row ***************************
   Log_name: master-102.000001
        Pos: 9262
 Event_type: Query
  Server_id: 102
End_log_pos: 9646
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char(64) not null, help_category_id s
mallint unsigned not null, description text not null, example text not null, url text not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'*************************** 12. row ***************************
   Log_name: master-102.000001
        Pos: 9646
 Event_type: Query
  Server_id: 102
End_log_pos: 9997
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS help_category ( help_category_id smallint unsigned not null, name  char(64) not null, parent
_category_id smallint unsigned null, url text not null, primary key (help_category_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help categories'*************************** 13. row ***************************
   Log_name: master-102.000001
        Pos: 9997
 Event_type: Query
  Server_id: 102
End_log_pos: 10339
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id  
int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'*************************** 14. row ***************************
   Log_name: master-102.000001
        Pos: 10339
 Event_type: Query
  Server_id: 102
End_log_pos: 10620
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS help_keyword (   help_keyword_id  int unsigned not null, name char(64) not null, primary key
 (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'*************************** 15. row ***************************
   Log_name: master-102.000001
        Pos: 10620
 Event_type: Query
  Server_id: 102
End_log_pos: 10876
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS time_zone_name (   Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY N
ame (Name) ) engine=MyISAM CHARACTER SET utf8   comment='Time zone names'*************************** 16. row ***************************
   Log_name: master-102.000001
        Pos: 10876
 Event_type: Query
  Server_id: 102
End_log_pos: 11198
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS time_zone (   Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','
N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8   comment='Time zones'*************************** 17. row ***************************
   Log_name: master-102.000001
        Pos: 11198
 Event_type: Query
  Server_id: 102
End_log_pos: 11557
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS time_zone_transition (   Time_zone_id int unsigned NOT NULL, Transition_time bigint signed N
OT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8   comment='Time zone transitions'*************************** 18. row ***************************
   Log_name: master-102.000001
        Pos: 11557
 Event_type: Query
  Server_id: 102
End_log_pos: 12010
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS time_zone_transition_type (   Time_zone_id int unsigned NOT NULL, Transition_type_id int uns
igned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8   comment='Time zone transition types'*************************** 19. row ***************************
   Log_name: master-102.000001
        Pos: 12010
 Event_type: Query
  Server_id: 102
End_log_pos: 12324
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS time_zone_leap_second (   Transition_time bigint signed NOT NULL, Correction int signed NOT 
NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8   comment='Leap seconds information for time zones'*************************** 20. row ***************************
   Log_name: master-102.000001
        Pos: 12324
 Event_type: Query
  Server_id: 102
End_log_pos: 13976
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, t
ype enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures'*************************** 21. row ***************************
   Log_name: master-102.000001
        Pos: 13976
 Event_type: Query
  Server_id: 102
End_log_pos: 14636
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NUL
L, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) COLLATE utf8_general_ci DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin   comment='Procedure privileges'*************************** 22. row ***************************
   Log_name: master-102.000001
        Pos: 14636
 Event_type: Query
  Server_id: 102
End_log_pos: 14986
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP(6) NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id INTE
GER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="General log"*************************** 23. row ***************************
   Log_name: master-102.000001
        Pos: 14986
 Event_type: Query
  Server_id: 102
End_log_pos: 15471
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP(6) NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME(6
) NOT NULL, lock_time TIME(6) NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"*************************** 24. row ***************************
   Log_name: master-102.000001
        Pos: 15471
 Event_type: Query
  Server_id: 102
End_log_pos: 17451
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) C
HARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'*************************** 25. row ***************************
   Log_name: master-102.000001
        Pos: 17451
 Event_type: Query
  Server_id: 102
End_log_pos: 17826
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR(255) NOT NULL, epoch BIGIN
T UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL, updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM*************************** 26. row ***************************
   Log_name: master-102.000001
        Pos: 17826
 Event_type: Query
  Server_id: 102
End_log_pos: 18371
       Info: use `mysql`; CREATE TABLE IF NOT EXISTS proxies_priv (Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT 
NULL, Proxied_host char(60) binary DEFAULT '' NOT NULL, Proxied_user char(16) binary DEFAULT '' NOT NULL, With_grant BOOL DEFAULT 0 NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp, PRIMARY KEY Host (Host,User,Proxied_host,Proxied_user), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User proxy privileges'*************************** 27. row ***************************
   Log_name: master-102.000001
        Pos: 18371
 Event_type: Query
  Server_id: 102
End_log_pos: 18490
       Info: DROP DATABASE IF EXISTS performance_schema
*************************** 28. row ***************************
   Log_name: master-102.000001
        Pos: 18490
 Event_type: Query
  Server_id: 102
End_log_pos: 18620
       Info: CREATE DATABASE performance_schema character set utf8
*************************** 29. row ***************************
   Log_name: master-102.000001
        Pos: 18620
 Event_type: Query
  Server_id: 102
End_log_pos: 18821
       Info: use `mysql`; CREATE TABLE performance_schema.cond_instances(NAME VARCHAR(128) not null,OBJECT_INSTANCE_BEGIN BIGINT not null)ENGINE=
PERFORMANCE_SCHEMA*************************** 30. row ***************************
   Log_name: master-102.000001
        Pos: 18821
 Event_type: Query
  Server_id: 102
End_log_pos: 19412
       Info: use `mysql`; CREATE TABLE performance_schema.events_waits_current(THREAD_ID INTEGER not null,EVENT_ID BIGINT unsigned not null,EVENT
_NAME VARCHAR(128) not null,SOURCE VARCHAR(64),TIMER_START BIGINT unsigned,TIMER_END BIGINT unsigned,TIMER_WAIT BIGINT unsigned,SPINS INTEGER unsigned,OBJECT_SCHEMA VARCHAR(64),OBJECT_NAME VARCHAR(512),OBJECT_TYPE VARCHAR(64),OBJECT_INSTANCE_BEGIN BIGINT not null,NESTING_EVENT_ID BIGINT unsigned,OPERATION VARCHAR(16) not null,NUMBER_OF_BYTES BIGINT unsigned,FLAGS INTEGER unsigned)ENGINE=PERFORMANCE_SCHEMA*************************** 31. row ***************************
   Log_name: master-102.000001
        Pos: 19412
 Event_type: Query
  Server_id: 102
End_log_pos: 20003
       Info: use `mysql`; CREATE TABLE performance_schema.events_waits_history(THREAD_ID INTEGER not null,EVENT_ID BIGINT unsigned not null,EVENT
_NAME VARCHAR(128) not null,SOURCE VARCHAR(64),TIMER_START BIGINT unsigned,TIMER_END BIGINT unsigned,TIMER_WAIT BIGINT unsigned,SPINS INTEGER unsigned,OBJECT_SCHEMA VARCHAR(64),OBJECT_NAME VARCHAR(512),OBJECT_TYPE VARCHAR(64),OBJECT_INSTANCE_BEGIN BIGINT not null,NESTING_EVENT_ID BIGINT unsigned,OPERATION VARCHAR(16) not null,NUMBER_OF_BYTES BIGINT unsigned,FLAGS INTEGER unsigned)ENGINE=PERFORMANCE_SCHEMA*************************** 32. row ***************************
   Log_name: master-102.000001
        Pos: 20003
 Event_type: Query
  Server_id: 102
End_log_pos: 20599
       Info: use `mysql`; CREATE TABLE performance_schema.events_waits_history_long(THREAD_ID INTEGER not null,EVENT_ID BIGINT unsigned not null,
EVENT_NAME VARCHAR(128) not null,SOURCE VARCHAR(64),TIMER_START BIGINT unsigned,TIMER_END BIGINT unsigned,TIMER_WAIT BIGINT unsigned,SPINS INTEGER unsigned,OBJECT_SCHEMA VARCHAR(64),OBJECT_NAME VARCHAR(512),OBJECT_TYPE VARCHAR(64),OBJECT_INSTANCE_BEGIN BIGINT not null,NESTING_EVENT_ID BIGINT unsigned,OPERATION VARCHAR(16) not null,NUMBER_OF_BYTES BIGINT unsigned,FLAGS INTEGER unsigned)ENGINE=PERFORMANCE_SCHEMA*************************** 33. row ***************************
   Log_name: master-102.000001
        Pos: 20599
 Event_type: Query
  Server_id: 102
End_log_pos: 21020
       Info: use `mysql`; CREATE TABLE performance_schema.events_waits_summary_by_instance(EVENT_NAME VARCHAR(128) not null,OBJECT_INSTANCE_BEGIN
 BIGINT not null,COUNT_STAR BIGINT unsigned not null,SUM_TIMER_WAIT BIGINT unsigned not null,MIN_TIMER_WAIT BIGINT unsigned not null,AVG_TIMER_WAIT BIGINT unsigned not null,MAX_TIMER_WAIT BIGINT unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** 34. row ***************************
   Log_name: master-102.000001
        Pos: 21020
 Event_type: Query
  Server_id: 102
End_log_pos: 21442
       Info: use `mysql`; CREATE TABLE performance_schema.events_waits_summary_by_thread_by_event_name(THREAD_ID INTEGER not null,EVENT_NAME VARC
HAR(128) not null,COUNT_STAR BIGINT unsigned not null,SUM_TIMER_WAIT BIGINT unsigned not null,MIN_TIMER_WAIT BIGINT unsigned not null,AVG_TIMER_WAIT BIGINT unsigned not null,MAX_TIMER_WAIT BIGINT unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** 35. row ***************************
   Log_name: master-102.000001
        Pos: 21442
 Event_type: Query
  Server_id: 102
End_log_pos: 21834
       Info: use `mysql`; CREATE TABLE performance_schema.events_waits_summary_global_by_event_name(EVENT_NAME VARCHAR(128) not null,COUNT_STAR B
IGINT unsigned not null,SUM_TIMER_WAIT BIGINT unsigned not null,MIN_TIMER_WAIT BIGINT unsigned not null,AVG_TIMER_WAIT BIGINT unsigned not null,MAX_TIMER_WAIT BIGINT unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** 36. row ***************************
   Log_name: master-102.000001
        Pos: 21834
 Event_type: Query
  Server_id: 102
End_log_pos: 22072
       Info: use `mysql`; CREATE TABLE performance_schema.file_instances(FILE_NAME VARCHAR(512) not null,EVENT_NAME VARCHAR(128) not null,OPEN_CO
UNT INTEGER unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** 37. row ***************************
   Log_name: master-102.000001
        Pos: 22072
 Event_type: Query
  Server_id: 102
End_log_pos: 22427
       Info: use `mysql`; CREATE TABLE performance_schema.file_summary_by_event_name(EVENT_NAME VARCHAR(128) not null,COUNT_READ BIGINT unsigned 
not null,COUNT_WRITE BIGINT unsigned not null,SUM_NUMBER_OF_BYTES_READ BIGINT unsigned not null,SUM_NUMBER_OF_BYTES_WRITE BIGINT unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** 38. row ***************************
   Log_name: master-102.000001
        Pos: 22427
 Event_type: Query
  Server_id: 102
End_log_pos: 22812
       Info: use `mysql`; CREATE TABLE performance_schema.file_summary_by_instance(FILE_NAME VARCHAR(512) not null,EVENT_NAME VARCHAR(128) not nu
ll,COUNT_READ BIGINT unsigned not null,COUNT_WRITE BIGINT unsigned not null,SUM_NUMBER_OF_BYTES_READ BIGINT unsigned not null,SUM_NUMBER_OF_BYTES_WRITE BIGINT unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** 39. row ***************************
   Log_name: master-102.000001
        Pos: 22812
 Event_type: Query
  Server_id: 102
End_log_pos: 23042
       Info: use `mysql`; CREATE TABLE performance_schema.mutex_instances(NAME VARCHAR(128) not null,OBJECT_INSTANCE_BEGIN BIGINT not null,LOCKED
_BY_THREAD_ID INTEGER)ENGINE=PERFORMANCE_SCHEMA*************************** 40. row ***************************
   Log_name: master-102.000001
        Pos: 23042
 Event_type: Query
  Server_id: 102
End_log_pos: 23339
       Info: use `mysql`; CREATE TABLE performance_schema.performance_timers(TIMER_NAME ENUM ('CYCLE', 'NANOSECOND', 'MICROSECOND', 'MILLISECOND'
, 'TICK') not null,TIMER_FREQUENCY BIGINT,TIMER_RESOLUTION BIGINT,TIMER_OVERHEAD BIGINT) ENGINE=PERFORMANCE_SCHEMA*************************** 41. row ***************************
   Log_name: master-102.000001
        Pos: 23339
 Event_type: Query
  Server_id: 102
End_log_pos: 23623
       Info: use `mysql`; CREATE TABLE performance_schema.rwlock_instances(NAME VARCHAR(128) not null,OBJECT_INSTANCE_BEGIN BIGINT not null,WRITE
_LOCKED_BY_THREAD_ID INTEGER,READ_LOCKED_BY_COUNT INTEGER unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** 42. row ***************************
   Log_name: master-102.000001
        Pos: 23623
 Event_type: Query
  Server_id: 102
End_log_pos: 23822
       Info: use `mysql`; CREATE TABLE performance_schema.setup_consumers(NAME VARCHAR(64) not null,ENABLED ENUM ('YES', 'NO') not null)ENGINE=PE
RFORMANCE_SCHEMA*************************** 43. row ***************************
   Log_name: master-102.000001
        Pos: 23822
 Event_type: Query
  Server_id: 102
End_log_pos: 24058
       Info: use `mysql`; CREATE TABLE performance_schema.setup_instruments(NAME VARCHAR(128) not null,ENABLED ENUM ('YES', 'NO') not null,TIMED 
ENUM ('YES', 'NO') not null)ENGINE=PERFORMANCE_SCHEMA*************************** 44. row ***************************
   Log_name: master-102.000001
        Pos: 24058
 Event_type: Query
  Server_id: 102
End_log_pos: 24305
       Info: use `mysql`; CREATE TABLE performance_schema.setup_timers(NAME VARCHAR(64) not null,TIMER_NAME ENUM ('CYCLE', 'NANOSECOND', 'MICROSE
COND', 'MILLISECOND', 'TICK') not null)ENGINE=PERFORMANCE_SCHEMA*************************** 45. row ***************************
   Log_name: master-102.000001
        Pos: 24305
 Event_type: Query
  Server_id: 102
End_log_pos: 24511
       Info: use `mysql`; CREATE TABLE performance_schema.threads(THREAD_ID INTEGER not null,PROCESSLIST_ID INTEGER,NAME VARCHAR(128) not null)EN
GINE=PERFORMANCE_SCHEMA*************************** 46. row ***************************
   Log_name: master-102.000001
        Pos: 24511
 Event_type: Query
  Server_id: 102
End_log_pos: 24609
       Info: use `mysql`; drop procedure if exists mysql.die
*************************** 47. row ***************************
   Log_name: master-102.000001
        Pos: 24609
 Event_type: Query
  Server_id: 102
End_log_pos: 24823
       Info: use `mysql`; CREATE DEFINER=``@`` PROCEDURE `mysql`.`die`()
signal sqlstate 'HY000' set message_text='Unexpected content found in the performance_schema database.'
*************************** 48. row ***************************
   Log_name: master-102.000001
        Pos: 24823
 Event_type: Query
  Server_id: 102
End_log_pos: 24911
       Info: use `mysql`; drop procedure mysql.die
*************************** 49. row ***************************
   Log_name: master-102.000001
        Pos: 24911
 Event_type: Query
  Server_id: 102
End_log_pos: 24980
       Info: BEGIN
*************************** 50. row ***************************
   Log_name: master-102.000001
        Pos: 24980
 Event_type: Table_map
  Server_id: 102
End_log_pos: 25089
       Info: table_id: 2 (mysql.db)
*************************** 51. row ***************************
   Log_name: master-102.000001
        Pos: 25089
 Event_type: Write_rows
  Server_id: 102
End_log_pos: 25183
       Info: table_id: 2 flags: STMT_END_F
*************************** 52. row ***************************
   Log_name: master-102.000001
        Pos: 25183
 Event_type: Query
  Server_id: 102
End_log_pos: 25253
       Info: COMMIT
*************************** 53. row ***************************
   Log_name: master-102.000001
        Pos: 25253
 Event_type: Query
  Server_id: 102
End_log_pos: 25390
       Info: DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_db` /* generated by server */
*************************** 54. row ***************************
   Log_name: master-102.000001
        Pos: 25390
 Event_type: Query
  Server_id: 102
End_log_pos: 25459
       Info: BEGIN
*************************** 55. row ***************************
   Log_name: master-102.000001
        Pos: 25459
 Event_type: Table_map
  Server_id: 102
End_log_pos: 25621
       Info: table_id: 3 (mysql.user)
*************************** 56. row ***************************
   Log_name: master-102.000001
        Pos: 25621
 Event_type: Write_rows
  Server_id: 102
End_log_pos: 26137
       Info: table_id: 3 flags: STMT_END_F
*************************** 57. row ***************************
   Log_name: master-102.000001
        Pos: 26137
 Event_type: Query
  Server_id: 102
End_log_pos: 26207
       Info: COMMIT
*************************** 58. row ***************************
   Log_name: master-102.000001
        Pos: 26207
 Event_type: Query
  Server_id: 102
End_log_pos: 26346
       Info: DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_user` /* generated by server */
*************************** 59. row ***************************
   Log_name: master-102.000001
        Pos: 26346
 Event_type: Query
  Server_id: 102
End_log_pos: 26415
       Info: BEGIN
*************************** 60. row ***************************
   Log_name: master-102.000001
        Pos: 26415
 Event_type: Table_map
  Server_id: 102
End_log_pos: 26483
       Info: table_id: 4 (mysql.proxies_priv)
*************************** 61. row ***************************
   Log_name: master-102.000001
        Pos: 26483
 Event_type: Write_rows
  Server_id: 102
End_log_pos: 26577
       Info: table_id: 4 flags: STMT_END_F
*************************** 62. row ***************************
   Log_name: master-102.000001
        Pos: 26577
 Event_type: Query
  Server_id: 102
End_log_pos: 26647
       Info: COMMIT
*************************** 63. row ***************************
   Log_name: master-102.000001
        Pos: 26647
 Event_type: Query
  Server_id: 102
End_log_pos: 26794
       Info: DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_proxies_priv` /* generated by server */
*************************** 64. row ***************************
   Log_name: master-102.000001
        Pos: 26794
 Event_type: Stop
  Server_id: 102
End_log_pos: 26813
       Info: 
64 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW BINLOG EVENTS\G      #比上面的命令查询的二进制日志更详细

2>.查看正在使用的二进制日志

[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 144
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-102.000001 |     26813 |
| master-102.000002 |    921736 |
| master-102.000003 |      1239 |
+-------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-102.000003 |     1239 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SHOW MASTER STATUS;

3>.查看slave节点的状态,可用查看到主从复制是否正常运行

[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: sslcopy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-102.000003
          Read_Master_Log_Pos: 1239
               Relay_Log_File: relay-log-103.000002
                Relay_Log_Pos: 1353
        Relay_Master_Log_File: master-102.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1239
              Relay_Log_Space: 1645
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]# 
MariaDB [(none)]> SHOW SLAVE STATUS\G

4>.查看当前节点正在运行的进程信息

[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW PROCESSLIST\G
*************************** 1. row ***************************
      Id: 3
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 1602
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 4
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 1164
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 9
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: SHOW PROCESSLIST
Progress: 0.000
3 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]# 
MariaDB [(none)]> SHOW PROCESSLIST\G

 

三.从服务器是否落后于主服务

[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: sslcopy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-102.000003
          Read_Master_Log_Pos: 1239
               Relay_Log_File: relay-log-103.000002
                Relay_Log_Pos: 1353
        Relay_Master_Log_File: master-102.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1239
              Relay_Log_Space: 1645
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
注意观察上面的输出信息,尤其要观察下面的值是否为0,如果为0说明复制不存在延迟,若不为0说明从库数据和主库数据延迟情况,我建议大家生产环境中要监控该参数项。
  Seconds_Behind_Master: 0

 

四.如何确定主从节点数据是否一致

  可用借助于第三方工具,比如:percona-tools

 

五.数据不一致如何修复

  删除从数据库,重新复制

 

六.复制的问题和解决方案

  1>.数据损坏或丢失
      Master: MHA + semi repl
      Slave: 重新复制
  2>.混合使用存储引擎
      MyISAM:不支持事务
      InnoDB: 支持事务
  3>.不惟一的server id
      重新复制
  4>.复制延迟
      需要额外的监控工具的辅助
      一从多主:mariadb10版后支持
      多线程复制:对多个数据库复制

 

posted @ 2019-11-11 05:33  尹正杰  阅读(389)  评论(0编辑  收藏  举报