{"id":72429,"date":"2024-10-02T10:38:10","date_gmt":"2024-10-02T10:38:10","guid":{"rendered":"https:\/\/supporthost.com\/?post_type=tutorial&#038;p=72429"},"modified":"2024-10-02T10:38:11","modified_gmt":"2024-10-02T10:38:11","slug":"import-large-databases-mysql","status":"publish","type":"tutorial","link":"https:\/\/supporthost.com\/tutorial\/import-large-databases-mysql\/","title":{"rendered":"How to import large databases"},"content":{"rendered":"\n<p>Normally, to import a database, we can use <a href=\"https:\/\/supporthost.com\/tutorial\/import-databases\/\" data-type=\"tutorial\" data-id=\"19495\">phpMyAdmin&#8217;s import tool<\/a>. However, there are cases when we need to import <strong>large databases<\/strong>, and the import process fails either because of a maximum size limit or because the process <em>times out<\/em>.<\/p>\n\n\n\n<p>In all cases where phpMyAdmin cannot be used, we can import the database using an alternative solution:<\/p>\n\n\n\n<ul>\n<li>Use a <strong>program <\/strong>such as Sequel Ace, Sequel Pro, or HeidiSQL.<\/li>\n\n\n\n<li>Use a <strong>terminal command<\/strong> for importing.<\/li>\n<\/ul>\n\n\n\n<p>Let&#8217;s see how to do it using both methods.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Using a program to import the database<\/h2>\n\n\n\n<p>Several programs allow us to manage databases remotely. For macOS, you can use <a href=\"https:\/\/sequel-ace.com\/\" rel=\"noopener\">Sequel Ace<\/a> or <a href=\"https:\/\/sequelpro.com\/\" rel=\"noopener\">Sequel Pro<\/a>.<\/p>\n\n\n\n<p>For Windows, you can use <strong>HeidiSQL<\/strong>, which is fully compatible with Windows 10 and 11, and can also be used with Windows 7, 8, and on Wine (although in the latter cases you may encounter some errors).<\/p>\n\n\n\n<p>Let&#8217;s walk through how to do it using HeidiSQL.<\/p>\n\n\n\n<p>Before proceeding, you need to enable remote connections to the database. To do this, go to cPanel and find the \u201c<strong>Remote MySQL<\/strong>\u201d tool.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"223\" src=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/cpanel-remote-mysql.png\" alt=\"Cpanel Remote Mysql\" class=\"wp-image-72431\" srcset=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/cpanel-remote-mysql.png 1024w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/cpanel-remote-mysql-300x65.png 300w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/cpanel-remote-mysql-768x167.png 768w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/cpanel-remote-mysql-120x26.png 120w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Here, you can add your computer&#8217;s IP address so that you can connect to the database. You can find your IP using <a href=\"https:\/\/ip.supporthost.com\/\" data-type=\"link\" data-id=\"https:\/\/ip.supporthost.com\/\">our tool<\/a>.<\/p>\n\n\n\n<p>Enter the IP in the \u201c<em>Host<\/em>\u201d field and then click on the \u201c<em>Add Host<\/em>\u201d button.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"473\" src=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/remote-mysql-add-ip.png\" alt=\"Remote Mysql Add Ip\" class=\"wp-image-72432\" srcset=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/remote-mysql-add-ip.png 1024w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/remote-mysql-add-ip-300x139.png 300w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/remote-mysql-add-ip-768x355.png 768w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/remote-mysql-add-ip-120x55.png 120w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Now, we can <a href=\"https:\/\/www.heidisql.com\/download.php\" data-type=\"link\" data-id=\"https:\/\/www.heidisql.com\/download.php\" rel=\"noopener\">donwload HeidiSQL<\/a> and install it on our computer, or start it if you&#8217;re using the <em>portable <\/em>version.<\/p>\n\n\n\n<p>After starting HeidiSQL, add a <strong>new session<\/strong> by clicking \u201c<em>New<\/em>.\u201d<\/p>\n\n\n\n<p>You will be prompted for the data to connect to the database:<\/p>\n\n\n\n<ul>\n<li><strong>Host name<\/strong>: the name or IP of the server.<\/li>\n\n\n\n<li><strong>User<\/strong>: the username connected to the database.<\/li>\n\n\n\n<li><strong>Password<\/strong>: the user\u2019s password.<\/li>\n\n\n\n<li><strong>Port<\/strong>: usually, you can leave the default one.<\/li>\n<\/ul>\n\n\n\n<p>Once all the fields are filled in, click \u201c<em>Open<\/em>\u201d to start the connection.<\/p>\n\n\n\n<p>If the data is correct, you will see the contents of the database. At this point, you can <strong>import your .sql file<\/strong> by clicking on <em>File &gt; Load SQL File<\/em> and selecting the .sql file to import.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"364\" src=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/heidisql-load-sql-file-1024x364.png\" alt=\"Heidisql Load Sql File\" class=\"wp-image-72433\" srcset=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/heidisql-load-sql-file-1024x364.png 1024w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/heidisql-load-sql-file-300x107.png 300w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/heidisql-load-sql-file-768x273.png 768w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/heidisql-load-sql-file-120x43.png 120w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/heidisql-load-sql-file.png 1474w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>For <strong>large databases<\/strong>, you may see a warning asking whether to run the file directly or load it into the editor. It\u2019s usually preferable to <strong>run it directly<\/strong> to avoid saturating memory and causing the program to crash.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"496\" src=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/import-large-database-file-heidisql-1024x496.png\" alt=\"Import Large Database File Heidisql\" class=\"wp-image-72434\" srcset=\"https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/import-large-database-file-heidisql-1024x496.png 1024w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/import-large-database-file-heidisql-300x145.png 300w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/import-large-database-file-heidisql-768x372.png 768w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/import-large-database-file-heidisql-120x58.png 120w, https:\/\/supporthost.com\/wp-content\/uploads\/2024\/08\/import-large-database-file-heidisql.png 1473w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>At this point, all you have to do is wait for the database import to complete.<\/p>\n\n\n\n<p>Now, let&#8217;s see how to import a large MySQL database from the <strong>terminal<\/strong>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Importing large databases from the terminal<\/h2>\n\n\n\n<p>As an alternative to the method described above, you can upload the .sql file to the server and then import it using a <strong>terminal command<\/strong>.<\/p>\n\n\n\n<p>First, you need to upload the file to the server\u2019s root folder. You can use <a href=\"https:\/\/supporthost.com\/filezilla\/\" data-type=\"post\" data-id=\"18692\">FileZilla<\/a> or the cPanel <a href=\"https:\/\/supporthost.com\/tutorial\/file-manager\/\" data-type=\"tutorial\" data-id=\"17616\">file manager<\/a>.<\/p>\n\n\n\n<p class=\"note\">With all SupportHost hosting plans, including <a href=\"https:\/\/supporthost.com\/shared-hosting\/\" data-type=\"link\" data-id=\"https:\/\/supporthost.com\/shared-hosting\/\">shared hosting<\/a>, an SSH connection is available, and the terminal can be accessed from cPanel.<\/p>\n\n\n\n<p>To proceed, you can open the terminal from cPanel or <a href=\"https:\/\/supporthost.com\/ssh-connection\/\" data-type=\"link\" data-id=\"https:\/\/supporthost.com\/ssh-connection\/\">connect via SSH<\/a> and run the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u db_user -p db_name &lt; filesql.sql<\/code><\/pre>\n\n\n\n<p>You need to replace the fields with the correct values:<\/p>\n\n\n\n<ul>\n<li><strong>db_user<\/strong>: replace with the database username.<\/li>\n\n\n\n<li><strong>db_name<\/strong>: the name of the database.<\/li>\n\n\n\n<li><strong>filesql.sql<\/strong>: specify the name and path of the .sql file you want to import.<\/li>\n<\/ul>\n\n\n\n<p>After running the command, you will be prompted for the <strong>database password<\/strong>. Once entered, wait for the import to complete. If there are any issues, you\u2019ll see an error message; otherwise, no warning will appear.<\/p>\n\n\n\n<p>If the file you uploaded is <strong>compressed<\/strong>, such as \u201c.sql.gz,\u201d you can extract it directly from the terminal using the <code>gzip<\/code> command like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>gzip -d database.sql.gz<\/code><\/pre>\n\n\n\n<p>Make sure to enter the correct name of the compressed database instead of \u201cdatabase.sql.gz.\u201d<\/p>\n","protected":false},"author":3,"featured_media":72435,"template":"","tutorial-cat":[14],"class_list":["post-72429","tutorial","type-tutorial","status-publish","has-post-thumbnail","hentry","tutorial-cat-database"],"acf":[],"_links":{"self":[{"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/tutorial\/72429","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/tutorial"}],"about":[{"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/types\/tutorial"}],"author":[{"embeddable":true,"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/users\/3"}],"version-history":[{"count":0,"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/tutorial\/72429\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/media\/72435"}],"wp:attachment":[{"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/media?parent=72429"}],"wp:term":[{"taxonomy":"tutorial-cat","embeddable":true,"href":"https:\/\/supporthost.com\/wp-json\/wp\/v2\/tutorial-cat?post=72429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}