Installing ecss-mysql and creating replication

For correct MySQL replication configuration script, the following requirements must be met:

Variables used:

  1. Install packages with MySQL, database configuration, and keepalive on both hosts:

    sudo aptitude install ecss-mysql keepalived

    When installing ecss-mysql, the following data will be requested:

  2. Bind-address configuration for mysql. 
    Open the mysql configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf and add the following line there:

    bind-address            = 0.0.0.0

    restart the mysql service:

    sudo systemctl restart mysql.service

    This step must be performed on both hosts.

  3. Setting hostnames.

    1. Check the current hostnames where ecss-mysql will be installed. If the servers are installed on the same host as the main system (ecss-node), then the name must correspond to ecss1/ecss2, respectively. If Mysql will be installed on separate servers, then the hostname can be any. Use the following command on both servers to change the hostname:

      hostnamectl set-hostname <HOST_NAME>


    2. After setting the host names, add IP address and name of a neighbour server to the end of /etc/hosts file. For example, execute on ecss1:

      sudo echo '<HOST_1_IP>       <HOST_1>' >> /etc/hosts
sudo echo '<HOST_2_IP>       <HOST_2>' >> /etc/hosts

      If MySQL is installed on separate servers, then add to the /etc/hosts file the names and IP addresses of servers with ecss-node, as well as servers with ecss-mysql.

  4. Support access between servers with ecss-mysql via ssh using rsa keys without using a password.

    1. On the ecss1 host, generate the rsa key with the following command (run the command without sudo so that the key is generated for the current user):

      ssh-keygen


    2. Transfer the key to ecss2:

      ssh-copy-id support@ecss2


    3. Similarly, generate a key on the ecss2 host and copy it to the first host:

      ssh-keygen
ssh-copy-id support@ecss1


  5. After successful installation of the ecss-mysql package on both hosts, configure replication between MySQL databases. To do this, run the replication creation script on the ecss1 host:

    /usr/lib/ecss/ecss-scripts/mysql-replication/install_replication.sh

    Duplicate the answers from the point with package installation to the question about replication configuration:

    The following section describes virtual IP address automatic configuration in keepalive to access the database. In cases of working off the reserve, the virtual address will automatically move to another host. 

    Upon completion of the installation, the script outputs the values of the variables Slave_IO_Running, Slave_SQL_Running to ecss1, ecss2. If the variables are Yes, then replication has started. The template for automatic file creation is located under the path /usr/lib/ecss/ecss-scripts/mysql-replication/keepalived.conf:

    vrrp_script check_mysqld {
       script "nc -z localhost 3306"   # cheaper than pidof
       interval 2                      # check every 2 seconds
       fall 1
       rise 2
}

!For MySQL
vrrp_instance MySQL {
    state BACKUP
    interface <INTERFACE>
    virtual_router_id <VIRTUAL_ROUTER_ID>
    priority 100
    advert_int 3
    smtp_alert
    nopreempt
    authentication {
        auth_type PASS
        auth_pass keepmysql
    }
    virtual_ipaddress {
        <VIRTUAL_IP>/<VIRTUAL_MASK> dev <INTERFACE> label <INTERFACE_DESCRIPTION>
    }
    track_script {
        check_mysqld
    }
}

    Where the entered values will be automatically substituted:

    1.  <INTERFACE> — physical or virtual interface of the server where the ecss-mysql package is installed. Traffic will be exchanged from this interface and the availability of the second server with MySQL will be monitored. Accordingly, through the interfaces specified in the configurations on both hosts, there should be access to hosts with ecss-mysql, as well as to ecss-node, if the server with MySQL is removed from the main system.
    2. <VIRTUAL_IP>/<VIRTUAL_MASK> — IP address and mask of the virtual address through which access to the MySQL database will be performed. After the keepalived configuration is completed, this IP address should rise on one of the hosts. In case of an accident on this host, the IP address should dynamically rise on the second host. This way MySQL will always be available.
    3. <INTERFACE_DESCRIPTION> — description of the interface. Specify a correct and explicit name, for example: "<INTERFACE>:mysql".

  6. Manual configuration of keepalived:

    This section must be completed if the answer to the question "DO YOU WANT TO SET MASTER (VIRTUAL) IP address?" was "no". In this case, a virtual address was not created during the replica creation process. Therefore, the reservation for ecss-mysql will not be processed.


    For fault tolerance, an ECSS-10 cluster uses the MySQL master-master replication mode. This allows transferring data correctly in any direction. However, writing to both MySQL servers at the same time while replicating in the opposite direction increases the chance of collisions, which reduces fault tolerance. Therefore, it is recommended to configure a dedicated virtual address for the MySQL cluster so that data is written to one node at a time.

    If you create the /etc/keepalived/mysql.conf files manually, then refuse automatic configuration when asked "DO YOU WANT TO SET REST OF keepalive CONFIG?" when running the replication creation script.

    The virtual address configuration for MySQL is recommended to be placed in a separate /etc/keepalived/mysql.conf file.

    # First mysql node configuration:

vrrp_script check_mysql {
    script "/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf -e 'SELECT 1;'"
    user root
    interval 2
    fall 1
    timeout 2
}

vrrp_instance MySQL {
    state MASTER                     # Initial state at a start
    interface <network_interface>    # Initial state at a start
    virtual_router_id <ID>           # Unique router id (0..255)
    priority 100                     # Priority (0..255) the higher the more
    advert_int 1                     # Notification sending interval (sec)
    preempt_delay 60                 # Master wait interval at daemon start (sec) at BACKUP initial state

    unicast_src_ip  <src_real IP>    # Own real IP address
    unicast_peer {
         <real_remote IP>            # Neighbour real IP адрес address
    }

    virtual_ipaddress {
        # Virtual IP address and a mask
        # dev - network interface on which virtual address will operate
        # label - virtual interface label (for ease of identification)
        <virtual_sip_IP>/<netmask> dev <>  label <label>
   }

    track_script {
        check_mysql
    }
}


    #  Second mysql node configuration:

vrrp_script check_mysql {
    script "/usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf -e 'SELECT 1;'"
    user root
    interval 2
    fall 1
    timeout 2
}

vrrp_instance MySQL {
    state BACKUP                     # Initial state at a start
    interface <network_interface>    # Name of the network interface, on which VRRP will operate
    virtual_router_id <ID>           # Unique router id (0..255)
    priority 50                      # Priority (0..255) the higher the more
    advert_int 1                     # Notification sending interval (sec)
    preempt_delay 60                 # Master wait interval at daemon start (sec) at BACKUP initial state

    unicast_src_ip  <src_real IP>    # Own real IP address
    unicast_peer {
         <real_remote IP>            # Neighbour real IP адрес address
    }

    virtual_ipaddress {
        # Virtual IP address and a mask
        # dev - dev - network interface on which virtual address will operate
        # label - virtual interface label (for ease of identification)
        <virtual_sip_IP>/<netmask> dev <>  label <label>
   }

    track_script {
        check_mysql
    }
}



7. To make access to replicated MySQL transparent to clients, create a virtual IP. It will move between ecss1, ecss2 hosts by means of VRRP+keepalive if mysqld has stopped on one of them. To do this, add the following line to the keepalived.conf configuration file on each node at the end (see the section General keepalived configuration):

include /etc/keepalived/mysql.conf

8. Перезапускаем keepalived на каждой из хостов ecss1, ecss2:

sudo systemctl restart keepalived.service

After that, an interface should be raised on one of the hosts, through which MySQL will be available. If MySQL is stopped on this host, the network interface will move to a neighboring host.

9. Configure dnsmasq:

This item is executed on a host with the ecss-node package installed.

In order to connect the ECSS-10 system to a replicated MySQL, configure dnsmasq. To do this, reconfigure the ecss-dns-env package:

sudo dpkg-reconfigure ecss-dns-env

In the configuration window that opens, select mysql, and then enter the address <VIRTUAL_IP>.

Checking the correctness of the replication settings

To verify that data replication is working correctly, follow these steps.

  1. Check the status of the replica. To do this, run the command:

    sudo mysql -uroot -p -e 'show slave status \G;' | grep -E "Slave_IO_Running:|Slave_SQL_Running:"

    This command should show the following information on both hosts:

    Slave_IO_Running: Yes
Slave_SQL_Running: Yes


  2. Check the status of events. To do this, run the command:

    sudo mysql -uroot -p -e 'use ecss_statistics; show events\G;' | grep Status

    Both hosts (on which the keepalive virtual address is present) should show the following status:

    Status: ENABLED


  3. Checking the status table:
    For the master, this number will be 1, for the slave 0

    mysql> select * from ecss_system.events_status;
+--------+---------+
| lock   | enabled |
+--------+---------+
| events |       0 |
+--------+---------+
1 row in set (0.00 sec)


Restoring replica via mysqldump

This step must be performed in case of replication errors. Example of data recovery on ecss2 based on ecss1 is given below.

ecss1-mysql> STOP SLAVE;
ecss1-mysql> RESET MASTER;
ecss1-mysql> FLUSH TABLES WITH READ LOCK;
ecss1-mysql> SHOW MASTER STATUS; # запомнить параметры File, Position (<FILE>, <POS>)

Without closing the connection to the master database, dump the necessary data. When the connection is closed, the reading mode is disabled.

ecss1-shell> mysqldump --verbose --single-transaction --quote-names --complete-insert --extended-insert --routines --events --triggers -uroot -proot --opt --databases ecss_audit ecss_address_book  ecss_calls_db ecss_statistics ecss_subscribers history_db web_conf > ecss1.mysql.dump

Remove the reading mode with the following command:

ecss1-mysql> UNLOCK TABLES;

Copy the ecss1.mysql.dump file to ecss2. After that, log in to mysql ecss2 and run the command:

sudo mysql-ecss2> STOP SLAVE;

Next, delete all logs from the master on the slave and start replication from the moment when the dump was made on the master:

mysql-ecss2> RESET SLAVE;
mysql-ecss2> CHANGE MASTER TO MASTER_LOG_FILE='<FILE>', MASTER_LOG_POS=<POS>;
mysql-ecss2> START SLAVE;
mysql-ecss2> SHOW SLAVE STATUS \G;

Make sure that the Slave_IO_Running and Slave_SQL_Running parameters are set to Yes. Next, in mysql ecss2, perform:

If after RESET SLAVE an error is issued that the relay file is not found, it is necessary to remove all relay files on this host (sudo rm /var/lib/mysql/slave-mysql-relay-bin.*). Then repeat RESET SLAVE.

Continue on the SLAVE:

mysql-ecss2> STOP SLAVE;
mysql-ecss2> FLUSH TABLES WITH READ LOCK;
mysql-ecss2> RESET MASTER;
mysql-ecss2> SHOW MASTER STATUS;

Remember File and Position and execute in mysql ecss1:

mysql-ecss1> CHANGE MASTER TO MASTER_LOG_FILE='<FILE>', MASTER_LOG_POS=<POS>;
mysql-ecss1> START SLAVE;
mysql-ecss1> SHOW SLAVE STATUS \G;

Make sure that the Slave_IO_Running and Slave_SQL_Running parameters are set to Yes. Remove the lock on ecss2 and start replication:

mysql-ecss2> UNLOCK TABLES;
mysql-ecss2> START SLAVE;

To receive warnings in case of replication violation, it is necessary to uncomment the following lines in the mediator configuration file /etc/ecss/ecss_mediator/md1@ecss1.config and restart ecss-mediator.0.

%   ,{manager, [
%              {config, [
%                        {dir, "/etc/ecss/snmp/manager/"},
%                        {verbosity, silence},
%                        {db_dir, "/var/lib/ecss/snmp/"} 
%                       ]},
%              {def_user_mod, snmp_alarm_user}
%             ]}

Removing replication

In order to remove a previously created replication, use a special script:

/usr/lib/ecss/ecss-scripts/mysql-replication/remove_replication.sh