Coretane Mr. Jaman

Senin, 20 Juli 2026

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';

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;

Selasa, 14 Juli 2026

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

Kamis, 10 Juli 2025

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

Senin, 10 Maret 2025

Backup vm dari proxmox agar bisa di restore pada proxmox yang berbeda

pilih VM yang akan di backup pada UI proxmox

File backup akan tersimpan di /var/lib/vz/dump/ atau storage yang dipilih

Transfer Backup ke Proxmox Tujuan dari proxmox sumber

proxmox sumber : 192.168.11.89
proxmox tujuan : 192.168.146.2

scp /var/lib/vz/dump/vzdump-qemu-110-2025_03_10-14_38_26.vma.lzo root@192.168.146.2:/var/lib/vz/dump/

Restore VM di Proxmox Tujuan

qmrestore /var/lib/vz/dump/vzdump-qemu-110-2025_03_10-14_38_26.vma.lzo 111

sumber tambahan https://www-tecmint-com.translate.goog/proxmox-backup-restore-vm/?_x_tr_sl=en&_x_tr_tl=id&_x_tr_hl=id&_x_tr_pto=tc

Senin, 24 Februari 2025

Cara Resize Vm Proxmox

Lewat web Proxmox masuk ke Vm yg mau di resize misal di tambah 500 GB
kalo lewat cli qm resize 201 scsi0 +500G
qm list
masuk Ke Vm nya lalu install dulu parted yg baru
yum install parted
Ikuti partisi berikut

[root@localhost ~]# parted /dev/sda
GNU Parted 3.1
Using /dev/sda
Welcome to GNU Parted! Type 'help' to view a list of commands.
(parted) print
Model: QEMU QEMU HARDDISK (scsi)
Disk /dev/sda: 215GB
Sector size (logical/physical): 512B/512B
Partition Table: msdos
Disk Flags:

Number  Start   End     Size    Type     File system  Flags
 1      1049kB  1075MB  1074MB  primary  xfs          boot
 2      1075MB  34.4GB  33.3GB  primary               lvm

(parted) resizepart 2 100%
(parted) print
Model: QEMU QEMU HARDDISK (scsi)
Disk /dev/sda: 215GB
Sector size (logical/physical): 512B/512B
Partition Table: msdos
Disk Flags:

Number  Start   End     Size    Type     File system  Flags
 1      1049kB  1075MB  1074MB  primary  xfs          boot
 2      1075MB  215GB   214GB   primary               lvm

(parted) quit

[root@localhost ~]#

Show current disk at VM

[root@localhost ~]# df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   30G  1.1G   29G   4% /
devtmpfs                 3.9G     0  3.9G   0% /dev
tmpfs                    3.9G     0  3.9G   0% /dev/shm
tmpfs                    3.9G  8.5M  3.9G   1% /run
tmpfs                    3.9G     0  3.9G   0% /sys/fs/cgroup
/dev/sda1               1014M  142M  873M  14% /boot
tmpfs                    783M     0  783M   0% /run/user/1000
[root@localhost ~]#

show current Phisycal Volume

[root@localhost ~]# pvdisplay
  --- Physical volume ---
  PV Name               /dev/sda2
  VG Name               centos
  PV Size               <31.00 GiB / not usable 3.00 MiB
  Allocatable           yes (but full)
  PE Size               4.00 MiB
  Total PE              7935
  Free PE               0
  Allocated PE          7935
  PV UUID               dWzC8B-TkO9-hiHN-Oodx-c2d-nzQ-zrJfGN

[root@localhost ~]#

Resize Phisycal Volume

[root@localhost ~]# pvresize /dev/sda2
  Physical volume "/dev/sda2" changed
  1 physical volume(s) resized / 0 physical volume(s) not resized
[root@localhost ~]# 
[root@localhost ~]# pvdisplay
  --- Physical volume ---
  PV Name               /dev/sda2
  VG Name               centos
  PV Size               <199.00 GiB / not usable 2.00 MiB
  Allocatable           yes
  PE Size               4.00 MiB
  Total PE              50943
  Free PE               43008
  Allocated PE          7935
  PV UUID               dWzC8B-TkO9-hiHN-Oodx-c2d-nzQ-zrJfGN

[root@localhost ~]#

Resize Logical Volume

[root@localhost ~]# lvresize --extents +100%FREE --resizefs /dev/centos/root
  Size of logical volume centos/root changed from <30.00 GiB (7679 extents) to <198.00 GiB (50687 extents).
  Logical volume centos/root successfully resized.
meta-data=/dev/mapper/centos-root isize=512    agcount=5, agsize=1965568 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0 spinodes=0
data     =                       bsize=4096   blocks=7863296, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal               bsize=4096   blocks=3839, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 7863296 to 51903488
[root@localhost ~]#

Test

show current disk

[root@localhost ~]# df -Th
Filesystem              Type      Size  Used Avail Use% Mounted on
/dev/mapper/centos-root xfs       198G  1.1G  197G   1% /
devtmpfs                devtmpfs  3.9G     0  3.9G   0% /dev
tmpfs                   tmpfs     3.9G     0  3.9G   0% /dev/shm
tmpfs                   tmpfs     3.9G  8.5M  3.9G   1% /run
tmpfs                   tmpfs     3.9G     0  3.9G   0% /sys/fs/cgroup
/dev/sda1               xfs      1014M  142M  873M  14% /boot
tmpfs                   tmpfs     783M     0  783M   0% /run/user/1000
[root@localhost ~]# sumber : https://hendro-wibiksono.web.id/2020/04/10/cara-resize-vm-proxmox/