ECSS-10 EN
Дерево страниц
Перейти к концу метаданных
Переход к началу метаданных

Installing ecss-mysql and creating replication

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

  • OS Version: Ubuntu 18.04;
  • MySQL version: mysql-server-5.7;
  • Two hosts to configure replication (default hostnames are ecss1, ecss2);
  • Each of the hosts must have a user with the same name, having root rights (needed only at the script execution stage). In the instructions below, the user name support will be used.

Variables used:

  • <DB_ROOT_LOGIN> — root name of the MySQL server (root)
  • <DB_ROOT_PASSWORD> — MySQL server root password
  • <HOST_1_IP> — IP address of the ecss1 host
  • <HOST_2_IP> — IP address of the ecss2 host
  • <HOST_1> — name of the first host (ecss1)
  • <HOST_2> — name of the second host (ecss2)
  • <REPLICA_USER> — user for data replication (replica)
  • <REPLICA_USER_PASSWORD> — password for REPLICA_USER
  • <HOST_2_USER> — user to access shell ecss2
  • <MEDIATOR_HOST> — host where mediator is deployed (127.0.0.1)
  • <SNMP_PORT> — SNMP port of mediator (162)

  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:

    • IP pattern for MySQL permission — the mask indicates which pool of IP addresses will be available to login to the database. Specify the address pool, which will include the address of the servers where ecss-node will be installed, as well as the address of another ecss-mysql server.

      Example:

      If both MySQL servers are on hosts where ecss-node will be installed: 


      		ecss1ecss2
      ecss-mysql192.168.1.1192.168.1.2
      ecss-node192.168.1.1192.168.1.2

      For this example, specify 192.168.1.% as a mask for MySQL rights.

      However, MySQL can be placed on separate servers, then the network mask must be configured so that the hosts have access to each other:


      		ecss1ecss2mysql-server-1mysql-server-2
      ecss-mysql

      		192.168.2.1192.168.2.2
      ecss-node192.168.1.1192.168.1.2

      For this example, specify 192.168.% as a mask for MySQL rights.

    • Login for MySQL root — this login will be set for mysql server. The login must be remembered, as it will be required during installation of other nodes. It is also used in the process of creating backup copies of the system. 

    • Password for MySQL root — this password will be set for the user specified in the answer to the previous question. The password must be remembered, as it will be required during the installation of other nodes. It is also used in the process of creating a backup system. 

      The same logins and passwords must be used on both servers where ecss-mysql packages are installed.

      My databases used by the ECSS-10 system will be stored under the path /var/lib/ecss-mysql after installation. When installing the ecss-mysql package, aptitude will ask about permission to change configuration file /etc/apparmor.d/local/usr.sbin.mysqld in order to change default path to MySQL databases. To install ecss-mysql successfully, agree (enter "Y"). To avoid entering the answer to the question when installing the package, additional keys can be used when entering installation command: sudo apt-get -o Dpkg::Options::="--force-confnew" install ecss-mysql.

  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:

    • Enter db root login — user to access MySQL.
    • Enter db root password — password for accessing MySQL.
    • Login for replica user — user with this login will be created in MySQL. This user is used to log in to MySQL during system replication. The password must be remembered, it is used in the process of restoring replication. 
    • Password for replica user — this password will be set for the user specified in the answer to the previous question. The password must be remembered, as it is used during the replication recovery process. 
    • Local host IP address — IP address of the host on which replication is started. In the example given, this is <HOST_1_IP>.
    • Remote host IP address  — IP address of the second host on which MySQL is deployed and with which replication occurs. In the example given, this is <HOST_2_IP>.
    • Remote host name — name of the neighboring host with the ecss—mysql package installed. In the example given, this is ecss2.
    • Login for remote host — username that will be used to log in via ssh to a neighboring host with ecss-mysql. In the example given, this is support.
    • Mediator host IP address — IP address of the host on which the ecss—node package will be deployed. In the example given, this is <HOST_1_IP>. However, if ecss-node and ecss-mysql will be on different servers in the deployed system, it is required to specify the IP address of the host with ecss-node.
    • Mediator SNMP port — port on which the server with the mediator listens to the SNMP traffic. Leave the default value set to 162.

    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. 

    • DO YOU WANT TO SET MASTER (VIRTUAL) IP address? — if the answer is "yes", then this script will independently create settings for the MySQL virtual address, if "no", then the settings must be set manually according to paragraph 6. 
    • WHICH HOST WILL BE MASTER? — (optional, appears if the previous one is answered "no") — indicates which of the hosts will be the master.
    • ENTER VIRTUAL IP address — enter the virtual IP address. This address will be shared by two MySQL databases and will be located on one of their hosts. After configuration, this address should be specified in all ECSS services that need access to the database. If one of the mysql hosts becomes unavailable, the keepalived address will move to the second host and the database will remain available.
    • DO YOU WANT TO SET REST OF keepalive CONFIG — if the answer is positive, it starts the keepalived configuration.
    • ENTER VIRTUAL MASK (0-32) — virtual address mask.
    • ENTER VIRTUAL ROUTER ID — Router-ID is used in the keepalived configuration for the ratio of virtual address sections. If keepalived was not configured on the host before installing ecss-mysql, then use any integer ID (104). If any settings of the keepalived service have already been made before installing ecss-mysql, then before entering the parameter, make sure that the value is no longer involved in other keepalived configurations. 
    • ENTER INTERFACE — interface on which the ecss-mysql virtual IP address has to be deployed.
    • ENTER INTERFACE DESCRIPTION — description of the interface. Specify a correct and explicit name, for example: "<INTERFACE>:mysql".

    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
  • Нет меток