MYSQL - Privilegi e Sincronizzazione

Per dare tutti i privilegi:

GRANT ALL ON Database TO root@'IP o %' IDENTIFIED BY 'Password';
FLUSH PRIVILEGES;

Esempio: utente/pwd: pippo/pluto - IP: 192.168.0.14 - DB: disney

GRANT ALL ON disney TO pippo@'192.168.0.14' IDENTIFIED BY 'pluto';
FLUSH PRIVILEGES;

============================================================= MYSQL – SINCRONIZZAZIONE DB - da WEB1 a WEB2 ============================================================= /etc/mysql/my.cnf di WEB1: bind-address = 192.168.0.151 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = db_name #binlog_ignore_db = mysql /etc/mysql/my.cnf di WEB2: bind-address = 192.168.0.152 server-id = 2 relay-log = /var/log/mysql/mysql-relay.log log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = db_name #binlog_ignore_db = mysql # #replicate-do-db=db1,db2,db3 #replicate-ignore-db=db4,db5,db6 replicate-ignore-table=db1.table_session replicate-ignore-table=db2.table_test replicate-ignore-table=db3.table_log # Ho commentato la gestione dei DB in quanto non funziona più la replica. #replicate-do-db=db1,db2,db3 #replicate-ignore-db=db4,db5,db6 /etc/init.d/mysql restart per caricare le configurazioni per caricare la modifica delle tabelle replicate bisogna fare service mysql restart ________________________________________ MASTER:

mysql –u root -p
Enter password: ********
mysql> GRANT REPLICATION SLAVE ON *.* TO 'reply'@'%' IDENTIFIED BY 'PASSWORD';
mysql> FLUSH PRIVILEGES;
mysql> USE nome_database;
Database changed
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
            File: mysql-bin.000007
        Position: 107
    Binlog_Do_DB:
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR:
No query specified

... configurazione dello SLAVE e poi si sbloccano tabelle ...

mysql> UNLOCK TABLES;
mysql> quit;

SLAVE

mysql –u root -p
Enter password:
mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.151', MASTER_USER='reply', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS= 523325;
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.151
                Master_User: reply
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000007
        Read_Master_Log_Pos: 16810
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000007
           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: 16810
            Relay_Log_Space: 235
            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: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> quit;

La sincronizzazione funziona se lo SLAVE restituisce Slave_IO_Running: Yes Slave_SQL_Running: Yes Se si vuole monitorare lo status con script cronjob:

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

###check if already notified###
cd /root
if [ -f DB_SYNC_ERR.txt ]; then
rm DB_SYNC_ERR.txt;
fi

###Check if slave running###
(echo "show slave status \G;") | mysql -u root -PASSWORD 2>&1 | grep "Slave_IO_Running: Yes"
if [ "$?" -ne "1" ]; 
   then (echo "show slave status \G;") | mysql -u root -PASSWORD 2>&1 | grep "Slave_SQL_Running: Yes"
        if [ "$?" -ne "1" ]; 
        then exit
        else
            echo "Replication failed" > /root/DB_SYNC_ERR.txt
        fi
   else
       echo "Replication failed" > /root/DB_SYNC_ERR.txt
fi

###Send notification if replication down###
cd /root
if [ -f DB_SYNC_ERR.txt ]; then
echo | mail -a "From: Questo indirizzo email è protetto dagli spambots. È necessario abilitare JavaScript per vederlo." -s "MYSQL Slave - Check Replication" Questo indirizzo email è protetto dagli spambots. È necessario abilitare JavaScript per vederlo.
fi

Appunti

MYSQL - Privilegi e Sincronizzazione