How to Copy Database in MySQL

Copying a MySQL database with mysqldump creates a logical SQL copy. It does not use MySQL’s Clone plugin, configure replication, or provide high availability or recovery if a server fails. The commands below copy one database to another database on the same MySQL server. They run in an operating-system shell, such as Command Prompt, PowerShell, or a Unix-like shell; SQL statements run in the MySQL client.

Copy a MySQL Database on the Same Server

There are two common ways to move the dump from the source database to the destination: write it to a SQL file, or pipe it directly into the MySQL client. Both methods need access to the MySQL command-line tools and credentials that can read the source and create or write to the destination. Replace the example names with your database names. The examples use the root account for illustration; use an account with only the privileges required for your task when possible.

Prepare the Source and Target Databases

Choose distinct source and destination database names, and confirm that you are connecting to the intended server. The destination must exist before importing into it. Create it by connecting with the MySQL client and entering CREATE DATABASE targetDb;, or use an account that has permission to create it. If targetDb already exists, importing may add or replace objects according to the SQL statements in the dump; inspect the destination first if its contents matter.

Have mysqldump and mysql available in your shell. If they are not on your system’s PATH, use their full executable paths. Choose a writable location for the dump file and use the same path for both export and import. The examples use firstDbDump.sql in the current working directory.

Copy the Database with a SQL Dump File

This method keeps an intermediate SQL file that you can inspect or retain. In the operating-system shell, run mysqldump -u root -p firstDb > firstDbDump.sql. The -u option selects the account, while -p makes the client prompt for its password. The > shell redirection writes the dump to the named file; it does not create the destination database.

After creating targetDb, import the file from the same shell directory with mysql -u root -p targetDb < firstDbDump.sql. Here, < sends the file contents to the MySQL client connected to targetDb. If you saved the dump elsewhere, give the same full path in both commands. A failed export should be resolved before importing, so an old file is not mistaken for a fresh copy.

These are service-backed MySQL operations; they have not been executed as part of this article. The commands require a reachable MySQL server, valid credentials, and suitable privileges.

Copy the Database with a Pipe

To stream the dump directly into the destination without keeping an intermediate file, first ensure targetDb exists, then run this in the operating-system shell: mysqldump -u root -p firstDb | mysql -u root -p targetDb.

The pipe (|) passes the standard output from mysqldump to the standard input of mysql. Each command is a separate client connection, and each -p prompts for a password, so expect two prompts. Do not put passwords directly in the command, where shell history or process listings may expose them. This approach avoids a temporary dump file, but it also leaves you without that file to inspect or reuse. Like the file method, it is a one-time logical copy, not a recurring backup.

The image below illustrates the pipe command and its two password prompts. The command remains available as selectable text above.

The terminal example shows separate password prompts for the two clients.

Verify the Copy

Connect to the intended server with mysql -u root -p, then run SHOW DATABASES; to confirm the destination name is present. Select it with USE targetDb;, and list its tables with SHOW TABLES;. These checks show that the database and its tables are visible in that connection; they do not prove that every row, view, trigger, routine, or other object matches the source. For stronger verification, compare the objects and data that matter to your application.

Understand Password Prompts and Limitations

The -p option without a value prompts interactively; it does not mean that the password is supplied on the next command line. In the pipe workflow, mysqldump and mysql prompt independently because they are separate programs. An unattended process needs a secure credential setup appropriate to its environment; avoid embedding passwords in shell commands.

Both workflows copy one database on the same server and run once when invoked. They do not schedule daily backups, establish replication, or guarantee recovery from server or node failure. Special objects, storage-engine behavior, privileges, character sets, and server options can affect a migration, so review the dump and adapt the command for your environment when those details matter.