Оптимизация архитектуры MySQL

Архитектура MySQL
Оптимизация архитектуры MySQL

  • Репликация mysql:

      实现在不同服务器上的分布：
  	利用二进制日志增量进行；
  	不需要太多的带宽，但是使用基于行的复制在进行大批量的更改时，会对带宽带来一定的压力，特别是跨IDC环境下进行的复制；	
  	应该分批进行
  实现数据读取的负载均衡，需要其他组件配合使用
  增加数据的安全性
  实现数据库高可用和故障切换
  实现数据库在线升级

  • Двоичный журнал MySQL: записывает все добавления, удаления и изменения в базе данных MySQL, а также изменения в таблицах и базах данных.

  • Инструмент командной строки binlog для просмотра

    Двоичный формат журнала:

      基于段的日志格式： binlog_format=STATEMENT

  基于行的日志格式： binlog_format=ROW
  	binlog_row_image=[FULL|MINIMAL|NOBLOB]

  mysqlbinlog -vv 日志文件名称；
  
  混合日志格式：binlog_format=MIXED
  	根据sql语句由系统决定使用基于段还是基于行的日志格式；
  	数据量的大小由所执行的sql语句决定

    Рекомендуется использовать binlog_format=MIXED или binlog_format=ROW и установить binlog_row_image=MINIMAL.

  • Репликация на основе операторов SQL (SBR)

    преимущество: 生成的日志量少，节约网络传输IO 并不强制要求主从数据库的表定义完全相同 相比于基于行的复制方式更为灵活

    недостаток: Для недетерминированных событий согласованность данных ведущий-ведомый не может быть гарантирована. Изменения, внесенные хранимыми процедурами, триггерами и пользовательскими функциями, также могут привести к несогласованности данных. Требуется больше блокировок строк при выполнении сверху по сравнению с репликацией на основе строк.

  • Построчная репликация (PBR)

    преимущество:

      可以应用于任何SQL的复制包括非确定确定函数，存储过程等；
  可以减少数据库锁的使用

    недостаток:

      要求主从数据库的表的结构相同，负责可能会中断复制；
  无法在从服务器上单独执行触发器

  • Как работает репликация mysql

mysql复制的工作方式

  1. Мастер-сервер записывает изменения в бинарный файл
  2. Прочитайте изменения бинарных журналов Master Server с сервера и напишите их в Relay_Log
  3. Логин воспроизводится с сервера в reli_log
Шаги для настройки репликации на основе точки журнала:
1.在主DB服务器上建立复制账号
	
	create user 'repl' @ 'IP段' identified by 'password';
	grant replication slave on *.* to 'repl' @ 'IP段';//授权
	
2.配置主库服务器

	bin_log=mysql-bin
	server_id = 100

3.配置数据库从服务器
	
	bin_log = mysql-bin
	server_id = 101
	relay_log = mysql-relay-bin
	log_slave_update = on[可选]
	read_only = on[可选]

4.初始化从服务器数据
	
	mysqldump --master-data=2 -single-transaction
	xtrabackup --slave-info[对innodb性能不进行锁表]

5.启动复制链路
	
	change master to master_host = '主服务器的ip地址' ，
		master_user = 'repl'，
		master_password = 'password'，
		master_log_file = 'mysql_log_file_name'，
		master_log_pos = 4; 
实际的配置：

	1.配置主库：
		create user repl@'192.168.25.%' identified by '123456';
		grant replication slave on *.* to repl@'192.168.25.%';
	
	2.修改主从配置文件

	3.主数据库备份：mysqldump --single-transcation --master-data --triggers --			routines --all-databases >> all.sql

	4.将主库的生成的备份文件传递到从服务器上：scp all.sql root@192.168.15.130:/root

	5.在从服务器上执行该sql文件： mysql -uroot -p < all.sql

	6.在从服务器上配置数据链路：change master to master_host='192.168.25.33',
							master_user='repl',
							master_password='123456',
							master_log_file='mysql-bin.0000003',
							mater_log_pos=1829; 
	
	7.在从服务器上启动复制链路：start slave;
基于日志点的复制优点：
	是mysql最早的复制技术，bug较少
	对于sql查询没有任何限制
	故障处理比较容易
基于日志点的复制缺点：
	故障转移时重新获取新主的日志点的信息比较困难
Шаги для настройки репликации на основе GTID:
	1.在主DB服务器上简历复制账号
	
		create user 'repl' @ 'IP段' identified by 'password';
		grant replication slave on *.* to 'repl' @ 'IP段';//授权
	
	2.配置主库服务器

		bin_log=mysql-bin
		server_id = 100
		gtid_mode = on
		
		enforce-gtid-constistency
			不支持以下操作：create table ... select
						  在事务中使用create temporary建立临时表
						  使用关联更新事务表和非事务表
		log-slave-updates = on[5.7版本之前加]

	3.配置数据库从服务器
	
		server_id = 101
		relay_log = mysql-relay-bin
		gtid_mode=on
		enforce-gtid-constistency
		read_only = on[可选]
		master_info_repository=table
		relay_log_info_repository=table
	
	4.初始化从服务器数据
	
		mysqldump --master-data=2 -single-transaction
		xtrabackup --slave-info

	5.启动复制链路
		
		change master to master_host = '主服务器的ip地址' ，
			master_user = 'repl'，
			master_password = 'password'，
			master_auto_position=1;

基于gtid的复制的优点：
	可以很方便的进行故障的转移
	从库不会丢失主库上的任何修改

基于gtid的复制的缺点：
	故障处理比较复杂
	对执行的SQL有一定的限制

  • Как выбрать режим копирования

    1. Используемая версия MySQL
    2. Архитектура репликации и метод переключения ведущий-ведомый
    3. Используемые компоненты управления высокой доступностью
    4. уровень поддержки приложения

  • Топология репликации MySQL

    До MySQL 5.7 подчиненная библиотека могла иметь только одну основную библиотеку.

    После MySQL 5.7 он поддерживает архитектуру с одним ведомым устройством и несколькими ведущими.

Общие рекомендации по проектированию топологии и проектированию:

  • Топология репликации «один главный — несколько подчиненных»

一主多从的复制拓扑

优点：配置简单
	 可以用多个从库分担读负载

用途：为不同业务使用不同的从库
	 将一台从库放到远程的IDC中，用作灾备恢复
	 分担主库的读负载
  • Топология репликации Master-Master

主-主复制拓扑

缺点：
	产生数据冲突而造成复制链路的中断
	耗费大量的时间
	造成数据的丢失

主主模式下的主主复制的配置的注意事项：
	两个主中所操作的表最好能够分开
	使用下面两个参数自增ID的生成
		auto_increment_increment = 2
		auto_increment_offset = 1|2
  • Главная - Топология репликации Подготовка

主-备复制的拓扑

特点：
	只有一台主服务器对外提供服务
	一台服务器处于只读状态并且只作为热备使用
	在对外提供的主库出现故障或是计划性的维护时才会进行切换,使原来的备库成为主库，而原来的主库会成为新的备库并处理只读或下线状态，待维护完成后重新上线

主备模式下的主主复制的配置的注意事项：
	确保两台服务器上的初始化的初始数据相同
	确保两台服务器上已经启动binlog并且有不同的server_id
	在两台服务器上启用log_slave_updates参数
	在初始的备库上启用read_only
  • Топология для репликации master-master с ведомыми устройствами

带从服务器的主主复制的拓扑

  • Топология каскадной репликации

    Настройте главный сервер и подчиненный сервер для распространения и репликации через подчиненный сервер.

    каскадная репликация mysql

Оптимизация производительности репликации MySQL

  • Факторы, влияющие на задержку Master-Slave
  1. Время, когда основная библиотека записывает бинарный журнал --> контролирует размер транзакций, выполняемых в основной библиотеке, и отделяет большие транзакции
  2. Время передачи двоичного журнала --> использовать формат смешанного журнала, установить set binlog_row_image=minimal;
  3. По умолчанию существует только один поток SQL, и одновременные изменения в основном потоке становятся сериализованными --> использовать многопоточную репликацию.

  • Как настроить многопоточную репликацию базы данных в MySQL 5.7 [Многопоточная репликация была введена в MYSQL 5.6, а потоки SQL можно распределять по логическим часам в MySQL 5.7]:

    1.stop slave // ​​Остановить репликацию master-slave 2.set global slave_parallel_type='logical_clock';//Устанавливаем способ распределения потоков 3.set global slave_parallel_workers=4;//Установить 4 потока репликации 4.стартовый ведомый;

Часто задаваемые вопросы о репликации MySQL

  • Ошибки репликации Master-Slave из-за повреждения или потери данных

    Основная библиотека или ошибки библиотеки из-за незапланированного простоя, вызванные -> пропустить события бинарного журнала/внедрение космических дел, способ восстановить прерванную копию ссылки, а затем использовать другие методы для сравнения данных с главного сервера

    Двоичный журнал в основной библиотеке поврежден --> повторно указан командой change master

    Журнал ретрансляции в резервной базе данных поврежден.

    Ошибка репликации master-slave, вызванная модификацией данных в подчиненной базе данных

    Не уникальный server_id или server_uuid (server_uuid записан в файле auto.cnf в каталоге данных, если mysql существует, server_uuid не будет сгенерирован)

    Ошибка репликации master-slave, вызванная настройкой max_allow_packet

Проблема, которую репликация master-slave MySQL не может решить

  • Невозможно разделить нагрузку записи первичной базы данных
  • Аварийное переключение и переключение ведущий-ведомый не могут выполняться автоматически.
  • Не обеспечивает разделения чтения и записи

Используйте репликацию master-slave для создания высокодоступной архитектуры

  • Высокая доступность означает повышение доступности систем и приложений за счет сведения к минимуму объема, вызванного плановыми операциями обслуживания (запланированными) и внезапными сбоями системы (незапланированными).

  • Высокая доступность

    «Факторы, предотвращающие недоступность системы, чтобы сократить время, в течение которого система недоступна [на сервере недостаточно места на диске, низкая производительность SQL и структура индексной таблицы не оптимизированы, основные данные из-за несогласованности, человеческие ошибки]

    》Установить полную систему мониторинга и сигнализации

    》Проверка восстановления данных резервной копии

    》Правильно настройте среду базы данных

    》Архивируйте и удаляйте ненужные данные

    》Увеличить избыточность системы, чтобы гарантировать, что система может быть восстановлена ​​как можно скорее, когда система недоступна [избегайте единой точки отказа, переключения между ведущими и подчиненными и аварийного переключения]

      利用sun共享存储或者drdb磁盘复制解决MySQL单点故障
  利用多写集群或者NDB集群来解决MySQL单点的故障
  利用MySQL的复制来解决MySQL的单点登录的问题

Архитектура MMM для базы данных MySQL

Аварийное переключение в случае простоя ведущего устройства и автоматическая настройка других ведомых устройств для репликации на новое ведущее устройство.

Предоставляет основной и записываемый виртуальный IP-адрес, который может автоматически переносить виртуальный IP-адрес при возникновении проблем с главным и подчиненным серверами.

MySQL数据库的MMM架构

  • Ресурсы, необходимые для развертывания MMM
имя ресурса количество инструкция
Главный сервер БД 2 Конфигурация основной основной копии для режима подготовки хоста
с сервера БД 0-N Можно настроить 0 или более подчиненных серверов, но не рекомендуется слишком много
монитор сервера 1 Используется для мониторинга кластера мониторинга mysql
айпи адрес 2*(n+1) n - количество серверов MySQL
Монитор пользователей 1 Пользователь MySQL для мониторинга состояния базы данных (клиент репликации)
прокси-пользователь 1 Пользователь MySQL для прокси MMM (супер, клиент репликации, процесс)
скопировать пользователя 1 Пользователь MySQL для настройки репликации MySQL (подчиненное устройство репликации)
Шаги развертывания для архитектуры MMM

  1. Настройте основную копию и кластер синхронизации master-slave.

  2. Установите пакет поддержки (для каждого пакета зависимостей), необходимый для главного и подчиненных узлов.

  3. Установите и настройте набор инструментов MMM

  4. Запустить МММ-монитор

  5. контрольная работа

Подробная конфигурация архитектуры MMM архитектуры высокой доступности MySQL

Преимущества и недостатки кластера МММ:

》Преимущества кластера МММ:

  1. Разработано и полностью открыто с использованием языка сценариев Perl.
  2. Предоставляет VIP для чтения и записи (виртуальный IP-адрес), делая изменения ролей сервера прозрачными для интерфейсных приложений.
  3. MMM обеспечивает мониторинг латентности подчиненных серверов
  4. MMM предоставляет возможность повторной синхронизации ведомого устройства с новым ведущим устройством после аварийного переключения основной базы данных.
  5. Неудавшуюся первичную базу данных легко вернуть в оперативный режим.
  6. MMM обеспечивает мониторинг латентности подчиненных серверов

》Недостатки кластера МММ:

  1. Время выпуска относительно раннее и не поддерживает новую функцию репликации MySQL.
  2. Нет функции балансировки нагрузки чтения
  3. Во время переключения ведущий-ведомый легко вызвать потерю данных
  4. Сервис мониторинга МММ имеет единую точку отказа

Схема MHA для базы данных MySQL

Мониторинг основного сервера базы данных доступен

Когда основной БД недоступен, выберите новый первичный сервер базы данных из нескольких с сервера.

Обеспечивает переключение ведущий-ведомый и аварийное переключение (MHA можно использовать в сочетании с полусинхронными функциями)

  • Как MHA выполняет переключение ведущий-ведомый

    При сбое основной базы данных попытайтесь сохранить двоичный журнал из отказавшей первичной базы данных.

    Выбор нового альтернативного главного сервера из нескольких альтернативных подчиненных серверов

    Синхронизируйте дифференциальные двоичные данные между альтернативным ведущим устройством и другими подчиненными устройствами.

    Применить для сохранения из исходного двоичного журнала главного сервера БД

    Улучшить альтернативный главный сервер БД для нового основного сервера БД

    Перенесите другие подчиненные БД в кластере в качестве подчиненных серверов новой главной БД.

  • MHA поддерживает репликацию GTID и репликацию точек журнала.

  • MMM не поддерживает репликацию GTID

Этапы развертывания для архитектуры MHA

  1. Настройте вход без аутентификации SSH для всех серверов в кластере.

    ssh -keygen

    ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.3.100

    ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.3.101

    ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.3.102

  2. Установите пакет MHA-node и пакет MHA-manager

  3. Установите пакет поддержки для MHA

    监控节点：yum -y install perl-Config-Tiny.noarch perl-Time-HiRes.X86_64 perl-Parallel-ForkManager perl-Log-Dispatch-Perl.noarch per-DB-MySQL ncftp

    数据节点：yum -y install perl -DBD-MySQL ncftp perl-DBI.X86

  4. Создайте кластер репликации master-slave

  5. Настройте узел управления MHA

  6. Проверьте конфигурацию с помощью master_check_ssh и masterha_check_repl.

  7. Запустите и протестируйте службу MHA

Введение MHA и настройка архитектуры высокой доступности MySQL

Преимущества и недостатки кластера MHA:

》Преимущества кластера MHA:

  1. Разработано и полностью открыто с использованием языка сценариев Perl.
  2. Может поддерживать режим копирования на GTID
  3. MHA менее подвержен потере данных при отработке отказа
  4. Один и тот же узел мониторинга может отслеживать несколько кластеров.

》Недостатки кластера МММ:

  1. Необходимо написать сценарии или использовать сторонние инструменты (keepalive и т. д.) для настройки VIP.
  2. После запуска MHA будет отслеживаться только первичная база данных.
  3. Его необходимо настроить на основе аутентификации без SSH, и существуют определенные риски безопасности.
  4. Не обеспечивает балансировку нагрузки чтения с сервера

Разделение базы данных на чтение и запись (как выполнять разные операторы SQL на разных ролях кластера репликации)

Программно реализованное разделение чтения и записи

优点：由开发人员控制什么样的查询再从库上来执行，因此比较灵活
	 由程序直接连接诶数据库，所以性能损耗比较少

缺点：增加了开发的工作量，使得程序代码更为复杂人为控制，
	 容易出现错误

Разделение промежуточного ПО для чтения и записи (mysql-proxy, maxScale)

优点：中间件根据查询语法的分析，自动完成读写分离；
	 对程序透明，对已有的程序不用做任何的调整。

缺点：增加了中间层，所以对查询效率有损耗；
	 对于延迟敏感业务无法在主库上执行

Реализовать балансировку нагрузки чтения (как базы данных с одной и той же ролью распределяют одну и ту же нагрузку)

Программное обеспечение: LVS, Haproxy, MaxScale

Оборудование: F5

Плагин maxScale:

maxScale插件体系结构

  1. Плагин проверки подлинности
  2. Плагин протокола протокола
  3. Плагины маршрутизации маршрутизатора (readconnroute, readwritesplit)
  4. Плагин мониторинга MONitor
  5. Плагины для логирования и фильтрации Filter&Logging

maxScale реализует разделение чтения и записи и балансировку нагрузки.

Категории