Mysql – How do MySQL logs compare to Postgresql logs

MySQLpostgresqlreplicationtransaction-log

MySQL has quite a few logs:

InnoDB transaction log
Binary log
General query log
Error log
Slow query log

I know about Postgresql's WAL, which is equivalent to InnoDB's transaction log (correct?).

What about the other MySQL logs such as bin log – are there Postgresql equivalence of them?

Best Answer

As MySQL implements multiple storage engines, theres need for both binary log for incremental backups / replication and InnoDB transaction log for ACID compliance (Only with InnoDB storage engine) In postgres, WAL can be used both for replication and ACID compliance, so WAL combines binary log and InnoDB transaction log.

You can make postgres log queries to syslog / separete log with log_statement and log_duration . So you get both query log and with directive log_min_duration_statement you can implement slow query log.

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