Mysqldump certain tables Post: 302743073

Sponsored Content

Top Forums UNIX for Dummies Questions & Answers Mysqldump certain tables Post 302743073 by timgolding on Wednesday 12th of December 2012 05:40:47 AM

12-12-2012

Registered User

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..

timgolding

View Public Profile for timgolding

Find all posts by timgolding

8 More Discussions You Might Find Interesting

1. 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:...

2. 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 ...

3. UNIX for Dummies Questions & Answers

mysqldump bus error - cored dumped

hi, I need to backup a database but I'm getting the error "bus error - core dumped" just after I run mysqldump command. The server has installed solaris 9. Any help would be appreciated

4. 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...

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...

6. 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...

7. 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...

8. 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...

LEARN ABOUT DEBIAN

mojo::upload

Mojo::Upload(3pm)					User Contributed Perl Documentation					 Mojo::Upload(3pm)

NAME

       Mojo::Upload - Upload container

SYNOPSIS

	 use Mojo::Upload;

	 my $upload = Mojo::Upload->new;
	 say $upload->filename;
	 $upload->move_to('/home/sri/foo.txt');

DESCRIPTION

       Mojo::Upload is a container for uploads.

ATTRIBUTES

       Mojo::Upload implements the following attributes.

   "asset"
	 my $asset = $upload->asset;
	 $upload   = $upload->asset(Mojo::Asset::File->new);

       Asset containing the uploaded data, defaults to a Mojo::Asset::File object.

   "filename"
	 my $filename = $upload->filename;
	 $upload      = $upload->filename('foo.txt');

       Name of the uploaded file.

   "headers"
	 my $headers = $upload->headers;
	 $upload     = $upload->headers(Mojo::Headers->new);

       Headers for upload, defaults to a Mojo::Headers object.

   "name"
	 my $name = $upload->name;
	 $upload  = $upload->name('foo');

       Name of the upload.

METHODS

       Mojo::Upload inherits all methods from Mojo::Base and implements the following new ones.

   "move_to"
	 $upload->move_to('/home/sri/foo.txt');

       Alias for "move_to" in Mojo::Asset::File.

   "size"
	 my $size = $upload->size;

       Alias for "size" in Mojo::Asset::File.

   "slurp"
	 my $string = $upload->slurp;

       Alias for "slurp" in Mojo::Asset::File.

SEE ALSO

       Mojolicious, Mojolicious::Guides, <http://mojolicio.us>.

perl v5.14.2							    2012-09-05							 Mojo::Upload(3pm)