How to reset mysql's replication settings completely, without reinstalling it?

Posted by user38060 on Server Fault See other posts from Server Fault or by user38060
Published on 2012-07-11T07:36:33Z Indexed on 2012/07/11 9:17 UTC
Read the original article Hit count: 398

Filed under:
|
  1. I set up mysql replication by adding references to binlogs, relay logs etc in my.cnf
  2. restarted mysql, it worked.
  3. I wanted to change it so I deleted all binlog related files including log-bin.index,
  4. removed binlog statements from my.cnf
  5. restarted server, works
  6. set master to '', purge master logs since now(), reset slave, stop slave, stop master.
  7. now, to set up replication again, I added binlog statements to the server. But then I hit this problem when restarting with:

    sudo mysqld
    

(the only way to see mysql's startup errors)

I get this error:

/usr/sbin/mysqld: File '/etc/mysql/var/log-bin.index' not found (Errcode: 13)

Because indeed, this file does not exist! (I deleted it, while trying to set up a new replication system) Hmm, if I change the config line to:

log-bin-index = log-bin.index

I get a different error:

[ERROR] Can't generate a unique log-filename /etc/mysql/var/bin.(1-999)
[ERROR] MSYQL_BIN_LOG::open failed to generate new file name.
[ERROR] Aborting

The first time I set up replication on this system, I didn't need to create this file. I did the same thing - added references to a previously non-existing file, and mysql created it. Same with relay logs, etc.

I don't know why mysql insists on trying to read the old folder.

Should I just reinstall the whole package again? That seems like overkill.

my my.cnf: [client] port = 3306 socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
skip-external-locking
bind-address        = IP
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
table_cache            = 64
sort_buffer            =64K
net_buffer_length      =2K
query_cache_limit       = 1M
query_cache_size        = 16M
slow_query_log_file     = /etc/mysql/var/mysql-slow.log
long_query_time        = 1
log-queries-not-using-indexes
expire_logs_days        = 10
max_binlog_size         = 100M

server-id = 3
log-bin = /etc/mysql/var/bin.log
log-slave-updates
log-bin-index = /etc/mysql/var/log-bin.index
log-error = /etc/mysql/var/error.log

relay-log = /etc/mysql/var/relay.log
relay-log-info-file = /etc/mysql/var/relay-log.info
relay-log-index = /etc/mysql/var/relay-log.index

auto_increment_increment = 10
auto_increment_offset = 3
master-host = HOST
master-user = USER
master-password=PWD
replicate-do-db = DBNAME
collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash

[myisamchk]
key_buffer_size = 16M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

!includedir /etc/mysql/conf.d/

Update: Changing all the /etc/mysql/var/xxx paths in binlog & relay log statements to local has somehow solved the problem.

I thought it was apparmor causing it at first, but when I added /etc/mysql/* rw, to apparmor's config and restarted it, it still couldn't read the full path.

© Server Fault or respective owner

Related posts about mysql

Related posts about mysql-replication