When mysql shows Slave_IO_Running and Slave_SQL_Running is false, then there is much evidence that the current slave relay log is broken. show slave status \G then looks for example like this: mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master.example.com Master_User: […]
To secure replication over ssl, mysql must support ssl. mysql -u root -p show variables like ‘%ssl%’; If the result looks like this ssl is not configured yet. have_openssl DISABLED have_ssl DISABLED ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key First, the directory for the ssl-keystore is created on both servers. mkdir -p […]
You can either put a log file for all queries (mysqld –log=log_file_name or log = log_file_name in your my.cnf) or write the queries in table mysql.general_log. The use of the table can be done without restarting mysqld and the stored values could be filtered much easier. The table can create […]
The solution from Block outdated clients won´t work with syslog-ng 3.4.2, because syslog-ng syslog-ng uses “lseek()” to get the end of /proc/net/xt_recent/something while the program() destination just starts the program. Withe the destination file you get log-entries like: Aug 29 00:00:44 mx03.schaal-24.de syslog-ng[20351]: Error suspend timeout has elapsed, attempting to […]
I use stunnel to secure my MySQL-replication. MySQL itself offers SSH, but it´s not so simple to install. If stunnel works on your sytsme, you can use it for much more daemons/ports (secure imap, pop3, http). With stunnel only the remote connects to the master server use the tunnel. Local […]
I´ve already pointed out here, that you can easily restart a replication. But you have to know that the replication isn´t working. Instead of continuously typing “CHECK SLAVE STATUS\G” in mysql i´ve decided to use nagios for that. If something is wrong, i get a mail and a notification in […]
If the mysql-replication is broken, there are two ways to fix the problem: skip the error rebuild the replication In some cases you could easily skip error. If you for example execute the same INSERT on both server. Skip the error Login into mysql as root: STOP SLAVE; SET GLOBAL […]
In contrast to a master-slave replication in a master-master replication changes made on one server are immediately available on both. The setup of a master-master-replication is similar to a master-slave-replication. I show here only the differences (for the basics see mysql master-slave-replication). In the following to old master remains as […]