Solved

mysql multiple table export into csv or text

Posted on 2014-11-21
4
636 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 500 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…

631 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