46134

MySQL dump CronJob

Question:

I'm trying to create a cron that daily backups my MySQL slave. The backup.sh content:

#!/bin/bash # # Backup mysql from slave # # sudo mysql -u root -p'xxxxx' -e 'STOP SLAVE SQL_THREAD;' sudo mysqldump -u root -p'xxxxx' ng_player | gzip > database_`date +\%Y-\%m-\%d`.sql.gz sudo mysqladmin -u root -p'xxxxx' start-slave

I made it executable by sudo chmod +x /home/dev/backup.sh and entered in to crontab by:

sudo crontab -e 0 12 * * * /home/dev/backup.sh

but it doesn't work, if I only run in the command line it works but not in crontab.

FIXED: I used the script from this link: <a href="https://stackoverflow.com/questions/19731148/mysqldump-doesnt-work-in-crontab?rq=1" rel="nofollow">mysqldump doesn't work in crontab</a>

Answer1:

Break the problem in half. First try sending only email from the cron job to see if you are getting it to even run. Put this above in a file and have your cron job point to it:

#!/bin/bash /bin/mail -s "test subject" "yourname@yourdomain" < /dev/null

The good thing about using this tester is that it is very simple and more likely to give you some results. It does not depend on your current working directory, which can sometimes be not what you expect it to be.

Answer2:

Try use full link to mysql bin directory in .sh file

example :

sudo /var/lib/mysql -u root -p'xxxxx' -e 'STOP SLAVE SQL_THREAD;'

Answer3:

I had this same problem.

I figured out that you can't use the command sudo in a non-interactive script. The sudo command would create a field where you would type in the password to your account (root).

If you are logged into a command prompt like ssh sudo works without typing in any passwords, but when another program runs sudo it would ask for password.

Try this instead su command doesn't require any logins and it does the same thing.

su --session-command="mysql -u root -p'xxxxx' -e 'STOP SLAVE SQL_THREAD;'" root su --session-command="mysqldump -u root -p'xxxxx' ng_player | gzip > database_`date +\%Y-\%m-\%d`.sql.gz" root su --session-command="mysqladmin -u root -p'xxxxx' start-slave" root

Replace root with your linux username.

<strong>EDIT:</strong> Look at this thread for a different answer. <a href="https://askubuntu.com/questions/173924/how-to-run-cron-job-using-sudo-command" rel="nofollow">https://askubuntu.com/questions/173924/how-to-run-cron-job-using-sudo-command</a>

Answer4:

Let's start with the silly stuff in the script.

The only command which you <strong>don't</strong> run via 'sudo' is the, spookily enough, <strong>only</strong> command which I would expect you might need to run via sudo (depending on the permissions of the target file).

Prefixing the commands in a script with sudo without a named user (i.e. running as root) serves no useful function if you are invoking the script as root.

On a typical installation, the mysql, mysqladmin and gzip programs are typically executable by any user - the authentication and authorization of the commands to the DBMS are authenticated by the DBMS using the authentication credentials passed as arguments - hence I would not expect that any of the operations here, except possibly writing to the output file (depending on its permissions).

You don't specify a path for the backup file - maybe it's writing it somewhere other than you expect?

(similarly, you should check if any of the executables are in a location which is not in the $PATH for the crontab execution environment).

<blockquote>

but it doesn't work

</blockquote>

....is not an error message.

The output of any command run via cron is mailed to the owner of the crontab - go read your mail.

Recommend

  • How to Cache Images Dynamically-Generated with PHP?
  • passing parameter to server in ExtJs
  • Time taken for Hadoop job to execute
  • Catch 500 internal server error with System.WebClient
  • Is there any way to use wpdb prepare statements for array implode(' OR ', $myArray)?
  • retrieving data from url in iphone
  • How to populate html table with info from list in django
  • Prevent page break in text block with iText, XMLWorker
  • Shouldn't else be indented in the below code
  • Most efficient way to move table rows from one table to another
  • How do I get the list of bad records that didn't load in Bigquery?
  • 550 Access denied - Invalid HELO name
  • Webgrid not refreshing after delete MVC
  • How do I access an unhandled exception in an MVC Error view?
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • Email verification using google app script and google forms
  • Could not find rake using whenever rails
  • Jquery UI tool tip close icon
  • Java: can you cast Class into a specific interface?
  • Q promise. Difference between .when and .then
  • DomPDF {PAGE_NUM} not on first page
  • Email format validation in mvc3 view
  • Javascript simulate pressing enter in input box
  • AES padding and writing the ciphertext to a disk file
  • Updating server-side rendering client-side
  • How to extract text from Word files using C#?
  • Join two tables and save into third-sql
  • sending/ receiving email in Java
  • How to model a transition system with SPIN
  • Importing jscolor library in angular 2
  • what is the difference between the asp.net mvc application and asp.net web application
  • ORA-29908: missing primary invocation for ancillary operator
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • Traverse Array and Display in markup
  • A cron job substitute?
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • coudnt use logback because of log4j
  • Qt: Run a script BEFORE make
  • Converting MP3 duration time