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.

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

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.

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

mysqldump -u username database table1 table2 table3 ...

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)

# 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

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

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.

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

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.

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.

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