MYSQL - Privilegi e Sincronizzazione

adminAB76
Autore della discussione
Amministratore

Di più

11 Anni 4 Mesi fa - 8 Anni 1 Mese fa #38 da adminAB76

MYSQL - Privilegi e Sincronizzazione è stato creato da adminAB76

Per dare tutti i privilegi:

Code:

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

Code:

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:

Code:

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

Code:

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:

Code:

#!/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: monitoring@mycompany.it" -s "MYSQL Slave - Check Replication" admin@mycompany.it
fi

Ultima Modifica 8 Anni 1 Mese fa da adminAB76.

Si prega Accesso a partecipare alla conversazione.