Solved

mysql multiple table export into csv or text

Posted on 2014-11-21
4
526 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
  • 2
  • 2
4 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility
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 76

Accepted Solution

by:
arnold earned 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Do you hate spam? I do, and I am willing to bet you do as well. I often wonder, though, "if people hate spam so much, why do they still post their email addresses on the web?" I'm not talking about a plain-text posting here. I am referring to the fa…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
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…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now