Mysql Master-Master Replication (MM репликация) и грабли

О чём статья

В организации Мастер-Мастер репликации в сервере баз данных Mysql (Мускул по нашему))) в принципе не такое и тяжёлое дело. Вся настройка сводится к двум простым этапам. В принципе в Интернете уже сто тысяч раз писали на эту тему.
Другое дело, что с работоспособностью репликации нет статей. Всё эта статья есть опыт (сын ошибок), который Я приобрёл в «процессе».
Попробую дать «своё» определение репликации данных:

Репликация или синхронизации сервера базы данных — копирование всех изменений в базах данных с одного на другой или несколько серверов. Мастер-Мастер репликация — поддержание одинаковых данных на 2-х (и более) серверах, когда не имеет значения на кокам сервере происходят изменения этих самых данных

Этап первый

Итак если Вы уже настроили свою Мастер-Мастре репликацию на Мускуле, то можете смело переходить к расмотрению граблей)

Дня начала настройки master master репликации в сервере mysql Вы должны понять, что такого режима не существует.
Дело в том, что изначально в mysql поддерживается синхронизация данных: Master-Slave Replication — это когда на одном сервере(master) происходят изменения, которые появляются на другом(slave). При отключении master’а, можно работать со slave’ом.

Минусы Мастер-Слайв Репликации (синхронизации): при переключении на слейв и внемении там изменений на мастере они никак не отобразятся. Именно для этого существует Мастер-Мастер репликация.

Мастер-Мастер реплицация представляет собой две(!!!) Мастер-Слейв репликации. смотрите на рисунок:

Пояснение к рисунку: у нас два сервера (Мастер и Мастер), но у нас так же и две синхроницации. Сервер DB-1 выступает Мастером, а Сервер DB-2 выступает слейвом при изменении данных на сервере баз данных DB-1. В случае изменения данных на сервере Сервер DB-2 он у нас становится Мастером, а Сервер DB-1 — Слейвом.

Для настройки Мастер-Мастер репликации нужно создать на обоих серверах пользователей, которым разрешено делать репликацию, а так же иметь доступ к базам данных которые синхронизируются(копируются). Так же для обоих сервером желательны идентичные настройки, что бы не было проблем.

Пример моей конфигурации на Linux 2.6.32 debian 5.0.9:

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
 
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
 
[mysqld]
 
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
old_passwords=1
 
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
bind-address            = 0.0.0.0
 
key_buffer		= 16M
max_allowed_packet	=  1000M
thread_stack		= 128K
thread_cache_size	= 8
myisam-recover		= BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
skip-name-resolve
 
server-id		= 1
log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 60
max_binlog_size         = 3000M
 
master-host = 191.168.1.1
master-user = replication
master-password = 66666666666666666
master-port = 3306
master-connect-retry=2
master-retry-count=4294967290
slave-net-timeout = 30
auto-increment-increment=10
auto-increment-offset=1 
 
replicate-do-db =blog
#replicate-do-db =mysql
#replicate-do-db  =information_schema
 
binlog_ignore_db	=mysql
binlog_ignore_db	=information_schema
skip-bdb
 
 
[mysqldump]
quick
quote-names
max_allowed_packet	= 1000M
 
[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition
 
[isamchk]
key_buffer		= 16M
 
#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1
 
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Грабли и их решение

1.Итак Вы можете не догадываться, но ваши файлы mysql-сервера могут быть испорчены!!! из-за этого репликация может не проходить (SQL-запросы не отрабатываются)

 
Feb 16 06:22:57 db2 mysqld[407]: 120216  6:22:57 [ERROR] Error reading packet FROM server: File '/var/log/mysql/mysql-bin.000091' NOT found (Errcode: 2) ( server_errno=29)
Feb 16 06:23:57 db2 mysqld[407]: 120216  6:23:57 [Note] Slave I/O thread: Failed reading log event, reconnecting TO retry, log 'mysql-bin.000091' POSITION 17992420
Feb 16 06:23:57 db2 mysqld[407]: 120216  6:23:57 [Note] Slave: connected TO master [email protected]:3306',replication resumed IN log 'mysql-bin.000091' at POSITION 17992420
Feb 16 06:23:57 db2 mysqld[407]: 120216  6:23:57 [ERROR] Error reading packet FROM server: File '/var/log/mysql/mysql-bin.000091' NOT found (Errcode: 2) ( server_errno=29)
Feb 16 06:24:57 db2 mysqld[407]: 120216  6:24:57 [Note] Slave I/O thread: Failed reading log event, reconnecting TO retry, log 'mysql-bin.000091' POSITION 17992420
Feb 16 06:24:57 db2 mysqld[407]: 120216  6:24:57 [Note] Slave: connected TO master [email protected]:3306',replication resumed IN log 'mysql-bin.000091' at POSITION 17992420
Feb 16 06:24:57 db2 mysqld[407]: 120216  6:24:57 [ERROR] Error reading packet FROM server: File '/var/log/mysql/mysql-bin.000091' NOT found (Errcode: 2) ( server_errno=29)
Feb 16 06:25:57 db2 mysqld[407]: 120216  6:25:57 [Note] Slave I/O thread: Failed reading log event, reconnecting TO retry, log 'mysql-bin.000091' POSITION 17992420
Feb 16 06:25:57 db2 mysqld[407]: 120216  6:25:57 [Note] Slave: connected TO master [email protected]:3306',replication resumed IN log 'mysql-bin.000091' at POSITION 17992420
Feb 16 06:25:57 db2 mysqld[407]: 120216  6:25:57 [ERROR] Error reading packet FROM server: Client requested master TO START replication FROM impossible POSITION ( server_errno=1236)
Feb 16 06:25:57 db2 mysqld[407]: 120216  6:25:57 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' FROM master WHEN reading DATA FROM BINARY log
Feb 16 06:25:57 db2 mysqld[407]: 120216  6:25:57 [Note] Slave I/O thread exiting, READ up TO log 'mysql-bin.000091', POSITION 17992420
Feb 16 09:16:02 db2 mysqld_safe[405]: started
Feb 16 09:16:02 db2 mysqld[408]: 120216  9:16:02 [Warning] The syntax FOR replication startup options IS deprecated AND will be removed IN MySQL 5.2. Please USE 'CHANGE MASTER' instead.
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: The log SEQUENCE NUMBER IN ibdata files does NOT MATCH
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: the log SEQUENCE NUMBER IN the ib_logfiles!
Feb 16 09:16:03 db2 mysqld[408]: 120216  9:16:03  InnoDB: DATABASE was NOT shut down normally!
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: Starting crash recovery.
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: Reading tablespace information FROM the .ibd files...
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: Restoring possible half-written DATA pages FROM the doublewrite
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: buffer...
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: IN a MySQL replication slave the LAST master binlog file
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: POSITION 0 15075535, file name mysql-bin.000091
Feb 16 09:16:03 db2 mysqld[408]: InnoDB: LAST MySQL binlog file POSITION 0 12959679, file name /var/log/mysql/mysql-bin.000091
Feb 16 09:16:03 db2 mysqld[408]: 120216  9:16:03  InnoDB: Started; log SEQUENCE NUMBER 0 36789779
Feb 16 09:16:03 db2 mysqld[408]: 120216  9:16:03 [Note] Recovering after a crash USING /var/log/mysql/mysql-bin
Feb 16 09:16:04 db2 mysqld[408]: 120216  9:16:04 [Note] Starting crash recovery...
Feb 16 09:16:04 db2 mysqld[408]: 120216  9:16:04 [Note] Crash recovery finished.
Feb 16 09:16:04 db2 mysqld[408]: 120216  9:16:04 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
Feb 16 09:16:04 db2 mysqld[408]: 120216  9:16:04 [Note] /usr/sbin/mysqld: ready FOR connections.
Feb 16 09:16:04 db2 mysqld[408]: Version: '5.0.51a-24+lenny5-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
Feb 16 09:16:04 db2 mysqld[408]: 120216  9:16:04 [Note] Slave SQL thread initialized, starting replication IN log 'mysql-bin.000091' at POSITION 17992420, relay log './mysqld-relay-bin.000155' POSITION: 192
Feb 16 09:16:04 db2 /etc/mysql/debian-START[455]: Upgrading MySQL TABLES IF necessary.
Feb 16 09:16:05 db2 /etc/mysql/debian-START[458]: Looking FOR 'mysql' IN: /usr/bin/mysql
Feb 16 09:16:05 db2 /etc/mysql/debian-START[458]: Looking FOR 'mysqlcheck' IN: /usr/bin/mysqlcheck
Feb 16 09:16:05 db2 /etc/mysql/debian-START[458]: This installation OF MySQL IS already upgraded TO 5.0.51a, USE --force if you still need to run mysql_upgrade
Feb 16 09:16:05 db2 /etc/mysql/debian-START[471]: Checking FOR insecure root accounts.
Feb 16 09:16:05 db2 /etc/mysql/debian-START[475]: Triggering myisam-recover FOR ALL MyISAM TABLES
Feb 16 09:16:05 db2 mysqld[408]: 120216  9:16:05 [ERROR] /usr/sbin/mysqld: TABLE './blog_isp_pl_ua/wp_options' IS marked AS crashed AND should be repaired
Feb 16 09:16:05 db2 mysqld[408]: 120216  9:16:05 [Warning] Checking TABLE:   './blog_isp_pl_ua/wp_options'
Feb 16 09:16:05 db2 mysqld[408]: 120216  9:16:05 [ERROR] /usr/sbin/mysqld: TABLE './blog_kh_ua/cache_page' IS marked AS crashed AND should be repaired
Feb 16 09:16:05 db2 mysqld[408]: 120216  9:16:05 [Warning] Checking TABLE:   './blog_kh_ua/cache_page'
Feb 16 09:16:06 db2 init: no more processes LEFT IN this runlevel
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [ERROR] /usr/sbin/mysqld: TABLE './blog_kh_ua/sessions' IS marked AS crashed AND should be repaired
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [Warning] Checking TABLE:   './blog_kh_ua/sessions'
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [ERROR] /usr/sbin/mysqld: TABLE './blog_wel/wp_options' IS marked AS crashed AND should be repaired
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [Warning] Checking TABLE:   './blog_wel/wp_options'
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [ERROR] /usr/sbin/mysqld: TABLE './cleu/forum_config' IS marked AS crashed AND should be repaired
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [Warning] Checking TABLE:   './cleu/forum_config'
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [ERROR] /usr/sbin/mysqld: TABLE './cleu/forum_login_attempts' IS marked AS crashed AND should be repaired
Feb 16 09:16:07 db2 mysqld[408]: 120216  9:16:07 [Warning] Checking TABLE:   './cleu/forum_login_attempts'
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [ERROR] /usr/sbin/mysqld: TABLE './cleu/forum_sessions' IS marked AS crashed AND should be repaired
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [Warning] Checking TABLE:   './cleu/forum_sessions'
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [ERROR] /usr/sbin/mysqld: TABLE './cleu/forum_topics' IS marked AS crashed AND should be repaired
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [Warning] Checking TABLE:   './cleu/forum_topics'
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [ERROR] /usr/sbin/mysqld: TABLE './cleu/forum_users' IS marked AS crashed AND should be repaired
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [Warning] Checking TABLE:   './cleu/forum_users'
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [ERROR] /usr/sbin/mysqld: TABLE './iphone_2u_biz/wp_options' IS marked AS crashed AND should be repaired
Feb 16 09:16:08 db2 mysqld[408]: 120216  9:16:08 [Warning] Checking TABLE:   './iphone_2u_biz/wp_options'
Feb 16 09:16:09 db2 mysqld[408]: 120216  9:16:09 [ERROR] /usr/sbin/mysqld: TABLE './koctik/wp_options' IS marked AS crashed AND should be repaired
Feb 16 09:16:09 db2 mysqld[408]: 120216  9:16:09 [Warning] Checking TABLE:   './koctik/wp_options'
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [ERROR] /usr/sbin/mysqld: TABLE './tochilka_blog/wp_npe_blacklist' IS marked AS crashed AND should be repaired
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [Warning] Checking TABLE:   './tochilka_blog/wp_npe_blacklist'
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [ERROR] /usr/sbin/mysqld: TABLE './tochilka_blog/wp_npe_whitelist' IS marked AS crashed AND should be repaired
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [Warning] Checking TABLE:   './tochilka_blog/wp_npe_whitelist'
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [ERROR] /usr/sbin/mysqld: TABLE './tochilka_blog/wp_options' IS marked AS crashed AND should be repaired
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [Warning] Checking TABLE:   './tochilka_blog/wp_options'
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [ERROR] /usr/sbin/mysqld: TABLE './vosmi_net/wp1_options' IS marked AS crashed AND should be repaired
Feb 16 09:16:12 db2 mysqld[408]: 120216  9:16:12 [Warning] Checking TABLE:   './vosmi_net/wp1_options'
Feb 16 09:16:25 db2 mysqld[408]: 120216  9:16:25 [ERROR] Slave I/O thread: error connecting TO master [email protected]:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 110'  errno: 2013  retry-TIME: 60  retries: 86400
Feb 16 09:30:56 db2 mysqld[408]: 120216  9:30:56 [Note] Slave I/O thread: connected TO master [email protected]:3306',  replication started IN log 'mysql-bin.000091' at POSITION 17992420
Feb 16 09:30:56 db2 mysqld[408]: 120216  9:30:56 [ERROR] Error reading packet FROM server: Client requested master TO START replication FROM impossible POSITION ( server_errno=1236)
Feb 16 09:30:56 db2 mysqld[408]: 120216  9:30:56 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' FROM master WHEN reading DATA FROM BINARY log
Feb 16 09:30:56 db2 mysqld[408]: 120216  9:30:56 [Note] Slave I/O thread exiting, READ up TO log 'mysql-bin.000091', POSITION 17992420
Feb 16 11:21:39 db2 mysqld[408]: 120216 11:21:39 [Note] Error reading relay log event: slave SQL thread was killed
Feb 16 11:30:26 db2 mysqld[408]: 120216 11:30:26 [Note] Slave SQL thread initialized, starting replication IN log 'mysql-bin.000091' at POSITION 168915503, relay log './mysqld-relay-bin.000040' POSITION: 4
Feb 16 11:30:27 db2 mysqld[408]: 120216 11:30:27 [Note] Slave I/O thread: connected TO master [email protected]:3306',  replication started IN log 'mysql-bin.000091' at POSITION 168915503

Для решения этих проблем нужно воспользоваться myisamchk —silent —force —fast —update-state /var/lib/mysql/*/*.MYI

 
myisamchk: MyISAM file /var/lib/mysql/blog/wp_options.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/blog/wp_options.MYI
myisamchk: warning: Size of indexfile is: 29696         Should be: 24576
myisamchk: error: Size of datafile is: 1487716           Should be: 1488328
myisamchk: MyISAM file /var/lib/mysql/cleu/forum_sessions.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/cleu/forum_topics.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly

Для предотвращения таких проблем на каждом сервере можно в кроне запускать:

5 0     * * *   root  /usr/bin/find /var/lib/mysql -name "*.MYI"|tr '/' ' '|tr '.' ' '|awk '{print "CHECK TABLE `"$4"`.`"$5"`; REPAIR TABLE `"$4"`.`"$5"`; OPTIMIZE TABLE `"$4"`.`"$5"`;"}'| mysql -uroot -pPASSWORD -h 192.168.1.1 > /dev/null 2>&1

Скрипт для ресинхронизации

#!/bin/sh
#rm -rf /sql
mkdir /sql
data=`date "+%Y.%m.%d_%H%M"`
mkdir /sql/$data/
echo /sql/$data/
echo "\n"
echo "stop slave;"| mysql -uroot -pPASS -h 192.168.1.1
echo "stop slave;"| mysql -uroot -pPASS -h 192.168.1.2
 
echo "MASTER on 192.168.1.2"
echo "----------"
echo "show master status\G;"| mysql -uroot -pPASS -h 192.168.1.2
F=`echo 'show master status\G;'| mysql -uroot -pPASS -h 192.168.1.2|grep File`
P=`echo 'show master status\G;'| mysql -uroot -pPASS -h 192.168.1.2|grep Position`
f=`echo $F|awk '{print$2}'`
p=`echo $P|awk '{print$2}'`
 
 
for i in `cat /tables.txt`;do
        echo $i;
        mysqldump -uroot -pPASS -h 192.168.1.2 $i > /sql/$data/$i.sql;
        mysqldump -uroot -pPASS -h 192.168.1.1  $i > /sql/$data/$i.sql.192.168.1.1;
 
        mysql -uroot -pPASS -h 192.168.1.1 $i < /sql/$data/$i.sql;
done
echo "CHANGE MASTER TO MASTER_HOST = \"192.168.1.2\", MASTER_USER = \"replicationuser\", MASTER_PASSWORD = \"4SJSfA8H2HXZQ2jL\", MASTER_LOG_FILE = \"$f\", MASTER_LOG_POS = $p;"| mysql -uroot -pPASS -h 192.168.1.1
echo "start slave;"| mysql -uroot -pPASS -h 192.168.1.1
 
echo "SLAVE on 192.168.1.1"
echo "show slave status\G;"| mysql -uroot -pPASS -h 192.168.1.1
 
F=`echo 'show master status\G;'| mysql -uroot -pPASS -h 192.168.1.1|grep File`
P=`echo 'show master status\G;'| mysql -uroot -pPASS -h 192.168.1.1|grep Position`
f=`echo $F|awk '{print$2}'`
p=`echo $P|awk '{print$2}'`
echo "CHANGE MASTER TO MASTER_HOST = \"178.165.24.231\", MASTER_USER = \"replicationuser\", MASTER_PASSWORD = \"4SJSfA8H2HXZQ2jL\", MASTER_LOG_FILE = \"$f\", MASTER_LOG_POS = $p;"| mysql -uroot -pPASS -h 192.168.1.2
 
#echo "SLAVE on 192.168.1.2"
#echo 'show master status\G;'| mysql -uroot -pPASS -h 192.168.1.2
echo "start slave;"| mysql -uroot -pPASS -h 192.168.1.2

Leave a Comment

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Загрузка...
Menu Title