mysql 5.7.18(主从)
从站状态错误
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'repl'@'xxx.xxx.xxx.xxx' (using password: YES) (Errno: 1045)
主错误(error.log)
2017-05-29T16:40:39.718290Z 213 [Note] Aborted connection 213 to db: 'unconnected' user: 'repl' host: 'xxx.xxx.xxx.xxx' (Got an error reading communication packets)
通过mysql客户端从slave连接到master
但是,如果我像这样从从属服务器命令行登录,则不会出现错误,并且从属服务器可以完美地连接到主服务器。所以主从配置肯定有不对的地方
mysql -h xxx.xxx.xxx.xxx -u repl -pxxxxxxxxxxxx
mysql> show grants;
+--------------------------------------------+
| Grants for [email protected] |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx' |
+--------------------------------------------+
1 row in set (0.00 sec)
在 master 上创建复制用户
CREATE USER 'repl'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'xxxxx';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx';
FLUSH PRIVILEGES;
掌握我的.cnf
[mysqld]
# GENERAL #
user = mysql
port = 3306
default_storage_engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
sysdate_is_now = 1
symbolic_links = 0
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
server_id = 1
log_bin = /var/lib/mysql/mysql-bin
log_slave_updates
expire_logs_days = 14
sync_binlog = 1
log_bin_trust_function_creators= 1
binlog_format = ROW
# REPLICATION #
gtid_mode = ON
enforce_gtid_consistency
# CACHES AND LIMITS #
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
wait_timeout = 60
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 12
innodb_autoinc_lock_mode = 2
innodb_adaptive_hash_index = 0
innodb_change_buffering = none
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
basedir = /usr
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
explicit_defaults_for_timestamp
奴隶 my.cnf
[mysqld]
# GENERAL #
user = mysql
port = 3306
default_storage_engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
sysdate_is_now = 1
symbolic_links = 0
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
server_id = 2
log_bin = /var/lib/mysql/mysql-bin
log_slave_updates
expire_logs_days = 14
sync_binlog = 1
log_bin_trust_function_creators= 1
binlog_format = ROW
# REPLICATION #
gtid_mode = ON
enforce_gtid_consistency
# CACHES AND LIMITS #
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
wait_timeout = 60
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_autoinc_lock_mode = 2
innodb_adaptive_hash_index = 0
innodb_change_buffering = none
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
basedir = /usr
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
explicit_defaults_for_timestamp
主人地位
show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: e4e90c71-4264-11e7-b970-4061862b8d34:1-12803
1 row in set (0.00 sec)
从属状态
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting to reconnect after a failed registration on master
Master_Host: xxx.xxx.xxx.xxx
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: Worker-Intel-i7-2600-16gb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
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: 154
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: 1130
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'repl'@'176.9.137.3' (using password: YES) (Errno: 1045)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 170529 17:24:04
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 8976078a-40a0-11e7-8043-c8600054b161:1-1085
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
启动/设置从站
# mysql -u root -pxxxxxxxxxxx
# stop slave;
# CHANGE MASTER TO
# MASTER_HOST = 'xxx.xxx.xxx.xxx',
# MASTER_PORT = 3306,
# MASTER_USER = 'repl',
# MASTER_PASSWORD = 'xxxxxxxxxxxxx',
# MASTER_AUTO_POSITION = 1;
# start slave;
# show slave status\G;
克拉克您提到USAGE只是因为申请的赠款您还需要REPLICATION SLAVE。
如果使用意味着您只能连接到 DB Server 而不能连接其他任何东西。而在这种情况下,REPLICATION SLAVE 将需要从 Master 读取和提取 binlog 事件。
要解决此问题:
在主执行:
[这次的不同之处在于您使用 REPLICATION SLAVE GRANT 的密码授予]
显示补助金:
如果您确实显示赠款;在你给出 GRANT REPLICATION 命令之后。您应该通过 CLI 从同一从属主机获得以下授权。
听起来像是密码问题。1045 你的密码有#字符吗?删除它 您的密码是否超过 32 个字符?缩短它
请参阅疑难解答 MYSQL 复制错误 1045
这不是问题的正确解决方案(错误代码确实不同),但我会留下它,因为我浪费了很多时间来寻找解决这个问题的方法。毕竟,答案可能对某人有用。
我在从属日志文件中有类似的错误
问题是主人需要安全连接。启动奴隶
修复了我的问题
很高兴之前的答案没有被删除,它为我指明了正确的方向。我在启用 SSL 的 VirtualBox 中设置了一个主-主环境。在 master1,当我运行 CHANGE MASTER TO MASTER_HOST 时,我没有设置 MASTER_SSL=1。但是,复制工作正常。在 master2 我收到以下错误消息。在 RESET SLAVE 之后,我在两个主机上使用正确的参数运行 CHANGE MASTER TO MASTER_HOST 来解决问题。