{"id":1886,"date":"2021-05-07T17:00:19","date_gmt":"2021-05-07T15:00:19","guid":{"rendered":"https:\/\/itsimple.info\/?p=1886"},"modified":"2021-05-07T17:00:20","modified_gmt":"2021-05-07T15:00:20","slug":"how-to-preform-mysql-database-backup-on-centos-with-mariadb","status":"publish","type":"post","link":"https:\/\/itsimple.info\/?p=1886","title":{"rendered":"How To preform MySql database backup on CentOS with MariaDB"},"content":{"rendered":"\n<p>We are going to use <a rel=\"noreferrer noopener\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqlpump.html\" target=\"_blank\"><strong>mysqldump<\/strong><\/a> to create a backup dump of the database, to backup all the databases, create backup folder :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mkdir \/var\/lib\/mysql\/backup<\/code><\/pre>\n\n\n\n<p>Now let check the databases size :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p\nSELECT table_schema AS \"Database\", SUM(data_length + index_length) \/ 1024 \/ 1024 \/ 1024 AS \"Size (GB)\" FROM information_schema.TABLES GROUP BY table_schema ;<\/code><\/pre>\n\n\n\n<p>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<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>yum install pv<\/code><\/pre>\n\n\n\n<p>Now Lets create some Database dumps, the basic is to dump all the database :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump -u root -p --all-databases | pv -W > \/var\/lib\/mysql\/backup\/all-database.sql<\/code><\/pre>\n\n\n\n<p>Note that the\u00a0<code>--all-databases<\/code>\u00a0option is applicable to backup only. The backup file itself will contain all the relevant\u00a0<code>CREATE DATABASE quux;<\/code>\u00a0commands for the restore.<\/p>\n\n\n\n<p>Make sure the dump is created <\/p>\n\n\n\n<pre id=\"block-05a27d72-2c17-4062-808b-430b1cf07bd5\" class=\"wp-block-code\"><code>ls -1 -s \/var\/lib\/mysql\/backup\/<\/code><\/pre>\n\n\n\n<p>To create zipped backup use :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump --single-transaction -u root -p 'Database name' | gzip | pv -W > \/var\/lib\/mysql\/backup\/'Database Name'.sql.gz<\/code><\/pre>\n\n\n\n<p>To restore all the databases :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p --all-databases &lt; \/var\/lib\/mysql\/backup\/all-database.sql<\/code><\/pre>\n\n\n\n<p>To restore specific database <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p 'Database name' &lt; \/var\/lib\/mysql\/backup\/'Database name'.sql<\/code><\/pre>\n\n\n\n<p>To restore from zipped backup\/dump <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>zcat \/var\/lib\/mysql\/backup\/'Database Name'.sql.gz | mysql -u root -p 'Database Name'<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-pullquote\"><blockquote><p>Good Luck<\/p><\/blockquote><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>We are going to use mysqldump to create a backup dump of the database, to backup all the databases, create backup folder : Now let check the databases size : 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, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,13,21,4,12,1],"tags":[],"class_list":["post-1886","post","type-post","status-publish","format-standard","hentry","category-centos","category-linux","category-operating-systems","category-tech","category-tutorials","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts\/1886","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1886"}],"version-history":[{"count":0,"href":"https:\/\/itsimple.info\/index.php?rest_route=\/wp\/v2\/posts\/1886\/revisions"}],"wp:attachment":[{"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itsimple.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}