SoFunction
Updated on 2025-03-02

How mysql imports all table structures and data from a database into another library

1. Problem description

  • Developing a function locally requires testing whether the code is correct based on specific data, but the data on the server cannot be modified casually. At this time, you need to batch import the tables of a database on the server to the local area and then test them.
  • Bulk imports all tables and data in database db1 (such as table1 and table2) into another database db2.

2. Solution

At the beginning, I have been trying to import and export using DataGrip and Navicat's SQL/csv formats. There are always errors or omissions, such as an error abort, 50 tables but only 32 pieces are imported, etc. It may be because of the error operation method or the data format is wrong.

I also tried using SQL statements, but it still failed. After reviewing the information and trying many times, it was finally successful. The following records are recorded.

1. Use the mysqldump command to back up the database

First, use the mysqldump command to back up the source database.

mysqldump is a command line tool that comes with MySQL. You can back up the entire database by executing the following commands in cmd (windows) or Linux terminal (Linux):

mysqldump -u <username> -p <database_name> >

Among them, it is the user name of the database, <database_name> is the name of the database to be backed up, and the name of the backup file.

After executing this command, a file named "named" will be generated in the current directory, which contains all the table structure and data of the source database.

For some other export statements, please refer to the following:

//Export the complete database structure and datamysqldump -u username -p Database name &gt; Exported file name  
//Export the database structuremysqldump -u username -p -d Database name &gt; Exported file name 
//Export a table, including table structure and datamysqldump -u username -p Database name Table name&gt; Exported file name     
//Export a table structuremysqldump -u username -p -dDatabase name  Table name&gt; Exported file name

Note:

It is recommended to use mysqldump to back up the database, which can completely preserve the structure and data of the source database.

DataGrip and Navicat import and export, due to the possible differences between the two databases, the data structure and format will change, resulting in the replication failure.

2. Create a target database

You need to create the corresponding database in the target database. You can use MySQL's command line tool to log in to the target database and execute the following command to create the database:

create database <database_name>;

Where <database_name> is the name of the target database.

3. Import data to the target database

With the backup file of the source database and the target database, you can use MySQL's command line tool to import the backup SQL statements into the target database.

Enter the directory you are in (if you are importing from the server to the local machine, you can download the files generated on the server to the local area), and execute the following command in the terminal of the target database:

mysql -u <username> -p <database_name> <

Among them, it is the user name of the target database, and <database_name> is the name of the target database, and it is the backup file of the source database.

After executing this command, the target database will contain all the table structures and data exactly the same as the source database.

3. Supplementary knowledge

3.1 Introduction to mysqldump command

[root@localhost ~]# mysqldump -help
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

Login options

1.Login options：
    -u user：Specify user
    -h host：Specify host
    -p：Indicates that you want to use a password
    -E, --events: Backup event scheduler
    -R, --routines: Backup stored procedures and stored functions
2.Backup options：
    --all-databases：Back up all databases
    --databases db1 db2：Backup the specified database
    --single-transaction：Execute hot standby for transaction engines
    --flush-logs：Update binary log files
    --master-data=2
        1：A new binary file is generated for each backup of a library.(default)
        2：Only generate a new binary file
    --quick：Specify this option when backing up large tables

3.2 Common backup commands

//Export command#Export all databases including system database (save under the bin folder by default)	mysqldump -u username -p --all-databases &gt; 
#Export the specified database table structure and data	mysqldump -u username -p dbname [tbname...] &gt; 	
#Export multiple databases:	mysqldump -u username -p --databases db1 db2 ...&gt; 
#Export multiple tables:	mysqldump -u username -p --databases db1 --tables t1 t2&gt; 
#Only export table structure and not export table data, add "-d" command parameter    mysqldump -u username -p -d dbname [tbname...] &gt; 
#Only export table data but not table structure, add "-t" command parameter    mysqldump -u username -p -t dbname [tbname...] &gt; d:/
#Only export the table structure of db1 database Export：mysqldump -u root -p --no-data --databases db1 &gt; 
 Import：mysql -u root -p db1 &lt; 
 
 
#System Line Import Command  mysql -u username -p dbname &lt; d:(path) 
  mysql -u root -p db_name &lt; d:/
#source import  mysql&gt; use db_name;
  mysql&gt; source /backup/mysqldump/(path)

Parameter description:

  • username: Indicates the user name;
  • dbname: Indicates the name of the database that needs to be backed up;
  • tbname: Indicates the data table that needs to be backed up in the database, and multiple data tables can be specified. When this parameter is omitted, the entire database will be backed up;
  • Right arrow ">": Used to tell mysqldump to write the definition and data of the backup data table to the backup file;
  • : Indicates the name of the backup file, and an absolute path can be added before the file name. Usually the database is backed up into a file with the suffix named .sql.

Notice:

The mysqldump command must be executed in the cmd window and cannot be executed in the MySQL service.

After entering the password, Mysql will back up.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.