If test and production are supposed to be identical, then use this:
mysqldump -h<IP for Prod> -u... -p... --triggers --routines <db-name on Prod> |
mysql -h<IP for Test> -u... -p... -A -D<db-name on Test>
If you havn't noticed, you do not dump the data to a file and ftp or scp
anything anywhere !!!
This will actually pipe all mysqldump commands striaght to the test database.
Make sure Linux can withstand the long connect time required.
If you want mysqldump all tables one at a time,
here is a Windows Batch File That I Use Every Week:
1) drop a target database,
2) recreate a target database,
3) Copy all stored procedures from Source to Target
4) Dynamically Create a Windows Batch File to mysqldump every table
(and its triggers, if applicable) starting with the largest table
Using this method, you need not worry about connection time because each table
will be shipped over from Source to Target using a Fresh Connection each time.
@echo off
set TABLESBAT=DynamicBatchFile.bat
echo SysData Full Copy Initiated > DataLoadFull.txt
date /t >> DataLoadFull.txt
time /t >> DataLoadFull.txt
rem
rem
rem Drop SysData_Test from Target
rem Create SysData_Test for Target
rem Load Stored Procedures from Host PROD_IP Database SysData_Prod into Host
TEST_IP Database SysData_Test
rem
rem
set SOURCE_HOST=PROD_IP
set SOURCE_USER=...
set SOURCE_PASS=...
set TARGET_HOST=TEST_IP
set TARGET_USER=...
set TARGET_PASS=...
rem
rem
echo DROP DATABASE IF EXISTS SysData_Test; > ResetTarget.sql
echo CREATE DATABASE SysData_Test; >> ResetTarget.sql
echo USE SysData_Test >> ResetTarget.sql
mysqldump -h%SOURCE_HOST% -u%SOURCE_USER% -p%SOURCE_PASS% --no-data
--no-create-info --routines NDW >> ResetTarget.sql
rem
rem
rem mysqldump all MyISAM tables from Source to Target in Size Order starting
from the Largest Table
rem
rem
mysql -h%SOURCE_HOST% -u%SOURCE_USER% -p%SOURCE_PASS% -A --skip-column-names
-DNDW -e"SELECT CONCAT('mysqldump -h%SOURCE_HOST% -u%SOURCE_USER%
-p%SOURCE_PASS% --triggers SysData_Prod ',A.table_name,' | mysql
-h%TARGET_HOST% -u%TARGET_USER% -p%TARGET_PASS% -A -DSysData_Test') FROM
(SELECT table_name,(data_length+index_length) table_length FROM
information_schema.tables WHERE table_schema='SysData_Prod' AND
engine='MyISAM') A ORDER BY A.table_length DESC,A.table_name;" >> %TABLESBAT%
echo exit >> %TABLESBAT%
mysql -h%TARGET_HOST% -u%TARGET_USER% -p%TARGET_PASS% -A < ResetTarget.sql
start "mysqldumps from PROD to TEST, DO NOT CLOSE !!!" /w %TABLESBAT%
del ResetTarget.sql
del %TABLESBAT%
echo SysData Full Completed >> DataLoadFull.txt
date /t >> DataLoadFull.txt
time /t >> DataLoadFull.txt
PLEASE BE CAREFUL WITH THIS !!!
----- Original Message -----
From: "Charles Brown" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Tuesday, March 27, 2007 3:17:46 PM (GMT-0500) Auto-Detected
Subject: a Linux -csh script to refresh test with production
Hello all.
Does anyone out there (in mysql world) have a Linux -csh script to
refresh test with production data.
My developers would like their test database to be refreshed nightly
with production data. The production and test mysql servers do not run
in the same box. They run on different boxes. Therefore there is some
ftp or scp required
Thanks
********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.
If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.
Thank you.
********************************************
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
