Skip to content

Import a (large) SQL file into MySQL

mysql square 206 - Import a (large) SQL file into MySQL

MySQL is a great resource to easily process large amounts of data. The PhpMyAdmin interface is very useful to facilitate the reading of this data. However, importing it from a data file through PhpMyAdmin is limited. To import a large stream of information, a large table or even to restore a backup that can range from a few megabytes to several gigabytes, it is convenient to use command lines and this will be the goal of this article. article.

MySQL Logo

Prerequisite

This little tutorial requires you to know a minimum of using the command lines. Also remember to make a backup of your database before doing anything you might regret.

Send the file to MySQL in one line

Rather than a long speech here is now the command which allows both to connect to MySQL from the command line and at the same time to send the file to the database:

mysql -h localhost -p -u   < gros_fichier.sql

Explanations:

  • mysql: the command line tool to access MySQL.
  • -h localhost: connection to the MySQL server on the indicated “host”. In a large majority of cases it is sufficient to leave “localhost”.
  • -p: allows you to set the password. If this option is left blank, the command will prompt for the password once you click the “enter” button.
  • -u : the term between brackets is of course to be replaced by your username in MySQL (cf. the same username used to connect to PhpMyAdmin).
  • : to be replaced by the name of the database into which the SQL file will be imported (see the name of the database that interests you in the left menu of PhpMyAdmin).
  • big_file.sql: the file must of course be available on the server which contains the MySQL Database Management System (DBMS). You must indicate the location of the file concerned (fictitious example: /home/user/mysql/mon_fichier.sql).

Nothing more to add for importing a .sql file. Be careful though, if this does not work, you must check in MySQL that the variable « max_allowed_packet » is large enough to allow the addition of a fairly large file (to be adapted according to your needs if necessary).