{"id":8187,"date":"2019-08-29T10:28:04","date_gmt":"2019-08-29T10:28:04","guid":{"rendered":"https:\/\/www.milesweb.in\/hosting-faqs\/?p=8187"},"modified":"2022-02-19T12:28:35","modified_gmt":"2022-02-19T06:58:35","slug":"how-to-optimize-a-mysql-database-using-ssh","status":"publish","type":"post","link":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/","title":{"rendered":"How to Optimize a MySQL Database Using SSH?"},"content":{"rendered":"<p>By optimizing your MySQL database tables you can make improvements to your website. The optimizing function helps in reorganizing the table and index data, reducing space and improving I\/O efficiency.<\/p>\n<p>In this guide, you will learn to optimize your tables via SSH.<\/p>\n<h3>Steps to Optimize Your MySQL Database Using SSH<\/h3>\n<p>Sometimes if you run an optimization via phpMyAdmin, it leads to failure and the page ultimately times out. The reason behind this is the large size of database which the phpMyAdmin can\u2019t handle. So, the alternative method is to optimize via SSH. If you are a shell user, you can SSH into your domain and perform the following command which is the same function run on phpMyAdmin:<\/p>\n<pre class=\"lang:default decode:true \">[server]$ mysql -u username -ppassword -h hostname databasename -e \"show tables\" | grep -v Tables_in | grep -v \"+\" | gawk '{print \"optimize table \" $1 \";\"}' | mysql -u username -ppassword -h hostname databasename<\/pre>\n<p>In the above command, replace the variables with your actual database information:<\/p>\n<ul>\n<li>username \u2013 database username<\/li>\n<li>password \u2013 user\u2019s password<\/li>\n<li>hostname \u2013 database hostname that you set up and is active<\/li>\n<li>databasename \u2013 the database\u2019s name in which you\u2019re running the command<\/li>\n<\/ul>\n<p>You can get the log of the command into a file for viewing by adding the below to the end of the command:<\/p>\n<pre class=\"lang:default decode:true \">&gt;&gt; results.txt<\/pre>\n<h3>Using a cron job to Optimize<\/h3>\n<p>Using a shell script and\/or cron job using <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysqlcheck.html\" target=\"_blank\" rel=\"nofollow noopener\">mysqlcheck<\/a>, you can also cleanup overhead which checks, repairs, and optimizes tables. It&#8217;s best to run a monthly cron job to do this for you. The cron job might look as below:<\/p>\n<pre class=\"lang:default decode:true \">#!\/bin\/sh\n\/usr\/bin\/mysqlcheck -o -v -u USER -p PASSWORD -h MYSQL.EXAMPLE.COM DATABASENAME;<\/pre>\n<p>The items in all-caps need to be replaced with the actual credentials for your database.<\/p>\n<h3>Repairing via SSH<\/h3>\n<p>A command can be run to repair a table instead of optimizing it:<\/p>\n<pre class=\"lang:default decode:true\">[server]$ mysql -u username -ppassword -h hostname databasename -e \"show tables\" | grep -v Tables_in | grep -v \"+\" | gawk '{print \"repair table \" $1 \";\"}' | mysql -u username -ppassword -h hostname databasename\n<\/pre>\n<p>In this way, you can optimize a MySQL database using SSH.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By optimizing your MySQL database tables you can make improvements to your website. The optimizing function helps in reorganizing the table and index data, reducing space and improving I\/O efficiency. In this guide, you will learn to optimize your tables via SSH. Steps to Optimize Your MySQL Database Using SSH Sometimes if you run an [&hellip;]<\/p>\n","protected":false},"author":16,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[230],"tags":[205,469,995],"class_list":["post-8187","post","type-post","status-publish","format-standard","placeholder-for-hentry","category-database","tag-mysql","tag-mysql-database","tag-mysql-database-optimization"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.6 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How to Optimize a MySQL Database Using SSH?<\/title>\n<meta name=\"description\" content=\"The article describes the ways to optimize a MySQL database using SSH.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Optimize a MySQL Database Using SSH?\" \/>\n<meta property=\"og:description\" content=\"The article describes the ways to optimize a MySQL database using SSH.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/\" \/>\n<meta property=\"og:site_name\" content=\"Web Hosting FAQs by MilesWeb\" \/>\n<meta property=\"article:published_time\" content=\"2019-08-29T10:28:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-19T06:58:35+00:00\" \/>\n<meta name=\"author\" content=\"Pallavi Godse\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Pallavi Godse\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/\"},\"author\":{\"name\":\"Pallavi Godse\",\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/#\\\/schema\\\/person\\\/7e3952607fa9eb4e82fea9f7cad9c945\"},\"headline\":\"How to Optimize a MySQL Database Using SSH?\",\"datePublished\":\"2019-08-29T10:28:04+00:00\",\"dateModified\":\"2022-02-19T06:58:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/\"},\"wordCount\":281,\"commentCount\":0,\"keywords\":[\"MySQL\",\"MySQL database\",\"MySQL database optimization\"],\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/\",\"url\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/\",\"name\":\"How to Optimize a MySQL Database Using SSH?\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/#website\"},\"datePublished\":\"2019-08-29T10:28:04+00:00\",\"dateModified\":\"2022-02-19T06:58:35+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/#\\\/schema\\\/person\\\/7e3952607fa9eb4e82fea9f7cad9c945\"},\"description\":\"The article describes the ways to optimize a MySQL database using SSH.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/how-to-optimize-a-mysql-database-using-ssh\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Optimize a MySQL Database Using SSH?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/#website\",\"url\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/\",\"name\":\"Web Hosting FAQs by MilesWeb\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/#\\\/schema\\\/person\\\/7e3952607fa9eb4e82fea9f7cad9c945\",\"name\":\"Pallavi Godse\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g\",\"caption\":\"Pallavi Godse\"},\"description\":\"Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.\",\"sameAs\":[\"https:\\\/\\\/www.milesweb.in\"],\"url\":\"https:\\\/\\\/www.milesweb.in\\\/hosting-faqs\\\/author\\\/pallavi\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Optimize a MySQL Database Using SSH?","description":"The article describes the ways to optimize a MySQL database using SSH.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/","og_locale":"en_US","og_type":"article","og_title":"How to Optimize a MySQL Database Using SSH?","og_description":"The article describes the ways to optimize a MySQL database using SSH.","og_url":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/","og_site_name":"Web Hosting FAQs by MilesWeb","article_published_time":"2019-08-29T10:28:04+00:00","article_modified_time":"2022-02-19T06:58:35+00:00","author":"Pallavi Godse","twitter_misc":{"Written by":"Pallavi Godse","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/#article","isPartOf":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/"},"author":{"name":"Pallavi Godse","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945"},"headline":"How to Optimize a MySQL Database Using SSH?","datePublished":"2019-08-29T10:28:04+00:00","dateModified":"2022-02-19T06:58:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/"},"wordCount":281,"commentCount":0,"keywords":["MySQL","MySQL database","MySQL database optimization"],"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/","url":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/","name":"How to Optimize a MySQL Database Using SSH?","isPartOf":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#website"},"datePublished":"2019-08-29T10:28:04+00:00","dateModified":"2022-02-19T06:58:35+00:00","author":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945"},"description":"The article describes the ways to optimize a MySQL database using SSH.","breadcrumb":{"@id":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/how-to-optimize-a-mysql-database-using-ssh\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.milesweb.in\/hosting-faqs\/"},{"@type":"ListItem","position":2,"name":"How to Optimize a MySQL Database Using SSH?"}]},{"@type":"WebSite","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#website","url":"https:\/\/www.milesweb.in\/hosting-faqs\/","name":"Web Hosting FAQs by MilesWeb","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.milesweb.in\/hosting-faqs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.milesweb.in\/hosting-faqs\/#\/schema\/person\/7e3952607fa9eb4e82fea9f7cad9c945","name":"Pallavi Godse","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3602d0ac1ab89332aea8205dcf474af4ff4eb319cfd584544038908cd5e4c966?s=96&d=blank&r=g","caption":"Pallavi Godse"},"description":"Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.","sameAs":["https:\/\/www.milesweb.in"],"url":"https:\/\/www.milesweb.in\/hosting-faqs\/author\/pallavi\/"}]}},"_links":{"self":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts\/8187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/comments?post=8187"}],"version-history":[{"count":3,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts\/8187\/revisions"}],"predecessor-version":[{"id":16216,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/posts\/8187\/revisions\/16216"}],"wp:attachment":[{"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/media?parent=8187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/categories?post=8187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.milesweb.in\/hosting-faqs\/wp-json\/wp\/v2\/tags?post=8187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}