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

Question

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.

Answer 1

First, reinstalling mysql isn't a real solution. Especially if you mean removing and installing the packages -- that won't touch the files in your data directory and your problem won't change.

Second, you should never have to touch binlog files on disk -- deleting them off the filesystem is not the right way to manage them. There are commands in mysql which will remove old logs. MySQL must maintain the index file as well, so the files on disk matches the list of existing binlogs it keeps. See Purging Binary Logs

So the mess you're in is because you're making assumptions and touching files you're not supposed to.

This error:

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

Doesn't mean it's not there; Error 13 means "Permission denied" Which probably means mysql can't write to /etc/mysql/var/

All you really need to do now, is fix the permissions on /etc/mysql/var/ Something like: chown mysql:mysql /etc/mysql/var; chmod 0775 /etc/mysql/var

If there is no .index file, and no binlogs mysql will just start over from scratch and create the files it needs. Remove any files in that directory.

Answer 2

/etc/mysql/var/ does not seem to be a correct path, you should check your datadir setting. It should be something like /var/lib/mysql