Sponsored Content
Full Discussion: Mysqldump certain tables
Top Forums UNIX for Dummies Questions & Answers Mysqldump certain tables Post 302743073 by timgolding on Wednesday 12th of December 2012 05:40:47 AM
Old 12-12-2012
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..
 

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:... (7 Replies)
Discussion started by: justthisguy
7 Replies

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 ... (5 Replies)
Discussion started by: bulkbiz
5 Replies

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 (3 Replies)
Discussion started by: dahr
3 Replies

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... (7 Replies)
Discussion started by: DILEEP410
7 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 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

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... (2 Replies)
Discussion started by: timgolding
2 Replies

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... (7 Replies)
Discussion started by: jackcracker
7 Replies
MYSQLSHOW(1)						      General Commands Manual						      MYSQLSHOW(1)

NAME
mysqlshow - Shows the structure of a mysql database (databases,tables and columns) USAGE
shell> mysqlshow [OPTIONS] [database [table [column]]] SYNOPSIS
mysqlshow [-#|--debug=...] [-?|--help] [-c|--character-sets-dir=...] [-C|--compress] [-h|--host=...] [-i|--status] [-k|--keys] [-p|--password[=...]] [-P|--port=...] [-S|--socket=...] [-u|--user=#] [-V|--version] DESCRIPTION
-#|--debug=... output debug log. Often this is 'd:t:o,filename` -?|--help display help and exit -c|--character-sets-dir=... Directory where character sets are -C|--compress Use compression in server/client protocol -h|--host=... connect to host -i|--status Shows a lot of extra information about each table -k|--keys show keys for table -p|--password[=...] password to use when connecting to server If password is not given it's asked from the tty. -P|--port=... Port number to use for connection -S|--socket=... Socket file to use for connection -u|--user=# user for login if not current user -V|--version output version information and exit NOTE
If last argument contains a shell or SQL wildcard (*,?,% or _) then only what's matched by the wildcard is shown. If no database is given then all matching databases are shown. If no table is given then all matching tables in database are shown If no column is given then all matching columns and columntypes in table are shown SEE ALSO
isamchk (1), isamlog (1), mysqlaccess (1), mysqladmin (1), mysqlbug (1), mysqld (1), mysqldump (1), mysqlshow (1), msql2mysql (1), perror (1), replace (1), safe_mysqld (1), which1 (1), zap (1), AUTHOR
Ver 1.0, distribution 3.23.29a Michael (Monty) Widenius (monty@tcx.se), TCX Datakonsult AB (http://www.tcx.se). This software comes with no warranty. Manual page by L. (Kill-9) Pedersen (kill-9@kill-9.dk), Mercurmedia Data Model Architect / system developer (http://www.mer- curmedia.com) 19 December 2000 MYSQLSHOW(1)
All times are GMT -4. The time now is 01:50 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy