Last active
March 23, 2021 12:11
-
-
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).)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
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
Example with single database:
mysqldump --no-data -h some_server_host --port some_server_port -u mysql_user -p --verbose --databases DATABASE_NAME --skip-lock-tables --routines --triggers > db_schemes_backup_file