DEV Community: SimpleBackups

Mini Guide: How to Import a SQL File in MySQL

SimpleBackups — Mon, 07 Dec 2020 14:32:15 +0000

Have you just begun to learn how to work with SQL files using MySQL? Maybe you feel a bit lost on how to import files with this tool. Luckily, importing and exporting files via MySQL is actually quite simple. Learn how to use MySQL to import SQL files by following the step-by-step guide below.

Import a SQL file using Command Line

Open XAMPP.
Launch Apache Server and MySQL Database.
Create a database via phpMyAdmin.
Copy the SQL file of your choice to the xampp/mysql/bin/ directory.
Open Command Prompt.
Go to xampp/mysql/bin/.
Type: mysql -u username -p database_name < file.sql
- The username refers to your MySQL username.
- database_name refers to the database you want to import.
- file.sql is your file name.
If you've assigned a password, type it now and press Enter.
Open phpMyAdmin and select your database to ensure that the tables have imported properly.

Import a SQL file using mysqldump

To import a .sql file with mysqldump, use the mysqlimport command and use the following flags and syntax $ mysqlimport -u magazine_admin -p magazines_production ~/backup/database/magazines.sql
- -u and -p are needed for authentication, and is then followed by the name of the database you want to import into.
- You'll need to specify the path to your SQL dump file that will contain your import data: ~/backup/database/magazines.sql
- You won't need to use > or < for importing, but you will need them for exporting in the next guide.
This will prompt a password request.
Your file will be automatically imported.

Export a SQL file using mysqldump

To export a MySQL database to a test file, start by using the mysqldump command.
Log in to MySQL.
Enter the mysqldump command using the following flags and options: $ mysqldump -u my_username -p database_name > output_file_path
- The -u flag specifies the MySQL username.
- The -p flag specifies a password prompt associated with the above username.
- database_name is the name of the database you want to export.
- The > symbol is a Unix directive for STDOUT, which will make it possible for Unix commands to output the subsequent results of the output command to another location. These locations are usually file paths.
Be sure to input the completely qualified path and its filename to your output file path, so that your file will be placed exactly where you want it to be.
Once the command is executed, you'll be prompted to enter your password. This will then create your exported backup file with a .sql extension.

Notes:

After entering this command, you may be asked to enter the password for the MySQL user that you used.
Please be careful when using an existing database that has records as this command will overwrite your existing database and end up losing your records.

Automate MySQL Backups with SimpleBackups

SimpleBackups is a database and website backup automation tool. SimpleBackups automates MySQL backups by using MySQLDump to securely send backup files offsite to the cloud for storage. When you need to ensure your MySQL backups are secure, you can trust SimpleBackups to take care of it for you.

MySQL Errors Messages And Common Problems

SimpleBackups — Mon, 07 Dec 2020 14:12:22 +0000

Errors or mistakes are common in any aspects, especially in development. Using MySQL or any database can't guarantee you an error-free environment.

In this article, we will discuss the structure or anatomy of MySQL errors and how to read them. We've also picked the top 10 most common MySQL errors and their description.

The anatomy of MySQL errors

Each MySQL errors consists of the following parts that identify the error:

ERROR NUMBER is a unique number that identifies each error.
SQLSTATE is a code which identifies SQL error conditions.
ERROR MESSAGE describes the error in human readable format.

Here's an example MySQL error:

ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

In this example above:

1146 is the ERROR NUMBER
42S02 is the SQLSTATE
Table 'test.no_such_table' doesn't exist is the ERROR MESSAGE

Common MySQL Errors

Here are the lists of most common MySQL errors:

ERROR 1045 (HY000): Access denied for user 'root'@'localhost' (using password: YES)

This one is probably encountered at least once by anyone using MySQL. This error can have many causes, such as wrong username and/or password, or lacks of permission to the database.

ERROR 2013: Lost connection to MySQL server during query

This error happens when the connection between your MySQL client and database server times out. Essentially, it took too long for the query to return data so the connection gets dropped.

ERROR 1040: Too many connections

This error means that all available connections are in use by other clients.

ERROR 2006 (HY000): MySQL server has gone away

The common reason for this error is that the server timed out and closed the connection. By default, the server closes the connection after 8 hours if nothing has happened.

ERROR 2008: MySQL client ran out of memory

This means that MySQL does not have enough memory to store the entire query result:

ERROR 1114 (HY000): The table is full

If a table-full error occurs, it may be that the disk is full or that the table has reached its maximum size. The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

ERROR: Packet too large

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.

A 1 GB packet size is the largest possible packet size that can be transmitted to or from the MySQL server or client. The MySQL server or client issues an ER_NET_PACKET_TOO_LARGE error and closes the connection if it receives a packet bigger than max_allowed_packet bytes.

ERROR: Communication Errors and Aborted Connections

If you find errors like the following in your error log.

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'simplebackups'

This means that something of the following has happened:

The client program did not call mysql_close() before exit.
The client had been sleeping more than wait_timeout or interactive_timeout without doing any requests.
The client program ended abruptly in the middle of the transfer.

ERROR: Can’t create/write to file

This indicates that MySQL is unable to create a temporary file in the temporary directory for the result set if we get the following error while executing a query.

ERROR: Commands out of sync

If you get this error in your client code, you are calling client functions in the wrong order. For example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result().

Automate MySQL Backups with SimpleBackups

SimpleBackups is a database and website backup automation tool. SimpleBackups automates MySQL backups by using MySQLDump to securely send backup files offsite to your cloud storage. When you need to ensure your MySQL backups are secure, you can trust SimpleBackups to take care of it for you.