MySQL restore error on an "all" database dump #301

New Issue

Closed

opened 2020-04-22 16:52:27 +00:00 by witten · 2 comments

witten commented 2020-04-22 16:52:27 +00:00

Owner

Copy Link

What I'm trying to do and why

Dump all MySQL databases and then restore them with borgmatic, so I can backup all databases on a host (without specifying a particular named database), and then restore them all. Say, in the case of host failure.

Steps to reproduce (if a bug)

Run a borgmatic restore of a borgmatic MySQL 8 all database dump.

Actual behavior (if a bug)

Restoring errors with:

ERROR 3552 (HY000) at line 24: Access to system schema 'mysql' is rejected.

Expected behavior (if a bug)

All databases in the dump restore without error.

Other notes / implementation ideas

• https://bugs.mysql.com/bug.php?id=92675
• https://stackoverflow.com/questions/53103876/errror-3554-while-importing-a-sql-file
• https://dba.stackexchange.com/questions/35081/any-option-for-mysqldump-to-ignore-databases-for-backup

The best bet for a work-around may be to stop using --all-databases during the dump, and instead dump each database on the system that isn't mysql.

Thanks to Immanuel for reporting this!

Environment

borgmatic version: 1.5.1

borgmatic installation method: pip install -e

Borg version: 1.1.9

Python version: 3.7.3

Database version (if applicable): MySQL 8.0.19

operating system and version: Manjaro stable

#### What I'm trying to do and why Dump `all` MySQL databases and then restore them with borgmatic, so I can backup all databases on a host (without specifying a particular named database), and then restore them all. Say, in the case of host failure. #### Steps to reproduce (if a bug) Run a `borgmatic restore` of a borgmatic MySQL 8 `all` database dump. #### Actual behavior (if a bug) Restoring errors with: ``` ERROR 3552 (HY000) at line 24: Access to system schema 'mysql' is rejected. ``` #### Expected behavior (if a bug) All databases in the dump restore without error. #### Other notes / implementation ideas * https://bugs.mysql.com/bug.php?id=92675 * https://stackoverflow.com/questions/53103876/errror-3554-while-importing-a-sql-file * https://dba.stackexchange.com/questions/35081/any-option-for-mysqldump-to-ignore-databases-for-backup The best bet for a work-around may be to stop using `--all-databases` during the dump, and instead dump each database on the system that *isn't* `mysql`. Thanks to Immanuel for reporting this! #### Environment **borgmatic version:** 1.5.1 **borgmatic installation method:** `pip install -e` **Borg version:** 1.1.9 **Python version:** 3.7.3 **Database version (if applicable):** MySQL 8.0.19 **operating system and version:** Manjaro stable

witten added the

bug

label 2020-04-22 16:53:22 +00:00

witten referenced this issue from a commit 2020-04-22 19:19:22 +00:00

Fix MySQL restore error on "all" database dump by excluding system tables (#301).

witten commented 2020-04-22 22:58:04 +00:00

Author

Owner

Copy Link

A fix for this is implemented in master, and will go out as part of the next release!

A fix for this is implemented in master, and will go out as part of the next release!

witten commented 2020-04-24 23:08:13 +00:00

Author

Owner

Copy Link

Released in borgmatic 1.5.2!

Released in borgmatic 1.5.2!

witten closed this issue 2020-04-30 17:09:50 +00:00

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

main

spot-check

1.8.10

1.8.9

1.8.8

1.8.7

1.8.6

1.8.5

1.8.4

1.8.3

1.8.2

1.8.1

1.8.0

1.7.15

1.7.14

1.7.13

1.7.12

1.7.11

1.7.10

1.7.9

1.7.8

1.7.7

1.7.6

1.7.5

1.7.4

1.7.3

1.7.2

1.7.1

1.7.0

1.6.6

1.6.5

1.6.4

1.6.3

1.6.2

1.6.1

1.6.0

1.5.24

1.5.23

1.5.22

1.5.21

1.5.20

1.5.19

1.5.18

1.5.17

1.5.16

1.5.15

1.5.14

1.5.13

1.5.12

1.5.11

1.5.10

1.5.9

1.5.8

1.5.7

1.5.7.dev0

1.5.6

1.5.5

1.5.4

1.5.3

1.5.2

1.5.1

1.5.0

1.4.22

1.4.21

1.4.20

1.4.19

1.4.18

1.4.17

1.4.16

1.4.15

1.4.14

1.4.13

1.4.12

1.4.11

1.4.10

1.4.9

1.4.8

1.4.7

1.4.6

1.4.5

1.4.4

1.4.3

1.4.2

1.4.1

1.4.0

1.3.26

1.3.25

1.3.24

1.3.23

1.3.22

1.3.21

1.3.20

1.3.19

1.3.18

1.3.17

1.3.16

1.3.15

1.3.14

1.3.13

1.3.12

1.3.11

1.3.10

1.3.9

1.3.8

1.3.7

1.3.6

1.3.5

1.3.4

1.3.3

1.3.2

1.3.1

1.3.0

1.2.18

1.2.17

1.2.16

1.2.15

1.2.14

1.2.13

1.2.12

1.2.11

1.2.10

1.2.9

1.2.8

1.2.7

1.2.6

1.2.5

1.2.4

1.2.3

1.2.2

1.2.1

1.2.0

1.1.15

1.1.14

1.1.13

1.1.12

1.1.11

1.1.10

1.1.9

1.1.7

1.1.6

1.1.5

1.1.4

1.1.3

1.1.2

1.1.1

1.1.0

1.0.3

1.0.2

1.0.1

1.0.0

0.1.8

0.1.7

0.1.6

0.1.5

0.1.4

0.1.3

0.1.2

0.1.1

0.1.0

0.0.7

0.0.6

0.0.5

0.0.4

0.0.3

0.0.2

Labels

Clear labels   

bug

  

data loss

  

design finalized

  

good first issue

  

new feature area

  

question / support

  

security

  

waiting for response

No Label

bug

data loss

design finalized

good first issue

new feature area

question / support

security

waiting for response

Milestone

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

1 Participants

Notifications

Subscribe

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: borgmatic-collective/borgmatic#301

No description provided.