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;

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