Mysqldump certain tables | Unix Linux Forums | UNIX for Dummies Questions & Answers

  Go Back    


UNIX for Dummies Questions & Answers If you're not sure where to post a UNIX or Linux question, post it here. All UNIX and Linux newbies welcome !!

Mysqldump certain tables

UNIX for Dummies Questions & Answers


Tags
mysqldump certain tables

Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 12-11-2012
timgolding timgolding is offline
Registered User
 
Join Date: Dec 2008
Last Activity: 4 November 2013, 5:24 AM EST
Posts: 83
Thanks: 10
Thanked 2 Times in 2 Posts
Mysqldump certain tables

Hi,

I have to upload part of my database periodically when i make changes to product data etc. However I only want to upload certain tables. We suffer from bandwidth chock here, so i want to write a couple of separate scripts that upload parts of the database that changed. The database is large and it is split into to sections Feed driven data, which is 27MB and Manual driven data which is 8MB. To reduce the upload time i decided to write a script that uploads the feed stuff (to be run once a month) and another script to upload the manual stuff (could be run daily) to reduce the upload time. I therefore decided i would have a script to copy the sections of the database in to a smaller database on the development server. Then a script on the live server to download the smaller database from the development server. I am however having troubles creating the first script.

Here is what i have.


Code:
mysqladmin -u root --password='mypassword' --socket=/tmp/mysql.sock drop DATABASE_TEMP
mysqladmin -u root --password='mypassword' --socket=/tmp/mysql.sock create DATABASE_TEMP


for table in TABLE TABLE2 TABLE3 TABLE4 TABLE5
do
echo "copying table"
mysqldump -u root --password='mypassword' DATABASE $table | mysql --socket=/tmp/mysql.sock -u root --password='mypassword' DATABASE_TEMP
done

Am i on the right track? Should i be doing this with a single command somehow? Will this work? I am reluctant to test it without some advice because i don't want to much anything up.
Sponsored Links
    #2  
Old 12-11-2012
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 22 October 2014, 4:22 PM EDT
Location: Saskatchewan
Posts: 19,649
Thanks: 818
Thanked 3,341 Times in 3,130 Posts
27 megabytes of what? Text from mysqldump? Any sort of compression should shrink that fantastically.

Instead of constantly using root to drop entire databases -- might just be the teensiest bit dangerous -- just list tables and delete them as the same user.
Code:
mysql -u username database -b --skip-column-names <<EOF > /tmp/$$-tables
SHOW TABLES
EOF

while read table
do
        echo "DROP TABLE $table"
done < /tmp/$$-tables | mysql -u username database
rm -f /tmp/$$-tables

And yeah, you can dump as many tables simultaneously as you want.


Code:
mysqldump -u username database table1 table2 table3 ...

The Following User Says Thank You to Corona688 For This Useful Post:
timgolding (12-12-2012)
Sponsored Links
    #3  
Old 12-12-2012
timgolding timgolding is offline
Registered User
 
Join Date: Dec 2008
Last Activity: 4 November 2013, 5:24 AM EST
Posts: 83
Thanks: 10
Thanked 2 Times in 2 Posts
Thanks, i wondered while i was in bed do i really need to create a smaller version of the database on the development server before i upload or would it be better to just dump remotely from the live server. i want the upload to be smooth to reduce any downtime when i do the upload. So i came up with this (ignore the drop database i will change it when i reverse engineer your script)


Code:
# I'll alter this once i understand your script.

mysqladmin -u root --password='mypassword' --socket=/tmp/mysql.sock drop DATABASE_TEMP
mysqladmin -u root --password='mypassword' --socket=/tmp/mysql.sock create DATABASE_TEMP

# Dump a smaller version of the database with compression

mysqldump -u MOVE1 --password='mypassword' -h '107.701.107.701' --opt --compress DATABASE TABLE1 TABLE2 TABLE3 | mysql --socket=/tmp/mysql.sock -u root --password='mypassword' DATABASE_TEMP
echo "temporary database successfully created"

# copy contents of temp database to the live version. Happens quickly and reduces downtime
for table in TABLE1 TABLE2 TABLE3
do
echo "moving data from $table"
mysql --socket=/tmp/mysql.sock -u root --password='mypassword' <<QUERY_INPUT
use DATABASE;
TRUNCATE TABLE $table;
INSERT INTO DATABASE.$table SELECT * FROM DATABASE_TEMP.$table;
QUERY_INPUT
done

If you see i added the tables to the dump like suggested. I will try to remove the dropping database from root now

---------- Post updated at 02:40 AM ---------- Previous update was at 01:33 AM ----------

Ok i get what your script does and have altered my script to drop the tables i want to drop rather than using show tables and dropping them all


Code:
tbl_names='TABLE1 TABLE2 TABLE3 TABLE4 TABLE5'

echo "dropping tables from temp database"
for table in $tbl_names
do
mysql --socket=/tmp/mysql.sock -u LOCAL_DROP --password='mypassword' <<QUERY_INPUT
use DATABASE_TEMP;
DROP TABLE $table;
QUERY_INPUT
done

mysqldump -u MOVE1 --password='mypassword' -h '107.701.107.701' --opt --compress DATABASE $tbl_names | mysql --socket=/tmp/mysql.sock -u root --password='mypassword' DATABASE_TEMP
echo "temporary database successfully created"

# copy contents of temp database to the live version. Happens quickly and reduces downtime
for table in $tbl_names
do
echo "moving data from $table"
mysql --socket=/tmp/mysql.sock -u root --password='mypassword' <<QUERY_INPUT
use DATABASE;
TRUNCATE TABLE $table;
INSERT INTO DATABASE.$table SELECT * FROM DATABASE_TEMP.$table;
QUERY_INPUT
done

Does that seem alright?

Last edited by timgolding; 12-12-2012 at 04:48 AM..
    #4  
Old 12-13-2012
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 22 October 2014, 4:22 PM EDT
Location: Saskatchewan
Posts: 19,649
Thanks: 818
Thanked 3,341 Times in 3,130 Posts
I was confused a moment until I saw how you were connecting to the remote server, yes, --compress should help.

You're running mysql 4 times when you only needed to run it once. Using -D makes it simpler too, less lines that need to be fed into it.


Code:
printf "DROP TABLE %s\n" $tbl_names |
        mysql --socket=/tmp/mysql.sock -u LOCAL_DROP -D DATABASE_TEMP --password='mypassword'

mysqldump -u MOVE1 --password='mypassword' -h '107.701.107.701' --opt --compress DATABASE $tbl_names |
        mysql --socket=/tmp/mysql.sock -u root --password='mypassword' DATABASE_TEMP

for table in TABLE1 TABLE2 TABLE3
do
        echo "TRUNCATE TABLE $table;"
        echo "INSERT INTO DATABASE.$table SELECT * FROM DATABASE_TEMP.$table;"
done | mysql --socket=/tmp/mysql.sock -u root --password='mypassword' -D DATABASE

The Following User Says Thank You to Corona688 For This Useful Post:
timgolding (12-14-2012)
Sponsored Links
    #5  
Old 12-14-2012
timgolding timgolding is offline
Registered User
 
Join Date: Dec 2008
Last Activity: 4 November 2013, 5:24 AM EST
Posts: 83
Thanks: 10
Thanked 2 Times in 2 Posts
Thanks a lot. That all makes sense. Ok. I'll implement the script as you have shown me, but won't get to test it until after Christmas when we go live with this new database. Thanks for all your help.
The Following User Says Thank You to timgolding For This Useful Post:
Corona688 (12-14-2012)
Sponsored Links
    #6  
Old 01-02-2013
timgolding timgolding is offline
Registered User
 
Join Date: Dec 2008
Last Activity: 4 November 2013, 5:24 AM EST
Posts: 83
Thanks: 10
Thanked 2 Times in 2 Posts
OK so i got round to testing the scripts and there was one error. Just needed a ; for the printf, so it treated the sql commands seperately.


Code:
printf  "DROP TABLE %s;\n" $tbl_names

After this change the scripts worked perfectly.

So thanks very very much for all your help Corona688.
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Help needed for mysqldump command proactiveaditya Shell Programming and Scripting 0 01-20-2010 08:32 PM
mysqldump slowing down the process? DILEEP410 UNIX for Advanced & Expert Users 7 04-19-2009 10:32 PM
mysqldump script without hardcode password bulkbiz Shell Programming and Scripting 5 12-21-2008 05:16 AM
Converting tables of row data into columns of tables justthisguy Shell Programming and Scripting 7 07-16-2007 04:42 PM



All times are GMT -4. The time now is 05:43 PM.