How to run a SQL script or file in MySQL & MariaDB

In database development, it is important to test the database with different data scenarios to ensure its functionality, performance, and reliability. Data import facilitates the population of test data sets, allowing developers to simulate real-world conditions and identify potential issues before deploying the database into production.

In the article, we'll delve into how to export and import MySQL data using a dump file, which will be created and then executed using the command line or an IDE tailored for MySQL or MariaDB - dbForge Studio for MySQL.

How to create a MySQL script file using the command line

One of the ways to generate a data export .sql file with a set of SQL statements is to use the command-line mysqldump utility. This file can be used to create tables and objects and insert data copied from the database. The utility allows you to dump specific tables, databases, or the entire server with all databases and tables, including their objects, or transfer them to another server. The syntax of the command is as follows:

mysqldump -u [user name] –p [password] -h [host name] [options] [database_name] [tablename] > [dumpfilename.sql]

where:

-u or --user=[username] specifies the MySQL username to connect to the database.
-p indicates that the utility should prompt for the MySQL user's password. After you enter the command, you'll be prompted to enter the password securely.
-h or --host=[hostname] specifies the hostname or IP address of the MySQL server to which you want to connect. If the MySQL server is running on the same machine where you're executing the command, you can omit this option.
options are optional parameters to customize the dump process.
[database_name] specifies the name of the MySQL database you want to dump.
[tablename] specifies a specific table within the database to be dumped. If you want to migrate the entire database, you can omit this option.
<> indicates whether to back up the database (>) or restore it (<).
[dumpfilename.sql] is a path to the dump file containing the SQL commands to recreate the database or table structure and insert the data.

Note: Since the mysqldump utility is a part of the mysql client package, you need to download and install the mysql client on your machine.

Let us now generate an export script for specific tables - city, country, and customer - from the sakila database using the mysqldump command.

To do this, open the Command Prompt and navigate to the MySQL server installation directory using the cd command. For example, for MySQL 8.0, the default path to the installation folder is C:\Program Files\MySQL\MySQL Server 8.0\bin.

After that, execute the following mysqldump command to create the data export file:

mysqldump --host=[hostname] --user=[username] --port=3306 -p [database] [table1] [table2] [table3] > D:\export_data.sql

Replace [hostname], [username], [database], [table1], [table2], [table3] and D:\export_data.sql with your actual data.

Create a MySQL script from the command line

The file with the exported data will appear in the specified folder.

Create a .sql dump file in IDE for MySQL and MariaDB

Now, we'll showcase how easy it is to create a .sql dump file with the MySQL database backup using dbForge Studio for MySQL, a feature-rich, versatile IDE for MySQL and MariaDB database development, management, and administration. The main features include:

Broad compatibility options, including MySQL server, MariaDB server, Percona PAM Authentication for MySQL, and Amazon RDS for MariaDB.
Advanced Source Control tool to version-control databases and manage database changes.
Comprehensive set of tools for intelligent SQL coding, such as code completion, flexible formatting, code snippets, simplified code navigation, quick object information, and instant syntax validation.
Visual Query Builder to build complex SQL queries using an intuitive graphical interface, without any need for manual SQL code writing.
Powerful schema comparison and synchronization tools to compare database schemas and synchronize them across different environments, which ensure consistency and integrity of database structures across development, testing, and production environments.
Advanced debugging tools, such as Query Profiler and SQL Debugger, to identify and optimize performance bottlenecks in their SQL queries.
Easy-to-use data export and import tools to migrate or back up data between MySQL databases and various file formats, including CSV, Excel, XML, etc.
Cross-platform compatibility with various operating systems, such as Windows, macOS, and Linux, making it accessible to a wide range of users regardless of their preferred platform.
Data Generator for quick and easy population of tables with random and meaningful data.

If you don't already have the Studio, you can download it from the Devart website and install it on your computer.

To proceed, open the Studio and connect to the database for which you want to create a .sql dump file. In Database Explorer, right-click the database and select Tasks > Backup Database. Alternatively, on the ribbon, select Database > Tasks > Backup Database.

The Database Backup Wizard opens. On the General page, do the following and click Next:

Select the database from which the data will be exported.
Specify a path to the output file that will contain the database backup.
Enter the name of the output file.
Optional: To exclude the date and time from the file name, clear the Append timestamp to the file name checkbox.

On the Backup content page, select database objects to back up and click Next to switch to the next page.

Note that Structure and Data and all database objects are selected by default. If you want to exclude any of them, clear the corresponding checkbox.

On the Options page, select or clear the checkboxes next to the options to customize the default behavior of the backup process.

On the Errors handling page, configure error handling behavior and logging options. Once done, click Backup to generate a .sql script.

On the Finish page, you can select the Open script checkbox. This will open the internal editor with the .sql script after you close the wizard.

Run a MySQL script file from terminal

Now, we import a .sql script file using the command line. To achieve this, use the mysql command:

mysql -u [username] –p [target_database_name] < [dumpfilename.sql]

where:

-u [user name] specifies the MySQL username to be used to connect to the MySQL server.
-p indicates that the utility should prompt for the MySQL user's password. After you enter the command, you'll be prompted to enter the password securely.
[target_database_name] specifies the name of the MySQL database where you want to import the data.
< is a parameter that refers to the process of a database restoration.
[dumpfilename.sql] is the path to the file that contains SQL commands to recreate the database structure and insert data.

To get started, create an empty database on the server that will contain the restored copy of the database. For demo purposes, create the sakila database on the demo-mysql MySQL server.

To restore the database from the file, do the following:

Open the Command Prompt.
Use the cd command to go to the MySQL Server installation folder. As mentioned before, the default path for MySQL 8.0 is C:\Program Files\MySQL\MySQL Server 8.0\bin.
Replace the parameters with your actual data in the mysql command and execute it:
```
mysql -u [username] –p [target_database_name] < [dumpfilename.sql]
```
Enter the password.

Execute a SQL file from the MySQL command line

After the database has been restored, we can verify whether the database objects and data have been copied to the empty database. To begin, connect to the required database using the following mysql command and replacing the parameters with the necessary connection details for your server:

mysql --host=[hostname] --user=[username] --password [database_name]

Then, check that the database objects from the dump file have been copied to the target database using the following command:

show tables;

As you can see, the command returns the list of all the tables from the sakila database.

Run a SQL script using the GUI tool for MySQL

Let's explore the process of restoring a database using a robust GUI tool, dbForge Studio for MySQL. Start by opening the Studio and creating an empty database to import data into it. In Database Explorer, right-click the connection on which you want to create the database for data export and select New Database. In the editor, specify the database name, set charset and collation options, and click Apply changes to save the changes.

Go back to Database Explorer to confirm the successful creation of the database.

Next, refresh Database Explorer, right-click the server connection on which you want to restore the database, and select Tasks > Restore Database. Alternatively, on the ribbon, select Database > Tasks > Restore Database. The Database Restore Wizard opens.

On the Database Script File page of the wizard, do the following:

Click Browse (...) in the SQL file name to choose the file from which the data will be exported.
If the database name from the script differs from the target database, specify the target name in the Database field. If the source and target database names are identical, leave the Database field as is - As in script.
Optional: Specify SQL file encoding.
Click Restore to restore the database.

On the Operation complete page, you will see a notification about the successful completion of the restore operation and the number of errors. Click Finish to close the wizard.

Return to Database Explorer and refresh it. As you can see, the database objects have been imported into the sales_new database. Then, expand the Tables and retrieve data from any table, for example, product. The output displays the grid with the data.

More advantages of dbForge Studio for MySQL for Export/Import

Here are some information that may help you decide on using dbForge Studio for MySQL for export/import tasks:

Support for frequently used file formats that allow users to tailor the export or import process to meet specific requirements and ensure compatibility with other systems or applications.
Export for specific tables, views, or query results rather than the entire database, which ensures export for only relevant data subsets and reduces export time and file size.
Schedule of export tasks at specified intervals and automation of repetitive export processes, such as regular data backup, reporting, or data synchronization tasks.
Ability to export only the database structure or the data that has changed since the last export. This option reduces export time and resource usage, especially for large databases with frequently updated data.
Ability to create and customize export templates, defining the structure and formatting of exported data files to ensure consistency across projects.
Handling of error processing behavior and logging options.
Customization of table grid options for exported data, providing flexibility in data presentation.
Import of the database to the same or different database server with options to import data into a new table or update the existing one.
Ability to select a data import mode based on the project requirements: Append, Update, Append/Update, Delete, or Repopulate.
Usage of automatic column mapping to optimize data recovery to the target destination for maximum performance and efficiency.
Import of the large MySQL database using the Execute Large Script wizard.

Conclusion

Data export and import are important operations in database development, testing, and management. dbForge Studio for MySQL with its intuitive interface can simplify, expedite, and automate backup and restore, export and import operations wherever possible. In addition to the Data Import and Export tools that come with dbForge Studio for MySQL, it is also possible to transfer data from a .sql file to the server or duplicate the database using the command line. However, this method is less convenient and user-friendly. Give a go to the Studio by taking advantage of its free 30-day trial period and evaluate its capabilities firsthand to witness increased productivity, faster task execution, and enhanced efficiency.