Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

mysql multiple table export into csv or text

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
adnankh
Asked:
adnankh
  • 2
  • 2
1 Solution
 
arnoldCommented:
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
 
adnankhAuthor Commented:
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
 
adnankhAuthor Commented:
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
 
arnoldCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now