With some versions of MariaDB, the server could stop responding to sql queries. service mysql status shows mysql[413]: ERROR 23 (HY000) at line 1: Out of resources when opening file ‘./…’ (Errcode: 24 “Too many open files”) [ERROR] Error in accept: Too many open files To adjust the limits of […]
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: […]
With mysqldump it is not possible by the parameters to exclude individual databases. However, the database can be easily queried from the information_schema and this makes an exclude. mysqldump –databases `mysql –skip-column-names -e “SELECT GROUP_CONCAT(schema_name SEPARATOR ‘ ‘) FROM information_schema.schemata WHERE schema_name NOT IN (‘mysql’,’performance_schema’,’information_schema’, ‘db_test’);”` >/dump.sql Thanks to Ronald […]
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 […]