mysql

mysql: BIGINT UNSIGNED value is out of range in

Всё дело в режимах sql_mode
Проблема такая вылезла у меня на MariaDB

> show variables LIKE 'sql_mode';
+---------------+--------------------------------------------------------------------+
| Variable_name | Value                                                              |
+---------------+--------------------------------------------------------------------+
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION                         |
+---------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

DEBIAN [Warning] Could not increase number of max_open_files to more than 16364 (request: 17059)

Случился такой WARNING в логах, после тюннинга mysql.

Помогла правка лимитов, только в systemd mysql

# grep Limit /etc/systemd/system/mysql.service 
LimitNOFILE=16364

меняем значение на

LimitNOFILE=65535

а затем

systemctl daemon-reload
systemctl restart mysql

Все варнинги и ошибки ушли.

Облегчаем docker контейнеры

Иногда требуется сократить использование RAM, например на слабых хостингах, в тестовых средах или для локальной разработки.

Для сокращения использования памяти контейнерами mysql, mariadb можно добавить "--innodb-buffer-pool-size=64m" к запуску. Например для docker-compose:

  db:                                                                                             
    image: mariadb:10.1
    ...                                                                          
    command: --innodb-buffer-pool-size=64m

LOAD DATA INFILE ERROR 1045 (28000): Access denied for user

GRANT FILE ON *.* TO USER@localhost

Verify MySQL replication integrity

Запустить тест:
pt-table-checksum --no-check-replication-filters

Проверить результаты:
pt-table-checksum --no-check-replication-filters --replicate-check-only

MySQL Character Set Support

Server Character Set and Collation

character_set_server collation_server

The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.

Database Character Set and Collation

character_set_database and collation_database

MySQL Performance Tuning: EXPLAIN

EXPLAIN Types

system              The table has only one row 
const               At the most one matching row, treated as a constant 
eq_ref              One row per row from previous tables 
ref                 Several rows with matching index value 
ref_or_null         Like ref, plus NULL values 
index_merge         Several index searches are merged 
unique_subquery     Same as ref for some subqueries 
index_subquery      As above for non-unique indexes 
range               A range index scan 
index               The whole index is scanned 

Advanced MySQL Replication Techniques

http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.htm...

In this broader schema, water is the master of air and slave of earth, which is slave of fire; and this last is in turn slave of air, thus completing the circle. The boxed numbers next to each server indicate the server ID, which must be different for each node; the auto_increment_increment, the same for all the nodes; and the auto_increment_offset, which guarantees the uniqueness of self-generated keys.

Here is the complete setup for all nodes:

# node A - water
[mysqld]

Increase innodb_log_file_size

If you modify the innodb_log_file_size, MySQL will fail to restart and InnoDB will complain about the size of the changed log file.

The proper way to increase the innodb_log_file_size:
shutdown mysql server
make backup of data and log files
remove InnoDB log files
set new value for innodb_log_file_size in my.cnf
start mysqld
check error logs to ensure everything went fine.

Host 'hostname' is blocked because of many connection errors

Host 'hostname' is blocked because of many connection errors.Unblock with 'mysqladmin flush-hosts'

проблема в том, что было слишком много ошибок при подключении к mysqld с этого хоста.
возможн это были прерваные запросы. поэтому мускул решил что это атака и заблокировал доступ этому хосту, пока не выполнишь команду mysqladmin flush-hosts
по дефолту значение стоит равное 10
его можно увеличить следующим образом

# safe_mysqld -O max_connect_errors=100 &

либо внести в конфиг my.cnf в секцию [mysqld]:
max_connect_errors = 100