Modifications entre les versions 2 et 3
⇤ ← Version 2 à la date du 2016-09-15 19:51:23 →
Taille: 81
Éditeur: JeanChristopheAndré
Commentaire:
← Version 3 à la date du 2016-09-16 20:16:05 → ⇥
Taille: 3904
Éditeur: LionelValentin
Commentaire:
Texte supprimé. Texte ajouté.
Ligne 3: Ligne 3:

= REPLICATION =

== Config MASTER : ==

=== /etc/mysql/conf.d/local.conf ===

[mysqld]
language = /usr/share/mysql/french
bind-address = 0.0.0.0
log_slow_queries = /var/log/mysql/mysql-slow.log

#Replication master->slave
server-id = 130
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = drupal

[mysqldump]
quote-names

=== MySQL ===

mysql> GRANT REPLICATION SLAVE ON *.* TO 'auf_repl'@'prod-db02-idneuf.vpc02.auf' IDENTIFIED BY 'fb327bb1765683e9fe0278';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 445 | drupal | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


== Config SLAVE ==

===/etc/mysql/conf.d/local.conf===

[mysqld]
language = /usr/share/mysql/french
bind-address = 0.0.0.0
log_slow_queries = /var/log/mysql/mysql-slow.log

#Replication master->slave
server-id = 131
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = drupal

[mysqldump]
quote-names

=== MySQL ===

mysql> CHANGE MASTER TO MASTER_HOST='prod-db01-idneuf.vpc02.auf',MASTER_USER='auf_repl', MASTER_PASSWORD='fb327bb1765683e9fe0278', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=719;
ERROR 1198 (HY000): Cette opération ne peut être réalisée avec un esclave actif, faites STOP SLAVE d'abord
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='prod-db01-idneuf.vpc02.auf',MASTER_USER='auf_repl', MASTER_PASSWORD='fb327bb1765683e9fe0278', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=719;
Query OK, 0 rows affected (0.01 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: prod-db01-idneuf.vpc02.auf
                  Master_User: auf_repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 719
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_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: 719
              Relay_Log_Space: 107
              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: 2005
                Last_IO_Error: error connecting to master 'auf_repl@prod-db01-idneuf.vpc02.auf:3306' - retry-time: 60 retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 130
1 row in set (0.00 sec)

ERROR:
No query specified

Rédiger « OVH/idneuf/MySQL » ici.

REPLICATION

== Config MASTER : ==

/etc/mysql/conf.d/local.conf

[mysqld] language = /usr/share/mysql/french bind-address = 0.0.0.0 log_slow_queries = /var/log/mysql/mysql-slow.log

#Replication master->slave server-id = 130 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = drupal

[mysqldump] quote-names

MySQL

mysql> GRANT REPLICATION SLAVE ON *.* TO 'auf_repl'@'prod-db02-idneuf.vpc02.auf' IDENTIFIED BY 'fb327bb1765683e9fe0278'; Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.01 sec)

mysql> SHOW MASTER STATUS; +

+

+

+

+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +

+

+

+

+ | mysql-bin.000001 | 445 | drupal | | +

+

+

+

+ 1 row in set (0.00 sec)

mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

Config SLAVE

===/etc/mysql/conf.d/local.conf===

[mysqld] language = /usr/share/mysql/french bind-address = 0.0.0.0 log_slow_queries = /var/log/mysql/mysql-slow.log

#Replication master->slave server-id = 131 relay-log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = drupal

[mysqldump] quote-names

MySQL

mysql> CHANGE MASTER TO MASTER_HOST='prod-db01-idneuf.vpc02.auf',MASTER_USER='auf_repl', MASTER_PASSWORD='fb327bb1765683e9fe0278', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=719; ERROR 1198 (HY000): Cette opération ne peut être réalisée avec un esclave actif, faites STOP SLAVE d'abord mysql> stop slave; Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='prod-db01-idneuf.vpc02.auf',MASTER_USER='auf_repl', MASTER_PASSWORD='fb327bb1765683e9fe0278', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=719; Query OK, 0 rows affected (0.01 sec)

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

mysql> show slave status\G; *************************** 1. row ***************************

  • Slave_IO_State: Connecting to master
    • Master_Host: prod-db01-idneuf.vpc02.auf Master_User: auf_repl Master_Port: 3306
    • Connect_Retry: 60
  • Master_Log_File: mysql-bin.000001
  • Read_Master_Log_Pos: 719
    • Relay_Log_File: mysql-relay-bin.000001
      • Relay_Log_Pos: 4
  • Relay_Master_Log_File: mysql-bin.000001
    • Slave_IO_Running: Connecting
    • Slave_SQL_Running: Yes
      • Replicate_Do_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: 719
      • Relay_Log_Space: 107 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_SQL_Errno: 0 Last_SQL_Error:
  • Replicate_Ignore_Server_Ids:
    • Master_Server_Id: 130

1 row in set (0.00 sec)

ERROR: No query specified

OVH/VPC02/idneuf/MySQL (dernière édition le 2016-10-06 13:34:22 par LionelValentin)