Category Archives: MySQL

Minimal privileges required for a MySQL backup user

It’s good security practice to have a separate “backup” user that only has global read privileges.

The minimum privileges required to accomplish this are:

  • SELECT
  • LOCK TABLES
  • SHOW DATABASES
  • SHOW VIEW

After setting up your new user, you can run:

mysqldump --all-databases -u backup -p > sqldump.sql

..to backup all your databases to the file sqldump.sql!

Check out my CentOS tutorial for information on how to set automatic database backup via CRON. (Step 15 in the guide.)

Display MySQL server uptime in PHP

With this snippet you can print the uptime of a MySQL server from a PHP script. It uses PDO to make the connection.

If you know a simpler way to do this, feel free to post a comment!

try
{                               
    $dbh = new PDO('mysql:host=localhost;dbname=database_name', 'user', 'password');
    foreach($dbh->query("SHOW GLOBAL STATUS LIKE 'Uptime'") as $row)
    {
        echo ' MySQL server OK, uptime ';   
        echo ($row['Value'] >= 31557000) ? ' >365 days' : (gmdate("z", ($row['Value'])) . ' days, ' . gmdate("H:i", $row['Value']));                        
    }
}
catch (PDOException $e)
{
    print "MySQL server error: <br/> " . $e->getMessage();
}

The result will look something like this:

15 days, 04:38

Show MySQL server uptime using a query

SHOW GLOBAL STATUS LIKE 'Uptime';

Result

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 322477 |
+---------------+--------+
1 row in set (0.00 sec)

(The uptime is given in seconds)
 

More status variables
There are many more status variables available.

Show them all with this query:

SHOW GLOBAL STATUS;

Batch adding custom field to all posts in WordPress

The MySQL query below will add my-custom-field with value “hello” to all published posts.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT wp_posts.ID, 'my-custom-field', 0 FROM wp_posts WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type='hello';

Note
Fields that already have custom field my-custom-field will get another one. (So they will have 2 in total.)

MySQL general usage snippets – connecting, listing, creating and deleting databases

Connect to mysql server

mysql -h localhost -u root -p

List databases

show databases;

Create a database

create database mydb;

Delete a database

drop database mydb;

Select a working database

use mydb;

Show all tables in database

show tables;

See table structure

describe table_name;

Show all rows in a table

SELECT * FROM table_name;

Get 30 results, order by id column (last in first)

SELECT * FROM table_name ORDER BY id DESC LIMIT 30;

Select row by value of a column

SELECT * FROM table_name WHERE table_field = 'meatballs';