MariaDB version: 10.0.34
Ubuntu version: 16.04.4 LTS
I am trying to turn on log_bin in a newly installed mariadb. I created a binlog.cnf file in /etc/mysql/conf.d/:
[mysqld]
server_id=1
log_bin=/var/lib/mysql/mariadb-bin
log_bin_index=/var/lib/mysql/mariadb-bin.index
log_queries_not_using_indexes
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=phpmyadmin
binlog-ignore-db=test
So mysqld --print-defaults indicates that the config is read, but show variables still shows that log_bin is off.
# mysqld --print-defaults
mysqld would have been started with the following arguments:
--server_id=1
--log_bin=/var/lib/mysql/mariadb-bin
--log_bin_index=/var/lib/mysql/mariadb-bin.index
--binlog-ignore-db=mysql
--binlog-ignore-db=performance_schema
--binlog-ignore-db=information_schema
--binlog-ignore-db=phpmyadmin
--binlog-ignore-db=test
[...]
> show variables like '%log_bin%';\G";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
The file /etc/mysql/my.cnfwas a symlink to /etc/alternatives/my.cnf which symlinked to /etc/mysql/mariadb.cnf ... so I deleted the my.cnf link and created an actual my.cnf file instead as a copy of mariadb.cnf. According to https://stackoverflow.com/a/46246285/3319392 a bug could mean that mariadb can't follow symlinks... but this didn't change anything.
I have restarted mysql many times, and I even tried to put rubbish in my.cnf to see if mysql would complain, but it doesn't. There is nothing in the error log and this is syslog when mysql is restarted:
May 22 16:17:26 smtp2 systemd[1]: Stopping LSB: Start and stop the mysql database server daemon...
May 22 16:17:26 smtp2 mysql[29682]: * Stopping MariaDB database server mysqld
May 22 16:17:27 smtp2 mysql[29682]: ...fail!
May 22 16:17:27 smtp2 systemd[1]: mysql.service: Control process exited, code=exited status=1
May 22 16:17:27 smtp2 systemd[1]: Stopped LSB: Start and stop the mysql database server daemon.
May 22 16:17:27 smtp2 systemd[1]: mysql.service: Unit entered failed state.
May 22 16:17:27 smtp2 systemd[1]: mysql.service: Failed with result 'exit-code'.
May 22 16:17:27 smtp2 systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
May 22 16:17:27 smtp2 mysql[29706]: * Starting MariaDB database server mysqld
May 22 16:17:27 smtp2 mysql[29706]: ...done.
May 22 16:17:27 smtp2 systemd[1]: Started LSB: Start and stop the mysql database server daemon.
Filepermissions in /etc/mysql/
drwxr-xr-x 4 root root 4096 May 22 16:50 .
drwxr-xr-x 108 root root 4096 May 17 06:33 ..
drwxr-xr-x 2 root root 4096 May 22 16:56 conf.d
-rw------- 1 root root 277 Mar 22 16:01 debian.cnf
-rwxr-xr-x 1 root root 1426 Mar 6 10:15 debian-start
-rw-r--r-- 1 root root 869 May 22 15:04 mariadb.cnf
drwxr-xr-x 2 root root 4096 Mar 22 16:01 mariadb.conf.d
-rw-r--r-- 1 root root 868 May 22 16:21 my.cnf
Filepermissions in /etc/mysql/conf.d/
-rw-r--r-- 1 mysql mysql 303 May 22 16:56 binlog.cnf
-rw-r--r-- 1 root root 8 Jan 21 2017 mysql.cnf
-rw-r--r-- 1 root root 55 Jan 21 2017 mysqldump.cnf
Filepermissions in /var/lib/mysql/:
drwxr-xr-x 7 mysql mysql 4096 May 22 16:00 .
drwxr-xr-x 48 root root 4096 May 2 06:16 ..
-rw-rw---- 1 mysql mysql 16384 Apr 11 16:43 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Apr 11 16:43 aria_log_control
-rw-r--r-- 1 root root 0 Mar 22 16:01 debian-10.0.flag
-rw-rw---- 1 mysql mysql 12582912 May 22 16:58 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May 22 16:58 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 22 16:01 ib_logfile1
-rw-rw---- 1 mysql mysql 0 May 22 15:27 mariadb-bin.index
-rw-rw---- 1 mysql mysql 0 Mar 22 16:01 multi-master.info
drwx------ 2 mysql root 4096 Mar 22 16:01 mysql
-rw-rw---- 1 mysql mysql 0 May 22 14:48 mysql-bin.index
-rw------- 1 root root 15 Mar 22 16:01 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 Mar 22 16:01 performance_schema
drwx------ 2 mysql mysql 4096 Apr 10 16:20 phpmyadmin
drwx------ 2 mysql mysql 4096 May 22 16:58 postfix
drwx------ 2 mysql mysql 4096 Mar 22 16:02 test
