qSx0LFaR

1#!/usr/bin/env bash
2## dump_mariadb_split_long.sh
3## Dump a MariaDB / MySQL to two files, a database/table definition file and a table values data file.
4## By: Ctrl-S
5## Created: 2020-10-03
6
7
8## == Config vars ==
9## Set these to what is appropriate for you.
10db_host="localhost"
11db_username="root"
12db_password="mypassword" # MariaDB password
13db_name="mydb"
14output_dir="${HOME}/db_dumps/" # Must have trailing slash, must be a dir, must exist.
15logfile="dbdump_log.txt"
16
17## == Dynamically generated vars ==
18## You probably don't need to touch these.
19timestamp="`date -u +%s`" ## Use a single timestamp for all of the dump tasks
20dump_name="${db_name}.ts${timestamp}" # Some sensible name for the dump.
21## dump_name="${db_name}" # Uncomment for static filename.
22defs_filepath="${output_dir}${dump_name}.defs.sql" # Filepath to store table definitions to.
23data_filepath="${output_dir}${dump_name}.data.sql" # Filepath to store actual data to.
24
25
26echo "This DB dump is named: ${dump_name}" | tee -a "${logfile}"
27
28
29## == Dump defs ==
30## Dump the DB and table definitions:
31echo "Now dumping defs..." | tee -a "${logfile}"
32defs_args=( # Use array for better commenting of args.
33	-h"${db_host}" -u"${db_username}" -p"${db_password}"
34	--tz-utc # Convert to UTC for export so that timstamps are preserved across timezones.
35	--quick # Fetch and dump one row at a time sequentially, for large databses.
36	--opt 
37	--single-transaction  # Use a transaction for consistent DB state in dump. (Needs InnoDB to do much.)
38	--no-data # Do not store any rows.
39	--skip-lock-tables # Prevent locking tables during dump process. (To prevent breaking asagi)
40	--result-file="${defs_filepath}" # Direct output to a given file.
41	"${db_name}"
42	)
43echo "defs_args=${defs_args[@]}" | tee -a "${logfile}" # Store args to file.
44mysqldump "${defs_args[@]}"
45## $mysqldump -h"${db_host}" -u"${db_username}" -p"${db_password}" --tz-utc --quick --opt --single-transaction --no-data --skip-lock-tables --result-file="${defs_filepath}" "${db_name}"
46echo "Done dumping defs" | tee -a "${logfile}"
47
48
49## == Dump data ==
50## Dump the table data:
51echo "Now dumping data..." | tee -a "${logfile}"
52data_args=( # Use array for better commenting of args
53	-h"${db_host}" -u"${db_username}" -p"${db_password}"
54	--tz-utc # Convert to UTC for export so that timstamps are preserved across timezones.
55	--quick # Fetch and dump one row at a time sequentially, for large databses.
56	--opt 
57	--single-transaction # Use a transaction for consistent DB state in dump. (Needs InnoDB to do much.)
58	--no-create-db # This option suppresses the CREATE DATABASE ... IF EXISTS statement...
59	--no-create-info # Do not write CREATE TABLE statements which re-create each dumped table.
60	--skip-lock-tables # Prevent locking tables during dump process. (To prevent breaking asagi)
61	--result-file="${data_filepath}" # Direct output to a given file.
62	"${db_name}"
63	)
64echo "data_args=${defs_args[@]}" | tee -a "${logfile}" # Store args to file.
65mysqldump "${data_args[@]}"
66##$ mysqldump -h"${db_host}" -u"${db_username}" -p"${db_password}" --tz-utc --quick --opt --single-transaction --no-create-db --no-create-info --skip-lock-tables --result-file="${data_filepath}" "${db_name}" 
67echo "Done dumping data" | tee -a "${logfile}"
68
69
70# Show what we did, so user knows files are not empty.
71echo "Files produced:" | tee -a "${logfile}"
72ls -lah "${defs_filepath}" | tee -a "${logfile}"
73ls -lah "${data_filepath}" | tee -a "${logfile}"
74
75
76echo "Finished dumping DB" | tee -a "${logfile}"
77
78
79## == Verbose notes==
80## dump_mariadb_split_long.sh
81## Dump a MariaDB / MySQL to two files, a database/table definition file and a table values data file.
82## By: Ctrl-S
83## Created: 2020-10-03
84## Modified: 2020-10-03
85##
86##
87## I believe that MariaDB and MySQL command should behave about the same.
88## LINK: https://mariadb.com/kb/en/mysqldump/
89## LINK: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
90## A seconds-since-epoch name prevents accidental clobbering, and makes it trivial to tell when the dump was produced.
91##
92##
93## To import a dump produced by this script: 
94## !!! UNTESTED !!!
95##  ( May be destructive if you have a DB with the same name! Use caution. )
96## 1. $ dumpname.defs.sql > mysql -u"USERNAME"
97## 2. $ dumpname.data.sql > mysql -u"USERNAME"
98##
99## Basically the table definitions must be imported before the table values data can be imported.
100##
101##
102##
103##
104## According to POSIX: "foo//bar" means the same as "foo/bar"
105## LINK: https://stackoverflow.com/questions/11226322/how-to-concatenate-two-strings-to-build-a-complete-path#24026057
106## LINK: https://en.wikibooks.org/wiki/Bourne_Shell_Scripting/Variable_Expansion
107##
108##
109##