mysql qcache enabling

/etc/my.cnf

tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 1
query-cache-size               = 32M
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

Mysql / MariaDB check tables engine

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'dbname' AND engine = 'MyISAM';


Where dbname - database name
And InnoDB - engine, you can use InnoDB


Convert MyISAM tables to InnoDB

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

Then run result as SQL query

For example:

 ALTER TABLE databasename.tablename ENGINE=InnoDB;

MariaDB enable slow logs

MariaDB [(none)]> SET GLOBAL slow_query_log = 'ON';
MariaDB [(none)]> set global slow_query_log_file = '/var/log/mariadb/slow.log';

mysql create user and db with privileges

> create database newdb;

 > GRANT ALL PRIVILEGES ON newdb . * TO 'newusername'@'localhost'  IDENTIFIED BY 'PASS';

> flush privileges;

// Change user password 

> SET PASSWORD FOR 'newusername'@'hostname' = PASSWORD('new-password');

> flush privileges;

mysqldump: ambiguous option '--all' (all-databases, allow-keywords) fsbackup

Есть такая хорошая штука, как MySQL-Maint
при бэкапе mysql базы данных получаем ошибку

mysqldump: ambiguous option '--all' (all-databases, allow-keywords)

открываем скрипт и удаляем ключ --all

if [ "_$backup_method" = "_full" ]; then
    echo "Creating full backup of all MySQL databases."
    ${backup_progdump_path}mysqldump --all --add-drop-table --all-databases --force --no-data $extra_mysqldump_flag --password=$backup_mysqlpassword --user=$backup_mysqluser > $backup_path/$backup_name-struct-mysql
    ${backup_progdump_path}mysqldump --all --all-databases  --add-drop-table --force $extra_mysqldump_flag --password=$backup_mysqlpassword --user=$backup_mysqluser |gzip > $backup_path/$backup_name-mysql.gz
    exit
fi