How to Upgrade Ubuntu 16.04 to Ubuntu 18.04

 

 

sudo apt-get update -y
sudo apt-get upgrade -y
sudo apt-get dist-upgrade -y

sudo apt install update-manager-core -y

sudo do-release-upgrade

 

SQL test query or validation query

 

Ref: https://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most

Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0 has a property called preferredTestQuery, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has validationQuery.

-- Access

SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 

-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica

SELECT 1

-- MemSQL, Oracle

SELECT 1 FROM DUAL

-- CUBRID

SELECT 1 FROM db_root

-- Db2

SELECT 1 FROM SYSIBM.DUAL

-- Derby

SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird

SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere

SELECT 1 FROM SYS.DUMMY

-- HSQLDB

SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix

SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata

SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

[Gitlab] HOW TO USE SPECIFIC RUNNERS IN GITLAB CI/CD PIPELINE

What are Gitlab Runners?

GitLab Runner is an application that works with GitLab CI/CD to run jobs in a pipeline. They can also run

inside a Docker container or be deployed into a Kubernetes cluster.

Types of Runners:

• Shared Runner: These types of runners are available globally and can automatically be allocated based on their availability.
• Specific Runner: For this, you have to register a runner and you can only use it in a specific project or can also use it in a specific stage of your pipeline. Will discuss this more in detail.
• Group Runner: These runners are shared across projects in the group.

How to register a specific runner?

sudo gitlab-runner register

This command will start the process of registering a runner. Then go to the Gitlab Project -Settings->CI/CD->Runners and copy the URL of specific runners.

How to use this specific runner in a pipeline?

Just add the tags field in your pipeline and name the tag which you added during the registration of a runner.

Here is the sample pipeline for your reference.

stage: test

script:

- echo "Testing"

- test -f "build/info.txt"

tags:

- testing

npm:

image: node:latest

stage: npm

How to use this specific runner in a pipeline?

script:

- echo "image pulled"

[Gitlab] gitlab runner doesn`t work on a specific project

Ref: gitlab runner doesn`t work on a specific project

Error:

Fetching changes with git depth set to 50...
fatal: remote origin already exists.
fatal: git fetch-pack: expected shallow list
ERROR: Job failed: exit status 1

Solution:

Centos 7 ships with git version 1.8.3.1 . This version doesn't support commands like git fetch-pack . To fix this problem, you could update git on your server from the IUS repository. update git on Centos 7 to version 2 from third-party IUS repo

$ git --version
git version 1.8.3.1

sudo yum -y install https://packages.endpointdev.com/rhel/7/os/x86_64/endpoint-repo.x86_64.rpm
sudo yum install git

[Gitlab] Install gitlab runner

 

Ref:https://docs.gitlab.com/runner/install/linux-manually.html

Download

To download the appropriate package for your system:

1. Find the latest file name and options at https://gitlab-runner-downloads.s3.amazonaws.com/latest/index.html.
2. Choose a version and download a binary, as described in the documentation for downloading any other tagged releases for bleeding edge GitLab Runner releases.

For example, for Debian or Ubuntu:

curl -LJO "https://gitlab-runner-downloads.s3.amazonaws.com/latest/deb/gitlab-runner_${arch}.deb"

For example, for CentOS or Red Hat Enterprise Linux:

curl -LJO "https://gitlab-runner-downloads.s3.amazonaws.com/latest/rpm/gitlab-runner_${arch}.rpm"

For example, for FIPS compliant GitLab Runner on RHEL:

curl -LJO "https://gitlab-runner-downloads.s3.amazonaws.com/latest/rpm/gitlab-runner_amd64-fips.rpm"

Install

1. Install the package for your system as follows.

   For example, for Debian or Ubuntu:
   dpkg -i gitlab-runner_<arch>.deb For example, for CentOS or Red Hat Enterprise Linux:
   rpm -i gitlab-runner_<arch>.rpm

2. Register a runner

Update

Download the latest package for your system then upgrade as follows:

For example, for Debian or Ubuntu:

dpkg -i gitlab-runner_<arch>.deb

For example, for CentOS or Red Hat Enterprise Linux:
rpm -Uvh gitlab-runner_<arch>.rpm

[Report Server] fully share files

Ref: https://reportserver.net/en/guides/admin/chapters/File-System/

 To fully share files you may place them in a folder where a tick is set at Share folder for web access.

 

GitlabCI run pipeline on specific branch and manual

 Ref: https://stackoverflow.com/questions/51642852/gitlabci-run-pipeline-on-specific-branch-and-manual

You want to run the builds on branch develop automatically but in branch web manually

You can't do this in one build, but you can use two builds for it:

my_build:develop
  stage: build
  only:
    - develop

my_build:web
  stage: build
  only:
    - web
  when: manual

java.desktop/sun.awt.FontConfiguration.getVersion with NullPointerException

 Ref: https://stackoverflow.com/questions/65509533/npe-from-sun-awt-fontconfiguration-getversion-using-apache-fop-jdk-11-on-linux-s

Error:

Caused by: java.lang.NullPointerException
        at java.desktop/sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1262)
        at java.desktop/sun.awt.FontConfiguration.readFontConfigFile(FontConfiguration.java:225)
        at java.desktop/sun.awt.FontConfiguration.init(FontConfiguration.java:107)
        at java.desktop/sun.awt.X11FontManager.createFontConfiguration(X11FontManager.java:719)

Solution:

Turns out the issue was not that I didn't have access to the fonts, the Linux OS required me to install fontconfig via yum.
yum install fontconfig
I am not sure why Oracle JDK 11 plus the missing Linux utility was the issue since this works now in my production environment with Oracle JDK 1.8 and without fontconfig being installed.

MYSQLDUMP PERMISSIONS

 Href: http://www.drlock.com/blog/2006/12/22/mysqldump-permissions/

I wanted to create a user who had just enough permissions to make a SQL dump of my database for backup purposes. I hunted all over the ‘net, but no one told what permissions were needed so by trial and error I found out:

GRANT SELECT, LOCK TABLES ON *.* TO backup_user@localhost IDENTIFIED BY ‘xxx’;

Now I can have a script that runs with my backup tools that executes:

mysqldump –user backup –password=xxx –all-databases –compact | gzip -9 > db_backup.sql.gz

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.