Tag Archives: MySQL

sysbench mariadb

sysbench \
--db-driver=mysql \
--mysql-table-engine=innodb \
--oltp-table-size=100000 \
--oltp-tables-count=12 \
--num-threads=4 \
--mysql-host=192.168.200.110 \
--mysql-db=test_database \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--test=/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua \
run

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