Copy Back Database with new name?

MySQL & MariaDB Other MySQL® Questions
, ,
1

Hi,

I have a backup of a database, called ‘test_db`, and would like to restore it on the same server but under a different database name.

If I have a DB test_db i need to create a replication of it with a new name test_db_copy.

How can I do this?

TIA

2

Hi Dilip,

Thanks for reaching out.

For your first question, it depend on how you are taking backup. If you are using mysqldump, then just pass “no create db” as its parameter. Before restoring, create your database and restore the sql file at destination. For more assurity, you can also check sql file should not contain the name of database in create table statement.

For your second question, some work around are mentioned below ::

  1. Create a chain topology of A-B-C mysql. Replicate from A to B with same name. Create additional database with your desired name on B. Create triggers on this new database to absorb all the changes. Replicate B to C with replication filters.

  2. Introduce a layer of tools like maxwell which can read the data from binary log of slave and write it on slave. You have to see how to avoid the database name in maxwell only.

1 Like
3

Hello @dillip,
You can simply rename databases using replication. Run the following on the replica:

STOP REPLICA;
CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB = ((olddb, newdb));
START REPLICA;

Note: The double (( )) is correct syntax.

1 Like
4

Is the below commands enough ti replicate the db in Server_1 to Server_2 ?

Backup

xtrabackup --host=${SQL_DB_HOST} --port=${SQL_DB_PORT} --user=${SQL_DB_USER} --password=${SQL_DB_PASSWORD} --databases=${src_database_name} --backup --datadir=/var/lib\mysql --target-dir=/tmp/xtrabackup_files/${src_database_name}

Prepare

xtrabackup --host=${SQL_DB_HOST} --port=${SQL_DB_PORT} --user=${SQL_DB_USER} --password=${SQL_DB_PASSWORD} --databases=${src_database_name} --prepare --datadir=/var/lib\mysql --target-dir=/tmp/xtrabackup_files/${src_database_name}

Restore

xtrabackup --host=${SQL_DB_HOST} --port=${SQL_DB_PORT_2} --user=${SQL_DB_USER} --password=${SQL_DB_PASSWORD} --databases=${src_database_name} --copy-back --datadir=/db2/target-dir=/tmp/xtrabackup_files/${src_database_name}
1 Like
5

@dillip
The commands you have will completely restore to Server_2, only a specific database. There will be no other data restored. If that is what you want, then that is fine. You don’t need to specify --databases on the prepare/restore steps.

1 Like
6

My Server_2 already has a few databases, I need to add this new database from server_1 to Server_2

Or any DIrect solution to Create a copy of the Database in Server_1 in Server_1 itself?

7

Easiest is to do a logical dump/restore with mydumper or mysqldump. Using xtrabackup to backup/restore a single database to an existing MySQL will require multiple steps and the use of InnoDB transportable tablespaces.

8

Got it, then in which cases xtrabackup is useful?

9

xtrabackup is the de-facto standard for hot physical backups of your entire MySQL database server supporting incremental backups, differential backups, streaming backups, compressed/encrypted backups, etc.

You can do exactly what you want with xtrabackup but if you are not familiar with all the steps and not familiar with InnoDB transportable tablespaces, it will be difficult for you to do. It is not a simple command to restore a single database using physical backups. This is not the fault of xtrabackup but the fault of InnoDB’s tablespace design.

10

Hey @dillip,
Check out this blog post for how to use xtrabackup to restore a single table or a single database to another server using those transportable tablespaces, Percona XtraBackup: Backup and Restore of a Single Table or Database - Percona Database Performance Blog

11

Hi @dillip

How did you create the original backup?

Pep