1. create script backup full
[root@teguhth fix]# cat backup_full_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
dbversion=$(mysql -uroot -proot -sNe "select @@version as VersionDB");
echo ".::: Backup Full All database using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
#echo "Backup begin $infotgl"
backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin using $LATEST_BINLOG"
echo ""
dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
#datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
echo "$datex --->>> Starting Backup Full for database '$listdb' in $path"
[root@teguhth fix]# cat backup_full_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
dbversion=$(mysql -uroot -proot -sNe "select @@version as VersionDB");
echo ".::: Backup Full All database using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
#echo "Backup begin $infotgl"
backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin using $LATEST_BINLOG"
echo ""
dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
#datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
echo "$datex --->>> Starting Backup Full for database '$listdb' in $path"
#echo "$datey --->>> Starting Backup Full for database '$listdb' in $path"
# mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --flush-logs --master-data=2 --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/[FULL]_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;
# mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --flush-logs --master-data=2 --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/[FULL]_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;
#datez=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
echo "$datex --->>> Finish Backup Full for database '$listdb' in $path"
#echo "$datez --->>> Finish Backup Full for database '$listdb' in $path"
done;
mysqladmin -uroot -proot flush-logs
echo ""
LATEST_BINLOG_AFTER=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin After flush $LATEST_BINLOG_AFTER"
echo ""
#ls -lh $path
#echo ""
echo "Backup finish $infotgl"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth fix]#
2. test run backup_full_script.sh
or other script
echo "$datex --->>> Finish Backup Full for database '$listdb' in $path"
#echo "$datez --->>> Finish Backup Full for database '$listdb' in $path"
done;
mysqladmin -uroot -proot flush-logs
echo ""
LATEST_BINLOG_AFTER=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin After flush $LATEST_BINLOG_AFTER"
echo ""
#ls -lh $path
#echo ""
echo "Backup finish $infotgl"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth fix]#
2. test run backup_full_script.sh
or other script
[root@teguhth scriptbackup]# cat backup_all_maria.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
dbversion=$(mysql -ubackup_user -pbackup_pwd -sNe "select @@version as VersionDB");
echo ".::: Backup Full All database using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
#echo "Backup begin $infotgl"
echo "Backup begin $datex"
backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin using $LATEST_BINLOG"
echo ""
dbname=$(mysql -ubackup_user -pbackup_pwd -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
mkdir -p $backuppath/$year/$month/$day/$listdb;
path="$backuppath/$year/$month/$day/$listdb";
#echo "$datex --->>> Starting Backup Full for database '$listdb' in $path"
echo "$datey --->>> Starting Backup Full for database '$listdb' in $path"
# mysqldump -ubackup_user -pbackup_pwd -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --flush-logs --master-data=2 --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
mysqldump -ubackup_user -pbackup_pwd -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/[FULL]_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;
datez=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
#echo "$datex --->>> Finish Backup Full for database '$listdb' in $path"
echo "$datez --->>> Finish Backup Full for database '$listdb' in $path"
done;
mysqladmin -ubackup_user -pbackup_pwd flush-logs
echo ""
LATEST_BINLOG_AFTER=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin After flush $LATEST_BINLOG_AFTER"
echo ""
#ls -lh $path
datefinish=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
echo "Backup finish $datefinish"
echo ""
echo "Delete Retention Start"
find $backuppath/* -type d -mtime +21 -exec ls -ld {} \;
#find $backuppath/* -type d -mmin +2 -exec ls -ld {} \;
find $backuppath/* -type d -mtime +21 -exec rm -rf {} \;
#echo ""
echo "Delete Retention Finish"
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth scriptbackup]#
3. create script backup log
[root@teguhth fix]# cat backup_log_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
dbversion=$(mysql -uroot -proot -sNe "select @@version as VersionDB");
#echo ".::: Backup Log database MariaDB / MySQL using custom in '$Server' with $ip_address & Version $dbversion :::."
echo ".::: Backup Transaction Log database MariaDB / MySQL using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
#echo "Backup begin $infotgl"
backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Current date and time
CURRENT_DATETIME=$(date +"%Y%m%d_%H%M%S")
# Calculate start datetime (1 hour ago)
START_DATETIME=$(date -d "1 hour ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "15 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "30 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "45 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "60 minutes ago" +"%Y-%m-%d %H:%M:%S")
# Stop datetime (current time)
STOP_DATETIME=$(date +"%Y-%m-%d %H:%M:%S")
#echo "Backup begin $infotgl for periode $START_DATETIME to $STOP_DATETIME"
#echo ""
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin using $LATEST_BINLOG"
echo ""
dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
# Output file path
OUTPUT_FILE="$path/[LOG]_${listdb}_${CURRENT_DATETIME}.sql.gz"
echo "$datex --->>> Backup Transaction Log for database '$listdb' in $path"
# Run mysqlbinlog command
mysqlbinlog --database=$listdb --start-datetime="$START_DATETIME" --stop-datetime="$STOP_DATETIME" "$LATEST_BINLOG" | sed '/^use / s/^/# /' | gzip -c > "$OUTPUT_FILE"
echo "$datex --->>> Finished Backup Transaction Log for database '$listdb' in $path"
done;
mysqladmin -uroot -proot flush-logs
echo ""
LATEST_BINLOG_AFTER=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin after flush using $LATEST_BINLOG_AFTER"
echo ""
#ls -lh $path
#echo ""
echo "Backup finish $infotgl"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth fix]#
4. test create table and insert in incrementaldb
CREATE DATABASE incrementaldb;
use incrementaldb
create table my_tbl(
my_id INT NOT NULL AUTO_INCREMENT,
my_field VARCHAR(100) NOT NULL,
submission_date DATE,
time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ( my_id )
);
use incrementaldb;
INSERT into my_tbl (my_field) VALUES ('val1');
INSERT into my_tbl (my_field) VALUES ('val2');
INSERT into my_tbl (my_field) VALUES ('val3');
show tables ;
select *,@@hostname from incrementaldb.my_tbl;
5. test create table and insert in teguhth
use teguhth;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0001','2002-06-24',1);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','J-0002','2002-06-24',1);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0001','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-02','B-0002','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0001','2002-06-20',5);
select * from pembelian;
6. run backup_log_script.sh
7. check folder backup
[root@teguhth 20240217]# pwd
/backup/20240217
[root@teguhth 20240217]# ls
clasicdb db dblink dbmaria edb incrementaldb secretdb srcdefdb sys teguhth testdb
[root@teguhth 20240217]# cd teguhth/
[root@teguhth teguhth]# ls
[FULL]_teguhth_20240217_062031.sql.gz [FULL]_teguhth_20240217_062206.sql.gz [FULL]_teguhth_20240217_062411.sql.gz
[FULL]_teguhth_20240217_062158.sql.gz [FULL]_teguhth_20240217_062407.sql.gz [LOG]_teguhth_20240217_062537.sql.gz
[root@teguhth teguhth]# cd ..
[root@teguhth 20240217]# cd incrementaldb/
[root@teguhth incrementaldb]# ls
[FULL]_incrementaldb_20240217_062031.sql.gz [FULL]_incrementaldb_20240217_062206.sql.gz [FULL]_incrementaldb_20240217_062411.sql.gz
[FULL]_incrementaldb_20240217_062158.sql.gz [FULL]_incrementaldb_20240217_062407.sql.gz [LOG]_incrementaldb_20240217_062537.sql.gz
[root@teguhth incrementaldb]#
8. gunzip [LOG]_incrementaldb_20240217_062537.sql.gz & [LOG]_teguhth_20240217_062537.sql.gz
[root@teguhth incrementaldb]# gunzip [LOG]_incrementaldb_20240217_062537.sql.gz
[root@teguhth incrementaldb]#
[root@teguhth teguhth]# gunzip [LOG]_teguhth_20240217_062537.sql.gz
9. cat log backup [LOG]_incrementaldb_20240217_062537.sql
[root@teguhth incrementaldb]# cat \[LOG\]_incrementaldb_20240217_062537.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240217 6:24:12 server id 1 end_log_pos 256 CRC32 0x23389db7 Start: binlog v 4, server v 10.11.6-MariaDB-log created 240217 6:24:12
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
nO7PZQ8BAAAA/AAAAAABAAABAAQAMTAuMTEuNi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgG3nTgj
'/*!*/;
# at 256
#240217 6:24:12 server id 1 end_log_pos 285 CRC32 0x4a97a682 Gtid list []
# at 285
#240217 6:24:12 server id 1 end_log_pos 328 CRC32 0xfba94dd7 Binlog checkpoint mysql-bin.000002
# at 328
#240217 6:24:12 server id 1 end_log_pos 371 CRC32 0x0e5756cc Binlog checkpoint mysql-bin.000003
# at 371
#240217 6:24:51 server id 1 end_log_pos 413 CRC32 0x90b89c14 GTID 0-1-1 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
START TRANSACTION
/*!*/;
# at 413
#240217 6:24:51 server id 1 end_log_pos 445 CRC32 0x178eedce Intvar
SET INSERT_ID=7/*!*/;
# at 445
#240217 6:24:51 server id 1 end_log_pos 566 CRC32 0x684d1799 Query thread_id=4 exec_time=0 error_code=0 xid=0
# use `incrementaldb`/*!*/;
SET TIMESTAMP=1708125891/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET @@session.character_set_client=utf8mb3,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
INSERT into my_tbl (my_field) VALUES ('val1')
/*!*/;
# at 566
#240217 6:24:51 server id 1 end_log_pos 597 CRC32 0x9a3fd16f Xid = 32536
COMMIT/*!*/;
# at 597
#240217 6:24:51 server id 1 end_log_pos 639 CRC32 0x5dbf715e GTID 0-1-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 639
#240217 6:24:51 server id 1 end_log_pos 671 CRC32 0x928c55b6 Intvar
SET INSERT_ID=8/*!*/;
# at 671
#240217 6:24:51 server id 1 end_log_pos 792 CRC32 0x6009e2ec Query thread_id=4 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125891/*!*/;
INSERT into my_tbl (my_field) VALUES ('val2')
/*!*/;
# at 792
#240217 6:24:51 server id 1 end_log_pos 823 CRC32 0xe6b043f0 Xid = 32537
COMMIT/*!*/;
# at 823
#240217 6:24:51 server id 1 end_log_pos 865 CRC32 0x14bf4c5e GTID 0-1-3 trans
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
START TRANSACTION
/*!*/;
# at 865
#240217 6:24:51 server id 1 end_log_pos 897 CRC32 0x6a383364 Intvar
SET INSERT_ID=9/*!*/;
# at 897
#240217 6:24:51 server id 1 end_log_pos 1018 CRC32 0x44984ce7 Query thread_id=4 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125891/*!*/;
INSERT into my_tbl (my_field) VALUES ('val3')
/*!*/;
# at 1018
#240217 6:24:51 server id 1 end_log_pos 1049 CRC32 0x13b36f13 Xid = 32538
COMMIT/*!*/;
# at 1049
#240217 6:25:04 server id 1 end_log_pos 1091 CRC32 0xb78b6be9 GTID 0-1-4 trans
/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
START TRANSACTION
/*!*/;
# at 1091
# at 1311
#240217 6:25:04 server id 1 end_log_pos 1342 CRC32 0xc38cec7c Xid = 32543
COMMIT/*!*/;
# at 1342
#240217 6:25:04 server id 1 end_log_pos 1384 CRC32 0x4884e5ae GTID 0-1-5 trans
/*!100001 SET @@session.gtid_seq_no=5*//*!*/;
START TRANSACTION
/*!*/;
# at 1384
# at 1604
#240217 6:25:04 server id 1 end_log_pos 1635 CRC32 0xb25a7aa5 Xid = 32544
COMMIT/*!*/;
# at 1635
#240217 6:25:04 server id 1 end_log_pos 1677 CRC32 0x4bd3f44c GTID 0-1-6 trans
/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
START TRANSACTION
/*!*/;
# at 1677
# at 1897
#240217 6:25:04 server id 1 end_log_pos 1928 CRC32 0xe3f0f898 Xid = 32545
COMMIT/*!*/;
# at 1928
#240217 6:25:04 server id 1 end_log_pos 1970 CRC32 0x1776179e GTID 0-1-7 trans
/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
START TRANSACTION
/*!*/;
# at 1970
# at 2190
#240217 6:25:04 server id 1 end_log_pos 2221 CRC32 0x03c83cfb Xid = 32546
COMMIT/*!*/;
# at 2221
#240217 6:25:04 server id 1 end_log_pos 2263 CRC32 0xe2c98549 GTID 0-1-8 trans
/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
START TRANSACTION
/*!*/;
# at 2263
# at 2483
#240217 6:25:04 server id 1 end_log_pos 2514 CRC32 0xae0fe253 Xid = 32547
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@teguhth incrementaldb]#
10. cat log backup [LOG]_teguhth_20240217_062537.sql
[root@teguhth teguhth]# cat [LOG]_teguhth_20240217_062537.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240217 6:24:12 server id 1 end_log_pos 256 CRC32 0x23389db7 Start: binlog v 4, server v 10.11.6-MariaDB-log created 240217 6:24:12
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
nO7PZQ8BAAAA/AAAAAABAAABAAQAMTAuMTEuNi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgG3nTgj
'/*!*/;
# at 256
#240217 6:24:12 server id 1 end_log_pos 285 CRC32 0x4a97a682 Gtid list []
# at 285
#240217 6:24:12 server id 1 end_log_pos 328 CRC32 0xfba94dd7 Binlog checkpoint mysql-bin.000002
# at 328
#240217 6:24:12 server id 1 end_log_pos 371 CRC32 0x0e5756cc Binlog checkpoint mysql-bin.000003
# at 371
#240217 6:24:51 server id 1 end_log_pos 413 CRC32 0x90b89c14 GTID 0-1-1 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
START TRANSACTION
/*!*/;
# at 413
#240217 6:24:51 server id 1 end_log_pos 445 CRC32 0x178eedce Intvar
SET INSERT_ID=7/*!*/;
# at 445
# at 566
#240217 6:24:51 server id 1 end_log_pos 597 CRC32 0x9a3fd16f Xid = 32536
COMMIT/*!*/;
# at 597
#240217 6:24:51 server id 1 end_log_pos 639 CRC32 0x5dbf715e GTID 0-1-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 639
#240217 6:24:51 server id 1 end_log_pos 671 CRC32 0x928c55b6 Intvar
SET INSERT_ID=8/*!*/;
# at 671
# at 792
#240217 6:24:51 server id 1 end_log_pos 823 CRC32 0xe6b043f0 Xid = 32537
COMMIT/*!*/;
# at 823
#240217 6:24:51 server id 1 end_log_pos 865 CRC32 0x14bf4c5e GTID 0-1-3 trans
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
START TRANSACTION
/*!*/;
# at 865
#240217 6:24:51 server id 1 end_log_pos 897 CRC32 0x6a383364 Intvar
SET INSERT_ID=9/*!*/;
# at 897
# at 1018
#240217 6:24:51 server id 1 end_log_pos 1049 CRC32 0x13b36f13 Xid = 32538
COMMIT/*!*/;
# at 1049
#240217 6:25:04 server id 1 end_log_pos 1091 CRC32 0xb78b6be9 GTID 0-1-4 trans
/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
START TRANSACTION
/*!*/;
# at 1091
#240217 6:25:04 server id 1 end_log_pos 1311 CRC32 0xe6b1a9f0 Query thread_id=5 exec_time=0 error_code=0 xid=0
# use `teguhth`/*!*/;
SET TIMESTAMP=1708125904/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET @@session.character_set_client=utf8mb3,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0001','2002-06-24',1)
/*!*/;
# at 1311
#240217 6:25:04 server id 1 end_log_pos 1342 CRC32 0xc38cec7c Xid = 32543
COMMIT/*!*/;
# at 1342
#240217 6:25:04 server id 1 end_log_pos 1384 CRC32 0x4884e5ae GTID 0-1-5 trans
/*!100001 SET @@session.gtid_seq_no=5*//*!*/;
START TRANSACTION
/*!*/;
# at 1384
#240217 6:25:04 server id 1 end_log_pos 1604 CRC32 0x6619567b Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','J-0002','2002-06-24',1)
/*!*/;
# at 1604
#240217 6:25:04 server id 1 end_log_pos 1635 CRC32 0xb25a7aa5 Xid = 32544
COMMIT/*!*/;
# at 1635
#240217 6:25:04 server id 1 end_log_pos 1677 CRC32 0x4bd3f44c GTID 0-1-6 trans
/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
START TRANSACTION
/*!*/;
# at 1677
#240217 6:25:04 server id 1 end_log_pos 1897 CRC32 0xb4eb6c37 Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0001','2002-06-25',2)
/*!*/;
# at 1897
#240217 6:25:04 server id 1 end_log_pos 1928 CRC32 0xe3f0f898 Xid = 32545
COMMIT/*!*/;
# at 1928
#240217 6:25:04 server id 1 end_log_pos 1970 CRC32 0x1776179e GTID 0-1-7 trans
/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
START TRANSACTION
/*!*/;
# at 1970
#240217 6:25:04 server id 1 end_log_pos 2190 CRC32 0x8035ca68 Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-02','B-0002','2002-06-25',2)
/*!*/;
# at 2190
#240217 6:25:04 server id 1 end_log_pos 2221 CRC32 0x03c83cfb Xid = 32546
COMMIT/*!*/;
# at 2221
#240217 6:25:04 server id 1 end_log_pos 2263 CRC32 0xe2c98549 GTID 0-1-8 trans
/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
START TRANSACTION
/*!*/;
# at 2263
#240217 6:25:04 server id 1 end_log_pos 2483 CRC32 0xbe6f50b5 Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0001','2002-06-20',5)
/*!*/;
# at 2483
#240217 6:25:04 server id 1 end_log_pos 2514 CRC32 0xae0fe253 Xid = 32547
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@teguhth teguhth]#
11. test restore log
https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
dbversion=$(mysql -ubackup_user -pbackup_pwd -sNe "select @@version as VersionDB");
echo ".::: Backup Full All database using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
#echo "Backup begin $infotgl"
echo "Backup begin $datex"
backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin using $LATEST_BINLOG"
echo ""
dbname=$(mysql -ubackup_user -pbackup_pwd -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
mkdir -p $backuppath/$year/$month/$day/$listdb;
path="$backuppath/$year/$month/$day/$listdb";
#echo "$datex --->>> Starting Backup Full for database '$listdb' in $path"
echo "$datey --->>> Starting Backup Full for database '$listdb' in $path"
# mysqldump -ubackup_user -pbackup_pwd -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --flush-logs --master-data=2 --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
mysqldump -ubackup_user -pbackup_pwd -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/[FULL]_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;
datez=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
#echo "$datex --->>> Finish Backup Full for database '$listdb' in $path"
echo "$datez --->>> Finish Backup Full for database '$listdb' in $path"
done;
mysqladmin -ubackup_user -pbackup_pwd flush-logs
echo ""
LATEST_BINLOG_AFTER=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin After flush $LATEST_BINLOG_AFTER"
echo ""
#ls -lh $path
datefinish=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N ");
echo "Backup finish $datefinish"
echo ""
echo "Delete Retention Start"
find $backuppath/* -type d -mtime +21 -exec ls -ld {} \;
#find $backuppath/* -type d -mmin +2 -exec ls -ld {} \;
find $backuppath/* -type d -mtime +21 -exec rm -rf {} \;
#echo ""
echo "Delete Retention Finish"
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth scriptbackup]#
3. create script backup log
[root@teguhth fix]# cat backup_log_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
dbversion=$(mysql -uroot -proot -sNe "select @@version as VersionDB");
#echo ".::: Backup Log database MariaDB / MySQL using custom in '$Server' with $ip_address & Version $dbversion :::."
echo ".::: Backup Transaction Log database MariaDB / MySQL using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
#echo "Backup begin $infotgl"
backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Current date and time
CURRENT_DATETIME=$(date +"%Y%m%d_%H%M%S")
# Calculate start datetime (1 hour ago)
START_DATETIME=$(date -d "1 hour ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "15 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "30 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "45 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "60 minutes ago" +"%Y-%m-%d %H:%M:%S")
# Stop datetime (current time)
STOP_DATETIME=$(date +"%Y-%m-%d %H:%M:%S")
#echo "Backup begin $infotgl for periode $START_DATETIME to $STOP_DATETIME"
#echo ""
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin using $LATEST_BINLOG"
echo ""
dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
# Output file path
OUTPUT_FILE="$path/[LOG]_${listdb}_${CURRENT_DATETIME}.sql.gz"
echo "$datex --->>> Backup Transaction Log for database '$listdb' in $path"
# Run mysqlbinlog command
mysqlbinlog --database=$listdb --start-datetime="$START_DATETIME" --stop-datetime="$STOP_DATETIME" "$LATEST_BINLOG" | sed '/^use / s/^/# /' | gzip -c > "$OUTPUT_FILE"
echo "$datex --->>> Finished Backup Transaction Log for database '$listdb' in $path"
done;
mysqladmin -uroot -proot flush-logs
echo ""
LATEST_BINLOG_AFTER=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin after flush using $LATEST_BINLOG_AFTER"
echo ""
#ls -lh $path
#echo ""
echo "Backup finish $infotgl"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth fix]#
4. test create table and insert in incrementaldb
CREATE DATABASE incrementaldb;
use incrementaldb
create table my_tbl(
my_id INT NOT NULL AUTO_INCREMENT,
my_field VARCHAR(100) NOT NULL,
submission_date DATE,
time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ( my_id )
);
use incrementaldb;
INSERT into my_tbl (my_field) VALUES ('val1');
INSERT into my_tbl (my_field) VALUES ('val2');
INSERT into my_tbl (my_field) VALUES ('val3');
show tables ;
select *,@@hostname from incrementaldb.my_tbl;
5. test create table and insert in teguhth
use teguhth;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0001','2002-06-24',1);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','J-0002','2002-06-24',1);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0001','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-02','B-0002','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0001','2002-06-20',5);
select * from pembelian;
6. run backup_log_script.sh
7. check folder backup
[root@teguhth 20240217]# pwd
/backup/20240217
[root@teguhth 20240217]# ls
clasicdb db dblink dbmaria edb incrementaldb secretdb srcdefdb sys teguhth testdb
[root@teguhth 20240217]# cd teguhth/
[root@teguhth teguhth]# ls
[FULL]_teguhth_20240217_062031.sql.gz [FULL]_teguhth_20240217_062206.sql.gz [FULL]_teguhth_20240217_062411.sql.gz
[FULL]_teguhth_20240217_062158.sql.gz [FULL]_teguhth_20240217_062407.sql.gz [LOG]_teguhth_20240217_062537.sql.gz
[root@teguhth teguhth]# cd ..
[root@teguhth 20240217]# cd incrementaldb/
[root@teguhth incrementaldb]# ls
[FULL]_incrementaldb_20240217_062031.sql.gz [FULL]_incrementaldb_20240217_062206.sql.gz [FULL]_incrementaldb_20240217_062411.sql.gz
[FULL]_incrementaldb_20240217_062158.sql.gz [FULL]_incrementaldb_20240217_062407.sql.gz [LOG]_incrementaldb_20240217_062537.sql.gz
[root@teguhth incrementaldb]#
8. gunzip [LOG]_incrementaldb_20240217_062537.sql.gz & [LOG]_teguhth_20240217_062537.sql.gz
[root@teguhth incrementaldb]# gunzip [LOG]_incrementaldb_20240217_062537.sql.gz
[root@teguhth incrementaldb]#
[root@teguhth teguhth]# gunzip [LOG]_teguhth_20240217_062537.sql.gz
9. cat log backup [LOG]_incrementaldb_20240217_062537.sql
[root@teguhth incrementaldb]# cat \[LOG\]_incrementaldb_20240217_062537.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240217 6:24:12 server id 1 end_log_pos 256 CRC32 0x23389db7 Start: binlog v 4, server v 10.11.6-MariaDB-log created 240217 6:24:12
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
nO7PZQ8BAAAA/AAAAAABAAABAAQAMTAuMTEuNi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgG3nTgj
'/*!*/;
# at 256
#240217 6:24:12 server id 1 end_log_pos 285 CRC32 0x4a97a682 Gtid list []
# at 285
#240217 6:24:12 server id 1 end_log_pos 328 CRC32 0xfba94dd7 Binlog checkpoint mysql-bin.000002
# at 328
#240217 6:24:12 server id 1 end_log_pos 371 CRC32 0x0e5756cc Binlog checkpoint mysql-bin.000003
# at 371
#240217 6:24:51 server id 1 end_log_pos 413 CRC32 0x90b89c14 GTID 0-1-1 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
START TRANSACTION
/*!*/;
# at 413
#240217 6:24:51 server id 1 end_log_pos 445 CRC32 0x178eedce Intvar
SET INSERT_ID=7/*!*/;
# at 445
#240217 6:24:51 server id 1 end_log_pos 566 CRC32 0x684d1799 Query thread_id=4 exec_time=0 error_code=0 xid=0
# use `incrementaldb`/*!*/;
SET TIMESTAMP=1708125891/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET @@session.character_set_client=utf8mb3,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
INSERT into my_tbl (my_field) VALUES ('val1')
/*!*/;
# at 566
#240217 6:24:51 server id 1 end_log_pos 597 CRC32 0x9a3fd16f Xid = 32536
COMMIT/*!*/;
# at 597
#240217 6:24:51 server id 1 end_log_pos 639 CRC32 0x5dbf715e GTID 0-1-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 639
#240217 6:24:51 server id 1 end_log_pos 671 CRC32 0x928c55b6 Intvar
SET INSERT_ID=8/*!*/;
# at 671
#240217 6:24:51 server id 1 end_log_pos 792 CRC32 0x6009e2ec Query thread_id=4 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125891/*!*/;
INSERT into my_tbl (my_field) VALUES ('val2')
/*!*/;
# at 792
#240217 6:24:51 server id 1 end_log_pos 823 CRC32 0xe6b043f0 Xid = 32537
COMMIT/*!*/;
# at 823
#240217 6:24:51 server id 1 end_log_pos 865 CRC32 0x14bf4c5e GTID 0-1-3 trans
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
START TRANSACTION
/*!*/;
# at 865
#240217 6:24:51 server id 1 end_log_pos 897 CRC32 0x6a383364 Intvar
SET INSERT_ID=9/*!*/;
# at 897
#240217 6:24:51 server id 1 end_log_pos 1018 CRC32 0x44984ce7 Query thread_id=4 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125891/*!*/;
INSERT into my_tbl (my_field) VALUES ('val3')
/*!*/;
# at 1018
#240217 6:24:51 server id 1 end_log_pos 1049 CRC32 0x13b36f13 Xid = 32538
COMMIT/*!*/;
# at 1049
#240217 6:25:04 server id 1 end_log_pos 1091 CRC32 0xb78b6be9 GTID 0-1-4 trans
/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
START TRANSACTION
/*!*/;
# at 1091
# at 1311
#240217 6:25:04 server id 1 end_log_pos 1342 CRC32 0xc38cec7c Xid = 32543
COMMIT/*!*/;
# at 1342
#240217 6:25:04 server id 1 end_log_pos 1384 CRC32 0x4884e5ae GTID 0-1-5 trans
/*!100001 SET @@session.gtid_seq_no=5*//*!*/;
START TRANSACTION
/*!*/;
# at 1384
# at 1604
#240217 6:25:04 server id 1 end_log_pos 1635 CRC32 0xb25a7aa5 Xid = 32544
COMMIT/*!*/;
# at 1635
#240217 6:25:04 server id 1 end_log_pos 1677 CRC32 0x4bd3f44c GTID 0-1-6 trans
/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
START TRANSACTION
/*!*/;
# at 1677
# at 1897
#240217 6:25:04 server id 1 end_log_pos 1928 CRC32 0xe3f0f898 Xid = 32545
COMMIT/*!*/;
# at 1928
#240217 6:25:04 server id 1 end_log_pos 1970 CRC32 0x1776179e GTID 0-1-7 trans
/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
START TRANSACTION
/*!*/;
# at 1970
# at 2190
#240217 6:25:04 server id 1 end_log_pos 2221 CRC32 0x03c83cfb Xid = 32546
COMMIT/*!*/;
# at 2221
#240217 6:25:04 server id 1 end_log_pos 2263 CRC32 0xe2c98549 GTID 0-1-8 trans
/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
START TRANSACTION
/*!*/;
# at 2263
# at 2483
#240217 6:25:04 server id 1 end_log_pos 2514 CRC32 0xae0fe253 Xid = 32547
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@teguhth incrementaldb]#
10. cat log backup [LOG]_teguhth_20240217_062537.sql
[root@teguhth teguhth]# cat [LOG]_teguhth_20240217_062537.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240217 6:24:12 server id 1 end_log_pos 256 CRC32 0x23389db7 Start: binlog v 4, server v 10.11.6-MariaDB-log created 240217 6:24:12
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
nO7PZQ8BAAAA/AAAAAABAAABAAQAMTAuMTEuNi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgG3nTgj
'/*!*/;
# at 256
#240217 6:24:12 server id 1 end_log_pos 285 CRC32 0x4a97a682 Gtid list []
# at 285
#240217 6:24:12 server id 1 end_log_pos 328 CRC32 0xfba94dd7 Binlog checkpoint mysql-bin.000002
# at 328
#240217 6:24:12 server id 1 end_log_pos 371 CRC32 0x0e5756cc Binlog checkpoint mysql-bin.000003
# at 371
#240217 6:24:51 server id 1 end_log_pos 413 CRC32 0x90b89c14 GTID 0-1-1 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
START TRANSACTION
/*!*/;
# at 413
#240217 6:24:51 server id 1 end_log_pos 445 CRC32 0x178eedce Intvar
SET INSERT_ID=7/*!*/;
# at 445
# at 566
#240217 6:24:51 server id 1 end_log_pos 597 CRC32 0x9a3fd16f Xid = 32536
COMMIT/*!*/;
# at 597
#240217 6:24:51 server id 1 end_log_pos 639 CRC32 0x5dbf715e GTID 0-1-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 639
#240217 6:24:51 server id 1 end_log_pos 671 CRC32 0x928c55b6 Intvar
SET INSERT_ID=8/*!*/;
# at 671
# at 792
#240217 6:24:51 server id 1 end_log_pos 823 CRC32 0xe6b043f0 Xid = 32537
COMMIT/*!*/;
# at 823
#240217 6:24:51 server id 1 end_log_pos 865 CRC32 0x14bf4c5e GTID 0-1-3 trans
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
START TRANSACTION
/*!*/;
# at 865
#240217 6:24:51 server id 1 end_log_pos 897 CRC32 0x6a383364 Intvar
SET INSERT_ID=9/*!*/;
# at 897
# at 1018
#240217 6:24:51 server id 1 end_log_pos 1049 CRC32 0x13b36f13 Xid = 32538
COMMIT/*!*/;
# at 1049
#240217 6:25:04 server id 1 end_log_pos 1091 CRC32 0xb78b6be9 GTID 0-1-4 trans
/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
START TRANSACTION
/*!*/;
# at 1091
#240217 6:25:04 server id 1 end_log_pos 1311 CRC32 0xe6b1a9f0 Query thread_id=5 exec_time=0 error_code=0 xid=0
# use `teguhth`/*!*/;
SET TIMESTAMP=1708125904/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET @@session.character_set_client=utf8mb3,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0001','2002-06-24',1)
/*!*/;
# at 1311
#240217 6:25:04 server id 1 end_log_pos 1342 CRC32 0xc38cec7c Xid = 32543
COMMIT/*!*/;
# at 1342
#240217 6:25:04 server id 1 end_log_pos 1384 CRC32 0x4884e5ae GTID 0-1-5 trans
/*!100001 SET @@session.gtid_seq_no=5*//*!*/;
START TRANSACTION
/*!*/;
# at 1384
#240217 6:25:04 server id 1 end_log_pos 1604 CRC32 0x6619567b Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','J-0002','2002-06-24',1)
/*!*/;
# at 1604
#240217 6:25:04 server id 1 end_log_pos 1635 CRC32 0xb25a7aa5 Xid = 32544
COMMIT/*!*/;
# at 1635
#240217 6:25:04 server id 1 end_log_pos 1677 CRC32 0x4bd3f44c GTID 0-1-6 trans
/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
START TRANSACTION
/*!*/;
# at 1677
#240217 6:25:04 server id 1 end_log_pos 1897 CRC32 0xb4eb6c37 Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0001','2002-06-25',2)
/*!*/;
# at 1897
#240217 6:25:04 server id 1 end_log_pos 1928 CRC32 0xe3f0f898 Xid = 32545
COMMIT/*!*/;
# at 1928
#240217 6:25:04 server id 1 end_log_pos 1970 CRC32 0x1776179e GTID 0-1-7 trans
/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
START TRANSACTION
/*!*/;
# at 1970
#240217 6:25:04 server id 1 end_log_pos 2190 CRC32 0x8035ca68 Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-02','B-0002','2002-06-25',2)
/*!*/;
# at 2190
#240217 6:25:04 server id 1 end_log_pos 2221 CRC32 0x03c83cfb Xid = 32546
COMMIT/*!*/;
# at 2221
#240217 6:25:04 server id 1 end_log_pos 2263 CRC32 0xe2c98549 GTID 0-1-8 trans
/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
START TRANSACTION
/*!*/;
# at 2263
#240217 6:25:04 server id 1 end_log_pos 2483 CRC32 0xbe6f50b5 Query thread_id=5 exec_time=0 error_code=0 xid=0
SET TIMESTAMP=1708125904/*!*/;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0001','2002-06-20',5)
/*!*/;
# at 2483
#240217 6:25:04 server id 1 end_log_pos 2514 CRC32 0xae0fe253 Xid = 32547
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@teguhth teguhth]#
11. test restore log
https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
No comments:
Post a Comment