A way to save database on different files

[babaz8]

I don't see a way there is only one unique dump file with all the databases. Thanks

[deitch]

Can you be more specific? What flow exactly are you trying to accomplish?

[babaz8]

Can you be more specific? What flow exactly are you trying to accomplish?

I would like to habe for each database inside the mysql instance a dedicated. . Sql file

Right now i see a.. Sql with all DBs inside

[deitch]

Have you tried DB_DUMP_BY_SCHEMA?

[babaz8]

I thought that was the solution but it gave me error with some lock permission and the backup fails.

On Mon, 1 Mar 2021, 20:06 Avi Deitcher, notifications@github.com wrote:

Have you tried DB_DUMP_BY_SCHEMA?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/databacker/mysql-backup/issues/157#issuecomment-788194458, or unsubscribe https://github.com/notifications/unsubscribe-auth/AKS3MUOS7J2PI2TZ5NPO4HTTBPQTLANCNFSM4YLACEZQ .

[deitch]

Run it with debug enabled and put the output here? It sounds like you have a way to do it, but the way is not working.

Be sure to check for any sensitive data in the debug output before posting.

[idonda]

I think what he is trying to say is that when you enable DB_DUMP_BY_SCHEMA you get: mysqldump: Got error: 1044: "Access denied for user 'root'@'%' to database 'information_schema'" when using LOCK TABLES

The solution to this would be to add the option --single-transaction to mysqldump like so (no quotes needed):

• MYSQLDUMP_OPTS=--single-transaction

[dandarie]

MYSQLDUMP_OPTS=--single-transaction

Tried that, it fails after:

mysqldump: Got error: 0: "" when trying to save the result of SHOW CREATE TABLE in ds_view.

[deitch]

And with debug enabled?

[vedmaka]

Also getting mysqldump: Got error: 0: "" when trying to save the result of SHOW CREATE TABLE in ds_view. with DB_DUMP_BY_SCHEMA=true and MYSQLDUMP_OPTS=--single-transaction --skip-lock-tables

++ mysql -h db -P tcp://xxxx:3306 -uroot -pxxxx -N -e 'show databases'
+ DB_NAMES='information_schema
mysql
performance_schema
sys
db_de
dbi_en'
+ '[' 0 -ne 0 ']'
+ for onedb in $DB_NAMES
+ '[' true = true ']'
+ NICE_CMD='nice -n19 ionice -c2'
+ nice -n19 ionice -c2 mysqldump -h db -P tcp://xxxx:3306 -uroot -pxxxx --databases information_schema --single-transaction --skip-lock-tables
mysqldump: Got error: 0: "" when trying to save the result of SHOW CREATE TABLE in ds_view.

not sure, but this seems related https://bugs.mysql.com/bug.php?id=79496

[deitch]

Is there a view with a " in the name, per that possibly related bug?

The command itself looks just fine.

[deitch]

I actually thought long and hard about going away from mysqldump entirely, and implementing it natively using mysql library (probably in go, not definitely). It is a lot of work, and simply not worth the time.

[fkollmann]

Same issue here... Any solution, yet?