Mysqldump certain tables


 
Thread Tools Search this Thread
# 1  
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.
# 2  
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 Smilie -- 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 ...

This User Gave Thanks to Corona688 For This Post:
# 3  
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 05:48 AM..
# 4  
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

This User Gave Thanks to Corona688 For This Post:
# 5  
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.
This User Gave Thanks to timgolding For This Post:
# 6  
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.
 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need Help with automatically Import from special mysqldump

Hi @ all I need a little bit help with a tricky problem … Here´s the situation: We´ve 2 MySQL-Servers, one is productive, the other is Backup. At the productive Server there runs every 2 hours a cron Job which does a Dump from MySQL-DB with script 'automysqlbackup.sh' and copy it then via... (7 Replies)
Discussion started by: jackcracker
7 Replies

2. Shell Programming and Scripting

Mysqldump rotate backup

I have a very simple script that uses a cron job to take a daily backup of our orders database. echo "Dumping ORDERS database"; mysqldump -u root --password='mypassword' -h '1.1.1.1' --opt --compress ORDERS $tbl_names > /Volumes/Files_Backup_1/db_backups/orders.sql echo "Copied database to... (2 Replies)
Discussion started by: timgolding
2 Replies

3. Shell Programming and Scripting

Modification of MySQLDump-files before compression needed

Hi @all! In my MySQL-backup-script I backup and compress every single table with this command: /usr/bin/mysqldump --opt database_x table_y | /usr/bin/pbzip2 -c > "/media/BackUpDrive/Backup/table_x.gz"Unfortunately these files need modification - they have to start with the following line(s):... (7 Replies)
Discussion started by: gogo555
7 Replies

4. UNIX for Dummies Questions & Answers

Problem with my crontab file, using mysqldump

My crontab file tells cron to run a certain shell script at 10:30 AM every day. The shell script backs up my database with mysqldump and then runs a sed script that does some editing of the backup file. I have programmed the shell script to write an error message to a file I have in my home... (1 Reply)
Discussion started by: Ultrix
1 Replies

5. Shell Programming and Scripting

Help needed for mysqldump command

I want to take a backup of a database and redirect the output of the whole process to a log file. I am using the below command: mysqldump -A --add-drop-table > mysql-daily-backup.sql &> /tmp/backup_log/mysql.log Is there anything wrong with the syntax? ---------- Post updated at 08:32 PM... (0 Replies)
Discussion started by: proactiveaditya
0 Replies

6. UNIX for Advanced & Expert Users

mysqldump slowing down the process?

Hi All, I have a data calculation process-a perl script running each and every hour which will do some calculations on the data stored in a mysql server. Normally it tooks around 2minutes (max) to complete. But in case if i did any actions on the linux box where the database is... (7 Replies)
Discussion started by: DILEEP410
7 Replies

7. Shell Programming and Scripting

mysqldump script without hardcode password

OS: Linux ambglx02 2.6.16.60-0.21-default #1 Tue May 6 12:41:02 UTC 2008 i686 i686 i386 GNU/Linux Shell: bash Currently I have a mysqldump script to backup my mysql database, the command is as below: /opt/novell/mysql/bin/mysqldump --add-drop-table -u root -p -h mydb > /home/john/mydb.sql ... (5 Replies)
Discussion started by: bulkbiz
5 Replies

8. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies

Featured Tech Videos