vitess.io/vitess@v0.16.2/support/mysqldump/mysqldump.sh (about)

     1  #!/bin/bash
     2  
     3  # Define your variables
     4  SOURCE_USER=root
     5  SOURCE_HOST=localhost
     6  SOURCE_PASSWORD=password
     7  SOURCE_DATABASE=source_dbname
     8  
     9  TARGET_USER=vtgate-test-user
    10  TARGET_HOST=vtgate-host
    11  TARGET_PASSWORD=vtgate-password
    12  TARGET_DATABASE=destination_keyspace
    13  
    14  SECURE='--ssl-mode=PREFER'
    15  
    16  # Uncomment if you have a ca certificate.
    17  # SECURE='--ssl-ca=./vtgate-ca-cert.pem --ssl-mode=VERIFY_CA'
    18  
    19  # Dump and restore schema only
    20  mysqldump -d $SOURCE_DATABASE --user=$SOURCE_USER --host=$SOURCE_HOST --password=$SOURCE_PASSWORD \
    21  --single-transaction --verbose | \
    22  sed 's/^\(\(LOCK\|UNLOCK\|CHANGE\|SET\).*\)$/-- m \1/ ; /.*CONSTRAINT.*/d ; s/^\(\/\*![0-9]\{5\}.*\(TIME_ZONE\|SQL_NOTES\|CHARACTER_SET\).*\/;\)$/-- m \1/I ; $!N;s/^\(\s*[^C].*\),\(\n\s*CONSTRAINT.*\)$/\1\2/;P;D' > ./schema.sql
    23  mysql --user=$TARGET_USER --host=$TARGET_HOST --password=$TARGET_PASSWORD $TARGET_DATABASE $SECURE < ./schema.sql
    24  
    25  # Dump and Restore Data Only
    26  mysqldump $SOURCE_DATABASE --user=$SOURCE_USER --host=$SOURCE_HOST --password=$SOURCE_PASSWORD \
    27  --no-create-info --triggers --routines --events --hex-blob --quick --order-by-primary \
    28  --no-autocommit --skip-comments --quote-names --no-tablespaces --skip-add-locks \
    29  --skip-disable-keys --single-transaction --set-gtid-purged=off --verbose --lock-tables=off | \
    30  sed 's/^\(\(LOCK\|UNLOCK\|CHANGE\|SET\).*\)$/-- m \1/ ; s/^\(\/\*![0-9]\{5\}.*\(TIME_ZONE\|SQL_NOTES\|CHARACTER_SET\).*\/;\)$/-- m \1/I ; s/'\''0000-00-00 00:00:00'\''/CURRENT_TIMESTAMP/g' > ./data.sql
    31  mysql --user=$TARGET_USER --host=$TARGET_HOST --password=$TARGET_PASSWORD $TARGET_DATABASE $SECURE < ./data.sql