How to reset mysql's replication settings completely, without reinstalling it?
- by user38060
I set up mysql replication by adding references to binlogs, relay logs etc in my.cnf
restarted mysql, it worked.
I wanted to change it so I deleted all binlog related files including log-bin.index,
removed binlog statements from my.cnf
restarted server, works
set master to '', purge master logs since now(), reset slave, stop slave, stop master.
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.
