.::: Backup & restore Single Table, Database & All Database MariaDB, MySQL include single-transaction & skip-lock-tables include using Date Time file Name :::.

 database/table non encripted can restore to database encripted

but database/table encripted cannot restore to database non encripted
A. Backup Database
1. list database
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| dbatools           |
| information_schema |
| mysql              |
| performance_schema |
| teguhth            |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>
 2.  Backup database full a database (example teguhth )
mysqldump -u root -p teguhth > db_teguhth.sql ;
mysqldump -u root -p teguhth  --single-transaction --skip-lock-tables > db_teguhth.sql ;

 
3. backup all database
mysqldump -u root -p --all-databases > alldb_teguh.sql ;
mysqldump -u root -p --all-databases --skip-lock-tables  > alldb_teguhskip.sql ;

mysqldump -u root -p --all-databases --single-transaction --skip-lock-tables  > alldb_teguhskip.sql ;

4. backup table
mysqldump -u root -p teguhth barang > table_barang.sql ;

[root@teguhth data]# mysqldump -u root -proot teguhth > db_teguhth.sql;
[root@teguhth data]# mysqldump -u root -proot --all-databases > alldb_teguh.sql;
[root@teguhth data]# mysqldump -u root -proot --all-databases --skip-lock-tables  > alldb_teguhskip.sql;
[root@teguhth data]# mysqldump -u root -proot teguhth barang > table_barang.sql;
[root@teguhth data]# ls
alldb_teguhskip.sql  alldb_teguh.sql  db_teguhth.sql  master  my2Collector-master  select.sql  table_barang.sql  tool
[root@teguhth data]# pwd
/data
[root@teguhth data]#

 

5. backup hanya schema
mysqldump -u root -p --no-data teguhth > teguhth_schema.sql

6. backup hanya schema pertable
mysqldump -u root -p --no-data teguhth barang customer > teguhth_schema_pertable.sql

7. backup hanya schema exclude table
mysqldump -u root -p --no-data teguhth --ignore-table=teguhth.pembelian > teguhth_schema_exclude_table.sql

8. backup hanya data saja
Perintah Backup Data Saja:
mysqldump -u root -p --no-create-info teguhth > teguhth_data.sql

9. table+data (tanpa function, procedure, trigger, view )

mysqldump -u root -p -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --no-create-db --no-create-info --single-transaction --events=false --routines=false --triggers=false --no-data=false TeguhDB | gzip -c > teguhthdb_withouttrigger.sql.gz;

Penjelasan opsi:

--no-create-db: Tidak menyertakan perintah CREATE DATABASE.
--no-create-info: Hanya backup data tanpa struktur tabel (hanya isi datanya).
--skip-triggers: Tidak menyertakan trigger dalam backup.
--routines=false: Tidak menyertakan fungsi dan prosedur yang ada.
--events=false: Tidak menyertakan event scheduler.

B. Restore Database

1. Create other database( example restore_db ) in one server 

create database restore_db;
show databases;

2. Restore database example restore db teguth

mysql -u root -p restore_db < db_teguhth.sql

or

mysql -u root -p restore_db < alldb_teguh.sql

or
mysql -u root -p restore_db < alldb_teguhskip.sql

or

mysql -u root -p restore_db < table_barang.sql

[root@teguhth data]# mysql -u root -proot restore_db < db_teguhth.sql
[root@teguhth data]# mysql -u root -proot restore_db < alldb_teguh.sql
[root@teguhth data]# mysql -u root -proot restore_db < alldb_teguhskip.sql
[root@teguhth data]# mysql -u root -proot restore_db < table_barang.sql
[root@teguhth data]#


3. check database

show databases;
use restore_db;
select * from barang;

MariaDB [(none)]> use restore_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [restore_db]> select * from barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01      | RICE COOKER | BUAH          |          20 |
| ELK-02      | LEMARI ES   | UNIT          |           8 |
| ELK-03      | TELEVISI    | UNIT          |          30 |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 |
| ELK-05      | KOMPUTER    | UNIT          |          28 |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.00 sec)

MariaDB [restore_db]> 

 

4. using date output

 

mysqldump -uroot -proot teguhth > teguhth_$(date +%F).sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d").sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S").sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S_%N").sql;
 

 

[root@teguhth tmp]# ls
[root@teguhth tmp]#
[root@teguhth tmp]#
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +%F).sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d").sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S").sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S_%N").sql;
[root@teguhth tmp]#
[root@teguhth tmp]# ls
teguhth_20230913_150623_142015568.sql  teguhth_20230913_150623.sql  teguhth_2023-09-13.sql  teguhth_20230913.sql
[root@teguhth tmp]#
 

 

other version

 

backup
sample DB Source = TeguhDB
file  DB  = teguhthdb.sql.gz
Sample DB Destination = TeguhDB02

mysql -uroot -p  -e "set global max_statement_time=0;"  
mysqldump -uroot -p -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false TeguhDB | gzip -c > teguhthdb.sql.gz
mysql -uroot -p -e "set global max_statement_time=60;"


restore
mysql -uroot -p -e "set global max_statement_time = 0;"
gunzip -c < teguhthdb.sql.gz  | mysql -uroot -p -f TeguhDB02
mysql -uroot -p -e "set global max_statement_time = 60;"
 
5. Backup all without default database

echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

infotgl=$(date)

echo ".::: Backup all database using custom in $Server 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`;
echo ""
echo "Backup begin $infotgl"

backuppath="/backup";
mkdir -p $backuppath/$year-$month-$day;
path="$backuppath/$year-$month-$day";

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
        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"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
done;
ls -lh $path
echo ""
echo "Backup finish $infotgl"
echo ""

echo ".::: Delete backup retention :::."
#
echo "Delete begin $infotgl"
backuppath="/backup";

find $path/* -type d -mtime +30 -exec rm -rf {} \;

echo "Delete finish $infotgl"
echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""

 

other fix script 

[root@teguhth backup]# cat backuptest.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

infotgl=$(date)

echo ".::: Backup all database using custom in $Server 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`;
echo "Backup begin $infotgl"

backuppath="/backup";
mkdir -p $backuppath/$year-$month-$day;
path="$backuppath/$year-$month-$day";

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
        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"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
done;
echo ""
ls -lh $path
echo ""
echo "Backup finish $infotgl"
echo ""
echo ".::: Delete backup retention :::."
echo ""
echo "Delete begin $infotgl"
backuppath="/backup";

find $path/* -type d -mtime +30 -exec rm -rf {} \;
echo ""
ls -lh $backuppath
echo ""
echo "Delete finish $infotgl"
echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth backup]#