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.
1. Download the repository packages:
wget https://repo.percona.com/apt/percona-release_0.1-3.$(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
Run:
set global relay_log_purge=1;
And flush the logs:
flush logs;
If you try to import / restore some big MySQL database you may have encountered such error:
MySQL server has gone away (error 2006)
To fix it you would need to increase wait_timeout and max_allowed_packet:
wait_timeout = 6000 max_allowed_packet = 128M
It should fix the problem.
Create a log:
touch /var/log/mysqld-general.log
Set the permissions:
chown mysql:mysql /var/log/mysqld-general.log
Run in mysql console:
mysql> SET GLOBAL general_log_file='/var/log/mysqld-general.log'; mysql> SET GLOBAL general_log = 1;
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 {
weekly
missingok
rotate 1
nocompress
copytruncate
notifempty
create 644 mysql mysql
}
[root@marge ~]#
If you need more than one rotated log adjust rotate values.