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.
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
- 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).