How to import a huge sql file/database in mysql

Step 1: Verify the SQL File Path and Permissions

Open a terminal window.

Use the ls -l command to check the file path and permissions:

bash

Copy code

ls -l /home/engineer/Desktop/fMadpstXVWBe1AjWNaJiJdY9fp5CI5.sql

Output example:

bash

Copy code

-rw-rw-r-- 1 engineer engineer 1061496962 Dec 1 2023 /home/engineer/Desktop/fMadpstXVWBe1AjWNaJiJdY9fp5CI5.sql

This output confirms that:

The file exists in the specified location.

It has read permissions (rw-rw-r--), meaning it’s accessible for reading.

Step 2: Use the MySQL Command to Import the File

Run the following command to import the SQL file into MySQL. Replace fMadpstXVWBe1AjWNaJiJdY9fp5CI5 with your database name:

bash

Copy code

sudo /opt/lampp/bin/mysql -u root -p fMadpstXVWBe1AjWNaJiJdY9fp5CI5 < /home/engineer/Desktop/fMadpstXVWBe1AjWNaJiJdY9fp5CI5.sql

Here’s a breakdown of this command:

sudo: Runs the command with superuser privileges, which may be required for large file imports.

/opt/lampp/bin/mysql: Path to the MySQL binary in the LAMPP installation.

-u root: Specifies the MySQL username, in this case, root.

-p: Prompts for the MySQL password.

fMadpstXVWBe1AjWNaJiJdY9fp5CI5: The name of the database where the file will be imported.

< /path/to/file.sql: Points to the SQL file you want to import.

After you run this command, you will be prompted to enter your MySQL password. Enter the password and press Enter.

Step 3: Monitor the Import Process

The import process will start immediately. It may take a while, especially if the file is large. If no errors are displayed, the import was successful.

Troubleshooting Tips

If you encounter any errors due to the file size or configuration limits:

Increase the MySQL max_allowed_packet Limit

Open the MySQL configuration file:

bash

Copy code

sudo nano /opt/lampp/etc/my.cnf

Under the [mysqld] section, add or modify:

ini

Copy code

max_allowed_packet=1G

Save the file and restart LAMPP:

bash

Copy code

sudo /opt/lampp/lampp restart

Split the SQL File

For extremely large files, consider splitting them into smaller chunks and importing each chunk separately.

Following these steps will help you successfully import a large SQL file into MySQL on Ubuntu using the terminal.