Use SQL statements to import big data files from mysql

Source: Internet
Author: User

For those who often use MYSQL, phpmyadmin is a necessary tool. This tool is very powerful and can complete almost all database operations, but it also has a weakness. It will be very slow when importing large data files to the remote server, even if there is no response for a long time.

Why is this happening? When selecting and submitting an SQL data file, the server first needs to upload the file to the server before executing the import code to import the data to the database. We know that phpmyadmin is a data file uploaded through the web, and the web upload is very unstable, especially when the network speed is slow, this is why we have waited for so many times in front of the computer, but ultimately there is no result.

Through the above analysis, we know that this problem is caused by web upload rather than the import program, so it is easy to avoid the problem of web upload. We may think of the powerful ftp upload tool, but phpmyadmin cannot select files on the remote server, which is very depressing.

Here, we will discard phpmysqladmin and use mysql statements to import the database. A lot of people will wonder how to execute SQL statements. This is a key issue. This requires your space to support SSH (Secure Shell Protocol), and you have to run some linux commands.

First, log on to the server through SSH (I use the putty logon tool), find our SQL file through some simple linux commands, and then run the following code:

Mysql db_name <data_file. SQL

If no prompt is displayed after the command is executed, the data has been imported successfully (note that the file format is date_file. SQL and SQL .gz may fail in Chinese ).

-------- 2008.12.9 update --------

Sometimes you may encounter an error message saying that you do not have sufficient permissions. Then we need to add some parameters.

Mysql-hlocalhost-uusername-p db_name <data_file. SQL

After you press enter, you will be prompted to enter the password, and then enter your database password to import it.

The-h parameter specifies the imported server, the-u parameter indicates the database user name, and the-p parameter indicates the password for logging on to the database. No space is required for the three parameters.

-------- 2008.12.9 update --------

This is a good method, but not everyone can use this method, because the premise is that your space must support SSH Login, there is still a small amount of space to support SSH Login, which is a pity.

Another way is to extract the import function module in phpmyadmin and change it slightly to achieve the same effect. Of course, if you are a PHP expert, you can also write an imported program by yourself. You can directly select a server path and look forward to ing...

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.