mysql: backup from remote host using ssh

Sometimes you need to dump a mysql database, and there’s no free space on the server. Here’s you can do to back up the database:

ssh -p <ssh port> user@host "mysqldump -u dbuser -ppassword dbname | gzip -9" > database.sql.gz

At the end you will get mysq database on a local server. Although ssh is slow and it would take some time to transfer the database.

Monitoring Galera cluster using Percona Nagios plugins on Debian

1. Download the repository packages:

wget$(lsb_release -sc)_all.deb

2. Install it:

dpkg -i percona-release_0.1-3.$(lsb_release -sc)_all.deb

3. Update the packages list:

apt-get update

4. Install

apt-get install percona-nagios-plugins

5. Grant the privileges:

GRANT USAGE, SELECT, REPLICATION CLIENT on *.* to 'test'@'localhost' IDENTIFIED BY 'password';

6. Test:

/usr/lib64/nagios/plugins/pmp-check-mysql-status -x wsrep_cluster_status -C == -T str -c non-Primary -l test -p password

MySQL slow queries log rotation

By default MySQL server doesn’t rotate slow queries log. Probably the simplest way to get rid of slow log which takes gigabytes is to use logrotate. Here’s an example:

[root@marge ~]# cat /etc/logrotate.d/mysql-slow
/var/log/mysqld-slow.log {
rotate 1
create 644 mysql mysql
[root@marge ~]#

If you need more than one rotated log adjust rotate values.