虚拟机 主机：192.168.130.100 从机：192.168.130.101 系统：CentoS7二、主机操作 1.开启binlog
[root@hadoop ~]# vim /etc/my.cnf
2.重启MySQL新增以下配置：
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
innodb_file_per_table = ON
skip_name_resolve = ON
[root@hadoop ~]# service mysqld restart
Restarting mysqld (via systemctl):
[ 确定 ]
3.进入MySQL
[root@hadoop ~]# mysql -uroot -p111213 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.51-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>4.查看binlog
mysql> show global variables like '%log%'; +-----------------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------------+--------------------------------+ | back_log | 80 | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | IGNORE_ERROR | | binlog_format | MIXED | | binlog_gtid_simple_recovery | OFF | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORE_ERROR | | expire_logs_days | 0 | | general_log | OFF | | general_log_file | /var/lib/mysql/hadoop.log | | innodb_api_enable_binlog | OFF | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_online_alter_log_max_size | 134217728 | | innodb_undo_logs | 128 | | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /var/log/mysqld.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log | | | relay_log_basename | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_info_repository | FILE | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | simplified_binlog_gtid_recovery | OFF | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/hadoop-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sync_binlog | 0 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | +-----------------------------------------+--------------------------------+ 61 rows in set (0.00 sec)5.查看主节点的server id
mysql> show global variables like '%server%'; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | character_set_server | latin1 | | collation_server | latin1_swedish_ci | | innodb_ft_server_stopword_table | | | server_id | 1 | | server_id_bits | 32 | | server_uuid | 56f00bc3-a8c3-11eb-b9ff-000c29a4a77e | +---------------------------------+--------------------------------------+ 6 rows in set (0.00 sec)6.创建用户
mysql> create user 'slave'@'192.168.130.100' IDENTIFIED by 'slave'; Query OK, 0 rows affected (0.00 sec)7.为用户分配主从复制权限，并授权从机
mysql> grant replication slave,replication client on *.* to 'slave'@'192.168.130.101' identified by 'slave'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)8.查看主节点日志
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 698 | +------------------+-----------+ 1 row in set (0.00 sec)9.查看日志内容
mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+-------------+-----------+-------------+-------------------------------------------------------- --------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+-------------------------------------------------------- --------------------------------------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.51-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Query | 1 | 295 | CREATE USER 'slave'@'192.168.130.100' IDENTIFIED BY PAS SWORD '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF' | | mysql-bin.000001 | 295 | Query | 1 | 528 | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ' slave'@'192.168.130.101' IDENTIFIED BY PASSWORD '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF' | | mysql-bin.000001 | 528 | Query | 1 | 607 | flush privileges | +------------------+-----+-------------+-----------+-------------+-------------------------------------------------------- --------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)三、从机操作 1.开启中继日志
2.重启MySQL[root@hadoop ~]# vim /etc/my.cnf
relay-log = relay-log
relay-log-index = relay-log.index
server-id = 2
innodb_file_per_table = ON
skip_name_resolve = ON
[root@hadoop ~]# service mysqld restart
Restarting mysqld (via systemctl):
[ 确定 ]
3.进入MySQL
[root@hadoop ~]# mysql -uroot -p111213 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.6.51-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>4.查看中继日志
mysql> show global variables like '%log%'; +-----------------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------------+--------------------------------+ | back_log | 80 | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | IGNORE_ERROR | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | OFF | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORE_ERROR | | expire_logs_days | 0 | | general_log | OFF | | general_log_file | /var/lib/mysql/hadoop.log | | innodb_api_enable_binlog | OFF | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_online_alter_log_max_size | 134217728 | | innodb_undo_logs | 128 | | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /var/log/mysqld.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log | relay-log | | relay_log_basename | /var/lib/mysql/relay-log | | relay_log_index | /var/lib/mysql/relay-log.index | | relay_log_info_file | relay-log.info | | relay_log_info_repository | FILE | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | simplified_binlog_gtid_recovery | OFF | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/hadoop-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sync_binlog | 0 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | +-----------------------------------------+--------------------------------+ 61 rows in set (0.00 sec)5.查看从节点的server id
mysql> show global variables like '%server%'; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | character_set_server | latin1 | | collation_server | latin1_swedish_ci | | innodb_ft_server_stopword_table | | | server_id | 2 | | server_id_bits | 32 | | server_uuid | 56f00bc3-a8c3-11eb-b9ff-000c29a4a77e | +---------------------------------+--------------------------------------+ 6 rows in set (0.00 sec)6.将从机的master设置为主机
mysql> CHANGE MASTER TO MASTER_HOST='192.168.130.100' ,MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_LOG_FILE='mysql-bin.000001'，MASTER_LOG_POS=985;
7.开启slave注意MASTER_LOG_FILE和MASTER_LOG_POS都要是主机日志的最后一条记录
start slave9.查看slave状态
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.130.100
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 698
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
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: 698
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs mustr replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 220108 18:38:22
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
四、问题解决 问题一(Slave_IO_Running连接失败)：Slave_SQL_Running已连接
Slave_IO_Running连接失败
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs mustr replication to work.
根据问题反馈是UUID的问题，想到自己的两个虚拟机是复制而来的，所以需要修改其中一个UUID。
查找auto.cnf修改auto.cnf 重启slave[root@hadoop ~]# find / -iname “auto.cnf”
/var/lib/mysql/auto.cnf
stop slave
start slave
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.100
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 698
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
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: 698
Relay_Log_Space: 450
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 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: 1
Master_UUID: 56f00bc3-a8c3-11eb-b9ff-000c29a4a77e
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
问题二(Slave_SQL_Running连接失败):
当同步完成后，尝试在主机的zxy数据库中建立a数据库，在查看从机的时候问题如下：
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.100
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 781
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
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: 1008
Last_Error: Error 'Can't drop database 'zxy'; database doesn't exist' on query. Default database: 'zxy' . Query: 'drop database zxy'
Skip_Counter: 0
Exec_Master_Log_Pos: 698
Relay_Log_Space: 533
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'zxy'; database doesn't exist' on query. Default database: 'zxy' . Query: 'drop database zxy'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 56f00bc3-a8c3-11eb-b9ff-000c29a4a77e
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 220108 18:50:00
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
想到是从机里没有zxy这个数据，最后在从机里建立zxy数据库。
正常后： 1.主机创建表mysql> create table zxy.b(id int ,name varchar(25)); Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1098 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> create table zxy.c(id int ,name varchar(25)); Query OK, 0 rows affected (0.00 sec)2.从机状态
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.100
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1211
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 396
Relay_Master_Log_File: mysql-bin.000001
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: 1211
Relay_Log_Space: 839
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 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: 1
Master_UUID: 56f00bc3-a8c3-11eb-b9ff-000c29a4a77e
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hive |
| mysql |
| performance_schema |
| qianfeng |
| zxy |
+--------------------+
6 rows in set (0.00 sec)
mysql> use zxy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_zxy |
+---------------+
| b |
| c |
+---------------+
2 rows in set (0.00 sec)
查看中继日志
mysql> show relaylog events in 'relay-log.000009'; +------------------+-----+-------------+-----------+-------------+---------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------------+ | relay-log.000009 | 4 | Format_desc | 2 | 120 | Server ver: 5.6.51-log, Binlog ver: 4 | | relay-log.000009 | 120 | Rotate | 1 | 0 | mysql-bin.000002;pos=120 | | relay-log.000009 | 167 | Format_desc | 1 | 0 | Server ver: 5.6.51-log, Binlog ver: 4 | | relay-log.000009 | 283 | Query | 1 | 232 | create table zxy.p(id int,name varchar(10)) | +------------------+-----+-------------+-----------+-------------+---------------------------------------------+ 4 rows in set (0.00 sec)
