Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mysql multiple table export into csv or text

Posted on 2014-11-21
4
Medium Priority
?
683 Views
Last Modified: 2014-11-25
Hi Expert,

I need advise here,
I've 80 plus tables in mysql database, and we have build the redshift database with these table structure already. Now I need to export all tables from mysql and upload into redshift database cluster. I've divide into tree parts
1- export all table data into Tablenameyyymmdd.csv (80 csv files with mentioned name) and tar or gzip
2- moved to redshift cluster
3- upload into Redshift use COPY command

I need help for part -1 here,
I've found script to export all mysql tables into csv files and tar it but it is not working
#!/bin/bash

# Schema to export:
DB=billing

DIR=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S)/

# Final tarball's location:
TARBALL=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S).tar.gz

CONFIG=/home/ec2-user/.my.cnf

# Set owner to this user: (lets you access the export)
OWNER=root

# Name of file to save tables names to
SCHEMAFILE=schema.txt


echo Saving to $DIR;
mkdir "$DIR";

#mysql -u root -h 111.10.10.10 -p billing

for table in $(mysql -u =$CONFIG -h 111.10.10.10 -p $DB -B -e "show tables;");
do
echo Processing $table
mysql --defaults-extra-file=$CONFIG $DB -B -e "
SELECT * INTO OUTFILE '/tmp/tabledump.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM \`$table\`;"
mv /tmp/tabledump.csv "$DIR$table.csv"
cat <(echo -n "$table   ") <(mysql $DB -B -e "DESCRIBE $table;" | awk 'BEGIN {ORS=","; getline} { print $1}' ) <(echo) >> "$DIR$SCHEMAFILE"
done

echo Zipping

tar -czvf "$TARBALL" "$DIR"

echo Changing permissions
chown $OWNER "$TARBALL"

echo Removing uncompressed
rm -R "$DIR"

echo -n "Done, filesize: "
ls -l "$TARBALL" | awk '{print $5}'

Open in new window


.my.cnf like this config file
user=root
password=

Open in new window


script output is
Saving to csvs-billing-2014-11-21-21-51-25/
Zipping
csvs-billing-2014-11-21-21-51-25/
Changing permissions
Removing uncompressed
Done, filesize: 124
~

and folder csvs-billing-2014-11-21-20-39-06 empty

Please advise as I'm not strong in shell scripting..

Regards,
0
Comment
Question by:adnankh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 79

Expert Comment

by:arnold
ID: 40458996
Your issue is that your mysql command in the script uses -p without a password
If you have a root password, you should either enter it in the script -ppassword or --password=password

Your mysql lines should be uniform
I.e. Define
Username=
Password=

Mysql_cmd="mysql -u '$username' --password='$password'""
$($Mysql_cmd) additional options


..

If your root does not have a password do not include the -p option

I.e. If mysql -u root grants you access into the mysql Db server, remove the -p from the line,  the issue in some I see you use -h host which means that root is likely not allowed with empty password.


Does your strûcture include foreign keys, triggers, etc?
If you can connect to the remote, you could script the exitract from one and insert into the other.
The confusion for me is that the mysql references are not uniform
0
 

Author Comment

by:adnankh
ID: 40459013
Thank you, I'm using root user without password. after chnages I'm getting this error

Processing binary-mode
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
mv: cannot stat ‘/tmp/tabledump.csv’: No such file or directory
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Processing FALSE
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
mv: cannot stat ‘/tmp/tabledump.csv’: No such file or directory
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Zipping
csvs-habi-2014-11-22-01-14-57/
csvs-habi-2014-11-22-01-14-57/schema.txt
Changing permissions
Removing uncompressed
Done, filesize: 3572

and script like this now
#!/bin/bash

# Schema to export:
DB=habi

DIR=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S)/

# Final tarball's location:
TARBALL=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S).tar.gz

CONFIG=/home/ec2-user/.my.cnf

# Set owner to this user: (lets you access the export)
OWNER=root

# Name of file to save tables names to
SCHEMAFILE=schema.txt


echo Saving to $DIR;
mkdir "$DIR";

for table in $(mysql -u $CONFIG -h 111.11.11.10 habi $DB -B -e "show tables;");
do
echo Processing $table
mysql -u $CONFIG $DB -B -e "
SELECT * INTO OUTFILE '/tmp/tabledump.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM \`$table\`;"
mv /tmp/tabledump.csv "$DIR$table.csv"
cat <(echo -n "$table   ") <(mysql $DB -B -e "DESCRIBE $table;" | awk 'BEGIN {ORS=","; getline} { print $1}' ) <(echo) >> "$DIR$SCHEMAFILE"
done

echo Zipping

tar -czvf "$TARBALL" "$DIR"

echo Changing permissions
chown $OWNER "$TARBALL"

echo Removing uncompressed
rm -R "$DIR"

echo -n "Done, filesize: "
ls -l "$TARBALL" | awk '{print $5}'

Open in new window

0
 

Author Comment

by:adnankh
ID: 40459052
here new updates, please advice

error msg

Saving to csvs-habi-2014-11-22-02-16-53/
./dbexporttxt.sh: line 51: unexpected EOF while looking for matching `"'
./dbexporttxt.sh: line 53: syntax error: unexpected end of file

and  cnf file
[mysql]
user = root

and shell script is
#!/bin/bash

# Schema to export:
DB=habi

DIR=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S)/

# Final tarball's location:
TARBALL=csvs-$DB-$(date +%Y-%m-%d-%H-%M-%S).tar.gz

#CONFIG=/home/ec2-user/.my.cnf

# Set owner to this user: (lets you access the export)
OWNER=root
#OWNER=bxt:bxtsgroup

# Name of file to save tables names to
SCHEMAFILE=schema.txt


echo Saving to $DIR;
mkdir "$DIR";


for table in $(mysql --defaults-extra-file=$CONFIG $DB -B -e "show tables;");

do
echo Processing $table
mysql --defaults-extra-file=$CONFIG $DB -B -e "

SELECT * INTO OUTFILE '/tmp/tabledump.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM \`$table\`;"
mv /tmp/tabledump.csv "$DIR$table.csv"
cat <(echo -n "$table   ") <(mysql $DB -B -e "DESCRIBE $table;" | awk 'BEGIN {ORS=","; getline} { print $1}' ) <(echo) >> "$DIR$SCHEMAFILE"
done

echo Zipping

tar -czvf "$TARBALL" "$DIR"

echo Changing permissions
chown $OWNER "$TARBALL"

echo Removing uncompressed
rm -R "$DIR"

echo -n "Done, filesize: "
ls -l "$TARBALL" | awk '{print $5}'

Open in new window

0
 
LVL 79

Accepted Solution

by:
arnold earned 1500 total points
ID: 40459086
You need to be uniform, your move file error are a consequence of the mysql error.

You need to be uniform when using mysql either always use -h host or not, but it looks as through the config you are using points to the mysql.socket in the wrong location.
You should use if test -f "filename" to test whether the file is there prior to trying to move.

The code posted does not have the length reported by the error.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question