{"id":6877,"date":"2017-07-21T16:36:09","date_gmt":"2017-07-21T08:36:09","guid":{"rendered":"http:\/\/rmohan.com\/?p=6877"},"modified":"2017-07-21T16:36:09","modified_gmt":"2017-07-21T08:36:09","slug":"realize-mysql-database-automatic-backup-under-linux","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6877","title":{"rendered":"Realize MySQL database automatic backup under Linux"},"content":{"rendered":"<p>1.Linux under MySQL to create a user backup role, and granted the role of SELECT, RELOAD, SHOW DATABASES, LOCK TABLES and other permissions.<\/p>\n<p>&nbsp;<\/p>\n<div class=\"linuxidc_code\">\n<pre>mysql&gt; create user 'backuper'@'localhost' identified by '********';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql&gt; grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on *.* to backuper@localhost;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n2. In the system to find a relatively large hard disk to create a backup directory, and create a shell script\r\n\r\n<\/pre>\n<pre>vim backup_mohan_db.sh\r\n\r\n#!\/bin\/bash\r\nmysqldump -ubackuper -p******** qianyu_veeker_db &gt; \/home\/mysql\/backup\/mohan_db_$(date +%Y%m%d_%H%M%S)\r\n\r\n3. To add the scheduled task, you need to install the crontab\u00a0\r\nvixie-cron package is the main program for cron; the\u00a0\r\ncrontabs package is\u00a0the program\u00a0used to install, uninstall, or list the tables used to drive the cron daemon.\r\n\r\n<\/pre>\n<pre>[root@~]# yum -y install vixie-cron\r\n[root@~]# yum -y install crontabs\r\n\r\n4. Set boot to start\r\n\r\n<\/pre>\n<div class=\"linuxidc_code\">\n<pre>[root@ ~]# chkconfig --level 345 crond on\r\n\r\n[root@linuxidc etc]# vim crontab\r\n\r\nSHELL=\/bin\/bash\r\nPATH=\/sbin:\/bin:\/usr\/sbin:\/usr\/bin\r\nMAILTO=root\r\nHOME=\/\r\n\r\n# For details see man4\u00a0crontabs\r\n\r\n# Example of job definition:\r\n# .---------------- minute (0 - 59)\r\n#|\u00a0 .------------- hour (0 - 23)\r\n#|\u00a0 |\u00a0 .---------- day of month (1 - 31)\r\n#|\u00a0 |\u00a0 |\u00a0 .------- month (1 - 12) OR jan,feb,mar,apr ...\r\n#|\u00a0 |\u00a0 |\u00a0 |\u00a0 .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat\r\n#|\u00a0 |\u00a0 |\u00a0 |\u00a0 |\r\n#*\u00a0 *\u00a0 *\u00a0 *\u00a0 * user-name command to be executed\r\n0 0 * * * \/home\/mysql\/backup\/mohan_db.sh<\/pre>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>1.Linux under MySQL to create a user backup role, and granted the role of SELECT, RELOAD, SHOW DATABASES, LOCK TABLES and other permissions.<\/p>\n<p>&nbsp;<\/p>\n<p> mysql&gt; create user &#8216;backuper&#8217;@&#8217;localhost&#8217; identified by &#8216;********&#8217;; Query OK, 0 rows affected (0.00 sec) mysql&gt; grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on *.* to backuper@localhost; Query OK, 0 rows affected [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6877"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6877"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6877\/revisions"}],"predecessor-version":[{"id":6878,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6877\/revisions\/6878"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6877"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6877"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6877"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}