.::: Archive table 6 months and 24 months SQL Server Using Copy import export write a query to specify the data to transfer :::.

A. Import using ssms
1. create table source

create table pembelian_source(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));

2. create table destination
create table pembelian_dest(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));
 

::: Enable Automate Startup/Shutdown of Oracle Database and Listener on Linux, automatic booting :::.

continue from
https://teguhth.blogspot.com/2021/11/install-database-oracle-19c-in-linux.html

1. check /etc/oratab

The Y on the end of the string signifies that the database can be started and stopped by the ORACLE_HOME/bin/dbstart and ORACLE_HOME/bin/dbshut scripts.

[oracle@teguhth bin]$ cat /etc/oratab
#


# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , 'Y', or should not,
# 'N', be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#tgh:/u01/app/oracle/product/19.0.0/dbhome_1:N
tgh:/u01/app/oracle/product/19.0.0/dbhome_1:Y
[oracle@teguhth bin]$
 

.::: List Index, size Index, unusage index, duplicate index & drop Index in SQL Server :::.

1. list index all

use [AdventureWorks2019]
go

select @@servername as ServerName, db_name() as [DBName],object_id, name from sys.indexes
where object_id in (select object_id from sys.objects )

 

2. size index all
 

.::: Load log file & insert into table in MariaDB MySQL (sample mariadb audit) :::.

 

continue from http://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

1.    Create table tbl_mariadb_audit
create database teguhthtools;
use teguhthtools;

CREATE TABLE `tbl_mariadb_audit`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` varchar(64) DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

MariaDB [teguhthtools]> select * from teguhthtools.tbl_mariadb_audit;
Empty set (0.00 sec)

MariaDB [teguhthtools]>

.::: Enable server audit in MariaDB MySQL :::.

 

1.    Check plugin log
[root@teguhth data]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.44-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
 

.::: Enable Slow Query Log in MariaDB MySQL :::.

1.    Configure using CLI. For testing . long_query_time using 0.0000000001 from default 10
direct command in mysql

MariaDB [(none)]> set global slow_query_log = 'ON'
MariaDB [(none)]> set global log_queries_not_using_indexes = 'ON';
MariaDB [(none)]> set global slow_query_log_file = 'teguh-slow.log';
MariaDB [(none)]> set global long_query_time = 0.000000000001;
MariaDB [(none)]> show variables like '%slow%';
MariaDB [(none)]> show variables like '%long%';
 

.::: Create Event Scheduler to run Query & List Store Procedure in MySQL, MariaDB :::.

 

1. refer link

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

2. create event scheduler using existing store procedure

CREATE EVENT event_sp_database_size_info
    ON SCHEDULE EVERY '1' day
    STARTS '2022-08-01 02:00:00'
   do call sp_database_size_info ;

CREATE EVENT event_sp_table_size_info
   ON SCHEDULE EVERY '1' day
   STARTS '2022-08-01 02:00:00'
  do call sp_table_size_info;
 

 

.::: 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 ~]#