*_*_*_*_* MYSQL REPLICA MASTER MASTER *_*_*_*_*              

Questo tutorial descrive come impostare la replica master-master su 
Mysql. Nel tutorial supponiamo di volere due master sincronizzati tra 
loro in modo che se uno dei due cade, l'altro potrebbero prendere 
il suo posto senza perdere dati. 

Note: il system1 avrà master1 e slave2 mentre il system2 
avrà master2 è slave1.

    +-----------+          +-----------+
    | Sistema 1 |          | Sistema 2 |
    |  *  *  *  |          |  *  *  *  |
    |           |    _____ | MASTER2   |
    |  MASTER1  |--./      |           |
    |   SLAVE2  |--/ \____ | SLAVE1    |
    +-----------+          +-----------+


.......... Step 1 ......................................................

  Installa MySQL  econfigura i servizi di rete sui due sistemi:

  Master1/Slave2 ip: 192.168.16.4
  Master2/Slave1 ip: 192.168.16.5 

.......... Step 2 ......................................................

Su Master1/Slave2, modifica my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

.......... Step 3 ......................................................

Su Master2/Slave 1, modifica my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

.......... Step 4 ......................................................

Su Master1/Slave2, crea un account di replica in MySQL:

mysql> grant replication slave on *.* to 'replication'@'%' \
identified by 'slave';

.......... Step 5 ......................................................

Su Master2/Slave1, crea un account di replica in MySQL:

mysql> grant replication slave on *.* to 'replication'@'%' \
identified by 'slave2';


.......... Step 6 ......................................................

Restart Master1 e Master2.

Star e controllo Slave:

mysql> start slave;
mysql> show slave status\G;


*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.4
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: MASTERMYSQL01-bin.000009
        Read_Master_Log_Pos: 4
             Relay_Log_File: MASTERMYSQL02-relay-bin.000015
              Relay_Log_Pos: 3630
      Relay_Master_Log_File: MASTERMYSQL01-bin.000009
           Slave_IO_Running: Yes
          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: 4
            Relay_Log_Space: 3630
            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: 1519187

1 row in set (0.00 sec)

 
Controllo Master:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 |      410 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

........................................................................
*** COMANDI UTILI ***

RESET MASTER; 

RESET SLAVE;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON TO "utente"@"macchina" \
IDENTIFIED BY "password";

CHANGE MASTER TO MASTER_HOST="masterhost", MASTER_USER="utente", \
MASTER_PASSWORD="password", MASTER_LOG_FILE="mysql-bin.0000xx" \
MASTER_LOG_POS=xx;

START SLAVE;