Home / Questions / 44345
Accepted
Jorius
Asked: 2020-01-17 13:02:59 +0800 CST 2020-01-17 13:02:59 +0800 CST

Clone database in MySQL

  • 772

I was cloning a database in MySQLusing the following command:

$user@host: mysqldump -u root -p MyOriginalDatabase | mysql -u root -p MyDatabaseCopy

And the output was the following:

Enter password: Enter password:

I entered the password and it stayed in the execution of this without stopping or showing a result, however , it generated the copy correctly so I ended up canceling the execution with a keyboard interruption ( ctrl + c )

I tried to make another copy with the same command just changing the name of the copy:

$user@host: mysqldump -u root -p MyOriginalDatabase | mysql -u root -p MyDatabaseCopy2

However, it gave me the following error:

ERROR 1049 (42000): Unknown database 'MyDatabaseCopy2'

It never finished the execution and it didn't copy the database correctly either.

  • Why did it work correctly the first time and then not?
  • Why did the execution never finish in both cases?
  • What is the most optimal and recommended way to clone a database in MySQL?
mysql

2 Answers

  1. Best Answer

    An important point is that, since you are using a pipeline , it may not be possible to read from STDINthe password to the server. You can include it directly in the command call:

    $ mysql -u root -pTuPassword

    Important: There are no spaces between the option -pand the password. You can do the same in the option -pof mysqldump.

    If you want to clone the database to the same server you are extracting it from, you must first create the destination database.

    In the MySQL CLI:

    mysql> create database MyDatabaseCopy;

    Once the database is created, you can run mysqldump:

    $ mysqldump -u root -pTuPassword MyOriginalDatabase | mysql -u root -pTuPassword MyDatabaseCopy

    If you are going to clone your database to another server, then you can use the pipeline ; just make sure to include the option --DATABASESin the call to mysqldump:

    $ mysqldump -h hostOrigen -u root -pTuPassword --databases MyOriginalDatabase | mysql -h hostDestino -u root -pTuPassword

    Important: If you do this, the database name on the destination host will be the same as on the source host.

    If you want to test that the command works correctly, but you don't want to wait for the entire database to be dumped, you can use the --no-data(o -d) option to create a dump that contains only the structure, not including the data for each table. :

    $mysqldump -h hostOrigen -u root -pTuPassword -d --databases MyOriginalDatabase > pruebaDump.sql

    Remember to include the relevant options when runningmysqldump . For example, if the database has stored procedures or functions, you must add the option -Rso that these are included in the dump.

    • 4

  2. When it was blank it was because it was cloning, depending on the size of the database you want to clone is the time it will take to complete the process.

    When you wanted to clone it for the second time, there was no database to clone to, that's why it gives you an error not finding the database "MyDatabaseCopy2"... before you can clone it, you must create the database to then clone it successfully....

    There are different ways that can work well for you, look, I'll give you a few steps to carry out the process you want... (I got it from an internet source)

    The first thing we need to do is dump the database to a file:

    mysqldump -u nombre_usuario -p nombre_base_datos > nombre_fichero_volcado

    The second thing will be to log in to the MySQL server with our client:

    mysql -u nombre_usuario -p

    Inside the server we create the database:

    CREATE DATABASE nombre_base_datos_copia;

    We leave the client:

    exit

    We can now copy the dump to the newly created database: 

    mysql -u nombre_usuario -p nombre_base-datos_copia < volcado
    • 1