How to Setup MariaDB Master and Slave Replication on Ubuntu 16.04

Ref: https://alibaba-cloud.medium.com/how-to-setup-mariadb-master-and-slave-replication-on-ubuntu-16-04-850c155c5481

MariaDB is a free, open source and one of the most popular open source relational database management system. It is a drop-in replacement for MySQL intended to remain free under the GNU GPL. You will need to increase the instances of your MariaDB server and replicate the data on multiple servers when your traffic grows. The Master-Slave replication provides load balancing for the databases. It is not used for any failover solution.

There are two ways to replicate data:

Master-Master Replication: In this mode, data to be copied from either server. In other words, perform reads or writes from either server. So whenever one server gets the write request it will sync data to other server. This mode will be very useful when you want the best redundancy.

Master-Slave Replication: In this mode, data changes happen on the master server, while the slave server automatically replicates the changes from the master server. This mode will be best suited for data backups.

In this tutorial, we will learn how to set up MariaDB Master-Slave replication on Alibaba Cloud Elastic Compute Service (ECS) with Ubuntu 16.04.

Requirements

1. Two fresh Alibaba Cloud instance with Ubuntu 16.04 installed.
2. A static IP address 192.168.0.101 is configured on the Master node and 192.168.0.102 is configured on the Slave node.
3. A Root password is set up on both instance.

Launch Alibaba Cloud ECS Instance

First, log in to your Alibaba Cloud ECS Console. Create a new ECS instance, choosing Ubuntu 16.04 as the operating system with at least 2GB RAM. Connect to your ECS instance and log in as the root user.

Once you are logged into your Ubuntu 16.04 instance, run the following command to update your base system with the latest available packages.

apt-get update -y

Install MariaDB

Before starting, you will need to install MariaDB server on both instance. You can install it by running the following command:

apt-get install mariadb-server -y

Once the installation is completed, start MariaDB service and enable it to start on boot time with the following command:

systemctl start mysql
systemctl enable mysql

By default, MariaDB is not secured. So you will need to secure it first. You can do this by running the following command:

mysql_secure_installation

Answer all the questions as shown below:

Set root password? [Y/n] n
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

Configure Master Node

First, you will need to edit /etc/mysql/my.cnf and make some changes inside it.

nano /etc/mysql/my.cnf

Make the following changes:

[mysqld]
bind-address = 192.168.0.101
server_id=1
log-basename=master
log-bin=/var/log/mysql/mariadb-bin
binlog-format=row
binlog-do-db=masterdb

Save and close the file, when you are finished.

Next, restart MariaDB service to apply the chnages:

systemctl restart mysql

Next, log in to MariaDB shell and configure replication:

mysql -u root -p

Enter your root password, then stop the slave and create a replication user and set password:

MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Next, flush the privileges:

MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Next, check the master server status:

MariaDB [(none)]> SHOW MASTER STATUS;

Output:

+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      615 | masterdb     |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Note: Remember the file mariadb-bin.000001 and position number 615.

Next, exit from the MariaDB shell:

MariaDB [(none)]> exit;

Next, take a backup of all databases on the master server and transfer it to the slave server.

First, take all database backup with the following command:

mysqldump --all-databases --user=root --password --master-data > alldatabase.sql

Next, transfer alldatabase.sql file to the slave server with the following command:

scp alldatabase.sql root@192.168.0.102:/root/

Next, log in to MariaDB console again and unlock the tables:

mysql -u root -pMariaDB [(none)]> UNLOCK TABLES; 
MariaDB [(none)]> exit;

Configure Slave Server

You will also need to edit /etc/mysql/my.cnf file and make some changes inside it:

nano /etc/mysql/my.cnf

Make the following changes:

[mysqld]
bind-address = 192.168.0.102
server-id = 2
replicate-do-db=masterdb

Save the file, then restart MariaDB service:

systemctl restart mysql

Next, import the alldatabase.sql which you have transferred from master server:

mysql -u root -p < alldatabase.sql

Next, log in to MariaDB shell:

mysql -u root -p

Enter your root password, then stop the slave:

MariaDB [(none)]> STOP SLAVE;

Next, configure the slave to use the master with the following command:

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=615;

Next, start the slave and check slave status:

MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;

Output:

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.20.10.6
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 615
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: masterdb

Test Replication

Both MariaDB master and slave server are now configured. It’s time to test replication.

Navigate to the Master server, and log in to MariaDB shell:

mysql -u root -p

Enter your root password, then create a database masterdb which you have specified in my.cnf file:

MariaDB [(none)]> create database masterdb;

Next, add some tables and entries on it:

MariaDB [(none)]> use masterdb;
MariaDB [masterdb]> create table mastertable (c int);
MariaDB [masterdb]> insert into mastertable (c) values (1);

Now, check the mastertable:

MariaDB [masterdb]> select * from mastertable;

Output:

+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Now, navigate to the slave server and verify whether the above created table has been replicated:

First, log in to MariaDB shell:

mysql -u root -p

Enter your root password, then change the database to masterdb:

MariaDB [(none)]> use masterdb;

Next, check the mastertable:

MariaDB [masterdb]> select * from mastertable;

Output:

+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

You should see that the database and table are replicated successfully from the master server to the slave server.

 

Adding trusted root certificates to the server

 Ref: https://manuals.gfi.com/en/kerio/connect/content/server-configuration/ssl-certificates/adding-trusted-root-certificates-to-the-server-1605.html

If you want to send or receive messages signed by root authorities and these authorities are not installed on the server, you must add a trusted root certificate manually.

Use the following steps to add or remove trusted root certificates to/from a server.

Mac OS X

Function	Method
Add	Use command: sudo security add-trusted-cert -d -r trustRoot -k /Library/Keychains/System.keychain ~/new-root-certificate.crt
Remove	Use command: sudo security delete-certificate -c "<name of existing certificate>"

Windows

Function	Method
Add	Use command: certutil -addstore -f "ROOT" new-root-certificate.crt
Remove	Use command: certutil -delstore "ROOT" serial-number-hex

Linux (Ubuntu, Debian)

Function	Method
Add	Copy your CA to dir /usr/local/share/ca-certificates/ Use command: sudo cp foo.crt /usr/local/share/ca-certificates/foo.crt Update the CA store: sudo update-ca-certificates
Remove	Remove your CA. Update the CA store: sudo update-ca-certificates --fresh

NOTE

Restart Kerio Connect to reload the certificates in the 32-bit versions or Debian 7.

Linux (CentOs 6)

Function	Method
Add	Install the ca-certificates package: yum install ca-certificates Enable the dynamic CA configuration feature: update-ca-trust force-enable Add it as a new file to /etc/pki/ca-trust/source/anchors/: cp foo.crt /etc/pki/ca-trust/source/anchors/ Use command: update-ca-trust extract

NOTE

Restart Kerio Connect to reload the certificates in the 32-bit version.

Linux (CentOs 5)

Function	Method
Add	Append your trusted certificate to file /etc/pki/tls/certs/ca-bundle.crt cat foo.crt >>/etc/pki/tls/certs/ca-bundle.crt

NOTE

Restart Kerio Connect to reload the certificates in the 32-bit version.

CI using Your own gitlab runner

 Ref: https://wiki.samba.org/index.php/CI_using_Your_own_gitlab_runner

Register your development machine as a runner for your own gitlab project

To increase speed of your CI runs you can run your own CI on your own machine for your gitlab samba project clone You can register your own development machine as a runner for your pipelines on your own clone of the samba gitlab repo.

These instructions are for SUSE so you will need to adjust the commands (e.g. apt-get instead of zypper etc.) as appropriate for your linux distro.

• Install docker

 zypper in docker

IMPORTANT you need to ensure the device mapper storage driver is enabled, to do this you need to edit /etc/docker/daemon.json. If it does not yet exist, create it and add

{
  "storage-driver": "devicemapper"
}

• Install gitlab runner

 sudo wget -O /usr/local/bin/gitlab-runner https://gitlab-runner-downloads.s3.amazonaws.com/latest/binaries/gitlab-runner-linux-amd64
 sudo chmod +x /usr/local/bin/gitlab-runner

• Optionally, you can install and run as a service

sudo useradd --system --comment 'GitLab Runner' --create-home gitlab-runner --shell /bin/bash
sudo gitlab-runner install --user=gitlab-runner --working-directory=/home/gitlab-runner

gitlab-runner will run as root, but will execute jobs as user specified by the install command. You may need to add this user to the 'docker' group. Easiest way to test if docker is working is to issue 'docker info' as the gitlab-runner 'user'

• Register your runner

# gitlab-runner register
Running in system-mode.                            

Please enter the gitlab-ci coordinator URL (e.g. https://gitlab.com/):
https://gitlab.com

Please enter the gitlab-ci token for this runner:
<you can find your token in your project's Settings ==> CI/CD ==> Runners settings>

Please enter the gitlab-ci description for this runner:
[hostname]: <your hostname>

Please enter the gitlab-ci tags for this runner (comma separated):

Whether to run untagged builds [true/false]:
[false]: ==> false

Whether to lock the Runner to current project [true/false]:
[true]: true

Registering runner... succeeded                     runner=M6jmUiFA
Please enter the executor: ssh, virtualbox, docker-ssh+machine, kubernetes, docker-ssh, parallels, shell, docker, docker+machine:
docker, private, shared

Please enter the default Docker image (e.g. ruby:2.1):
registry.gitlab.com/samba-team/samba:latest

Runner registered successfully. Feel free to start it, but if it's running already the config should be automatically reloaded!

By default iirc gitlab-runner will just execute one job per pipeline at a time, to increase that you will need to edit /etc/gitlab-runner/config.toml and change the 'concurrent' value to something your machine can handle.

To manage your runner (and CI) you need to refer to the gitlab-runner help, additional options regarding CI and managing your runner specifically for your project can be accessed from your project settings available from the sidebar e.g. 

Note: you will need to change some defaults, from your project settings (navigate Settings | CI/CD from the sidebar), expand the 'General Pipeline settings' and change

   Timeout: 10h

   Custom CI config path: .gitlab-ci-private.yml

If you don't change the custom CI config path then by default the CI will run a reduced set of tests.

Credit for most the content above should go to Samuel Cabrero

Custom build directories in gitlab

Ref: https://docs.gitlab.com/ee/ci/runners/configure_runners.html

Edit config.toml

  [runners.custom_build_dir]

    enabled = true

By default, GitLab Runner clones the repository in a unique subpath of the $CI_BUILDS_DIR directory. However, your project might require the code in a specific directory (Go projects, for example). In that case, you can specify the GIT_CLONE_PATH variable to tell the runner the directory to clone the repository in:

variables:

  GIT_CLONE_PATH: $CI_BUILDS_DIR/project-name

test:

  script:

    - pwd

The GIT_CLONE_PATH has to always be within $CI_BUILDS_DIR. The directory set in $CI_BUILDS_DIR is dependent on executor and configuration of runners.builds_dir setting.

 

Supported options for self-signed certificates targeting the GitLab server

 

Ref:https://docs.gitlab.com/runner/configuration/tls-self-signed.html

• Specify a custom certificate file: GitLab Runner exposes the tls-ca-file option during registration (gitlab-runner register --tls-ca-file=/path), and in config.toml under the [[runners]] section. This allows you to specify a custom certificate file. This file will be read every time the runner tries to access the GitLab server.

VS Code: Fixing Git Certificate Issues

Ref: https://dougdefrank.wordpress.com/2018/02/28/vs-code-fixing-git-certificate-issues/ 

git config --system http.sslBackend schannel