I can see mysqldump being a little pain in the neck because of transaporting data out and then in.
Let's say you are copying data from proddb to stagedb. Here is a query to create the script for lateral copying of every table:
select
concat
(
'drop table if exists ',db2,'.',tbl,'; ',
'create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ',
'insert into ',db2,'.',tbl,' select * from ',db1,'.',tbl,';'
)
from (select table_schema db1,table_name tbl
from information_schema.tables where table_schema='stack_dev') tbls,
(select 'stack_test' db2) dbname;
Just generate the script as a SQL text file and import the text file to the target server:
PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" > /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql
This should work just fine for MyISAM. It should also work for InnoDB if there are no constraints. If there are constraints, you may have to disable them for the import session:
PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
echo "SET FOREIGN_KEY_CHECKS=0;" > /root/CopyFromProdToStage.sql
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" >> /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql
UPDATE 2012-03-09 17:14 EST
I have a stored procedure that will accomplish this. Run the following only once:
DROP DATABASE IF EXISTS utility;
CREATE DATABASE utility;
DELIMITER $$
DROP PROCEDURE IF EXISTS `utility`.`CopyDB` $$
CREATE PROCEDURE `utility`.`CopyDB` (sourceDB VARCHAR(64),targetDB VARCHAR(64))
TheStoredProcedure:BEGIN
DECLARE found_count,ndx,ndx_last INT;
DECLARE sqlcmd VARCHAR(1024);
SELECT COUNT(1) INTO found_count
FROM information_schema.tables
WHERE table_schema = sourceDB;
IF found_count = 0 THEN
LEAVE TheStoredProcedure;
END IF;
DROP TABLE IF EXISTS DBTablesToCopy;
CREATE TABLE DBTablesToCopy
(
id INT NOT NULL AUTO_INCREMENT,
src VARCHAR(64),
tgt VARCHAR(64),
tbl VARCHAR(64),
PRIMARY KEY (id)
) ENGINE=MyISAM;
DROP TABLE IF EXISTS SQLScriptToCopyTables;
CREATE TABLE SQLScriptToCopyTables
(
id INT NOT NULL AUTO_INCREMENT,
sqltext VARCHAR(1024),
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO DBTablesToCopy (src,tgt,tbl)
SELECT sourceDB,targetDB,table_name
FROM information_schema.tables
WHERE table_schema = sourceDB;
INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
(CONCAT('DROP DATABASE IF EXISTS ',targetDB));
INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
(CONCAT('CREATE DATABASE ',targetDB));
SELECT MAX(id) INTO ndx_last FROM DBTablesToCopy;
SET ndx = 0;
WHILE ndx < ndx_last DO
SET ndx = ndx + 1;
INSERT INTO SQLScriptToCopyTables (sqltext)
SELECT CONCAT('CREATE TABLE ',tgt,'.',tbl,' LIKE ',src,'.',tbl)
FROM DBTablesToCopy WHERE id = ndx;
END WHILE;
SET ndx = 0;
WHILE ndx < ndx_last DO
SET ndx = ndx + 1;
INSERT INTO SQLScriptToCopyTables (sqltext)
SELECT CONCAT('INSERT INTO ',tgt,'.',tbl,' SELECT * FROM ',src,'.',tbl)
FROM DBTablesToCopy WHERE id = ndx;
END WHILE;
SELECT MAX(id) INTO ndx_last FROM SQLScriptToCopyTables;
SET ndx = 0;
WHILE ndx < ndx_last DO
SET ndx = ndx + 1;
SELECT sqltext INTO @stmt FROM SQLScriptToCopyTables WHERE id = ndx;
PREPARE s1 FROM @stmt;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END WHILE;
END $$
DELIMITER ;
then anytime you feel like copying stack_dev
to stack_test
, do this:
call utility.copydb('stack_dev','stack_test');
and that's all.
Give it a Try !!!
When you issue an ALTER TABLE
in PostgreSQL it will take an ACCESS EXCLUSIVE
lock that blocks everything including SELECT
. However, this lock can be quite brief if the table doesn't require re-writing, no new UNIQUE
, CHECK
or FOREIGN KEY
constraints need expensive full-table scans to verify, etc.
If in doubt, you can generally just try it! All DDL in PostgreSQL is transactional, so it's quite fine to cancel an ALTER TABLE
if it takes too long and starts holding up other queries. The lock levels required by various commands are documented in the locking page.
Some normally-slow operations can be sped up to be safe to perform without downtime. For example, if you have table t
and you want to change column customercode integer NOT NULL
to text
because the customer has decided all customer codes must now begin with an X
, you could write:
ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );
... but that would lock the whole table for the re-write. So does adding a column with a DEFAULT
. It can be done in a couple of steps to avoid the long lock, but applications must be able to cope with the temporary duplication:
ALTER TABLE t ADD COLUMN customercode_new text;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;
This will only prevent writes to t
during the process; the lock name EXCLUSIVE
is somewhat deceptive in that it excludes everything except SELECT
; the ACCESS EXCLUSIVE
mode is the only one that excludes absolutely everyting. See lock modes. There's a risk that this operation could deadlock-rollback due to the lock upgrade required by the ALTER TABLE
, but at worst you'll just have to do it again.
You can even avoid that lock and do the whole thing live by creating a trigger function on t
that whenever an INSERT
or UPDATE
comes in, automatically populates customercode_new
from customercode
.
There are also built-in tools like CREATE INDEX CONCURRENTLY
and ALTER TABLE ... ADD table_constraint_using_index
that're designed to allow DBAs to reduce exclusive locking durations by doing work more slowly in a concurrency-friendly way.
The pg_reorg
tool or its successor pg_repack
can be used for some table restructuring operations as well.
Best Answer
No and yes.
No -- in that you would need to dump the data from your single server; this is invasive and costly.
Yes -- in that there are several ways of setting up Replication to make this task a breeze, possibly near-instantaneous, regardless of dataset size.
With a Master (Prod) + Slave, you could disconnect the Slave to use it as Staging; it would be up to date to the moment when it was grabbed. But if you modify the Staging server, it could not be put back as the Slave.
Galera -- Have 3 (or more) nodes. Removing a node from the cluster is easy. Adding a node to the cluster is easy -- the dump+reload is automatic. Then the grabbed node becomes Staging; then throw it away.
LVM -- This requires setting up the Prod machine with LVM before building the database. However, a one-minute downtime is all it takes to grab a snapshot of the prod machine. You then have hours to copy (perhaps via rsync) to the Staging server.