MySQL

MariaDB Galera Cluster on Ubuntu 20

by

MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB with support for XtraDB/InnoDB storage engines. It has the following top features.

  • It provides active-active multi-master topology
  • You can read and write to any cluster node
  • It has an automatic node joining
  • Automatic membership control, failed nodes drop from the cluster
  • Has true parallel replication, on row level
  • Direct client connections
sudo apt update && sudo apt -y upgrade
sudo reboot

Setup Hostnames

Here’re the IP plans of my servers-

Read more

Share

Install MariaDB 10.3 on CentOS

by

Step 1: Add MariaDB Yum Repository

 Create a new repo file /etc/yum.repos.d/mariadb.repo and add the below code changing the base url according to the operating system version and architecture.

# vi /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos73-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Step 2 – Install MariaDB Server

Let’s use the following command to install MariaDB 10.3

Read more

Share

Delete/Purging mysql-bin (binlog) files safely

by

The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are:

PURGE BINARY LOGS TO ‘binlogname’;
PURGE BINARY LOGS BEFORE ‘datetimestamp’;

These will clear all binary logs before the binlog or timestamp you just specified. For example, if you login to mysql run

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000223’;

this will erase all binary logs before ‘mysql-bin.000223’.

If you run

mysql> PURGE BINARY LOGS BEFORE DATE(NOW() – INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.

If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:

mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf

[mysqld]
expire-logs-days=3

Share

MySQL: Show Users, Privileges and Passwords

by

Show all MySQL users:

mysql> SELECT user FROM mysql.user;

List only unique user names:

mysql> SELECT DISTINCT user FROM mysql.user;

Show MySQL users and hosts they are allowed to connect from:

mysql> SELECT user,host FROM mysql.user;

Show MySQL users, their passwords and hosts:

mysql> SELECT user,host,password FROM mysql.user;

in MySQL 5.7 and higher:

mysql> SELECT host,user,authentication_string FROM mysql.user;

Show privileges granted to the current MySQL user:

mysql> SHOW GRANTS;

Show privileges granted to the MySQL user (if you don’t specify a host for the user name, MySQL assumes % as the host):

mysql> SHOW GRANTS FOR 'user_name';

Show privileges granted to a particular MySQL user account from a given host:

mysql> SHOW GRANTS FOR 'user_name'@'host';

– e.g. –

mysql> SHOW GRANTS FOR 'root'@'localhost';
mysql> SHOW GRANTS FOR 'root'@'%';
mysql> SHOW GRANTS FOR 'admin'@'192.168.0.1';
Share

Installing MySQL on Debian

by

Step 1 – Prerequisites

Login to your Debian 9 system using shell access. For remote systems connect with SSH. Windows users can use Putty or other alternatives applications for SSH connection.

ssh root@debian9

Run below commands to upgrade the current packages to the latest version.

sudo apt update 
sudo apt upgrade

Step 2 – Configure MySQL PPA

MySQL team provides official MySQL PPA for Debian Linux. You can download and install the package on your Debian system, which will add PPA file to your system. Run below command to enable PPA.

wget http://repo.mysql.com/mysql-apt-config_0.8.13-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb

During the installation of MySQL apt config package, It will prompt to select MySQL version to install. Select the MySQL 5.7 or 5.6 option to install on your system.

Read more

Share

Install Mydumper Myloader on Centos 7

by

If you want to execute logical backups using an alternative to mysqldump that works with parallel threads giving faster execution times mydumper is the correct tool.

wget https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm

In order to use mydumper and myloader you can use the following sample commands:

For dumping/exporting database:

mydumper -u <username> -p <password> -B <database name> -d /path/to/dump

For importing database (for percona or master-master replication):

myloader -u <username> -p <password> -B <database name> -d /dump/path --enable-binlog

Read more

Share

Exporting Percona Database

by

Exporting percona database is however a bit tricky way to make funcitonal. The usual-

mysqldump -u root -p [database] > database.sql

will stuck and shoot you many error. All you need is the add an extended parameters to execute the dump, and here it is:

mysqldump -u root -p [database] lock-for-backup > database.sql

And that should do the trick!

Src: 

https://www.percona.com/doc/percona-server/5.7/flexibility/extended_mysqldump.html

Share

Install Percona XtraDB Cluster for MySQL 5.7 on Debian 8

by

First of all, why we choose three nodes and not only two? In any cluster, the number of nodes should be odd, so in the case of disconnection of a node, we assume that the highest group of servers has the fresh data, and should be replicated to the down node to avoid data loss. This is related only to resolve conflicts in data replication, we won’t loose data written only to the disconnected node.

This is used to avoid a circumstance called split brain, in which we can’t automatically choose which node has correct data. Think for example of a 2 node cluster where both nodes are disconnected from each other, and the same record is written to both nodes: who wins when they come back online? We don’t know, so split brain happens, and we have to manually decide wich record is the right one.

The number of nodes that is needed to determine wich part of the cluster has the right data is called QUORUM, in our case, the quorum will be 2. So we need 2 servers always be connected to each other. In case all three nodes will go down, we have a split brain and we must decide wich server should go in bootstrap mode manually, this is the procedure to determine wich will be the main server to resume from the split brain.

Configuring Percona XtraDB Cluster on Debian 8

This tutorial describes how to install and configure three Percona XtraDB Cluster nodes on Debian 8 servers, we will be using the packages from the Percona repositories.

Node 1
Hostname: mysql1.local.vm
IP address: 192.168.152.100
Node 2
Hostname: mysql2.local.vm
IP address: 192.168.152.110
Node 3
Hostname: mysql3.local.vm
IP address: 192.168.152.120

On each host, modify file /etc/hosts as follows to ensure DNS will work correctly.

Read more

Share