Using xtrabackup to make fast MySQL backups – backup and restore

Percona provides a really interesting tool for MySQL backups! It works on a live running MySQL server by copying the MySQL binary files from the data directory and because the tool knows how the engines work (InnoDB, MyISAM, and so on) it can make point-in-time consistent MySQL data files. Of course, using this tool on a database with InnoDB tables only is the best case, because no write lock will be used.

There are two main tasks when making a backup and one, which is not mandatory:

  1. Execute xtrabackup with –backup option to copy the MySQL data files and additional information for the tool
  2. Execute xtrabackup with –prepare option to prepare the MySQL (InnoDB) files ready for use of a MySQL server. The files are consistented, i.e. the whole backup is consistent despite the copy of the different files that happened at different times.
  3. Execute xtrabackup with –prepare option again to further prepare the MySQL (InnoDB) files ready for use of a MySQL server. Additional preparation such as InnoDB log files and more. This step is not mandatory and it may be skipped because the MySQL server, which uses the data files, will create the InnoDB log files.

The directory with MySQL copied files contains not only the MySQL files but additional information plus my.cnf (MySQL current configuration) backup. The backup files may be prepared (and restore) in a different server than the original, on which the backup was made.

xtrabackup may enable compressing on-the-fly when copying the binary files with the option –compress but the steps to use (restore) the backup in a new server are different – additional step to decompress before prepare. So the above steps become:

  1. xtrabackup –backup –compress – copy and compress on-the-fly.
  2. xtrabackup –decompress – decompress the backup files
  3. xtrabackup –prepare – make point-in-time consistent MySQL data files, which are ready for a MySQL server.
  4. xtrabackup –prepare – additional preparation to make the start up of the MySQL server with those files faster.

And here is a real-world example:

STEP 1) Install the xtrabackup utility

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-24 qpress

The percona-xtrabackup-24 provides the percona xtrabackup tool for backup of MySQL 5.1, 5.5, 5.6 and 5.7 servers, as well as Percona Server for MySQL with XtraDB. Install percona-xtrabackup-80 for use with MySQL 8 and later.
The whole output is included in the Bonus 3 section below.

STEP 2) Make backup

Make the backup. The datadir of the MySQL server is needed and a new direcotry for the backup files. The MySQL server is running and serving requests.

[root@srv ~]# xtrabackup --backup --slave-info --datadir=/var/lib/mysql/ --target-dir=/mnt/backups/sql-xtrabackup/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin --server-id=1 --open_files_limit=5000 --innodb_buffer_pool_size=256M --innodb_log_buffer_size=32M --innodb_log_files_in_group=2 --innodb_flush_log_at_trx_commit=0 --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT --datadir=/var/lib/mysql/ 
xtrabackup: recognized client arguments: --password=* --backup=1 --slave-info=1 --target-dir=/mnt/backups/sql-xtrabackup/ 
200919 14:35:11  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: YES).
200919 14:35:11  version_check Connected to MySQL server
200919 14:35:11  version_check Executing a version check against the server...
200919 14:35:11  version_check Done.
200919 14:35:11 Connecting to MySQL server host: localhost, user: not set, password: set, port: not set, socket: not set
Using server version 5.7.31-log
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 5000, set to 5000
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
200919 14:35:11 >> log scanned up to (1135932912)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 34 for mywordpress/wp_users, old maximum was 0
200919 14:35:12 [01] Copying ./ibdata1 to /mnt/backups/sql-xtrabackup/ibdata1
200919 14:35:12 >> log scanned up to (1135932912)
200919 14:35:13 >> log scanned up to (1135932912)
200919 14:35:14 [01]        ...done
200919 14:35:14 >> log scanned up to (1135932912)
200919 14:35:15 >> log scanned up to (1135932912)
200919 14:35:16 [01] Copying ./mywordpress/wp_users.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_users.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 [01] Copying ./mywordpress/wp_gglcptch_whitelist.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_gglcptch_whitelist.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 [01] Copying ./mywordpress/wp_usermeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_usermeta.ibd
200919 14:35:16 [01]        ...done
200919 14:35:16 >> log scanned up to (1135932912)
200919 14:35:17 [01] Copying ./mywordpress/wp_postmeta.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_postmeta.ibd
200919 14:35:17 [01]        ...done
200919 14:35:17 [01] Copying ./mywordpress/wp_posts.ibd to /mnt/backups/sql-xtrabackup/mywordpress/wp_posts.ibd
200919 14:35:17 >> log scanned up to (1135932912)
200919 14:35:18 >> log scanned up to (1135932912)
200919 14:35:19 [01]        ...done
200919 14:35:19 >> log scanned up to (1135932912)
.....
.....
200919 14:36:30 [01] Copying ./mysql/columns_priv.MYD to /mnt/backups/sql-xtrabackup/mysql/columns_priv.MYD
200919 14:36:30 [01]        ...done
200919 14:36:30 >> log scanned up to (1135933567)
200919 14:36:31 [01] Copying ./mysql/proxies_priv.frm to /mnt/backups/sql-xtrabackup/mysql/proxies_priv.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 [01] Copying ./mysql/help_category.frm to /mnt/backups/sql-xtrabackup/mysql/help_category.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 [01] Copying ./mysql/proc.frm to /mnt/backups/sql-xtrabackup/mysql/proc.frm
200919 14:36:31 [01]        ...done
200919 14:36:31 Finished backing up non-InnoDB tables and files
200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_slave_info
200919 14:36:31 [00]        ...done
200919 14:36:31 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_binlog_info
200919 14:36:31 [00]        ...done
200919 14:36:31 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1135933558'
xtrabackup: Stopping log copying thread.
.200919 14:36:31 >> log scanned up to (1135933567)

200919 14:36:32 Executing UNLOCK TABLES
200919 14:36:32 All tables unlocked
200919 14:36:32 [00] Copying ib_buffer_pool to /mnt/backups/sql-xtrabackup/ib_buffer_pool
200919 14:36:32 [00]        ...done
200919 14:36:32 Backup created in directory '/mnt/backups/sql-xtrabackup/'
MySQL binlog position: filename 'srv-bin.000001', position '1004'
200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/backup-my.cnf
200919 14:36:32 [00]        ...done
200919 14:36:32 [00] Writing /mnt/backups/sql-xtrabackup/xtrabackup_info
200919 14:36:32 [00]        ...done
xtrabackup: Transaction log of lsn (1135932903) to (1135933567) was copied.
200919 14:36:33 completed OK!

In the end, there must be written: “completed OK!”.
The whole output of the command is included in the Bonus 1 section below. An example with compress option enabled is in Bonus 2 section below.
Here is what contains the backup diectory:
Keep on reading!

gitlab in podman cannot create unix sockets in glusterfs because of SELinux

Installing gitlab-ee (and gitlab-ce) under CentOS 7 with enabled SELinux (i.e. enforcing mode) looped endlessly the container in restarting the installation process! There were multiple errors for missing sockets in the podman logs of the gitlab container. Here are some of the errors:
Missing postgresql unix socket in “/var/opt/gitlab/postgresql”:

Recipe: gitlab::database_migrations
  * bash[migrate gitlab-rails database] action run
    [execute] rake aborted!
              PG::ConnectionBad: could not connect to server: No such file or directory
                Is the server running locally and accepting
                connections on Unix domain socket "/var/opt/gitlab/postgresql/.s.PGSQL.5432"?
              /opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:53:in `block (3 levels) in <top (required)>'
              /opt/gitlab/embedded/bin/bundle:23:in `load'
              /opt/gitlab/embedded/bin/bundle:23:in `<main>'
              Tasks: TOP => gitlab:db:configure
              (See full trace by running task with --trace)
    
    
    Error executing action `run` on resource 'bash[migrate gitlab-rails database]'
.....
.....
Running handlers:
There was an error running gitlab-ctl reconfigure:

bash[migrate gitlab-rails database] (gitlab::database_migrations line 55) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '1'
---- Begin output of "bash"  "/tmp/chef-script20200915-35-lemic5" ----
STDOUT: rake aborted!
PG::ConnectionBad: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/opt/gitlab/postgresql/.s.PGSQL.5432"?
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:53:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => gitlab:db:configure
(See full trace by running task with --trace)
STDERR: 
---- End output of "bash"  "/tmp/chef-script20200915-35-lemic5" ----
Ran "bash"  "/tmp/chef-script20200915-35-lemic5" returned 1

Missing redis socket in

Running handlers:
There was an error running gitlab-ctl reconfigure:

redis_service[redis] (redis::enable line 19) had an error: RuntimeError: ruby_block[warn pending redis restart] (/opt/gitlab/embedded/cookbooks/cache/cookbooks/redis/resources/service.rb line 65) had an error: RuntimeError: Execution of the command `/opt/gitlab/embedded/bin/redis-cli -s /var/opt/gitlab/redis/redis.socket INFO` failed with a non-zero exit code (1)
stdout: 
stderr: Could not connect to Redis at /var/opt/gitlab/redis/redis.socket: No such file or directory

It should be noted that the /var/opt/gitlab directory has been mapped in /mnt/storage/podman/gitlab/data. GlusterFS is used for /mnt/storage, so the gitlab files resides on a GlusterFS volume.

ERROR 1) Cannot create unix socket.

Checking the /var/log/audit/audit.log reveiled the problem immediately:
Keep on reading!

gpg list key and display key details from a file (without importing the key)

Files with GPG keyspublic or private. Here is how to get more information without importing the keys.
GPG cli could give enough information for an explored key in a file:

  • public or private key
  • encrypted or unencrypted key
  • user id description (including email)
  • key id and issuer fpr v4
  • when the key was generated and when it will expire
  • the algo for the encrypted key
  • more

The key may be in binary or ascii format. No difference.
Here is the GNU GPG cli command:

gpg --list-packets < ./filewith.key

All examples below are made with gpg (GnuPG) 2.2.19.
Keep on reading!

syslog – UDP local to syslog-ng and send remote. Forward syslog to remote server.

After writing an article for the rsyslog daemon about forwarding local UDP logging to a remote server using TCP – UDP local to rsyslog and send remote with TCP and compression this time going to use syslog-ng daemon for those who use it as default in their Linux distribution.
As mentioned in the previous article always use a non-blocking way of writing logs using UDP locally and then transfer (forward) the logs to the centralized log server(s). The example here transfers the web server’s access logs to a remote server. The web server is an Nginx web server.
The goal is to use

  • UDP for the client program (Nginx in the case) for non-blocking log writes.
  • TCP between our local machine and the remote syslog server – to be sure not to lose messages on bad connectivity.
  • local caching for our client machine – not to lose messages if the remote syslog is temporary unreachable.

The configuration and the commands are tested on CentOS 7, CentOS 8, Gentoo and Ubuntu 18 LTS. Check out UDP remote logging here – nginx remote logging to UDP rsyslog server (CentOS 7) to see how to build the server-side part – the syslog server accepting the syslog messages and writing them into files.

STEP 1) Listen for local UDP connections

Configuration file /etc/syslog-ng/syslog-ng.conf

source udp_local {
    network(ip(127.0.0.1) port(514) transport("udp") so_rcvbuf(67108864) log_fetch_limit(1000) max-connections(1000) log-iw-size(1000000));
};

Keep on reading!

CentOS 8 add a storage driver (megaraid_sas) when booting the installation disk

Installing CentOS 8 in relatively old hardware maybe a real challenge because of an old hardware device like storage, network, or both.
This article shows how to make the CentOS 8 Installation wizard detect the storage – a hardware controller AOC-USAS2LP-H8iR (smc2108 with LSI 2108). Unfortunately, the CentOS 8 (in fact, RHEL 8 removed the support, too) team decided to remove support for the LSI SAS2008/2108/2116 storage controllers by removing the “megaraid_sas” kernel driver. There are still servers in production with similar controllers, which were sold 4-5 years ago from the big vendors such as DELL, HP, and so on.

The method here is to boot the installation CD/USB with modified kernel boot parameters to include an URL link to the installation driver iso (where the megaraid_sas driver is included).

The offered way to load the megaraid_sas (or any other driver) includes:

  1. Use assisted driver update to load an elrepo driver ISO during the first stage of the CentOS 8 Installation Wizard. elrepo is a famous community efford – http://elrepo.org/tiki/tiki-index.php. More on the assited diver update here – https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8/html/performing_an_advanced_rhel_installation/updating-drivers-during-installation_installing-rhel-as-an-experienced-user#performing-an-assisted-driver-update_updating-drivers-during-installation
  2. Configure the network of the server to be able to download the driver ISO in the early stage of the CentOS 8 Installation Wizard. Add boot parameters to set up a valid network configuration.

The installation CD/USB can download an iso with kernel drivers. And of course, to download a file from the Internet a network should be set in the earliest stage of the CentOS 8 installation wizard.
The added string to the boot CD/USB CentOS 8 Installation disk is:

 inst.dd=https://elrepo.org/linux/dud/el8/x86_64/dd-megaraid_sas-07.710.50.00-1.el8_2.elrepo.iso ip=10.10.10.10::10.10.10.1:255.255.255.0::enp8s0f0:off nameserver=8.8.8.8

SCREENSHOT 1) Select with the arrows “Install CentOS Linux 8” and hit “TAB” button to edit the boot parameters.

As shown in the picture just add ” inst.dd=https://elrepo.org/linux/dud/el8/x86_64/dd-megaraid_sas-07.710.50.00-1.el8_2.elrepo.iso ip=10.10.10.10::10.10.10.1:255.255.255.0::enp8s0f0:off nameserver=8.8.8.8″. The “inst.dd” instructs the installation wizard where are the driver ISO located. The “ip” and “nameserver” command just sets a proper network in the early stage of the CentOS 8 Installation wizard to be able to download the driver ISO. Setting the network by these parameters is really important, because the download of the driver iso happens in this early stage of loading the installation wizard. Replace the IP and the whole network configuration if needed.

main menu
Installation wizard edit boot parameters

Keep on reading!

storcli with multiple disks from different enclosures

Creating a Virtual device with the AVAGO storcli command-line tool under Linux. Two examples are included:

  1. All disks are from one of the enclosure. All disks are included explicitly.
  2. Disks from two enclosures are included. One controller with two enclosures.

Check out how to Install the new storcli to manage (LSI/AVAGO/Broadcom) MegaRAID controller under CentOS 7
There are 31 disks of 36 harddisk bays. 5 are missing on purpose for the examples.

The initial states of the controller and the disks.

livecd ~ # opt/MegaRAID/storcli/storcli /c0 show
Generating detailed summary of the adapter, it may take a while to complete.

CLI Version = 007.0510.0000.0000 May 4, 2018
Operating system = Linux 4.19.72-gentoo
Controller = 0
Status = Success
Description = None

Product Name = LSI 2108 MegaRAID
Serial Number = FW-ABQRCBEAARBWA
SAS Address =  5003048004015f00
PCI Address = 00:06:00:00
System Time = 07/20/2020 22:58:35
Mfg. Date = 00/00/00
Controller Time = 07/20/2020 22:58:36
FW Package Build = 12.15.0-0239
FW Version = 2.130.403-4660
BIOS Version = 3.30.02.2_4.16.08.00_0x06060A05
Driver Name = megaraid_sas
Driver Version = 07.706.03.00-rc1
Vendor Id = 0x1000
Device Id = 0x79
SubVendor Id = 0x15D9
SubDevice Id = 0x700
Host Interface = PCI-E
Device Interface = SAS-6G
Bus Number = 6
Device Number = 0
Function Number = 0
Physical Drives = 31

PD LIST :
=======

-----------------------------------------------------------------------------------
EID:Slt DID State DG     Size Intf Med SED PI SeSz Model                   Sp Type 
-----------------------------------------------------------------------------------
12:0      0 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HUA723020ALA640 D  -    
12:1      1 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    D  -    
12:2      2 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:3      3 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:4      4 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:5      5 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS722T2TALA604    D  -    
12:6      6 UGood -  1.817 TB SATA HDD N   N  512B TOSHIBA DT01ACA200      D  -    
12:7      7 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:8      8 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:9      9 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:10    10 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
12:11    11 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 D  -    
37:0     13 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:1     14 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:2     15 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:3     16 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HUA723020ALA640 U  -    
37:4     17 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:6     19 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:7     20 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS722T2TALA604    U  -    
37:8     21 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
37:10    23 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:11    24 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:13    26 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:14    27 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
37:16    29 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
37:17    30 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HUA723020ALA640 U  -    
37:19    32 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:20    33 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:21    34 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS722T2TALA604    U  -    
37:22    35 UGood -  1.817 TB SATA HDD N   N  512B Hitachi HDS723020BLA642 U  -    
37:23    36 UGood -  1.817 TB SATA HDD N   N  512B HGST HUS724020ALA640    U  -    
-----------------------------------------------------------------------------------

EID-Enclosure Device ID|Slt-Slot No.|DID-Device ID|DG-DriveGroup
DHS-Dedicated Hot Spare|UGood-Unconfigured Good|GHS-Global Hotspare
UBad-Unconfigured Bad|Onln-Online|Offln-Offline|Intf-Interface
Med-Media Type|SED-Self Encryptive Drive|PI-Protection Info
SeSz-Sector Size|Sp-Spun|U-Up|D-Down/PowerSave|T-Transition|F-Foreign
UGUnsp-Unsupported|UGShld-UnConfigured shielded|HSPShld-Hotspare shielded
CFShld-Configured shielded|Cpybck-CopyBack|CBShld-Copyback Shielded

Keep on reading!

aptly delete a mirror and remove all files

Executing drop command on a mirror will only remove the meta information for the mirror and it will not remove the package files occupying space on the file system.

Dropping mirror in aptly supposes to execute a clean command with aplty

aptly db cleanup

The newly created Bionic mirrors in the prevoius article on the aptly subject – Mirror the official Ubuntu repositories using aptly will be deleted here and removing all files with:

aptly@srv:~$ aptly mirror drop bionic-main
Mirror `bionic-main` has been removed.
aptly@srv:~$ aptly mirror drop bionic-security-main
Mirror `binonic-security-main` has been removed.
aptly@srv:~$ aptly mirror drop bionic-universe     
Mirror `bionic-universe` has been removed.
aptly@srv:~$ aptly mirror drop bionic-updates-main
Mirror `binonic-updates-main` has been removed.
aptly@srv:~$ aptly mirror drop bionic-updates-universe
Mirror `bionic-updates-universe` has been removed.
aptly@srv:~$ aptly mirror list
No mirrors found, create one with `aptly mirror create ...`.

The occupied space on the disk mounted in /srv is 270G:

aptly@srv:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            1.9G     0  1.9G   0% /dev
tmpfs           395M  3.5M  391M   1% /run
/dev/sda3        19G  4.6G   13G  27% /
tmpfs           2.0G  204K  2.0G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/sda4       470G  270G  176G  61% /srv
tmpfs           395M     0  395M   0% /run/user/0
tmpfs           395M     0  395M   0% /run/user/1001

Actually freeing the space on the disk with the clean aptly command:

aptly@srv:~$ aptly db cleanup
Loading mirrors, local repos, snapshots and published repos...
Loading list of all packages...
Deleting unreferenced packages (143121)...
Building list of files referenced by packages...
Building list of files in package pool...
Deleting unreferenced files (194097)...
Disk space freed: 268.80 GiB...
Compacting database...

The occupied space on the disk mounted in /srv is below 2G after the cleaning command:

aptly@srv:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            1.9G     0  1.9G   0% /dev
tmpfs           395M  3.5M  391M   1% /run
/dev/sda3        19G  4.6G   13G  27% /
tmpfs           2.0G  204K  2.0G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/sda4       470G    1G  176G   1% /srv
tmpfs           395M     0  395M   0% /run/user/0
tmpfs           395M     0  395M   0% /run/user/1001

Upgrading Ubuntu 18 to Ubuntu 20 – software versions upgrade table – head to head

In the following article a comparison between two LTS version of Ubuntu is presented – Ubuntu 18.04 LTS (Bionic) versus Ubuntu 20.04 LTS (Focal). The latest version of Ubuntu 18.04 and Ubuntu 20.04 (17.06.2020) is used to generate the software versions below.

In the Desktop world upgrading to the new and latest version of a Linux distribution is almost mandatory, but in the server world, upgrading is more complicated. The first step in updating a server is to check what software versions come with the new distribution version and then check whether the running custom (application) software supports the software versions. For example, updating to a new distribution version, which comes with PHP 7.4, but the current application supports only 7.2 is not very wise and in addition, the current version may have years with support in the future.

Having a head to head version comparison to check is the main target of this article – a fast check what version the user could expect from the new (aka latest) Linux distribution.

SoftwareUbuntu 20.04Ubuntu 18.04
Linux kernel



5.4.0
5.6.0
4.15.0
4.18.0
5.0.0
5.3.0
5.4.0
libc2.312.27
OpenSSL
1.1.1f
1.0.2n
1.1.1
GNU GCC


7.5.0
8.4.0
9.3.0
10-20200411
4.8.5
5.5.0
6.5.0
7.5.0
8.4.0
PHP7.47.2
Python2.7.17
3.8.2
2.7.15
3.6.7
Perl5.30.05.26.1
Ruby2.72.5.1
OpenJDK8u252-b09
11.0.7
13.0.3
14.0.1
8u252-b09
11.0.7
Go lang1.13.8
1.14.2
1.8
1.9
1.10
Rust1.41.01.41.0
llvm



6.0.1
7.0.1
8.0.1
9.0.1
10.0.0
3.7.1
3.9.1
4.0.1
5.0.1
6.0
7
8
9
10.0.0
nodejs10.19.08.10.0
Subversion1.131.9.7
Git2.25.22.17.1
Apache2.4.412.4.29
Nginx1.17.101.14.0
MySQL server8.0.205.7.30
MariaDB10.3.2210.1.44
PostgreSQL12.210.12
SQLite3.22.03.31.1
Xorg X server1.20.81.19.6
Gnome Shell3.36.23.28.4

edit mysql options in docker (or docker-compose) mysql

Modifying the default options for the docker (podman) MySQL server is essential. The default MySQL options are too conservative and even for simple (automation?) tests the options could be .
For example, modifying only one or two of the default InnoDB configuration options may lead to boosting multiple times faster execution of SQL queries and the related automation tests.

Here are three simple ways to modify the (default or current) MySQL my.cnf configuration options:

  • Command-line arguments. All MySQL configuration options could be overriden by passing them in the command line of mysqld binary. The format is:
    --variable-name=value
    

    and the variable names could be obtained by

    mysqld --verbose --help
    

    and for the live configuration options:

    mysqladmin variables
    
  • Options in a additional configuration file, which will be included in the main configuration. The options in /etc/mysql/conf.d/config-file.cnftake precedence.
  • Replacing the default my.cnf configuration file/etc/mysql/my.cnf.

Check out also the official page – https://hub.docker.com/_/mysql.
Under CentOS 8 docker is replaced by podman and just replace the docker with podman in all of the commands below.

OPTION 1) Command-line arguments.

This is the simplest way of modifying the default my.cnf (the one, which comes with the docker image or this in the current docker image file). It is fast and easy to use and change, just a little bit of much writing in the command-line. As mentioned above all MySQL options could be changed by a command-line argument to the mysqld binary. For example:

mysqld --innodb_buffer_pool_size=1024M

It will start MySQL server with variable innodb_buffer_pool_size set to 1G. Translating it to (for multiple options just add them at the end of the command):

  • docker run

    root@srv ~ # docker run --name my-mysql -v /var/lib/mysql:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=111111 \
    -d mysql:8 \
    --innodb_buffer_pool_size=1024M \
    --innodb_read_io_threads=4 \
    --innodb_flush_log_at_trx_commit=2 \
    --innodb_flush_method=O_DIRECT
    1bb7f415ab03b8bfd76d1cf268454e3c519c52dc383b1eb85024e506f1d04dea
    root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
    | innodb_buffer_pool_size                                  | 1073741824
    
  • docker-compose:

    # Docker MySQL arguments example
    version: '3.1'
    
    services:
    
      db:
        image: mysql:8
        command: --default-authentication-plugin=mysql_native_password --innodb_buffer_pool_size=1024M --innodb_read_io_threads=4 --innodb_flush_log_at_trx_commit=2 --innodb_flush_method=O_DIRECT
        restart: always
        environment:
          MYSQL_ROOT_PASSWORD: 111111
        volumes:
         - /var/lib/mysql_data:/var/lib/mysql
        ports:
          - "3306:3306"
    

    Here is how to run it (the above text file should be named docker-compose.yml and the file should be in the current directory when executing the command below):

    root@srv ~ # docker-compose up
    Creating network "docker-compose-mysql_default" with the default driver
    Creating my-mysql ... done
    Attaching to my-mysql
    my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
    my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
    my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
    my-mysql | 2020-06-16T09:45:36.293747Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
    my-mysql | 2020-06-16T09:45:36.293906Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
    my-mysql | 2020-06-16T09:45:36.307654Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    my-mysql | 2020-06-16T09:45:36.942424Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    my-mysql | 2020-06-16T09:45:37.136537Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
    my-mysql | 2020-06-16T09:45:37.279733Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
    my-mysql | 2020-06-16T09:45:37.306693Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
    my-mysql | 2020-06-16T09:45:37.353358Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
    

    And check the option:

    root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
    | innodb_buffer_pool_size                                  | 1073741824
    

OPTION 2) Options in a additional configuration file.

Create a MySQL option file with name config-file.cnf:

[mysqld]
innodb_buffer_pool_size=1024M
innodb_read_io_threads=4
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
  1. docker run
  2. The source path must be absolute path!

    docker run --name my-mysql \
    -v /var/lib/mysql_data:/var/lib/mysql \
    -v /etc/mysql/docker-instances/config-file.cnf:/etc/mysql/conf.d/config-file.cnf \
    -e MYSQL_ROOT_PASSWORD=111111 \
    -d mysql:8
    
  3. docker-compose
    The source path may not be absolute path.

    # Docker MySQL arguments example
    version: '3.1'
    
    services:
    
      db:
        container_name: my-mysql
        image: mysql:8
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
          MYSQL_ROOT_PASSWORD: 111111
        volumes:
         - /var/lib/mysql_data:/var/lib/mysql
         - ./config-file.cnf:/etc/mysql/conf.d/config-file.cnf
        ports:
          - "3306:3306"
    

OPTION 3) Replacing the default my.cnf configuration file.

Add the modified options to a my.cnf template file and map it to the container on /etc/mysql/my.cnf. When overwriting the main MySQL option file – my.cnf you may map the whole /etc/mysql directory (just replace /etc/mysql/my.cnf with /etc/mysql below), too. The source file (or directory) may be any file (or directory) not the /etc/mysql/my.cnf (or /etc/mysql)

  • docker run:
    The source path must be absolute path.

    docker run --name my-mysql \
    -v /var/lib/mysql_data:/var/lib/mysql \
    -v /etc/mysql/my.cnf:/etc/mysql/my.cnf \
    -e MYSQL_ROOT_PASSWORD=111111 \
    --publish 3306:3306 \
    -d mysql:8
    

    Note: here a new option “–publish 3306:3306” is included to show how to map the ports out of the container like all the examples with the docker-compose here.

  • docker-compose:
    The source path may not be absolute path, but the current directory.

    # Use root/example as user/password credentials
    version: '3.1'
    
    services:
    
      db:
        container_name: my-mysql
        image: mysql:8
        command: --default-authentication-plugin=mysql_native_password
        restart: always
        environment:
          MYSQL_ROOT_PASSWORD: 111111
        volumes:
         - /var/lib/mysql_data:/var/lib/mysql
         - ./mysql/my.cnf:/etc/mysql/my.cnf
        ports:
          - "3306:3306"
    

Configure Bond (802.3ad LACP) device in CentOS 8 – configuration files

Upgrading to a bond device is a common step when the server exhausts its current network port bandwidth.
The hardware setup of the bond example here is:

  • two 10G network cards – ens1f0 and ens1f0
  • bond name – bond0
  • bond mode – 802.3ad – Link Aggregation Control Protocol (LACP)

The systemd reconfiguration procedure consists of:

  • Stop the network target
    systemctl stop network
    
  • Set several configuration files – network device files for the network interfaces, bonding interface – master and slave devices.
  • Start the network target
    systemctl start network
    

*Note: the 802.3ad bonding mode needs aditional configuration in the switch of which the server is connected.

The example here is using CentOS 8 configuration file to make a permanent (i.e. persistent over reboots using the CentOS 8 network configuration files) bonding configuration.
Check out the official bonding documentation for all modes and options – https://www.kernel.org/doc/Documentation/networking/bonding.txt.

CONF 1) Configure the network interfaces.

The interface should be in down state in the configuration file.
Interface 1 – /etc/sysconfig/network-scripts/ifcfg-ens1f0:

TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=dhcp
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens1f0
UUID=3b399a23-570e-45ed-9369-4ff5b87efb2c
DEVICE=ens1f0
ONBOOT=no

Interface 2 – /etc/sysconfig/network-scripts/ifcfg-ens1f1:

TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=dhcp
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens1f1
UUID=ecdc5d5b-9739-4424-9d67-362411974281
DEVICE=ens1f1
ONBOOT=no

CONF 2) Configure bonding master device – create a bonding group bond0

This device should be started up at boot.
Bonding device 1 – with name bond0 – /etc/sysconfig/network-scripts/ifcfg-Bond_connection_1:

BONDING_OPTS="downdelay=200 miimon=100 mode=802.3ad updelay=200"
TYPE=Bond
BONDING_MASTER=yes
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=none
IPADDR=10.10.10.10
PREFIX=24
GATEWAY=10.10.10.1
DNS1=10.10.10.2
DNS2=10.10.10.3
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_PRIVACY=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME="Bond connection 1"
UUID=f0a35f9a-20e4-484e-850c-689128642555
DEVICE=bond0
ONBOOT=yes

BONDING_OPTS are specific options for the bonding group with name bond0 and the bonding mode is set here, too.

CONF 3) Configure bonding slave devices – the two network cards

Adding the two network cards to the bonding group bond0. These devices should be started up at boot.
Interface 1 – /etc/sysconfig/network-scripts/ifcfg-bond0_slave_1:

HWADDR=90:E2:BA:8A:13:8C
TYPE=Ethernet
NAME="bond0 slave 1"
UUID=c49e0ced-6411-41fa-9a3b-a01a430664a7
DEVICE=ens1f0
ONBOOT=yes
MASTER=bond0
SLAVE=yes

Interface 2 – /etc/sysconfig/network-scripts/ifcfg-bond0_slave_2:

HWADDR=90:E2:BA:8A:13:8D
TYPE=Ethernet
NAME="bond0 slave 2"
UUID=90de1cad-1d9f-48cb-8e5a-7d8bfdde91d2
DEVICE=ens1f1
ONBOOT=yes
MASTER=bond0
SLAVE=yes