Skip to content

Instantly share code, notes, and snippets.

@0x49D1
Last active March 23, 2021 12:11
Show Gist options
  • Save 0x49D1/0144be5d7a13324338f24c85ae1eba91 to your computer and use it in GitHub Desktop.
Save 0x49D1/0144be5d7a13324338f24c85ae1eba91 to your computer and use it in GitHub Desktop.
Remote database server backup of all databases (all schemes) with mysqldump example with comments. And restore command with retained comments in procedures (for example).)
#!/bin/bash
# For example: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
# --no-data Do not dump table contents
# --triggers Do add triggers too
# --host Dump data from the MySQL server on the given host. The default host is localhost.
# --port For TCP/IP connections, the port number to use.
# --all-databases Dump all tables in all databases.
# -u The user name of the MySQL account to use for connecting to the server.
# --verbose Verbose mode. Print more information about what the program does.
# -p The password of the MySQL account used for connecting to the server. The password value is optional. If not given, mysqldump prompts for one. If given, there must be no space between --password= or -p and the password following it. If no password option is specified, the default is to send no password.
# > Redirect the output to file, for example
# install mysqldump (it's actually part of mysql client software) on ubuntu: sudo apt install mysql-client
# maybe add --skip-lock-tables
mysqldump --no-data --host some_server_host --port some_server_port --all-databases --routines --events -u mysql_user -p --verbose > db_schemes_backup_file.sql
# Restore with retained comments (default is --skip-comments (discard comments)):
sudo mysql -u USER -p -h HOST -D DATABASE --comments < db_backup_file.sql
@0x49D1
Copy link
Author

0x49D1 commented Jun 12, 2020

Example with removing the DEFINER from everything:
mysqldump -u mysql_user -p -h localhost --port 3306 --routines --events --comments DATABASE_NAME --verbose | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*PROCEDURE/PROCEDURE/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*FUNCTION/FUNCTION/' > local_db_backup_20200612.sql
https://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps#comment72272227_9447215

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment