.::: Backup MariaDB mysqldump mariadb-dump using --ignore-table-data, --ignore-table Some Table :::.

  

1. sample create table dummy sample typ*

CREATE TABLE typa (text VARCHAR(100));
CREATE TABLE typb (text VARCHAR(100));
CREATE TABLE typc (text VARCHAR(100));
CREATE TABLE typd (text VARCHAR(100));
CREATE TABLE type (text VARCHAR(100));

INSERT INTO typa (text) VALUES ('typa sample typa');
INSERT INTO typb (text) VALUES ('typb sample typb');
INSERT INTO typc (text) VALUES ('typc sample typc');
INSERT INTO typd (text) VALUES ('typd sample typd');
INSERT INTO type (text) VALUES ('type sample type');
 
2. sample create table dummy sample txp*

CREATE TABLE txpa (text VARCHAR(100));
CREATE TABLE txpb (text VARCHAR(100));
CREATE TABLE txpc (text VARCHAR(100));
CREATE TABLE txpd (text VARCHAR(100));
CREATE TABLE txpe (text VARCHAR(100));

INSERT INTO txpa (text) VALUES ('txpa sample txpa');
INSERT INTO txpb (text) VALUES ('txpb sample txpb');
INSERT INTO txpc (text) VALUES ('txpc sample txpc');
INSERT INTO txpd (text) VALUES ('txpd sample txpd');
INSERT INTO txpe (text) VALUES ('txpe sample txpe');

3. Generate daftar --ignore-table-data

mysql -u root -pxxx -N -e "
SELECT CONCAT('--ignore-table-data=', TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='teguhth'
  AND (TABLE_NAME LIKE 'typ%' OR TABLE_NAME LIKE 'txp%');"

Contoh output:

+----------------------------------+
| --ignore-table-data=teguhth.typa |
| --ignore-table-data=teguhth.typb |
| --ignore-table-data=teguhth.typc |
| --ignore-table-data=teguhth.typd |
| --ignore-table-data=teguhth.type |
| --ignore-table-data=teguhth.txpa |
| --ignore-table-data=teguhth.txpb |
| --ignore-table-data=teguhth.txpc |
| --ignore-table-data=teguhth.txpd |
| --ignore-table-data=teguhth.txpe |
+----------------------------------+
[root@teguhth10x manyexclude]#

4. mysqlump using many table exclude --ignore-table

mysqldump -u root -pxxx $(mysql -u root -pxxx -N -e "
SELECT CONCAT('--ignore-table=', TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='teguhth' AND (TABLE_NAME LIKE 'typ%' OR TABLE_NAME LIKE 'txp%');") "teguhth" > "teguhth.sql"

mysqldump -u root -pxxx $(mysql -u root -pxxx -N -e "
SELECT CONCAT('--ignore-table=', TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='teguhth' AND (TABLE_NAME LIKE 'typ%' OR TABLE_NAME LIKE 'txp%');") "teguhth" | gzip -c > "teguhth.sql.gz"

5. mysqlump using many table exclude ignore-table-data

mysqldump -u root -pxxx $(mysql -u root -pxxx -N -e "
SELECT CONCAT('--ignore-table-data=', TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='teguhth' AND (TABLE_NAME LIKE 'typ%' OR TABLE_NAME LIKE 'txp%');") "teguhth" > "teguhth.sql"

mysqldump -u root -pxxx $(mysql -u root -pxxx -N -e "
SELECT CONCAT('--ignore-table-data=', TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='teguhth' AND (TABLE_NAME LIKE 'typ%' OR TABLE_NAME LIKE 'txp%');") "teguhth" | gzip -c > "teguhth.sql.gz"

6. mysqlump using many table exclude ignore-table-data

mysqldump -uroot -pxxx \
-CfQq \
--max-allowed-packet=1G \
--hex-blob \
--order-by-primary \
--single-transaction \
--routines=true \
--triggers=true \
--no-data=false \
--ignore-table-data=teguhth.log_sap \
--ignore-table-data=teguhth.maria_audit \
$(mysql -uroot -pxxx -N -e "
SELECT CONCAT('--ignore-table-data=', TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='teguhth'
  AND (
       TABLE_NAME LIKE 'typ%'
       OR TABLE_NAME LIKE 'txp%'
  );
") \
"teguhth" \
| gzip -c > teguhth.sql.gz