WordPress database error: [Can't create/write to file '/tmp/#sql_60b_0.MYI' (Errcode: 28)]
SHOW FULL COLUMNS FROM `wp_options`

WordPress database error: [Can't create/write to file '/tmp/#sql_60b_0.MYI' (Errcode: 28)]
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (66) ORDER BY t.name ASC

Script to backup MySql Databases | Data Networking Blog

WordPress database error: [Can't create/write to file '/tmp/#sql_60b_0.MYI' (Errcode: 28)]
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (60) ORDER BY t.name ASC

WordPress database error: [Can't create/write to file '/tmp/#sql_60b_0.MYI' (Errcode: 28)]
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (95) ORDER BY t.name ASC

Data Networking Blog
Blog for Admins

Script to backup MySql Databases

Standard

In a previous article I showed you how to use rsync to backup your files. This works fine for most of situations but I was still unable to backup my mySql databases without first manually exporting them to a .sql format. So I decided to write a small shell script to do this for me. This way I can use cron to dump all databases and the same rsync process will copy the files to a remote location for safe keeping.

With MySql there is a utility called mysqldump which we can use to export one or all databases to a file with .sql extension. Let’s suppose I have a database called test then I can dump it using:

Of course this will prompt me to enter my password and then dump the file in your current working directory as test.sql. This works fine for manual export but we want to automate the process and like you just saw it asks you for the password that means the script will have to provide the password too. For this reason I will not use the root account with my script but rather let’s create a new MySql account with minimum rights required to dump databases.

Below I am logging into mysql as a root user and granting Select and Lock Tables rights to a new user called backup and setting the password to be password123. This user is only allowed to login from localhost. Please ensure you use stronger passwords.

Alright so we have a user that we can use in our script. Let’s start coding now. The script below requires that you pass at least one database name as the argument. I will call this script autosqldump.sh

 

Save this script file and change the executable flag so we can run it.

And now run it. I am backing up mysql database but you can add more with a space separated list.

And if all goes well you should see the following output.

If you ls your directory you should have a .tar file created. You can easily schedule this script through cron but just make sure to change the locations of where you are saving your .sql and .tar files. 🙂

 

February 19, 2015 Uncategorised Jd

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Font Size
Decrease Size Default Size Increase Size
Select Skin
Select Underlay Background
Select Overlay Background
Scheme Switcher Toggle