Web Projects Consulting

Preparing for Fedora 11 – MySQL Database-per-file Dump

Yes, yes, man, I am still NOT on Fedora 11… Because I know how newly-out distributions of Fedora tend to behave on my hardware (which is never really brand-pathetic but effective in the terms of gigahertzes and terabytes). I have to wait a month or two before I really make a decision to slowly migrate the oldest of my laptops to 11 as soon as I see that Livna and other repos are really ripe ready for that…

The below is a pretty short solution for backup up your MySQL databases in case you really need it.

Moreover, it’s a pretty good stimulus to clean everything up and kinda start from the scratch. Itchy jobless freelancers would probably know what I mean during the world economical crisis as it was said on the TV…

So, create a ~/bin dir is it still does not exist and a script file to be used for MySQL dumping.

mkdir -p ~/bin
touch ~/bin/mysqlfiledump.sh
chmod +x ~/bin/mysqlfiledump.sh
gedit ~/bin/mysqlfiledump.sh

It will open a window with an empty file to edit. Copy and paste the below.

#!/bin/bash
<h1>backup each mysql db into a different file, rather than one big file</h1>
<h1>as with --all-databases - will make restores easier</h1>
USER=&quot;MYSQL_USER_NAME&quot;
PASSWORD=&quot;MYSQL_PASSWORD&quot;
OUTPUTDIR=&quot;./&quot;
MYSQLDUMP=&quot;mysqldump&quot;
MYSQL=&quot;mysql&quot;
databases=<code>$MYSQL --user=$USER --password=$PASSWORD -e &quot;SHOW DATABASES;&quot; | tr -d &quot;| &quot; | grep -v Database</code>
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --routines --user=$USER --password=$PASSWORD --databases $db &gt; &quot;$OUTPUTDIR/$db.dump.sql&quot;
done

Make needed replacements, save and run in the terminal in just any directory – this will create a pack of *.dump.sql files with the desired dumps.

mysqlfiledump.sh

For the security purposes, I would delete the script file thereafter or simply clear the MySQL user and password data. And bookmark this site for future reference, of course.

3 thoughts on “Preparing for Fedora 11 – MySQL Database-per-file Dump

  1. Anders Ingemann

    Just use -B or –batch.
    Column names and pipes wont be outputtet then.
    You still need to filter out the mysql and information_schema database

  2. Alec

    To fix the “information_schema” stuff, I use the following now:

    $MYSQLDUMP --force --opt --routines --single-transaction --user=$USER 
    --password=$PASSWORD --databases $db ...
    

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.