Install SNMP klien pada server linux Ubuntu 22.x

sudo apt update
sudo apt install snmpd snmp -y

sudo mv /etc/snmp/snmpd.conf /etc/snmp/snmpd.conf.bak

sudo nano /etc/snmp/snmpd.conf

agentaddress udp:161
rocommunity mso default
syslocation PT BPR xxx
syscontact Admin IT <admin@domain.com>

sudo systemctl restart snmpd
sudo systemctl enable snmpd

sudo ufw allow 161/udp

Cara restore salah 1 table dari file backup full

 Berikut langkah-langkahnya:

Gunakan perintah sed untuk mencari dan menyalin blok data milik tabel yang Anda inginkan. Blok data biasanya diawali dengan keterangan Dumping data dan diakhiri dengan UNLOCK TABLES.

Jalankan perintah ini (ganti nama_tabel dengan nama tabel Anda):

 restore dulu tablenya jika antara table dan data terpisah file backupnya, lalu jalankan perintah dibawah pada file data 

 sed -n '/^-- Dumping data for table `nama_tabel`/,/^UNLOCK TABLES;/p' /var/backups/mbs/data/data/banjar-TANGGAL > data_tabel_target.sql

apabila ada eror pada Foreign Key jalankan perintah ini ketika restore

(echo "SET FOREIGN_KEY_CHECKS=0;"; cat /data/backups/mbs/data/data/data_kredit_detail.dump; echo "SET FOREIGN_KEY_CHECKS=1;") | mysql -uroot -pxxx -c mbsbackup03

Backup database MySQL no locking table

 mysqldump \
-u$MY_USER \
-p$MY_PASSWORD \
--single-transaction \
--skip-lock-tables \
--skip-add-locks \
--quick \
--default-character-set=ascii \
--set-gtid-purged=OFF \
--routines \
--no-data \
mbs-online \
> /var/backups/mbs/innodb/$MY_FILE1.dump

mysqldump \
-u$MY_USER \
-p$MY_PASSWORD \
--single-transaction \
--skip-lock-tables \
--skip-add-locks \
--quick \
--default-character-set=ascii \
--set-gtid-purged=OFF \
--skip-triggers \
--no-data \
mbs-online \
> /var/backups/mbs/data/table/$MY_FILE1.dump

mysqldump \
-u$MY_USER \
-p$MY_PASSWORD \
--single-transaction \
--skip-lock-tables \
--skip-add-locks \
--quick \
--default-character-set=ascii \
--set-gtid-purged=OFF \
--no-create-info \
--skip-triggers \
--no-create-db \
mbs-online \
> /var/backups/mbs/data/data/$MY_FILE1.dump

Cara mengetahui ukuran data InnoDB,MyISAM dan Jml table di MySQL

Jalankan query berikut:

SELECT
ROUND(SUM(data_length + index_length)/1024/1024/1024,2) AS innodb_size_gb
FROM information_schema.tables
WHERE engine='InnoDB';

jika hanya ke salah satu database saja

SELECT 
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS innodb_size_gb 
FROM information_schema.tables 
WHERE engine = 'InnoDB' 
AND table_schema = 'mydb';

Cara mengecek apakah buffer pool sudah cukup

Lihat rasio pembacaan dari disk:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; 

Kalau untuk MyISAM, query-nya sama, hanya engine-nya diganti menjadi MyISAM.

SELECT
ROUND(SUM(data_length + index_length)/1024/1024/1024,2) AS myisam_size_gb
FROM information_schema.tables
WHERE engine='MyISAM'; 

Jika diketahui ukuran index MyISAM

SELECT
ROUND(SUM(data_length)/1024/1024/1024,2) AS data_gb,
ROUND(SUM(index_length)/1024/1024/1024,2) AS index_gb
FROM information_schema.tables
WHERE engine='MyISAM';

Menghitung jumlah table dalam 1 database 

SELECT COUNT(*) AS jumlah_table
FROM information_schema.tables
WHERE table_schema = 'mbs-online'
AND table_type = 'BASE TABLE'; 

Kalau ingin melihat jumlah berdasarkan engine

Untuk kasus server Anda yang memiliki banyak InnoDB + MyISAM, jalankan:

SELECT
    ENGINE,
    COUNT(*) AS jumlah_table
FROM information_schema.tables
WHERE table_schema = 'mbs-online'
  AND table_type = 'BASE TABLE'
GROUP BY ENGINE
ORDER BY jumlah_table DESC;

Kalau ingin menghitung semua database sekaligus  

SELECT
    table_schema AS database_name,
    COUNT(*) AS jumlah_table
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN (
      'information_schema',
      'mysql',
      'performance_schema',
      'sys'
  )
GROUP BY table_schema
ORDER BY jumlah_table DESC;

Backup ignore table di MySQL

 mysqldump -uroot -p --routines --events --ignore-table={mbsbck2015.setup_adjust_jamin,mbsbck2015.setup_proses,mbsbck2015.setup_adjust_ppap1} mbsbck2015 > mbsbck2015.dump

mysqldump -uroot -p --routines --events --ignore-table={mbsbck2016.setup_adjust_jamin,mbsbck2016.setup_proses,mbsbck2016.setup_adjust_ppap1} mbsbck2016 > mbsbck2016.dump

Skip error replica Master to Master

 STOP REPLICA;

STOP REPLICA SQL_THREAD;
SET @@SESSION.sql_log_bin = 0;
SET GLOBAL sql_replica_skip_counter = 1;
START REPLICA;
SHOW REPLICA STATUS\G


SELECT * FROM performance_schema.replication_applier_status_by_worker;

STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO
  MASTER_HOST='10.207.7.1',
  MASTER_USER='mirror',
  MASTER_PASSWORD='J1d8$9Ym8tSkOQPE2',
  MASTER_AUTO_POSITION=1;
START SLAVE;
SHOW REPLICA STATUS\G


STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='10.211.1.1',
  MASTER_USER='mirror',
  MASTER_PASSWORD='J1d8$9Ym8tSkOQPE2',
  MASTER_LOG_FILE='mysql-bin.000236',
  MASTER_LOG_POS=15192461;
START SLAVE;

RESET MASTER;    //untuk membuat mysql-bin.xx mulai dari 1 (mysql-bin.000001)

##
STOP REPLICA;
RESET SLAVE ALL;
CHANGE MASTER TO
  MASTER_HOST='10.211.11.1',
  MASTER_USER='mirror',
  MASTER_PASSWORD='J1d8$9Ym8tSkOQPE2',
  MASTER_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

STOP REPLICA;
RESET SLAVE ALL;
CHANGE MASTER TO
  MASTER_HOST='10.207.7.1',
  MASTER_USER='mirror',
  MASTER_PASSWORD='J1d8$9Ym8tSkOQPE2',
  MASTER_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G
##

STOP REPLICA SQL_THREAD;
SET @@SESSION.sql_log_bin = 0;
SET gtid_next = 'bcdceaeb-cd5e-11ee-a729-1402ec32de48:1318256';
START TRANSACTION;
COMMIT;
SET gtid_next = 'AUTOMATIC';
START REPLICA;
SHOW REPLICA STATUS\G


STOP REPLICA;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='17.5.16.100',
  SOURCE_USER='mirror',
  SOURCE_PASSWORD='J1d8$9Ym8tSkOQPE95',
START SLAVE;


### 
STOP REPLICA FOR CHANNEL 'master1';
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='17.5.16.100',
  SOURCE_USER='mirror',
  SOURCE_PASSWORD='J1d8$9Ym8tSkOQPE95',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=485
FOR CHANNEL 'master1';

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='17.5.16.100',
  SOURCE_USER='mirror',
  SOURCE_PASSWORD='J1d8$9Ym8tSkOQPE95',
  SOURCE_AUTO_POSITION=1
FOR CHANNEL 'master1';

CHANGE REPLICATION FILTER
REPLICATE_REWRITE_DB = ((coba, coba1))
FOR CHANNEL 'master1';

START REPLICA FOR CHANNEL 'master1';
SHOW REPLICA STATUS FOR CHANNEL 'master1'\G


STOP REPLICA FOR CHANNEL 'master2';
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='17.5.16.102',
  SOURCE_USER='mirror',
  SOURCE_PASSWORD='J1d8$9Ym8tSkOQPE95',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=157
FOR CHANNEL 'master2';

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='17.5.16.102',
  SOURCE_USER='mirror',
  SOURCE_PASSWORD='J1d8$9Ym8tSkOQPE95',
  SOURCE_AUTO_POSITION=1
FOR CHANNEL 'master2';

CHANGE REPLICATION FILTER
REPLICATE_REWRITE_DB = ((coba, coba2))
FOR CHANNEL 'master2';

START REPLICA FOR CHANNEL 'master2';

SHOW REPLICA STATUS FOR CHANNEL 'master2'\G

Create user SFTP pada Linux

sudo groupadd sftpgroup
sudo groupadd sftpusers

sudo mkdir -p /home/sftp-data/mbsapp
sudo chown root:root /home/sftp-data
sudo chmod 755 /home/sftp-data
sudo chown root:sftpgroup /home/sftp-data/mbsapp
sudo chmod 770 /home/sftp-data/mbsapp

sudo useradd -d /home/sftp-data/ -s /usr/sbin/nologin -g sftpgroup sftpcaesar
passwd sftpcaesar
*****

sudo useradd -d /home/sftp-data/ -s /usr/sbin/nologin -g sftpgroup sftpkiky
passwd sftpkiky
*****

sudo useradd -d /home/sftp-data/ -s /usr/sbin/nologin -g sftpgroup sftpmumu
passwd sftpmumu
*****

pico /etc/ssh/sshd_config

Match Group sftpgroup
    ChrootDirectory /home/sftp-data
    ForceCommand internal-sftp
    X11Forwarding no
    AllowTcpForwarding no


#############################################################################

sudo adduser --home /home/sftpbackup --shell /usr/sbin/nologin sftpbackup
New password:
SftpBackup@35

sudo groupadd backups
sudo usermod -aG backups sftpbackup 

pico /etc/ssh/sshd_config
Match User sftpbackup
    ForceCommand internal-sftp
    PasswordAuthentication yes
    ChrootDirectory /var/backups/mbs/data
    PermitTunnel no
    AllowAgentForwarding no
    AllowTcpForwarding no
    X11Forwarding no


#############################################################################

userdel sftpcaesar
useradd -d /home/sftpcaesar -m sftpcaesar
passwd sftpcaesar
Supernova@4

pico /etc/passwd
sftpcaesar:x:0:0::/home/sftpcaesar:/bin/sh