Tag Archives: MySQL

mysql daily backup 7 days


mkdir -vp /backups/databases
vi /some/path/make_db_back.sh

#!/bin/bash

week_day=`date +%u`
back_dir=/backups/databases/$week_day

if [ -d "$back_dir" ]; then
rm -rvf $back_dir
fi

mkdir $back_dir
mysql -s -e 'show databases' | egrep -v "mysql|information_schema|performance_schema" > /root/db_list
cat /root/db_list | while read db; do mysqldump -h localhost --single-transaction --events $db > $back_dir/$db.$(date +'%F').sql; sleep 5 ; done
gzip $back_dir/*.sql
rm -f /root/db_list

Run every day cron:

crontab -e
0 10 * * * /some/path/make_db_back.sh

ERROR 1290 (HY000) at line 1: The MySQL server is running with the –secure-file-priv

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

mysql> Bye

mysql -e "SELECT CONCAT('KILL ',ID,';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'user' AND COMMAND = 'Query' AND TIME > 100 INTO OUTFILE '/var/lib/mysql-files/kill_list.txt';" && mysql -e "source /var/lib/mysql-files/kill_list.txt" && rm -rf /var/lib/mysql-files/kill_list.txt

tokudb

Better performance:
with up to 20x faster response times than InnoDB without tuning
Stronger ROI:
with lower operational and infrastructure costs and increased scalability and flexibility
Higher availability:
with less downtime for demanding applications in public, private and hybrid cloud environments

Number of processes running now: 0

If your MySQL or MariaDB server is rebooting itself using something like ISPConfig:

max_connections=50
innodb_buffer_pool_size = 512M

Other settings is custom:

innodb_file_per_table = 1
innodb_buffer_pool_size = 512M
max_connections=50
max_allowed_packet=256M
table_cache=512
sort_buffer_size=4M
myisam_sort_buffer_size=64M
thread_cache=128
wait_timeout=300
thread_concurrency=1

Error:

161213 11:49:56 mysqld_safe Number of processes running now: 0
161213 11:49:56 mysqld_safe mysqld restarted
161213 11:50:00 [Note] /usr/sbin/mysqld (mysqld 10.0.28-MariaDB) starting as process 1233 …
161213 11:50:00 [Note] InnoDB: Using mutexes to ref count buffer pool pages