Шпаргалка по работе с MySQL

Запуск сервера баз данных (Mysql+Postgresql) в виде docker-compose.yml.

Во внешний том вынесены конфигурации и данные MySQL и Postgresql, также настроены health-чеки контейнеров.

version: "3.9"
services:
    mysql:
      container_name: mysql
      image: mysql:5.7
      ports:
        - "3306:3306/tcp"
      stdin_open: true
      tty: true
      restart: always
      environment:
        - MYSQL_ROOT_PASSWORD=password
      healthcheck:
          test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
          timeout: 20s
          retries: 10
      volumes:
      - ./mysql-data:/var/lib/mysql
      - ./mysql/my.cnf:/etc/my.cnf
      - /etc/localtime:/etc/localtime:ro

    postgresql:
      container_name: postgresql
      image: postgres:11.2-alpine
      restart: always
      environment:
        - POSTGRES_USER=postgres
        - POSTGRES_PASSWORD=password
      ports:
        - '5432:5432'
      healthcheck:
        test: ["CMD-SHELL", "pg_isready"]
        interval: 20s
        timeout: 10s
        retries: 5
      volumes: 
        - ./postgresql-data:/var/lib/postgresql/data
        - /etc/localtime:/etc/localtime:ro

Изменить часть строки в столбце

Для автоматизации этой рутинной операции существует две функции REPLACE и REGEXP_REPLACE первая заменяет все вхождения искомой строки на новую, а вторая соответственно делает то же самое, но на базе регулярных выражений. Вот собственно два примера как это работает:

REPLACE

UPDATE TestTable settext=REPLACE(text,'http://emulator','http://crm')

REGEXP_REPLACE

UPDATE TestTable settext= REGEXP_REPLACE(text,'^http://emulator','http://crm');

Настройка Master-Master репликации двух баз данных MySQL

Подготовка хостов

На первом хосте добавляем опции:

# Replication settings
server_id = 1
log_bin = mysql-bin.log
log_bin_index = mysql-bin.log.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
binlog-ignore-db = mysql
binlog-ignore-db = sys
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

Перезапускаем:

# systemctl restart mysql

Логинимся в консоль Mysql и проверяем статус Master-а:

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 124
Server version: 10.3.14-MariaDB-log MariaDB Server

Copyright (c)2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+-------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                |
+------------------+----------+--------------+-------------------------------------------------+
| mysql-bin.000002 |      782 |              | mysql,sys,performance_schema,information_schema |
+------------------+----------+--------------+-------------------------------------------------+
1 row in set(0.000 sec)

 На втором хосте все настройки и шаги аналогичные, кроме:

# Replication settings
server_id = 2
...

Создаем пользователей для репликации

 На первом сервере выполняем команду (в консоли mysql):

> > GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.54.110.41' IDENTIFIED BY 'secretPASSWORD';

 Проверяем, что пользователь создан:

MariaDB [(none)]> select Host, User from mysql.user where User='replication';
+--------------+-------------+
| Host         | User        |
+--------------+-------------+
| 10.54.110.41 | replication |
+--------------+-------------+
2 rows in set(0.000 sec)

На втором сервере аналогично, но разрешаем доступ с адреса  первого.

> > GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.77.77.254' IDENTIFIED BY 'secretPASSWORD';

Снимаем дамп необходимых таблиц с первого сервера и разворачиваем на втором

Первый сервер:

# mysqldump database > /tmp/dump.sql
# scp /tmp/dump.sql chernousov@10.54.110.41:/tmp/

Второй сервер:

# cat /tmp/dump.sql | mysql database

Настраиваем репликации второго сервера с первого

Данные по текущему бинлогу и позиции мастера берем естественно с мастера:

> STOP SLAVE;
> CHANGE MASTER TO master_host='10.77.77.254', master_port=3306, master_user='replication', master_password='secretPASSWORD', master_log_file='mysql-bin.000001', master_log_pos=782;
> START SLAVE;

Настраиваем репликации первого сервера со второго

Данные по текущему бинлогу и позиции мастера берем естественно с мастера:

> STOP SLAVE;
> CHANGE MASTER TO master_host='10.54.110.41', master_port=3306, master_user='replication', master_password='secretPASSWORD', master_log_file='mysql-bin.000002', master_log_pos=342;
> START SLAVE; 0

Ошибка row size is 8155 which is greater than maximum allowed size (8126) при работе с временными таблицами

В конфиге надо вот такое допилить:

internal_tmp_disk_storage_engine = MyISAM

Резервное копирование всех баз данных MySQL кроме системных

#!/bin/sh

MYSQL_USER=root
MYSQL_PASS=SecretPassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h 127.0.0.1"
#
# Collect all database names except for
# mysql, information_schema, performance_schema and sys
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema','sys')"
 
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
 
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
 
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql

Обслуживание базы данных

Инициализация каталога с данными:

# mysqld --initialize-insecure --user=mysql
# mysql_install_db --user=mysql

Работа с пользователями

Список пользователей:

>SELECT * FROM mysql.user;

Смена пароля пользователя root:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PassoRD7';
ALTER USER 'root'@'%' IDENTIFIED BY 'PassoRD7';
FLUSH PRIVILEGES;