Mariadb – Changing location of binlog prevents starting up

mariadb

I've copied data/binlog files between MariaDB (10.3) instances and wanted to change the location of data/log files on the new machine. I adjusted the paths in the my.cnf file. On start on the new machine, the mysqld continues to refer to the old locations, ignoring the paths specified in the my.cnf file – this obviously results in an error like so:

mysqld [ERROR] mysqld: File '<old-location>/mariadb-bin.000160' not found (Errcode: 2 "No such file or directory")
mysqld [ERROR] Failed to open log (file '<old-location>/mariadb-bin.000160', errno 2)
mysqld [ERROR] Could not open log file
mysqld [ERROR] Can't init tc log
mysqld [ERROR] Aborting'

I grepped and found that the ibdata1 file has a reference to the old path – is this something that needs to be updated with mysql CLI prior to the migration?

As far as I can tell, this is not the same as the problem described here: https://stackoverflow.com/questions/7660410/try-to-change-bin-log-directory-mysql-bin-index-not-found-errcode-13

The new instance continues to refer to the old path, despite the my.cnf change. Neither this is a permissions problem (like with apparmor or file ownership).

Best Answer

I finally figured it out: MariaDB keeps an "index" file, fortunately ASCII, that you need to edit: mariadb-bin.index located in the binlog directory (or wherever your log_bin_index configuration points at). This file lists all the binlog files with their full paths. This is unfortunate as the paths should be relative to the log_bin configuration setting. Example:

$ head mariadb-bin.index
/<binlog path>/mariadb-bin.000001
/<binlog path>/mariadb-bin.000002

Related Solutions

Mysql – Slave failing to replication in MariaDB 10

max_connections=100000 -- Unreasonable, and dangerous. Even 1000 may be unnecessarily high for the number of simultaneous connections.

10G for the buffer pool -- I assume you have at least 15GB of RAM?

It sounds like the data on the two servers, at least for redacct, is not consistent. How did you initially populate the Slave?

Mysql – Mariadb default logs location & –log-error

Going off the documentation, the error log should be in the default location of /var/lib/mysql and it should be named the default hostname.err, but it's just not there.

In the directory /var/lib/mysql I do have a tc.log, iblogfile0, iblogfile1, aria_log.00000001, and aria_log_control files. But I cannot read any of them, they are encrypted. Anyone know what these are?

BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?

After running the following command,

cat /etc/systemd/system/mysqld.service | grep "mysql"

we can see that the line that systemd uses to start the process is ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION. The options passed to MariaDB are stored in the $MYSQLD_OPTS variable.

If we also run cat /etc/systemd/system/mysqld.service | grep "MYSQLD_OPTS" we get the following.

MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
Use the [Service] section and Environment="MYSQLD_OPTS=...".
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

The file MY_SPECIAL.conf does not exist on my machine, as find / -name "*MY_SPECIAL*" reveals.

I do have entries for mysqld in journalctl. So I'm thinking that it uses the the systemd system logs by default for us systemd users.

On the MariaDB Knowledge base page for the error-log there is a paragraph that states.

systemd has its own logging system, and Linux distributions running systemd may log errors there instead. To view the systemd logs, use journalctl -u mariadb.

This answers the question because --log-error would be an option that could be added to MY_SPECIAL.conf file and journalctl is where mariadb logs errors by default on a systemd system.

Best Answer

Related Solutions

Mysql – Slave failing to replication in MariaDB 10

Mysql – Mariadb default logs location & –log-error

Related Question