mysql master-slave

[playnet]

Мастер работает как нужно, надо для начала сделать к нему слейва, и при необходимости менять их ролями.
Возможно, дальше будет мастер-мастер, но для начала надо слейв отладить.
Итак. Создал юзера на мастере, сконнектил со слейвом.. Получил

Код: Выделить всё

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> load data from master;
Query OK, 0 rows affected (2.17 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status;
+----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
| Slave_IO_State | Master_Host        | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File        | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                                                                     | Last_SQL_Errno | Last_SQL_Error |
+----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
|                | www1 | replica     |        3306 |            60 | mysql-bin.000025 |            41288204 | www2-relay-bin.000001 |             4 | mysql-bin.000025      | No               | Yes               | db   |                     |                    |                        |                         |                             |          0 |            |            0 |            41288204 |             106 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |          1236 | Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' |              0 |                |
+----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
1 row in set (0.00 sec)

И ЕЩЕ Момент волнует, в доке сказано, с какой-то 5.х не будет больше load data from master, как тогда быть?

Может кто дать асю, немного помучаю вопросами?

[Хостинг HostFood.ru]

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

[playnet]

И еще очень важный вопрос: как проверять "схожесть" данных? Если в базе А 100 записей, в Б тоже должно быть 100. А если я сделал stop slave, через день start slave, как проверить, что все в норме?

[playnet]

вывод статуса в удобном виде

Код: Выделить всё

root@www2:/var/db/mysql# mysql -u root -p -e "show slave status\G"
Enter password:
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: www1
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000025
          Read_Master_Log_Pos: 41288204
               Relay_Log_File: www2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000025
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 41288204
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:

Last_IO_Error перевел, но не понял, что от меня хотят.
Сделал на мастере
echo "">mysql-bin.index
rm mysql-bin.0*

mysql> flush logs;
теперь мастер-статус такой:

Код: Выделить всё

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1018 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

[playnet]

Еще к размышлению..
слейв, еррлог:

Код: Выделить всё

....
100517  2:45:00 [ERROR] Run recovery again without -q
100517  2:45:00 [Note] Retrying repair of: './db/b_sonet_features' with keycache
100517  2:45:00 [ERROR] Delete link points outside datafile at 304
100517  2:45:00 [Note] Retrying repair of: './db/b_sonet_features2perms' with keycache
100517  2:45:00 [ERROR] Delete link points outside datafile at 304
100517  2:45:00 [ERROR] Couldn't fix table with quick recovery: Found wrong number of deleted records
....
100517  2:45:00 [ERROR] Found block that points outside data file at 11164
100517  2:45:00 [ERROR] Found block with too small length at 12916; Skipped
100517  2:45:00 [ERROR] Found block that points outside data file at 20992
100517  2:45:00 [ERROR] Couldn't fix table with quick recovery: Found wrong number of deleted records
....

экранов 20.. это при load data from master сыпется пачками.

Повреждена база? Проверим. (мастер)
sudo mysqlcheck -u root -p -c -e db
Все ОК. Значит, база в норме. Ничего не понимаю!

[playnet]

И еще. Мастер.

Код: Выделить всё

mysql> show binary logs;
Empty set (0.00 sec)

mysql> show binlog events;
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      169 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Ничего не понимаю...