Linux – Is it possible to safely edit thesql-bin.index file while thesql server is running

linuxmariadbMySQLmysql-replication

I changed location of mysql bin-log files. I copied them so mysql started fine, because they where in both locations, but then removed them from old location. I forget about correcting the path in index file and now I have problems with purging old binary logs.

So my question is it safe to correct paths in index file while mysql server is running

For example cat correct.index > mysql-bin.index

My server version is mariadb 5.5

Best Answer

This is not a supported procedure as highlighted in official MySQL documentation:

To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same basename as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.

So no, it's not safe to edit binary log index while mysqld is running.

Related Solutions

Mysql – the.cnf parameters to enable binary logging theSQL 4.1.20

In general on Linux systems, an Errno 13 means Permissions Denied or Access Denied.

What's the output of

# ls -ld /var/log/mysql
# ls -l /var/log/mysql

Does this match with your MySQL user?

If not, suggested solution (providing mysql is your mysql user and mysql is your group):

chown -R mysql:mysql /var/log/mysql

MySQL replication issues after a power outage

I went for the first option.

That worked up to the point where the slave started trying to do inserts that conflicted with the primary keys. The slave had done more work than the master bin-log had persisted, as previously mentioned. One aspect I didn't anticipate was that the slave contained data that wasn't in the master; i.e. the slave persisted some transactions before the power outage that the master HAD NOT persisted.

Since for my case, these transactions weren't payment-related or similar, I chose to delete the data from the slave (thereby losing some data that had happened, but which didn't exist in the master) and then let replication run again. This brought the slaves completely up to date. If the data had been more important, we have auto-increment offsets sufficient to give us some wriggle room for manually wrangling the data and ensuring referential integrity was not compromised. Thankfully we didn't need to do that in this case.

For a machine in (passive) master-master configuration which was in this predicament, I chose a similar approach. By passive master-master, I mean that we have an active master (serverA) which is where all the writes go, and a passive master (serverB) that is in place to allow for schema updates to take place with zero downtime. The data in active master (serverA) was chosen as the true values, despite knowing that this meant we lost a couple of persisted transactions which weren't deemed important.

Changed the log file and position on the slave.

CHANGE MASTER MASTER_LOG_FILE='bin-log.004713', MASTER_LOG_POS=0; -- on serverB

Restarted slave replication on the passive master (serverB) until it failed with primary key constraint violations, as with the other slaves.
```
 START SLAVE; -- on serverB
```
Stopped slave replication from the passive master (serverB) to the active master (serverA).
```
STOP SLAVE; -- on serverA
```

DELETE the rows on the slave (serverB) which didn't exist in the master on serverA.

DELETE FROM SOME_TABLE WHERE ID IN (???,????); -- on serverB
SHOW MASTER STATUS\G; -- get the new master log position on serverB

Move the active master (serverA) slave exec position to skip those deletes from the passive master (serverB).
```
CHANGE MASTER TO MASTER_LOG_POS=???; --on serverA; use the value just obtained from serverB
```

Restart replication on both the active master (serverA) and passive master.

START SLAVE; -- on both machines. serverA does nothing and serverB starts catching up.

Best Answer

Related Solutions

Mysql – the.cnf parameters to enable binary logging theSQL 4.1.20

MySQL replication issues after a power outage

Related Topic