In a simple MySQL replication Master-Slave configuration I have a problem where Master tries to connect to itself as a slave on reboot.
So when I restart MySQL on Master, I see errors related to the same server trying to replicate to itself and I have to manually run mysql -e "STOP SLAVE;"
every time I restart MySQL.
How can I disable slave on master for good?
Here's the relevant portion of my.cnf
:
## Logging
binlog_format = mixed
log_bin = /var/log/mysql/mysql-bin.log
sync_binlog = 1
pid_file = /var/run/mysqld/mysqld.pid
log_error = /var/log/mysql/error.log
#general_log = 0
#general_log_file = /var/log/mysql/general.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 3
expire_logs_days = 14
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
## Replication
server_id = 200
## Master Configuration
binlog-do-db = my_db_1
binlog-do-db = my_db_2
binlog-do-db = my_db_3
binlog-do-db = my_db_4
binlog-do-db = my_db_5
binlog-do-db = my_db_6
Also, when I run SELECT * FROM mysql.user;
I don't see the repl
user that's allegedly a "slave" on Master.
BUT, I do see that localhost
has replication grants:
mysql> select Host, User, grant_priv, Repl_slave_priv, Repl_client_priv from mysql.user;
+-----------------+---------------+------------+-----------------+------------------+
| Host | User | grant_priv | Repl_slave_priv | Repl_client_priv |
+-----------------+---------------+------------+-----------------+------------------+
| localhost | root | Y | Y | Y |
| localhost | mysql.sys | N | N | N |
Here's an example of the errors I see on Reboot (before I run STOP SLAVE;
on Master):
2016-09-01T15:22:23.845505Z 384 [Note] Access denied for user 'repl'@'192.168.100.200' (using password: YES)
2016-09-01T15:22:23.845761Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'repl@192.168.100.200:3306' - retry-time: 30 retries: 8, Error_code: 1045
2016-09-01T15:22:50.191636Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6843ms. The settings might not be optimal. (flushed=15210 and evicted=0, during the time.)
Apart from this, replication is running fine. Writes to Master show up flawlessly on the real, read-only, Slave.
Full my.cnf
:
[mysql]
default_character_set = utf8
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
## Custom Configuration
skip_external_locking = 1
skip_name_resolve
open_files_limit = 20000
## Cache
thread_cache_size = 16
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 4M
## Per-thread Buffers
sort_buffer_size = 32M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
join_buffer_size = 2M
## Temp Tables
tmp_table_size = 1024M
max_heap_table_size = 1024M
## Networking
back_log = 250
max_connections = 512
max_connect_errors = 100000
max_allowed_packet = 128M
interactive_timeout = 1800
wait_timeout = 1800
character_set_client_handshake = FALSE
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
### Storage Engines
default_storage_engine = InnoDB
innodb = FORCE
## MyISAM
key_buffer_size = 128M
myisam_sort_buffer_size = 16M
## InnoDB
innodb_buffer_pool_size = 46G
innodb_buffer_pool_instances = 64
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 1
## Logging
binlog_format = mixed
log_bin = /var/log/mysql/mysql-bin.log
sync_binlog = 1
pid_file = /var/run/mysqld/mysqld.pid
log_error = /var/log/mysql/error.log
#general_log = 0
#general_log_file = /var/log/mysql/general.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 3
expire_logs_days = 14
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
## Replication
# Master Server ID:
server_id = 200
# Slave Server ID:
# server_id = 300
## Master Configuration
# Comment out on Slave
binlog-do-db = db_1
binlog-do-db = db_2
binlog-do-db = db_3
binlog-do-db = db_4
binlog-do-db = db_5
binlog-do-db = db_6
## Slave Configuration
# Uncomment the following on Slave
# relay-log = /var/log/mysql/mysql-relay-bin.log
# binlog-do-db = db_1
# binlog-do-db = db_2
# binlog-do-db = db_3
# binlog-do-db = db_4
# binlog-do-db = db_5
# binlog-do-db = db_6
# log_slave_updates = 1
# read_only = 1
# slave_skip_errors = 1062
[mysqld_safe]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
pid_file = /var/run/mysqld/mysqld.pid
log_error = /var/log/mysql/error.log
Best Answer
Since this is MySQL 5.7, you need
Running
STOP SLAVE;
is required because replication is updatingmaster.info
,relay-log.info
, and relay logs.Once stopped, running
RESET SLAVE ALL;
does everything. Why ?MySQL 5.5
You needed to do this:
and that was all.
MySQL 5.6
There was a problem with the previous method (does not erase memory structures for replication, the ones that have the host, port, user, password).
With that problem, you had run
and the restart mysqld
Another method would have been
Go to the OS, delete
master.info
, and still restart mysqld.BUMMER !!!
MySQL 5.7
RESET SLAVE ALL;
successfully clears up the replication memory structures.RESET SLAVE;
is not enough. Once you runRESET SLAVE ALL;
, you can runSHOW SLAVE STATUS\G