{"id":5437,"date":"2023-11-10T08:22:22","date_gmt":"2023-11-10T08:22:22","guid":{"rendered":"https:\/\/natsav.com\/blog\/?p=5437"},"modified":"2024-02-07T12:15:56","modified_gmt":"2024-02-07T12:15:56","slug":"backup-mysql-on-centos-7","status":"publish","type":"post","link":"https:\/\/natsav.com\/blog\/backup-mysql-on-centos-7\/","title":{"rendered":"How To Take Backup of MySQL on Centos 7."},"content":{"rendered":"<p><strong>Step 1<\/strong> : We are going to use mysqldump to create a backup dump of the database, to backup all the databases, create backup folder.<br \/>\n[ mkdir \/var\/lib\/mysql\/backup]<\/p>\n<p><strong>Step 2 : <\/strong>Lets check the databases size:<br \/>\n[ mysql -u root -p SELECT table_schema AS &#8220;Database&#8221;, SUM(data length + index length) \/ 1024 \/ 1024 \/ 1024 AS &#8220;Size (GB)&#8221; FROM information_schema.TABLES GROUP BY table_schema ; ]<\/p>\n<p><strong>Step 3:<\/strong> In case of a large database this can take a long time, you might want to install pv progress bar to get some information during, the data will be piped through the progress bar, for example =|pv -W.<br \/>\n[ yum install pv ]<\/p>\n<p><strong>Step 4: <\/strong>Now Lets create some Database dumps, the basic is to dump all the database.<br \/>\n[ bmysqldump -u root -p &#8211;all-databases | pv -W &gt; \/var\/lib\/mysql\/backup\/all-database.sql ]<\/p>\n<p><strong>Step 5:<\/strong> Make sure the dump is created.<br \/>\n[ ls -1 -s \/var\/lib\/mysql\/backup\/ ]<\/p>\n<p><strong>Step 6:<\/strong> To create zipped backup.<br \/>\n[ mysqldump &#8211;single-transaction -u root -p &#8216;Database name&#8217; | gzip | pv -W &gt; \/var\/lib\/mysql\/backup\/&#8217;Database Name&#8217;.sql.gz ]<\/p>\n<p><strong>Step 7:<\/strong> To restore all the databases.<br \/>\n[ mysql -u root -p &#8211;all-databases &lt; \/var\/lib\/mysql\/backup\/all-database.sql ]<\/p>\n<p><strong>Step 8:<\/strong> To restore specific database.<br \/>\n[ mysql -u root -p &#8216;Database name&#8217; &lt; \/var\/lib\/mysql\/backup\/&#8217;Database name&#8217;.sql ]<\/p>\n<p><strong>Step 9:<\/strong> To restore from zipped backup\/dump.<br \/>\n[ zcat \/var\/lib\/mysql\/backup\/&#8217;Database Name&#8217;.sql.gz | mysql -u root -p &#8216;Database Name&#8217; ]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Step 1 : We are going to use mysqldump to create a backup dump of the database, to backup all the databases, create backup folder. [ mkdir \/var\/lib\/mysql\/backup] Step 2 : Lets check the databases size: [ mysql -u root -p SELECT table_schema AS &#8220;Database&#8221;, SUM(data length + index length) \/ 1024 \/ 1024 \/ [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5452,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[256],"tags":[],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/natsav.com\/blog\/wp-content\/uploads\/2023\/11\/MySql-2-e1699606560165.jpg?fit=1000%2C665&ssl=1","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/posts\/5437"}],"collection":[{"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/comments?post=5437"}],"version-history":[{"count":2,"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/posts\/5437\/revisions"}],"predecessor-version":[{"id":5447,"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/posts\/5437\/revisions\/5447"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/media\/5452"}],"wp:attachment":[{"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/media?parent=5437"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/categories?post=5437"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/natsav.com\/blog\/wp-json\/wp\/v2\/tags?post=5437"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}