{"id":279,"date":"2024-06-30T15:11:34","date_gmt":"2024-06-30T15:11:34","guid":{"rendered":"https:\/\/www.rajeshkumar.xyz\/blog\/?p=279"},"modified":"2024-06-30T15:11:36","modified_gmt":"2024-06-30T15:11:36","slug":"how-to-upload-large-database-to-a-mysql-server","status":"publish","type":"post","link":"https:\/\/www.rajeshkumar.xyz\/blog\/how-to-upload-large-database-to-a-mysql-server\/","title":{"rendered":"How to upload large Database to a MySQL server"},"content":{"rendered":"\n<p>To upload a large database of 1.2 GB to a MySQL server, you need to modify several MySQL configurations to handle large file sizes and ensure smooth performance. Here are the key configuration settings you should adjust in your MySQL configuration file (<code>my.cnf<\/code> or <code>my.ini<\/code>), along with the steps to make these changes.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Key Configuration Settings to Modify<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>max_allowed_packet<\/strong>\n<ul class=\"wp-block-list\">\n<li>This setting controls the maximum size of a single packet or any generated\/intermediate string.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">&#91;mysqld]\nmax_allowed_packet = 1G\n<\/code><\/span><\/pre>\n\n\n<p><strong>innodb_buffer_pool_size<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>This setting determines the size of the buffer pool, which InnoDB uses to cache data and indexes.<\/li>\n<\/ul>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">&#91;mysqld]\ninnodb_buffer_pool_size = 2G\n<\/code><\/span><\/pre>\n\n\n<p><strong>innodb_log_file_size<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>This setting determines the size of each log file in the log group. Increasing this can improve performance during large data imports.<\/li>\n<\/ul>\n\n\n\n<p><strong>innodb_log_buffer_size<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>This setting controls the size of the buffer that InnoDB uses to write log files.<\/li>\n<\/ul>\n\n\n\n<p><strong style=\"font-size: revert; color: var(--wp--preset--color--body-text); font-family: var(--wp--preset--font-family--inter);\">bulk_insert_buffer_size<\/strong><ul><li>This setting affects the buffer size used for bulk inserts into MyISAM tables.<\/li><\/ul><\/p>\n\n\n\n<p><strong>net_buffer_length<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>This setting determines the initial size of the buffer used for client\/server communication.<\/li>\n<\/ul>\n\n\n\n<p><strong>innodb_flush_log_at_trx_commit<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Setting this to <code>2<\/code> can improve performance during large imports, but it may reduce durability.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Example Configuration<\/h3>\n\n\n\n<p>Below is an example of a <code>my.cnf<\/code> configuration file with the recommended changes:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">&#91;mysqld]\nmax_allowed_packet = 1G\ninnodb_buffer_pool_size = 2G\ninnodb_log_file_size = 512M\ninnodb_log_buffer_size = 128M\nbulk_insert_buffer_size = 512M\nnet_buffer_length = 32K\ninnodb_flush_log_at_trx_commit = 2\n<\/code><\/span><\/pre>","protected":false},"excerpt":{"rendered":"<p>To upload a large database of 1.2 GB to a MySQL server, you need to modify several MySQL configurations to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-279","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts\/279","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/comments?post=279"}],"version-history":[{"count":1,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts\/279\/revisions"}],"predecessor-version":[{"id":280,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/posts\/279\/revisions\/280"}],"wp:attachment":[{"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/media?parent=279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/categories?post=279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rajeshkumar.xyz\/blog\/wp-json\/wp\/v2\/tags?post=279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}