.::: MariaDB MySQL : Create Store Procedure Simple Calculator :::.

 
1. Create store procedure

DELIMITER //

CREATE PROCEDURE Sp_SimpleCalculator(
    IN num1 DECIMAL(10, 2),
    IN num2 DECIMAL(10, 2),
    IN operator CHAR(1),
    OUT result DECIMAL(10, 2)
)
BEGIN
    CASE
        WHEN operator = '+' THEN SET result = num1 + num2;
        WHEN operator = '-' THEN SET result = num1 - num2;
        WHEN operator = '*' THEN SET result = num1 * num2;
        WHEN operator = '/' THEN SET result = num1 / num2;
        ELSE SET result = NULL;
    END CASE;
END //

DELIMITER ;
 

.::: Create Script Backup Database Full, Differential & Log Using T-SQL & Run using CMD SQL Server :::.

1. Create Script Backup Full > sp_backup_full


CREATE procedure [dbo].[sp_backup_full]
as
begin
DECLARE @BackupPath NVARCHAR(255);
DECLARE @DateSuffix NVARCHAR(20);
DECLARE @BackupFileName NVARCHAR(255);
declare @DBName NVARCHAR(255);
-- Replace 'YourDatabaseName' with the name of your database
SET @DBName ='teguhth'
SET @BackupPath = 'C:\backupdb_full\' -- Replace 'C:\YourBackupFolder\' with the desired backup folder path
 

.::: MSSQL: Create Store Procedure Simple Calculator :::.

1. Create store procedure Sp_Simple_Calculator

CREATE PROCEDURE Sp_Simple_Calculator
    @Num1 decimal,
    @Num2 decimal
AS
BEGIN
    SELECT @@ServerName as ServerName,db_name() as [DBName],getdate() [Date],'Result @Num1 & @Num2' as Desctiption,@Num1 as [Num1],@Num2 as [Num2], @Num1 + @Num2 AS 'Result Additional',
    @Num1 - @Num2 AS 'Result Substraction',@Num1 * @Num2 AS 'Result Multiplication',@Num1 / @Num2 AS 'Result Division';
END
 

.::: Backup and Restore PostgreSQL EDB Databases using Pg_dump Utility :::.

1. Backup Single Database

 pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]

 pg_dump -h 10.10.10.8 -p 5432 -U admin teguhth > teguhth_remote.sql 

restore

psql -h 10.10.10.8 -p 5432 -U admin teguhth  < teguhth_remote.sql

Backup

pg_dump teguhth  > teguhth_singledb.sql

-bash-4.2$ pg_dump teguhth  > teguhth_singledb.sql

[root@teguhth edb]# su - postgres
Last login: Tue Jul 25 13:09:57 WIB 2023 on pts/1
-bash-4.2$ cd 14/backups/
-bash-4.2$ pwd
/var/lib/pgsql/14/backups
-bash-4.2$
-bash-4.2$ ls
-bash-4.2$
-bash-4.2$ pg_dump teguhth  > teguhth_singledb.sql
-bash-4.2$
-bash-4.2$ ls
teguhth_singledb.sql
-bash-4.2$ ls -l
total 8
-rw-r--r-- 1 postgres postgres 7023 Jul 25 13:12 teguhth_singledb.sql
-bash-4.2$
 

.::: Backup & Restore MariaDB MySQL using remote from another Server/ Client :::.

1. check from destination (sever teguhth-lab02 database teguhth02_source )

mysql -uroot -p
select @@hostname,@@version;
show databases like '%teguhth02%';
select * from teguhth02_source.barang;
select * from teguhth02_destination.barang;

log

.::: Backup & Restore MariaDB MySQL using Shell Script in Linux :::.

1. create user backup

mysql -u root -p

CREATE USER 'backupuser'@'%' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON *.* TO 'backupuser'@'%';
GRANT RELOAD ON *.* TO 'backupuser'@'%';
FLUSH PRIVILEGES;

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON *.* TO 'backupuser'@'localhost';
GRANT RELOAD ON *.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;
 

.::: How to Maintenance Cleanup Task for delete file Backup Database Full, Differential & Transaction Log SQL Server Using Maintenance Plan :::.

1. Clean backup Database Full
2. Clean backup database Differential
3. Clean backup database Transaction Log

 

.::: How to Backup Database Full, Differential & Transaction Log SQL Server Using Maintenance Plan :::.

1. backup Database Full
2. backup database Differential
3. backup database Transaction Log

 

.::: How to Check Uptime MariaDB & Service MariaDB MySQL server was started :::.

 

 

1. uptime 1
SHOW GLOBAL STATUS LIKE 'Uptime';
MariaDB [teguhth]> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 165   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [teguhth]>
 

.::: Get backup Error ERROR 1118 (42000) at line 52172: Row size too large (> 8126) BLOB prefix of 768 bytes :::.

 

1. log as alonge backup

ERROR 1118 (42000) at line 52172: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
 

.::: How to Setting Streaming replication Redundant / Mirroring (Master Slave) EnterpriseDB 15 on Centos 7 :::.

 

 
A.

B. Setting di Master Node
1. Create user replication

su - enterprisedb

CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin';
ALTER ROLE replicate CONNECTION LIMIT 5;

[root@teguhth ~]# su - enterprisedb
Last login: Thu Jul 20 14:21:33 WIB 2023 on pts/0
-bash-4.2$ psql edb
psql (15.3.0, server 15.3.0)
Type "help" for help.

edb=# CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin';
CREATE ROLE
edb=#
edb=# ALTER ROLE replicate CONNECTION LIMIT 5;
ALTER ROLE
edb=#
 

.::: Install EnterpriseDB 15 (EDB 15) in Centos 7 :::.

 
1. get repo access from web login using new account only one month
https://www.enterprisedb.com/accounts/login

https://www.enterprisedb.com/repos


2. curl & install setup.rpm.sh
[root@teguhth data]# curl -1sLf 'https://downloads.enterprisedb.com/f9xxxxxxx/enterprise/setup.rpm.sh' | sudo -E bash
Executing the  setup script for the 'enterprisedb/enterprise' repository ...

   OK: Checking for required executable 'curl' ...
   OK: Checking for required executable 'rpm' ...
   OK: Detecting your OS distribution and release using system methods ...
 ^^^^: ... Detected/provided for your OS/distribution, version and architecture:
 >>>>:
 >>>>: ... distro=centos  version=7  codename=Core  arch=x86_64
 >>>>:

.::: How To Install MySQL 8 on CentOS 7 :::.

1. download repository
https://dev.mysql.com/downloads/repo/yum/
wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm

[root@teguhth data]# wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
--2023-07-03 14:27:10--  https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.201.152.213
Connecting to repo.mysql.com (repo.mysql.com)|23.201.152.213|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 11196 (11K) [application/x-redhat-package-manager]
Saving to: ‘mysql80-community-release-el7-7.noarch.rpm’

100%[========================================================================================================================>] 11,196      --.-K/s   in 0.005s

2023-07-03 14:27:12 (2.11 MB/s) - ‘mysql80-community-release-el7-7.noarch.rpm’ saved [11196/11196]

[root@teguhth data]# ls
mysql80-community-release-el7-7.noarch.rpm
[root@teguhth data]#