Clone database in MySQL

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

$ mysql -u root -pTuPassword 

Important: There are no spaces between the -p option and the password. You can do the same in the option -p of mysqldump .

If you want to clone the database on the same server from which you are extracting it, you must previously create the target 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 on another server, then you can use the pipeline ; just make sure you include the option --DATABASES in the call to mysqldump :

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

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

If you want to prove that the command works correctly, but you do not want to wait for the entire database dump, you can use the --no-data (or -d ) option to create a dump that contains only the structure, not including the data of each table:

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

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

When I was blank it was because I 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, so it tells you an error when not finding the database "MyDatabaseCopy2" .... before you can clone it, you must create the base of data to later clone it successfully ....

There are different ways that you can work well, look, I take a few steps to perform the process you want ... (I got it from an internet source)

The first thing we must do is to dump the database into 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