Clone database in MySQL

1

I was cloning a database in MySQL using the following command:

[email protected]: 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 by changing the name of the copy:

[email protected]: mysqldump -u root -p MyOriginalDatabase | mysql -u root -p MyDatabaseCopy2

However, it generated the following error:

  

ERROR 1049 (42000): Unknown database 'MyDatabaseCopy2'

It never finished the execution and it did not copy the database correctly.

  • Why did it work correctly the first time and then not?
  • Why did the execution never end in both cases?
  • What is the most optimal and recommended way to clone a base of data in MySQL ?
asked by Jorius 16.01.2017 в 22:02
source

2 answers

3

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.

    
answered by 17.01.2017 / 00:06
source
0

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
    
answered by 16.01.2017 в 22:23