.::: Create Store procedure for database & size capacity in MariaDB, MySQL :::.

 1. referlink

https://teguhth.blogspot.com/2020/12/database-size-table-capacity-in-mysql.html
http://teguhth.blogspot.com/2022/08/create-store-procedure-for-database.html
https://teguhth.blogspot.com/2022/08/create-event-scheduler-to-run-query.htm

 

A. Database size info
2. create store procedure for Database size capacity

 
use teguhthtools;

DELIMITER //
Create Procedure sp_database_size_info()

begin

insert into teguhthtools.tbl_dbsize
SELECT @@hostname as ServerName, current_timestamp() as DateTime,s.schema_name as SchemaName,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00)) TotalSizeMB,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00)) DataUsedMB,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00)) DataFreeMB  
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY SchemaName ;


end //

DELIMITER ;
 

.::: Backup & restore Single Table, Database & All Database MariaDB, MySQL include single-transaction & skip-lock-tables include,ignore using Date Time file Name :::.

 

database/table non encripted can restore to database encripted

but database/table encripted cannot restore to database non encripted
A. Backup Database
1. list database
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| dbatools           |
| information_schema |
| mysql              |
| performance_schema |
| teguhth            |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>
 

.::: Create user MariaDB MySQL admin, readonly, show priviledge database MySQL MariaDB & change password with all hostname & specific hostname :::.

1. Create user & database;

 

-- for access local
CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- for access remote
CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

ALTER USER 'root'@'%' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

-- for access local
CREATE USER 'teguh'@'%' IDENTIFIED BY 'teguhteguh';
GRANT ALL PRIVILEGES ON *.* TO 'teguh'@'%' WITH GRANT OPTION;

ALTER USER 'teguh'@'%' IDENTIFIED BY 'teguhteguh';
FLUSH PRIVILEGES;

-- for access remote
CREATE USER 'teguh'@'localhost' IDENTIFIED BY 'teguhteguh';
GRANT ALL PRIVILEGES ON *.* TO 'teguh'@'localhost' WITH GRANT OPTION;

ALTER USER 'teguh'@'localhost' IDENTIFIED BY 'teguhteguh';
FLUSH PRIVILEGES;

create database tgh;
create user 'teguh' identified by 'teguhteguh';
grant all privileges on *.* to 'teguh'@'%' identified by 'teguhteguh' with grant option;
FLUSH PRIVILEGES;
 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

 
ALTER USER 'root'@'%' IDENTIFIED BY 'root';

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

UPDATE mysql.user SET host='%' WHERE user='root'; 

FLUSH PRIVILEGES;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
ALTER USER 'admin'@'localhost' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
ALTER USER 'admin'@'%' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

sample log

.::: Compare date and time and if statement in MySQL MariaDB, SQL Server :::.

A. Date and Time
-- MariaDB MySQL
select curdate() as tanggalskrng;
SELECT DAYNAME(curdate()) as haritanggal;
select date(NOW()) - interval 1 day as onedayagotanggal;
SELECT DAYNAME(date(NOW()) - interval 1 day) as harionedayagotanggal;
select current_timestamp() as dateandtimelast;
select date(current_timestamp()) as onlydatetimelast;
select DAYNAME(current_timestamp()) as haridateandtimelast;
 

.::: Install MySQL Dashboard and Collector in Grafana :::.

 B. Install MySQL Dashboard and Collector in Grafana

1. visit to collect Dasboard template (2mysql-simple-dashboard_rev6.json)
https://grafana.com/grafana/dashboards/?dataSource=mysql

2. Downlaod script
Before installing the dashboard, we need to set up a collector on our MySQL server. The collector script is downloaded from https://github.com/meob/my2Collector

wget https://codeload.github.com/meob/my2Collector/zip/refs/heads/master

.::: install and configure Grafana on CentOS 7 :::.

A. Install Grafana
1. Disable SELinux

2. Installing Grafana via YUM Repository

[root@nmslinux ~]# cat  /etc/yum.repos.d/grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
[root@nmslinux ~]#
 

.::: Install MySQL Router load balancer for Galera MariaDB MySQL :::.

1. Disable selinux & firewalld
[root@mysqlrouter data]# sestatus
SELinux status:                 disabled
[root@mysqlrouter data]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@mysqlrouter data]#

 

.::: How to Install and Configure MaxScale for MariaDB :::.

1. Add into the MaxScale server MaxScale server the MariaDB repositories

[root@maxscale data]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.8 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg /data
/data
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb-main mariadb-maxscale mariadb-tools updates
Cleaning up list of fastest mirrors
[root@maxscale data]#
 

.::: Install ProxySQL load balancer for Galera MariaDB MySQL :::.

 1. Disable selinux & firewalld
[root@proxysql ~]# sestatus
SELinux status:                 disabled
[root@proxysql ~]#
[root@proxysql ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@proxysql ~]#

2. additional hosts
[root@proxysql ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.51 mariadb01
10.10.10.52 mariadb02
10.10.10.100 severnines
10.10.10.140 proxysql
[root@proxysql ~]#
 

.::: How to Enable Performance report performance-schema MySQL, MariaDB :::.

1. Open MySQL WorkBench Server -> Performance report
mysqld --verbose --help | grep performance-schema

2. enable performance_schema in /etc/my.cnf
[mysqld]
performance_schema
#performance_schema_events_waits_history_size=20
#performance_schema_events_waits_history_long_size=15000

.::: Install HAProxy Load Balancer to MariaDB MySQL :::.

 1. Preparation
https://teguhth.blogspot.com/2022/03/how-to-create-ha-high-availability.html
wsrep_cluster_name="cluster1" , 0.0.0.0:3306 -> detect ip loopback & statistic
mariadb01 mariadb02 haproxy
[root@mariadb01 ~]# cat /etc/my.cnf.d/server.cnf
#
......................
# * Galera-related settings
#
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.10.51,10.10.10.52"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="cluster1"
wsrep_sst_method=rsync
wsrep_node_address= "10.10.10.51"
wsrep_node_name="mariadb01"

#wsrep_sst_auth=teguh:triharto
 

.::: SQL Server OFFSET FETCH :::.

select * from pembelian order by KODE_PEMBELIAN; select * from pembelian order by KODE_PEMBELIAN offset 5 rows;
select * from pembelian order by KODE_PEMBELIAN offset 5 rows FETCH NEXT 2 ROWS ONLY; select * from pembelian order by KODE_PEMBELIAN offset 0 rows FETCH NEXT 2 ROWS ONLY;

 

 

https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/ 

.::: Simple Archive data table in SQL :::.

 1. Main table will be archive  
USE [teguhth]
GO
select @@servername as ServerName, db_name() as DBName,* from pembelian -- original data from table
select @@servername as ServerName, db_name() as DBName,* from pembelian where TANGGAL_PEMBELIAN < '2020-07-02 00:00:00' -- original data will archive from table