MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

Step 1. Virtual Environment Setup using either VMWare Viewer or Oracle VM Box

Download VMWare Player – Evaluation Learing Purpose
https://www.vmware.com/products/workstation-player/workstation-player-evaluation.html

Step 2. Download the respective VM Image
https://www.linuxvmimages.com/images/ubuntu-2004/#ubuntu-20044

Download VM Image for Ubuntu 20.4
03/22/2022 01:11 PM 2,599,524,771 Ubuntu_20.04.4_VM.7z

Uncompress the above file

03/08/2022 03:46 AM 8,607,367,168 Ubuntu_20.04.4_VM_LinuxVMImages.COM.vmdk
03/08/2022 03:46 AM 3,071 Ubuntu_20.04.4_VM_LinuxVMImages.COM.vmx

Step 3. Import VM Images in VMware Workstation (VMDK File)
Step 3.1: Go to the downloads directory of your system (default one) or to the location where you have saved the VM image.
Step 3.2: Extract the compressed file using the 7-Zip.
Optionally, move the extracted folder to where you have your other VMs.
Step 3.3: Open VMware Workstation and go to File » Open.
Import Virtual Appliance
Step 3.4: Browse to the location where you have extracted the VM image. Select the image and then click on Open.
Select VM Image
Step 3.5: In a second or two, you should see a new Virtual Machine in the VMware Workstation.
Virtual Machine
Step 3.6: If required, you can change the virtual machine’s resources like CPU, Memory, Network, and other settings by Edit virtual machine settings.
Edit Virtual Machine Settings
Step 3.7: You can now power on the virtual machine to start working on it.
Power On Virtual Machine
Step 3.8: Click on I Copied It.
Copy Virtual Machine

Step 4. Download the MariaDB respective version with ColumnStore

MariaDB 10.7 With ColumnStore – Download and Install – With Internet

Step 4.1: Start the New VM with any Name
ubuntu/ubuntu
Network Setting change to Bridged so that we can have the IP address same as our desktop to SCP

Step 4.2: Create a mysql user (from ubuntu)
sudo adduser mysql

Step 4.3: Give Sudo access to mysql user (from ubuntu)
sudo usermod -aG sudo mysql

Logout and login confirm
sudo whoami
(should return root)

Step 4.4: Download the MariaDB Community Server with ColumnStore

sudo mkdir /db_dumps
sudo chown mysql:mysql /db_dumps

D:\Data-Thanam\Download>scp mariadb-10.7.3-ubuntu-focal-amd64-debs.tar mysql@192.168.3.24:/db_dumps

Step 4.5: Install the MariaDB with ColumnStore
cd /db_dumps
tar -xf mariadb-10.7.3-ubuntu-focal-amd64-debs.tar
cd mariadb-10.7.3-ubuntu-focal-amd64-debs/
sudo ./setup_repository

sudo su –

apt-get update && apt-get install mariadb-server mariadb-backup libmariadb3 mariadb-client mariadb-plugin-columnstore

Step 6: Check and Confirm Columstore

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_LIBRARY LIKE ‘ha_columnstore%’;

CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE IF NOT EXISTS test.contacts (first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100)) ENGINE=ColumnStore;

INSERT INTO test.contacts VALUES (“Walker”, “Percy”, “w.percy@example.com”), (“Flannery”, “O’Connor”, “flan@example.edu”);

select * from test.contacts;

show create table test.contacts\G;

Step 7. Confirm the MariaDB and ColumnStore Services Stop and Start

MariaDB Service Stop and Start
systemctl stop mariadb
systemctl start mariadb
systemctl restart mariadb

Note: When MariaDB service is stopped both of them are not available

ColumnStore Service Stop and Start

systemctl stop mariadb-columnstore
systemctl start mariadb-columnstore
systemctl restart mariadb-columnstore

Note: When ColumnStore service is stopped DB is available but the Table and schemas related with ColumnStore engine may not be accessible.

MariaDB [test]> select * from contacts;
ERROR 1815 (HY000): Internal error: DBRM is not responding. Cannot accept queries
MariaDB [test]>

How to ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s)

mysql> select user from mysql.user;

ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

mysql> SELECT CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)) FROM mysql.global_priv where user in (‘root’,’mariadb.sys’)\G;
* 1. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): mariadb.sys@localhost => {
“access”: 0,
“plugin”: “mysql_native_password”,
“authentication_string”: “”,
“account_locked”: true,
“password_last_changed”: 0
}
* 2. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@localhost => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “XXXXXXXX”,
“password_last_changed”: 1633918260
}
* 3. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@127.0.0.1 => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “XXXXXXXXX”,
“password_last_changed”: 1633918260
}
* 4. row *
CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)): root@::1 => {
“access”: 1073741823,
“plugin”: “mysql_native_password”,
“authentication_string”: “*XXXXXX”,
“password_last_changed”: 1633918260
}

mysql > select * from mysql.tables_priv where User=’mariadb.sys’;
Empty set (0.001 sec)

mysql > INSERT INTO tables_priv (Host, Db, User, Table_name, Grantor, Timestamp, Table_priv, Column_priv) VALUES (‘localhost’,’mysql’,’mariadb.sys’,’global_priv’, ‘root@localhost’,’0000-00-00 00:00:00′,’Select,Update,Delete’,”);
ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column mysql.tables_priv.Timestamp at row 1

mysql > select now();
+———————+
| now() |
+———————+
| 2022-02-07 01:09:07 |
+———————+
1 row in set (0.001 sec)

mysql > INSERT INTO tables_priv (Host, Db, User, Table_name, Grantor, ,Column_priv`) VALUES (‘localhost’,’mysql’,’mariadb.sys’,’global_priv’,’root@localhost’,current_timestamp(),’Select,Up
Query OK, 1 row affected (0.017 sec)

mysql > select * from user;
ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view

mysql -P 3306 -e “SET global innodb_fast_shutdown = 1;”

Stop MySQL

Start MySQL

mysql > select user from mysql.user where user=’root’;
+——+
| User |
+——+
| root |
+——+
1 rows in set (0.001 sec)

How to rename a column in MariaDB

mysql > desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int(11)     | YES  |     | NULL    |       |
| ename | varchar(15) | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

mysql > alter table emp change column empno eno int(11);
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eno   | int(11)     | YES  |     | NULL    |       |
| ename | varchar(15) | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

mysql >

How to collect log for mariadb issue investigation

When you have an incident of database crash or any issue we may need to collect detailed log for further investigation. Will be good to have these details

You can run from the unix host. This command has been tested and confirmed on Centos Linux. Make sure you are able to login to mysql instance using “mysql -P 3306” in case it is not working you have to change the command accordingly

Set filename

outfile=”CrashLog-$(hostname)-$(date +%Y%m%d)-globvar-info.out”
date>>$outfile
echo>>$outfile

Global Variables

echo “Global Variables:”>>$outfile
mysql -P 3306 -ABNe “show global variables;” | sort | sed ‘s/; /;~\&/g’ | tr -s ‘~’ ‘\n’ | tr -s ‘\&’ ‘\t’ >>$outfile

Global Status x2 60s apart

echo >>$outfile
echo “Global Status x2:”>>$outfile
mysql -P 3306 -ABNe “show global status; select sleep(60); show global status;” | sort >>$outfile

Replication Status

echo >>$outfile
echo “Replication Status:”>>$outfile
mysql -P 3306 -ABNe “show master status;” >>$outfile
echo >>$outfile
mysql -Ae “show all slaves status\G” >>$outfile

Plugins

echo >>$outfile
echo “Plugins:”>>$outfile
mysql -P 3306 -ABNe “show plugins;”>>$outfile

Tables with no primary key

echo >>$outfile
echo “Tables with no Primary Key:”>>$outfile
mysql -P 3306 -ABNe “select t.table_schema, t.table_name from information_schema.tables as t left join information_schema.key_column_usage as c \
on ( t.table_name = c.table_name and c.constraint_schema = t.table_schema and c.constraint_name = ‘PRIMARY’ ) \
where t.table_schema not in (‘information_schema’, ‘performance_schema’, ‘mysql’) and t.table_type <> ‘VIEW’ and c.constraint_name IS NULL \
order by t.table_schema, t.table_name;” >>$outfile

Data size

echo >>$outfile
echo “Dataset Size:”>>$outfile
mysql -P 3306 -ABNe “select ifnull(B.engine,’Total’) \”Storage Engine\”, concat(lpad(format( \
B.DSize/power(1024,pw),3),17,’ ‘),’ ‘,substr(‘ KMGTP’,pw+1,1),’B’) \”Data Size\”, \
concat(lpad(format(B.ISize/power(1024,pw),3),17,’ ‘),’ ‘, \
substr(‘ KMGTP’,pw+1,1),’B’) \”Index Size\”,concat(lpad(format(B.TSize/ \
power(1024,pw),3),17,’ ‘),’ ‘,substr(‘ KMGTP’,pw+1,1),’B’) \”Table Size\” \
from (select engine,sum(data_length) DSize, \
sum(index_length) ISize,SUM(data_length+index_length) TSize from information_schema.tables \
where table_schema not in (‘mysql’,’information_schema’,’performance_schema’) AND \
engine is not null group by engine with rollup) B,(SELECT 2 pw) A order by TSize;” >>$outfile

Service relevant values

echo >>$outfile
echo “systemctl service timeouts:”>>$outfile
systemctl show mariadb | grep “^Timeout” >>$outfile

Kernel

echo >>$outfile
uname -r >>$outfile

Available RAM

echo >>$outfile
echo “MemInfo and CPU Core Count”>>$outfile
cat /proc/meminfo | grep MemTotal >>$outfile

CPU cores

echo >>$outfile
echo “CPU cores” >>$outfile
cat /proc/cpuinfo | egrep “core id|physical id” | tr -d “\n” | sed s/physical/\nphysical/g | grep -v “^$” | sort | uniq | wc -l >>$outfile

Disk space

echo >>$outfile
echo “Disk space” >>$outfile
df -h >>$outfile

Installed packages

echo >>$outfile
echo “Installed packages” >>$outfile
yum list installed | egrep -i “mariadb|percona” >>$outfile

Fix Lost connection of mysql server during query

How to fix lost connection of MySQL server during query?

This issue caused of running a long query.

  1. To fix this on MySQL Workbench, you can change the option of connection read time out. In my case using Mac click : MySQLWorkbench > Preferences > SQL Editor.
  2. On MySQL Session part, increase the “DBMS connection read timeout interval (in seconds)” value.
  3. Click OK to save then restart your connection on MySQL Workbench.

How to Configure Multi Master Replication on MYSQL 8

How to Configure MySQL 8 Multi Master Replication on Ubuntu

Note : Please follow the Master-Slave Configuration and make sure replication is working between Master to Slave. I tested and created the document on the same here

Now let us continue how to enable the replication from Slave to Master. We can call it as Multi Master replication or Bi-directional replication

1. On MySQL-Slave Node
We have to create replication user in MySQL-Slave and allow access from MySQL-Master

ubuntu@MySQL-Slave:~$ mysql -u root -p
mysql> create user rpl_user@192.168.0.8 identified by ‘password’;
Query OK, 0 rows affected (0.08 sec)
Grant the user REPLICATION SLAVE privileges:

mysql> grant replication slave on . to rpl_user@192.168.0.8;
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Confirm grants for created user:

mysql> show grants for rpl_user@192.168.0.8;

ALTER USER rpl_user@192.168.0.8 IDENTIFIED WITH mysql_native_password BY ‘password’;

mysql> show master status \G;

Take a note of current Master log file and position

2. On MySQL-Master – Node
We have to update the MASTER_HOST in MySQL-Master node as MySQL-Slave

mysql>CHANGE MASTER TO MASTER_HOST=’192.168.0.9′,
MASTER_USER=’rpl_user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000006′,
MASTER_LOG_POS=1178;

mysql> start slave;

Check and confirm the Master and Slave Status on Both Node

Yes !!!! Now Any Change on Master node will be replicated to Slave Node and vice versa

How to Configure MySQL 8 Replication

How to Configure MySQL 8 Master Slave Replication on Ubuntu

Setup Prerequisites:
You need to have MySQL Server installed on all servers
Make sure the Hostname and IP addresses are added on /etc/hosts
# Hosts Entry for MySQL Master & Slave Node
192.168.0.8 MySQL-Master MySQL-Master
192.168.0.9 MySQL-Slave MySQL-Slave

Step 1: Configure the Master Server:

server-id and log_bin location have to be configured

The first configuration change to make is setting Server ID for the master database:

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add the line below under [mysqld] section. Note that the number set needs to be unique, it can not be re-used on any node in the cluster.

server-id = 1

Set log_bin location, this is where all replication information is located. All the changes made on the master are written to this file. All slaves will copy data from it.

log-bin = /var/log/mysql/mysql-bin.log
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log

A complete simple configuration looks like below:
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log
Restart mysql service for changes to take effect:

$ sudo systemctl restart mysql

Step 2: Create Replication user on Master database server

We now need to create a database user to be used by slaves when connecting.
Login to MySQL database as root user and create the user:
ubuntu@MySQL-Master:~$ mysql -u root -p
mysql> create user rpl_user@192.168.0.9 identified by ‘password’;
Query OK, 0 rows affected (0.08 sec)
Grant the user REPLICATION SLAVE privileges:

mysql> grant replication slave on . to rpl_user@192.168.0.9;
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Confirm grants for created user:

mysql> show grants for rpl_user@192.168.0.9;
+——————————————————————+
| Grants for rpl_user@192.168.0.9 |
+——————————————————————+
| GRANT REPLICATION SLAVE ON . TO rpl_user@192.168.0.9;|
+——————————————————————+
1 row in set (0.00 sec)

Step 3: Configure Slave Server

Let us configure slave by editing the file:
we have to configure below three configuration
server-id
read_only
log_bin

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
log_bin = /var/log/mysql/mysql-bin.log
server-id = 2
read_only = 1
tmpdir = /tmp
binlog_format = ROW
max_binlog_size = 500M
sync_binlog = 1
expire-logs-days = 7
slow_query_log = 1

read_only = 1:

This sets the slave to read-only mode. Only users with the SUPER privilege and the replication slave thread will be able to modify data on it. This ensures there are no applications that can accidentally modify data on the slave instead of master.

server-id = 2:

This is a Unique server identification number. It will default to 1 if “master-host” is not set.

log_bin = /var/log/mysql/mysql-bin.log:

This enables binary logging. This is required for acting as a MASTER in a replication configuration. You also need the binary log if you need the ability to do point in time recovery from your latest backup.

Restart mysql server after you’ve finished making changes:

$ sudo systemctl restart mysql

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
sudo systemctl restart mysql
sudo mysql -u root -p
show variables like ‘server_id’;
show variables like ‘read_only’;
show variables like ‘log_bin’;

Step 4: Initialize Replication process

We should be ready to start Replication process on the slave server. Start by checking Status on the master:

mysql> show master status\G
* 1. row *
File: mysql-bin.000005
Position: 156
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Take a note of current Master log file and position. Then configure Slave server with details obtained from the master status command:

ubuntu@MySQL-Slave:~$ mysql -u root -p

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.8′,
MASTER_USER=’rpl_user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000005‘,
MASTER_LOG_POS=156;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

Then start replication on the slave:

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

To check slave status, use:

mysql> show slave status\G
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.8
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 452
Relay_Log_File: MySQL-Slave-relay-bin.000003
Relay_Log_Pos: 323
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 452
Relay_Log_Space: 537

Slave IO and SQL should indicate running state:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Appendix: Issues and Fixes

Issue 1:
Last_IO_Error: error connecting to master ‘rpl_user@192.168.0.8:3306’ – retry-time: 60 retries: 12 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

Solution:

ALTER USER rpl_user@192.168.0.9 IDENTIFIED WITH mysql_native_password BY ‘password’;

user rpl_user@192.168.0.9 identified by ‘password’;

Issue 2:
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Reason:

Server UUIDs is automatically generated but it was copied when we cloned mysql instance

It can be simply fixed by removing that auto generated auto.conf file and restart MySQL server. Enjoy your MySQL Master-Slave replication!!!

Solution:

root@MySQL-Slave:~# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=3b17a6cb-8749-11ea-b5df-080027e5f7b1

root@MySQL-Master:~# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=3b17a6cb-8749-11ea-b5df-080027e5f7b1

On the Slave Node
Stop MySQL
sudo systemctl stop mysql
sudo rm -f /var/lib/mysql/auto.cnf
sudo reboot (You can restart the computer or Just restart the MySQL instance)

How To Install MySQL 8 on Ubuntu

Step 1: Install Oracle VM VirtualBox or Any available virtual environment

Step 2: Download Ubuntu 18 VM Image from any available website.

I downloaded from linuxvmimages
https://www.linuxvmimages.com/images/ubuntu-1804/
Please make important note provided in the page where you are downloading the images
SYSTEM & LOGIN DETAILS
vCPU: 1 , RAM: 2GB , DISK: 100GB , /boot : 500MB , swap : 4GB , / : 85GB ,/home: 10GB
Network: Bridged, Username: ubuntu , Password : ubuntu (to become root, use sudo su -)

Observation:
While Downloading and importing virtual appliance faced below error and noticed it is because of below two reasons

  1. Space issue
  2. Download issue – Double confirm you downloaded properly.
    Note: You do not need 100GB. It will grow only when you start using it.

Step 3: Open Oracle VM Box and Import Alliance using the above downloaded file.

Step 4: Login to the VM Machine and confirm you are able to access internet and Download MySQL 8.0

$ sudo apt-get install wget
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb

Note: The above command will run successfully only when you do not see any lock on dpkg. As ubuntu releases lot patches and if your VM is connected with internet then there as possible some unattended updates may be in-progress. Please make sure you kill those process or wait until it get completed.

ubuntu@ubuntu1804:~$ lsof /var/lib/dpkg/lock
ubuntu@ubuntu1804:~$ ps aux | grep -i apt
ubuntu 2152 0.0 0.0 21532 1032 pts/0 S+ 18:26 0:00 grep --color=auto -i apt
ubuntu@ubuntu1804:~$

Step 5: Install MySQL Server

$ sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb
Note: The above will allow you to Choose the version and press "OK"
This will bring back to Terminal Window
Once the repo has been added, update apt index and install mysql-server:
$ sudo apt update
$ sudo apt install mysql-server
Accept Agreement
Set root password for MySQL database
Select default Authentication plugin
Confirm installed version of MySQL server:
ubuntu@ubuntu1804:~$ sudo apt policy mysql-server
mysql-server:
Installed: 8.0.19-1ubuntu18.04
Candidate: 8.0.19-1ubuntu18.04
Version table:
*** 8.0.19-1ubuntu18.04 500
500 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 Packages
100 /var/lib/dpkg/status

Step 6: Enable the MySQL Server

ubuntu@ubuntu1804:~$ sudo systemctl enable --now mysql
Synchronizing state of mysql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable mysql
ubuntu@ubuntu1804:~$
Check the Status of MySQL
ubuntu@ubuntu1804:~$ systemctl status mysql
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2020-04-24 18:32:46 EDT; 2min 34s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 7775 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 2332)
CGroup: /system.slice/mysql.service
7775 /usr/sbin/mysqld
Apr 24 18:32:17 ubuntu1804 systemd[1]: Starting MySQL Community Server…
Apr 24 18:32:46 ubuntu1804 systemd[1]: Started MySQL Community Server.
ubuntu@ubuntu1804:~$
Login to the database and confirm the functionality
ubuntu@ubuntu1804:~$mysql -u root -p
ubuntu

Issues Faced and Fixes :

root@ubuntu1804:~# sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb
dpkg: error: dpkg frontend is locked by another process
root@ubuntu1804:~#
Reason:
May be some other package is using the dpkg
Find who is using it
lsof /var/lib/dpkg/lock
ps aux | grep -i apt
Kill the process if you do not need it.
kill PID
wait
kill -9 PID
Make sure process is done:
Then remove the lock file:
sudo rm /var/lib/dpkg/lock
Let dpkg fix itself:
sudo dpkg --configure -a

How to Make Replication Faster in MySQL

How to Make Replication Faster in MySQL

innodb_flush_log_at_trx_commit to 2 will help on increasing speed of replication in case of latency between Master and Slave. It is not a recommended solution and you should not consider as permanent solution.

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

With a setting of 0, logs are written and flushed to disk once per second. Transactions or which logs have not been flushed can be lost in a crash.

With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

Suggession and Recommendation

innodb_flush_log_at_trx_commit = 0 if: it is my development computer or home mini database where is no sensitive data.

innodb_flush_log_at_trx_commit = 2 if: it is blog/stats/e-commerce (with ~100x shop in day), etc.

innodb_flush_log_at_trx_commit = 1 if: you have a lot of customers or you need to work with money transaction like bank. so this time you should split your dataflow between several servers to have speed & safety.

You can use 2, because it has ~75x faster write speed and it fails ONLY if hardware fails.

How to Replication Works in MySQL

How to – Replication Works in MySQL

MySQL Replication Events:
Statement based – in which case these are write queries
Row based – in this case these are changes to records, sort of row diffs if you will
Master :
Master needs to be writing replication events to a special log called binary log. This is usually very lightweight activity (assuming events are not synchronized to disk), because writes are buffered and because they are sequential. The binary log file stores data that replication slave will be reading later.
Whenever a replication slave connects to a master, master creates a new thread for the connection (similar to one that’s used for just about any other server client).
This connection will do the following
(a) feeding replication slave with events from the binary log.
(b) notifying slave about newly written events to its binary log.
Slaves that are up to date will mostly be reading events that are still cached in OS cache on the master.
However, when you connect a replication slave that is few hours or even days behind, it will initially start reading binary logs that were written hours or days ago – master may no longer have these cached, so disk reads will occur.
Slave : 
When you start replication, two threads are started on the slave
1. IO thread
This process called IO thread connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log.
Master_Log_File – last file copied from the master (most of the time it would be the same as last binary log written by a master)
Read_Master_Log_Pos – binary log from master is copied over to the relay log on the slave up until this position.
you can compare it to the output of “show master status” from the master.
2. SQL thread
The second process – SQL thread – reads events from a relay log stored locally on the replication slave (the file that was written by IO thread) and then applies them as fast as possible.
Relay_Master_Log_File – binary log from master, that SQL thread is “working on”
Exec_Master_Log_Pos – which position from master binary log is being executed by SQL thread.
Key word to remember is Bin Log and Relay Log
1 2