Description:
Using the instanceDump utility to dump users only is easily accomplished with the --includeSchemas parameter. One can specify a non-existent schema, or a space: --includeSchemas=" ", but an empty string: --includeSchemas="", dumps all databases.
$ mysqlsh --version
mysqlsh Ver 8.0.29 for Linux on x86_64 - for MySQL 8.0.29 (MySQL Community Server (GPL))
I ran a dedeployer loop on a couple versions of Percona Server with an empty string for includeSchemas (no space):
DBDBASE=/opt/percona_server/;
SANDBOX=test_instDump
while read h; do
echo -e "version is: $h\n";
dbdeployer deploy single ${DBDBASE}$h --port=22238 --sandbox-directory=${SANDBOX}.$h --skip-library-check --force
mkdir /home/dov.endress/sandboxes/backup/$h
mysql --port=22238 --socket=/tmp/mysql_sandbox22238.sock -umsandbox -pmsandbox -e 'CREATE DATABASE test_instanceDump';
mysqlsh --socket=/tmp/mysql_sandbox22238.sock --user=msandbox -pmsandbox --host=localhost -- util dumpInstance /home/dov.endress/sandboxes/backup/$h --threads=1 --users=true --consistent=false --includeSchemas="" 2>&1 | tee -a /home/dov.endress/sandboxes/backup/$h.instDump.log
ls -larth /home/dov.endress/sandboxes/${SANDBOX}.$h/data/ >> /home/dov.endress/sandboxes/backup/$h/$h.instDump.log
ls -larth /home/dov.endress/sandboxes/backup/$h/ >> /home/dov.endress/sandboxes/backup/$h/$h.instDump.log
dbdeployer delete ${SANDBOX}.$h
done < ps.versions
Both instances dumped all schemata available to mysqlsh dumpInstance:
version is: 5.7.35
Database installed in $HOME/sandboxes/test_instDump.5.7.35
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started
...
Total duration: 00:00:00s
Schemas dumped: 2
Tables dumped: 0
...
version is: 8.0.36
Database installed in $HOME/sandboxes/test_instDump.8.0.36
run 'dbdeployer usage single' for basic instructions'
... sandbox server started
2 out of 6 schemas will be dumped and within them 0 tables, 0 views.
13 out of 16 users will be dumped.
And the subsequent files generated:
$ ls ~/sandboxes/backup/5.7.35
5.7.35.instDump.log @.done.json @.json @.post.sql @.sql test_instanceDump.json test_instanceDump.sql test.json test.sql @.users.sql
$ ls ~/sandboxes/backup/8.0.36
8.0.36.instDump.log @.done.json @.json @.post.sql @.sql test_instanceDump.json test_instanceDump.sql test.json test.sql @.users.sql
The same test but with includeSchemas=" " (with space) behaves as expected:
version is: 5.7.35
Database installed in $HOME/sandboxes/test_instDump.5.7.35
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started
...
Total duration: 00:00:00s
Schemas dumped: 0
Tables dumped: 0
...
version is: 8.0.36
Database installed in $HOME/sandboxes/test_instDump.8.0.36
run 'dbdeployer usage single' for basic instructions'
... sandbox server started
...
Gathering information...
0 out of 6 schemas will be dumped and within them 0 tables, 0 views.
13 out of 16 users will be dumped.
The same test was performed using MySQL Community 5.7.36 and 8.0.32 with the same results as expected.
How to repeat:
Start an instance 5.7 or greater and create a new database (instanceDump will not dump information_schema, mysql, ndbinfo, performance_schema, or sys by design).
Execute instanceDump with includeSchemas=""
mysqlsh --user=* -p* --host=* -- util dumpInstance --threads=1 --users=true --consistent=false --includeSchemas=""
Newly created schema will be noted as dumped and dump file created.
Suggested fix:
I leave it up to you as to whether this should be a feature request: make "" behave the same as " " or alternatively add userDropOnly option to mimic functionality and error on includeSchemas=" " (or non-existent schema), an actual 'bug' that should be fixed, or a documentation update to make the behavior clearly known.
Given that other tools, i.e. mysql cli and mysqldump will error on a non-existent schema, I would lean toward adding the option to dump user info only and have mysqlsh error on includeSchemas=" " or includeSchemas="<schema_that_does_not_exist>" for consistency sake alone.
While a minor and somewhat edge case, this behavior could be devastating to the user who unwisely writes a backup to the same volume as their data directory.
Cheers!
Dov Endress
Description: Using the instanceDump utility to dump users only is easily accomplished with the --includeSchemas parameter. One can specify a non-existent schema, or a space: --includeSchemas=" ", but an empty string: --includeSchemas="", dumps all databases. $ mysqlsh --version mysqlsh Ver 8.0.29 for Linux on x86_64 - for MySQL 8.0.29 (MySQL Community Server (GPL)) I ran a dedeployer loop on a couple versions of Percona Server with an empty string for includeSchemas (no space): DBDBASE=/opt/percona_server/; SANDBOX=test_instDump while read h; do echo -e "version is: $h\n"; dbdeployer deploy single ${DBDBASE}$h --port=22238 --sandbox-directory=${SANDBOX}.$h --skip-library-check --force mkdir /home/dov.endress/sandboxes/backup/$h mysql --port=22238 --socket=/tmp/mysql_sandbox22238.sock -umsandbox -pmsandbox -e 'CREATE DATABASE test_instanceDump'; mysqlsh --socket=/tmp/mysql_sandbox22238.sock --user=msandbox -pmsandbox --host=localhost -- util dumpInstance /home/dov.endress/sandboxes/backup/$h --threads=1 --users=true --consistent=false --includeSchemas="" 2>&1 | tee -a /home/dov.endress/sandboxes/backup/$h.instDump.log ls -larth /home/dov.endress/sandboxes/${SANDBOX}.$h/data/ >> /home/dov.endress/sandboxes/backup/$h/$h.instDump.log ls -larth /home/dov.endress/sandboxes/backup/$h/ >> /home/dov.endress/sandboxes/backup/$h/$h.instDump.log dbdeployer delete ${SANDBOX}.$h done < ps.versions Both instances dumped all schemata available to mysqlsh dumpInstance: version is: 5.7.35 Database installed in $HOME/sandboxes/test_instDump.5.7.35 run 'dbdeployer usage single' for basic instructions' .. sandbox server started ... Total duration: 00:00:00s Schemas dumped: 2 Tables dumped: 0 ... version is: 8.0.36 Database installed in $HOME/sandboxes/test_instDump.8.0.36 run 'dbdeployer usage single' for basic instructions' ... sandbox server started 2 out of 6 schemas will be dumped and within them 0 tables, 0 views. 13 out of 16 users will be dumped. And the subsequent files generated: $ ls ~/sandboxes/backup/5.7.35 5.7.35.instDump.log @.done.json @.json @.post.sql @.sql test_instanceDump.json test_instanceDump.sql test.json test.sql @.users.sql $ ls ~/sandboxes/backup/8.0.36 8.0.36.instDump.log @.done.json @.json @.post.sql @.sql test_instanceDump.json test_instanceDump.sql test.json test.sql @.users.sql The same test but with includeSchemas=" " (with space) behaves as expected: version is: 5.7.35 Database installed in $HOME/sandboxes/test_instDump.5.7.35 run 'dbdeployer usage single' for basic instructions' .. sandbox server started ... Total duration: 00:00:00s Schemas dumped: 0 Tables dumped: 0 ... version is: 8.0.36 Database installed in $HOME/sandboxes/test_instDump.8.0.36 run 'dbdeployer usage single' for basic instructions' ... sandbox server started ... Gathering information... 0 out of 6 schemas will be dumped and within them 0 tables, 0 views. 13 out of 16 users will be dumped. The same test was performed using MySQL Community 5.7.36 and 8.0.32 with the same results as expected. How to repeat: Start an instance 5.7 or greater and create a new database (instanceDump will not dump information_schema, mysql, ndbinfo, performance_schema, or sys by design). Execute instanceDump with includeSchemas="" mysqlsh --user=* -p* --host=* -- util dumpInstance --threads=1 --users=true --consistent=false --includeSchemas="" Newly created schema will be noted as dumped and dump file created. Suggested fix: I leave it up to you as to whether this should be a feature request: make "" behave the same as " " or alternatively add userDropOnly option to mimic functionality and error on includeSchemas=" " (or non-existent schema), an actual 'bug' that should be fixed, or a documentation update to make the behavior clearly known. Given that other tools, i.e. mysql cli and mysqldump will error on a non-existent schema, I would lean toward adding the option to dump user info only and have mysqlsh error on includeSchemas=" " or includeSchemas="<schema_that_does_not_exist>" for consistency sake alone. While a minor and somewhat edge case, this behavior could be devastating to the user who unwisely writes a backup to the same volume as their data directory. Cheers! Dov Endress