Skip to content

Instantly share code, notes, and snippets.

@hanvari

hanvari/mysqldump-tips.md

Last active February 12, 2021 07:03
Show Gist options
  • Save hanvari/a142b5dcb4d0c5760beca4ff686b9b45 to your computer and use it in GitHub Desktop.
Save hanvari/a142b5dcb4d0c5760beca4ff686b9b45 to your computer and use it in GitHub Desktop.
Download ZIP
mysqldump helpful tips for export/import
Raw
mysqldump-tips.md

mysqldump Tips

CLI command format

$ mysqldump <mysqldump options> [<dbname1> <dbname2> ...] [> outputfile.sql]

CLI Options

  • --all-databases Will export all databases. You don't need to provide databasenames anymore.

  • --triggers (default is True) To include triggeres in the dumped file.

  • --routines (default is False) To include routines (procedures, functions, etc.) in the dumped file.

  • --events

  • --tz-utc (defaults to True) Setting session timezone to UTC (+00:00) to export (and later import) dates to/from UTC timezone.

  • --result-file=... The filepath/name to write the resulting sql queries, instead of STDOUT. If usign this option, there is no need to redirect command output to a file anymore.

  • --no-data To only export structure and no data.

Definer field

In dumped file, thre are 'definer' fields for each view/routine/etc. This may result in issues when importing the dumped file into mysql/mariadb again. To fix the issue, the definer section of queris shouls be removed:

To remove definer from queries

Approach 1

Using mysqlpump instead of mysqldump, which has a --skip-definer option:

$ mysqlpump --skip-definer ...

Note that this option is not available for mysqldump

Approach 2

After generating dump file using mysqldump, remove the definer fields using this CLI command:

$ sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment