MySQL/MariaDB数据库的复制监控和维护
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.清理日志
1>.删除指定日志文件名称之前的日志（也可用基于时间）
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003'; #删除"mysql-bin.000003"之前的日志
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index
/data/logbin/mysql-bin.
/data/logbin/mysql-bin.
/data/logbin/mysql-bin.
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003'; #删除"mysql-bin.000003"之前的日志
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> PURGE BINARY LOGS BEFORE '2019-11-4 22:00:00';
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index
/data/logbin/mysql-bin.
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..6开始支持TO #
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> RESET MASTER; #删除所有二进制文件，并重新生产日志文件，文件名称从默认从1开始计数。
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
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. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> RESET MASTER TO ; #删除所有二进制日志并指定起始文件名称数字为3
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: sslcopy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000002
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> STOP SLAVE;
Query OK, rows affected (0.01 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 172.30.1.102
Master_User: sslcopy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000002
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> RESET SLAVE; #它会自动删除master.info和relay-log.info文件，所有中继日志文件，并启动新的中继日志文件。
Query OK, rows affected (0.02 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 172.30.1.102
Master_User: sslcopy
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000001
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
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. and later, you can use RESET SLAVE ALL to reset these
connection parameters (Bug #). 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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-102.000001 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Format_desc
Server_id:
End_log_pos:
Info: Server ver: 5.5.-MariaDB, Binlog ver:
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; flush tables
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS db ( Host char() binary DEFAULT '' NOT NULL, Db char() binary DEFAULT '' NOT NULL, Use
r char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS host ( Host char() binary DEFAULT '' NOT NULL, Db char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS user ( Host char() binary DEFAULT '' NOT NULL, User char() binary DEFAULT '' NOT NULL,
Password char() 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() unsigned DEFAULT NOT NULL, max_updates int() unsigned DEFAULT NOT NULL, max_connections int() unsigned DEFAULT NOT NULL, max_user_connections int() DEFAULT NOT NULL, plugin char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS func ( name char() binary DEFAULT '' NOT NULL, ret tinyint() DEFAULT '' NOT NULL, dl ch
ar() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS plugin ( name varchar() DEFAULT '' NOT NULL, dl varchar() DEFAULT '' NOT NULL, PRIMARY
KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci comment='MySQL plugins'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS servers ( Server_name char() NOT NULL DEFAULT '', Host char() NOT NULL DEFAULT '', Db ch
ar() NOT NULL DEFAULT '', Username char() NOT NULL DEFAULT '', Password char() NOT NULL DEFAULT '', Port INT() NOT NULL DEFAULT '', Socket char() NOT NULL DEFAULT '', Wrapper char() NOT NULL DEFAULT '', Owner char() NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS tables_priv ( Host char() binary DEFAULT '' NOT NULL, Db char() binary DEFAULT '' NOT NU
LL, User char() binary DEFAULT '' NOT NULL, Table_name char() binary DEFAULT '' NOT NULL, Grantor char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS columns_priv ( Host char() binary DEFAULT '' NOT NULL, Db char() binary DEFAULT '' NOT N
ULL, User char() binary DEFAULT '' NOT NULL, Table_name char() binary DEFAULT '' NOT NULL, Column_name char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS help_topic ( help_topic_id int unsigned not null, name char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS help_category ( help_category_id smallint unsigned not null, name char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS help_keyword ( help_keyword_id int unsigned not null, name char() not null, primary key
(help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS time_zone_name ( Name char() NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY N
ame (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
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:
Event_type: Query
Server_id:
End_log_pos:
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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
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 NOT NULL, Is_DST tinyint unsigned DEFAULT NOT NULL, Abbreviation char() DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS proc (db char() collate utf8_bin DEFAULT '' NOT NULL, name char() DEFAULT '' NOT NULL, t
ype enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char() 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() 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() collate utf8_bin, collation_connection char() collate utf8_bin, db_collation char() collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS procs_priv ( Host char() binary DEFAULT '' NOT NULL, Db char() binary DEFAULT '' NOT NUL
L, User char() binary DEFAULT '' NOT NULL, Routine_name char() COLLATE utf8_general_ci DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP() NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id INTE
GER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR() NOT NULL, argument MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="General log"*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP() NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME(
) NOT NULL, lock_time TIME() NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR() 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"*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS event ( db char() CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char() C
HARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer char() CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int() 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() CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char() CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char() collate utf8_bin, collation_connection char() collate utf8_bin, db_collation char() collate utf8_bin, body_utf8 longblob, PRIMARY KEY (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR() 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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE IF NOT EXISTS proxies_priv (Host char() binary DEFAULT '' NOT NULL, User char() binary DEFAULT '' NOT
NULL, Proxied_host char() binary DEFAULT '' NOT NULL, Proxied_user char() binary DEFAULT '' NOT NULL, With_grant BOOL DEFAULT NOT NULL, Grantor char() 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'*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: DROP DATABASE IF EXISTS performance_schema
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: CREATE DATABASE performance_schema character set utf8
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.cond_instances(NAME VARCHAR() not null,OBJECT_INSTANCE_BEGIN BIGINT not null)ENGINE=
PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.events_waits_current(THREAD_ID INTEGER not null,EVENT_ID BIGINT unsigned not null,EVENT
_NAME VARCHAR() not null,SOURCE VARCHAR(),TIMER_START BIGINT unsigned,TIMER_END BIGINT unsigned,TIMER_WAIT BIGINT unsigned,SPINS INTEGER unsigned,OBJECT_SCHEMA VARCHAR(),OBJECT_NAME VARCHAR(),OBJECT_TYPE VARCHAR(),OBJECT_INSTANCE_BEGIN BIGINT not null,NESTING_EVENT_ID BIGINT unsigned,OPERATION VARCHAR() not null,NUMBER_OF_BYTES BIGINT unsigned,FLAGS INTEGER unsigned)ENGINE=PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.events_waits_history(THREAD_ID INTEGER not null,EVENT_ID BIGINT unsigned not null,EVENT
_NAME VARCHAR() not null,SOURCE VARCHAR(),TIMER_START BIGINT unsigned,TIMER_END BIGINT unsigned,TIMER_WAIT BIGINT unsigned,SPINS INTEGER unsigned,OBJECT_SCHEMA VARCHAR(),OBJECT_NAME VARCHAR(),OBJECT_TYPE VARCHAR(),OBJECT_INSTANCE_BEGIN BIGINT not null,NESTING_EVENT_ID BIGINT unsigned,OPERATION VARCHAR() not null,NUMBER_OF_BYTES BIGINT unsigned,FLAGS INTEGER unsigned)ENGINE=PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
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() not null,SOURCE VARCHAR(),TIMER_START BIGINT unsigned,TIMER_END BIGINT unsigned,TIMER_WAIT BIGINT unsigned,SPINS INTEGER unsigned,OBJECT_SCHEMA VARCHAR(),OBJECT_NAME VARCHAR(),OBJECT_TYPE VARCHAR(),OBJECT_INSTANCE_BEGIN BIGINT not null,NESTING_EVENT_ID BIGINT unsigned,OPERATION VARCHAR() not null,NUMBER_OF_BYTES BIGINT unsigned,FLAGS INTEGER unsigned)ENGINE=PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.events_waits_summary_by_instance(EVENT_NAME VARCHAR() 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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.events_waits_summary_by_thread_by_event_name(THREAD_ID INTEGER not null,EVENT_NAME VARC
HAR() 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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.events_waits_summary_global_by_event_name(EVENT_NAME VARCHAR() 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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.file_instances(FILE_NAME VARCHAR() not null,EVENT_NAME VARCHAR() not null,OPEN_CO
UNT INTEGER unsigned not null)ENGINE=PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.file_summary_by_event_name(EVENT_NAME VARCHAR() 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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.file_summary_by_instance(FILE_NAME VARCHAR() not null,EVENT_NAME VARCHAR() 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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.mutex_instances(NAME VARCHAR() not null,OBJECT_INSTANCE_BEGIN BIGINT not null,LOCKED
_BY_THREAD_ID INTEGER)ENGINE=PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.rwlock_instances(NAME VARCHAR() 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*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.setup_consumers(NAME VARCHAR() not null,ENABLED ENUM ('YES', 'NO') not null)ENGINE=PE
RFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.setup_instruments(NAME VARCHAR() not null,ENABLED ENUM ('YES', 'NO') not null,TIMED
ENUM ('YES', 'NO') not null)ENGINE=PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.setup_timers(NAME VARCHAR() not null,TIMER_NAME ENUM ('CYCLE', 'NANOSECOND', 'MICROSE
COND', 'MILLISECOND', 'TICK') not null)ENGINE=PERFORMANCE_SCHEMA*************************** 45. row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE TABLE performance_schema.threads(THREAD_ID INTEGER not null,PROCESSLIST_ID INTEGER,NAME VARCHAR() not null)EN
GINE=PERFORMANCE_SCHEMA*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; drop procedure if exists mysql.die
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; CREATE DEFINER=``@`` PROCEDURE `mysql`.`die`()
signal sqlstate 'HY000' set message_text='Unexpected content found in the performance_schema database.'
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: use `mysql`; drop procedure mysql.die
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: BEGIN
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Table_map
Server_id:
End_log_pos:
Info: table_id: (mysql.db)
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Write_rows
Server_id:
End_log_pos:
Info: table_id: flags: STMT_END_F
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: COMMIT
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_db` /* generated by server */
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: BEGIN
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Table_map
Server_id:
End_log_pos:
Info: table_id: (mysql.user)
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Write_rows
Server_id:
End_log_pos:
Info: table_id: flags: STMT_END_F
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: COMMIT
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_user` /* generated by server */
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: BEGIN
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Table_map
Server_id:
End_log_pos:
Info: table_id: (mysql.proxies_priv)
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Write_rows
Server_id:
End_log_pos:
Info: table_id: flags: STMT_END_F
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: COMMIT
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Query
Server_id:
End_log_pos:
Info: DROP TEMPORARY TABLE IF EXISTS `mysql`.`tmp_proxies_priv` /* generated by server */
*************************** . row ***************************
Log_name: master-102.000001
Pos:
Event_type: Stop
Server_id:
End_log_pos:
Info:
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-102.000003 | | | |
+-------------------+----------+--------------+------------------+
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: sslcopy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000002
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Id:
User: system user
Host:
db: NULL
Command: Connect
Time:
State: Waiting for master to send event
Info: NULL
Progress: 0.000
*************************** . row ***************************
Id:
User: system user
Host:
db: NULL
Command: Connect
Time:
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Progress: 0.000
*************************** . row ***************************
Id:
User: root
Host: localhost
db: NULL
Command: Query
Time:
State: NULL
Info: SHOW PROCESSLIST
Progress: 0.000
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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: sslcopy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000002
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
注意观察上面的输出信息，尤其要观察下面的值是否为0,如果为0说明复制不存在延迟，若不为0说明从库数据和主库数据延迟情况，我建议大家生产环境中要监控该参数项。
Seconds_Behind_Master:
四.如何确定主从节点数据是否一致
可用借助于第三方工具，比如:percona-tools
五.数据不一致如何修复
删除从数据库，重新复制
六.复制的问题和解决方案
1>.数据损坏或丢失
Master： MHA + semi repl
Slave： 重新复制
2>.混合使用存储引擎
MyISAM：不支持事务
InnoDB： 支持事务
3>.不惟一的server id
重新复制
4>.复制延迟
需要额外的监控工具的辅助
一从多主:mariadb10版后支持
多线程复制：对多个数据库复制