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 LINUX

dput

dput(1)                                                       General Commands Manual                                                      dput(1)

NAME

       dput - package upload tool for Debian

SYNTAX

       dput [options] [host] package.changes ...

DESCRIPTION

       This  is  a  tool  for uploading Debian packages into the archive. You may specify to which host it should upload the file by passing it an
       host argument. If omitted, dput uses the host specified by default_host (or, historically default_host_non-us) configuration option.   Also
       you have to pass the program one or more name of package.changes files and then they will be sequentially uploaded.

       This  program  will  then  upload  the  package for you into the archive, using a selected upload method. Currently dput supports ftp, scp,
       rsync, http, https, and local. The method scripts have been split from the main script  so  it  is  easy  to  add  new  methods.   Look  in
       /usr/share/dput/ for examples.

       Should  something  go  wrong  with your upload to destinations using the Debian upload queue daemon (e.g. ftp-master), you can remove files
       from the upload queue with dcut(1).

OPTIONS

       -c, --config - define a config file to use.

       -d, --debug - activate debugging mode, helpful if bugs occur.

       -D, --dinstall - do a dry run of dinstall after the upload.

       -f, --force - force an upload of an already uploaded package.

       -h, --help - print help information and exit.

       -H, --host-list - print the lists of hosts that dput knows about.

       -l, --lintian - run lintian before the upload.

       -U, --no-upload-log - do not write a .upload log file after uploading.

       -o, --check-only - check only the package and do not upload.

       -p, --print - print the configuration that dput is using.

       -P, --passive - use passive ftp instead of active. Note that passive ftp is the default unless specified  otherwise  in  the  configuration
       file.

       -s, --simulate - simulate an upload only.

       -u, --unchecked - don't check GnuPG signature on the changes file.

       -e,  --delayed  - Upload to a DELAYED queue, rather than the usual Incoming. This takes an argument from 0 to 15. Note that a delay of 0 is
       different from no delay at all.

       -v, --version - print version information and exit.

       -V, --check-version - check if the user has already installed and tested the package before putting it into the archive.

ENVIRONMENT

       This program doesn't depend on any environment variables. But if the variable USER is set, it will be used.

LICENSE

       This program is distributed under the terms of the GPL.

BUGS

       Please send bug reports to the author.

FILES

       /etc/dput.cf
              global dput configuration file

       ~/.dput.cf
              peruser dput configuration file

SEE ALSO

       dput.cf(5)
       dcut(1)
       /usr/share/doc/dput
       /usr/share/dput

AUTHOR

       dput was written Christian Kurz. The current maintainer is Thomas Viehmann <tv@beamnet.de>.

       Many other people have contributed to this code. See the Thanks file.

COMMENTS

       The author does appreciate comments and suggestions from you.

                                                                  October 5, 2001                                                          dput(1)