bash scripting for mysqldump

Question

i use the following code to download all mysql database in a different file and not in one file (like --all-databases) and put them in the /backup/mysql folder

#!/bin/bash

mysqldump=`which mysqldump`
echo $mysqldump

mkdir /backup/mysql/$(date '+%d-%b-%Y')
echo "creating folder for current date done"


for line in "$(mysqlshow |cut -f1 -d"-" | cut -c 3- | cut -f1 -d" ")"
do

        $mysqldump $line > /backup/mysql/$(date '+%d-%b-%Y')/"$line"
        echo "$line\n"
done

I used the cut pipes to remove dashes and empty space before and at the end of the database name and it gave me what I want.

The problem is at line 13 according to bash but with no more details. Any ideas what I'm doing wrong?

error message

You don't need to invoke mysqldump from its full path (otherwise which mysqldump would fail). Just write mysqldump $line > .... Furthermore, you should create a variable to store your mysqldump path (i.e., DB_PATH="/backup/mysql/$(date '+%d-%b-%Y')") and use it later in your script (i.e. mkdir "$DB_PATH", ... > "$DB_PATH/$line") — Aserre
– Aserre, Commented Jul 23, 2014 at 8:28

Édouard Lopez · Accepted Answer · 2014-07-23 09:13:51Z

1

`mysqlshow` output format

+---------------------+
|      Databases      |
+---------------------+
| information_schema  |
| gitlabhq_production |
| mysql               |
| performance_schema  |
| phpmyadmin          |
| test                |
+---------------------

Your script doesn't manage the first 3 lines nor the last one, so you $line variable is invalid.

Solution

mysql | tail -n +4 | head -n -1 | tr -d '| '

tail -n +4: skip first four lines (may need adjustement);
head -n -1: ignore last line ;
tr -d '| ': remove pipe and space.

Advices

quotes your variables ;
use $() instead of backtick ;
don't use for i in $(ls *.mp3).
read How can I read a file (data stream, variable) line-by-line (and/or field-by-field)?

Better solution

Instead of a for loop you should use a while with a Process Substitution:

while read -r db; do 
  echo "[$db]"; 
done < <(mysqlshow -u root -p | tail -n +3 | head -n -1 | tr -d ' |' )

edited Jul 23, 2014 at 9:13

answered Jul 23, 2014 at 8:59

Édouard Lopez

43.7k30 gold badges134 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spyros Savvanis Over a year ago

i adjust a bit so now it is : for line in "$(mysqlshow | tail -n +5 | head -n -1 | tr -d '| ')" but it give me an error only with dbname prntscr.com/45gyye

Édouard Lopez Over a year ago

1) update your question 2) copy/paste in your question 3) try running bash -x ./your-script.sh

Collectives™ on Stack Overflow

bash scripting for mysqldump

1 Answer 1

`mysqlshow` output format

Solution

Advices

Better solution

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

mysqlshow output format

Solution

Advices

Better solution

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

`mysqlshow` output format