.::: Backup Database Full & Transaction & Restore Database SQL Server Alternative Full Script :::.

 
still corelation http://teguhth.blogspot.com/2023/05/backup-database-full-transaction.html

1. check database position
select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb')

select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('db_restore');

.::: Move SQL Server MDF and LDF Files location :::.

Step 1: Original Location

SELECT @@servername as ServerName,name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb');
 

Step 2: Take Database Offline
ALTER DATABASE secretdb SET OFFLINE;

 

.::: Trouble Shooting GTID Replication, slave not running :::.

 1. check slave status
MariaDB [teguhth]> select @@hostname,@@version;
+------------+---------------------+
| @@hostname | @@version           |
+------------+---------------------+
| teguhth02  | 10.5.21-MariaDB-log |
+------------+---------------------+
1 row in set (0.000 sec)

.::: Enabling GTIDs Replication in MariaDB Server for Master - Slave or Mirroring :::.

 1. enable /etc/hosts <optional>

[root@teguhth01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.31 teguhth01
10.10.10.32 teguhth02
10.10.10.33 teguhth03
10.10.10.34 teguhth04
10.10.10.35 teguhth05
[root@teguhth01 ~]#

.::: Upgrading from MariaDB 10.4 to MariaDB 10.6 :::.

1. check Repo for 10.4
[root@teguhth ~]# cat /etc/yum.repos.d/mariadb.repo
[mariadb-main]
name = MariaDB Server
baseurl = https://dlm.mariadb.com/repo/mariadb-server/10.4/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 0
enabled = 1

.::: Enable Log MariaDB MySQL & limited Size Log :::.

1. Check directory mariadb
[root@teguhth mysql]# pwd
/var/lib/mysql
[root@teguhth mysql]#
[root@teguhth mysql]# ls
aria_log.00000001  ib_buffer_pool  ib_logfile0  multi-master.info  mysql.sock          performance_schema  teguhth
aria_log_control   ibdata1         ibtmp1       mysql              mysql_upgrade_info  secretdb            teguhth.pid
[root@teguhth mysql]#

.::: Check Number Active User SQL Server connections :::.

sp_who2 'active'

1. Query 1
SELECT @@servername as ServerName,a.*
FROM
(SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame) a
ORDER BY a.DBName, a.LoginName
 

.::: List sessions / active connections on MySQL, MariaDB :::.

show status where variable_name like 'threads%';

1. open tab useing cli or mysql workbench or dbviewer
 

::: Test Result Backup from Database NonEncrypted & Restore Database Encrypted and otherwise :::.

A. Continue from
http://teguhth.blogspot.com/2023/06/test-result-backup-from-database.html

backup restore nonencripted/ teguh02 and restore to encripted then continue below
then backup encripted teguhth and restore to nonencripted/ > result success

B. backup & restore again

1. backup database from restore encripted
MariaDB [nonsecretdb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nonsecretdb        |
| performance_schema |
| secretdb           |
| teguhth            |
+--------------------+
6 rows in set (0.000 sec)
 

.::: Test Result Backup from Database Encrypted & Restore Database Non Encrypted and otherwise MariaDB, MySQL :::.

A. Environment Encrypted<teguhth01> to Database Non Encrypted <teguhth02>

SELECT  NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
 

SELECT  @@hostname,@@version;

SELECT  NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
 

.::: Enable Encryption Table & Database for MariaDB or MySQL or MarinaDB :::.

 
A. Pre Configure
1. Create database

create database secretdb;
use secretdb;

MariaDB [teguhth]> create database secretdb;
Query OK, 1 row affected (0.000 sec)

MariaDB [teguhth]>

MariaDB [teguhth]> use secretdb;
Database changed
MariaDB [secretdb]>