2

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 '[email protected]: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
3
  • 1
    It sounds like you did a CHANGE MASTER on the Master, as if you wanted dual-master?
    – Rick James
    Sep 3, 2016 at 23:26
  • 1
    You can skip the slave start using SKIP_SLAVE_START: dev.mysql.com/doc/refman/5.7/en/… Sep 4, 2016 at 2:07
  • just run - RESET SLAVE must help;
    – a_vlad
    Nov 25, 2016 at 9:23

1 Answer 1

0

Since this is MySQL 5.7, you need

STOP SLAVE;
RESET SLAVE ALL;

Running STOP SLAVE; is required because replication is updating master.info, relay-log.info, and relay logs.

Once stopped, running RESET SLAVE ALL; does everything. Why ?

MySQL 5.5

You needed to do this:

STOP SLAVE;
CHANGE MASTER TO master_host='';

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

STOP SLAVE;
CHANGE MASTER TO master_host='';

and the restart mysqld

Another method would have been

STOP SLAVE;

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 run RESET SLAVE ALL;, you can run SHOW SLAVE STATUS\G

mysql> show slave status\G
Empty set (0.00 sec)

mysql>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.