mysql: import/export all databases in separate files

Post Reply
User avatar
hiccup
Site Admin
Posts: 24
Joined: Sat Aug 01, 2015 8:44 pm

mysql: import/export all databases in separate files

Post by hiccup » Mon Jul 06, 2020 6:18 pm

Take a mysqldump back-up to separate files

Code: Select all

mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" -r "$dbname".sql; done
--complete-insert, -c
Use complete INSERT statements that include column names.

--routines, -R
Included stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.

--triggers
Include triggers for each dumped table in the output. This option is enabled by default; disable it with --skip-triggers.

--single-transaction
This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

--result-file=file_name, -r file_name
Direct output to a given file. This option should be used on Windows to prevent newline "\n" characters from being converted to "\r\n" carriage return/newline sequences. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.

Import files to mysql from each .SQL file
Warning: this overwrites your databases, without prompting for confirmation. Use with caution!

Code: Select all

for sql in *.sql; do dbname=${sql/\.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done
save mysql user pass in homedirectory
.my.cnf

Code: Select all

[client]
user = root
password = 'password'

Post Reply