Mysqldump table name prefix

Question

I have two mysql databases that have almost the same structure and represent data from the same web application, but one of them represents the current version, and the second represents a long time ago.

How to create a database with two dumps inside, but with the prefix old_ for tables from the first and new prefix for tables from the second database?

Are there any mysqldump options for setting a prefix or other solution?

Answer 1

• Restore both databases as is.
• Use the following stored procedure to move all tables from one database to another database after adding the prefix.
• After moving, delete the original database.

MySQL inmemory information_schema RENAME.

DELIMITER $$

USE `db`$$

DROP PROCEDURE IF EXISTS `renameDbTables`$$

CREATE DEFINER=`db`@`%` PROCEDURE `renameDbTables`(
    IN from_db VARCHAR(20),
    IN to_db VARCHAR(30),
    IN to_name_prefix VARCHAR(20)
)
BEGIN
/*
call db.renameDbTables('db1','db2','db_'); 
db1.xxx will be renamed to db2.db_xxx
*/
    DECLARE from_state_table VARCHAR(20) DEFAULT '';
    DECLARE done INT DEFAULT 0;
    DECLARE b VARCHAR(255) DEFAULT '';
    DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA=from_db;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    OPEN cur1;

    REPEAT
        FETCH cur1 INTO from_state_table;
        IF NOT done THEN
--          select from_state_table;
            SET @QUERY = '';
            SET @QUERY = CONCAT(@QUERY,'RENAME TABLE ',from_db,'.', from_state_table,' TO ',to_db,'.', to_name_prefix, from_state_table,';');
--          SELECT @query;
            PREPARE s FROM @QUERY;
            EXECUTE s;
            DEALLOCATE PREPARE s;
        END IF;
    UNTIL done END REPEAT;
    CLOSE cur1;
    END$$

DELIMITER ;

Answer 2

sed script, , . sed -f .

s/\(-- Table structure for table `\)\([^`]\+\)\(`\)/\1xyzzy_\2\3/
s/\(DROP TABLE IF EXISTS `\)\([^`]\+\)\(`\)/\1xyzzy_\2\3/
s/\(CREATE TABLE `\)\([^`]\+\)\(` (\)/\1xyzzy_\2\3/
s/\(-- Dumping data for table `\)\([^`]\+\)\(`\)/\1xyzzy_\2\3/
s/\(\/\*!40000 ALTER TABLE `\)\([^`]\+\)\(` DISABLE KEYS \*\/\)/\1xyzzy_\2\3/
s/\(LOCK TABLES `\)\([^`]\+\)\(` WRITE\)/\1xyzzy_\2\3/
s/\(INSERT INTO `\)\([^`]\+\)\(` VALUES (\)/\1xyzzy_\2\3/
s/\(\/\*!40000 ALTER TABLE `\)\([^`]\+\)\(` ENABLE KEYS \*\/\)/\1xyzzy_\2\3/

xyzzy_ .

Answer 3

" mysqldump" - , SQL, .

1) .

2) "" :

• use mydatabase;
• , old_ .

3) cat dump1 dump2 > combined_dump

4) mysql < combined_dump

Answer 4

. , newdb olddb. 1 old_table1, :

insert into newdb.old_table1
select *
from olddb.table1

, script, :

select concat('insert into newdb.old_',  table_name,
    'select * from olddb.', table_name, ';')
from information_schema.tables
where table_schema = 'olddb'

Answer 5

mysqldump sed, , .

$ mysqldump -u user --password=mypass MyDB MyTable | sed s/MyTable/old_Mytable/ | mysql -u other_user -p NewDB

script , , , , , .

Peer

Answer 6

, , , SQL, dbs, , .

IF this is what you are trying to do, the easiest approach is to simply insert the correct "use database" command before each dump.

Same:

echo "use old_db;" > /tmp/combined_dump.sql
mysqldump old_db >> /tmp/combined_dump.sql
echo "use new_db;" >> /tmp/combined_dump.sql
mysqldump new_db >> /tmp/combined_dump.sql