Tag: mysql

Migrating database servers

As I’m migrating websites and applications from one server to another, I’m also migrating databases from one server to another.

Even though I’ve done this dozens, if not hundreds, of times, I always find myself looking up how to do this. I’m migrating from one MySQL (MariaDB) servers to another MySQL (MariaDB), so relatively straightforward but still some command syntax I don’t remember off the top of my head.

First, export the old database to a file:

DBHOST=old-db-host.osric.com
DBUSER=dbusername
DBNAME=dbname
mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME >$DBNAME.07-NOV-2023.bak.sql

The this mysqldump command produces output that will re-create the necessary tables and insert the data.

In this case I’m not compressing the output, but it would be trivial to pipe the output of mysqldump to a compression utility such as xz, bzip2, or gzip. For my data, which is entirely text-based, any of these utilities performs well, although xz achieves the best compression:

mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | xz -c >$DBNAME.07-NOV-2023.bak.sql.xz
mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | bzip2 -c >$DBNAME.07-NOV-2023.bak.sql.bz2
mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | gzip -c >$DBNAME.07-NOV-2023.bak.sql.gz

Next, create the new database and a database user account. This assumes there is a database server running:

sudo mysql -u root
CREATE DATABASE dbname;
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'your-t0p-s3cr3t-pa55w0rd';
GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';

Note that the CREATE USER and GRANT PRIVILEGES commands will result in a “0 rows affected” message, which is normal:

Query OK, 0 rows affected (0.002 sec)

There are other ways to create the database, see 7.4.2 Reloading SQL-Format Backups in the MySQL documentation.

Next, import the database from the file. This example uses the root user because I did not grant the dbuser PROCESS privileges (which are not table-level privileges):

sudo mysql --user=root --host=localhost dbname <dbname.07-NOV-2023.bak.sql

Running Joomla on Docker

I was looking for a well-known CMS (Content Management System) that I could easily run in a Docker container as a target for information security reconnaissance tools, such as WhatWeb.

I found an official Docker image for Joomla, a CMS that I had used previously some years ago: https://hub.docker.com/_/joomla
Continue reading Running Joomla on Docker

MySQL date_add and date_sub functions running against millions of rows

One of my servers runs a query once a week to remove all rows from a Syslog table (>20,000,000 rows) in a MySQL database that are older than 60 days. This was running terribly slowly and interfering with other tasks on the server.

Although the original query used a DELETE statement I’ve used SELECT statements in the examples below.

SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < DATE_SUB(NOW(), INTERVAL 60 DAY);

That selects about 900,000 rows and takes about 45 seconds.

SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < DATE_ADD(CURRENT_DATE, INTERVAL -60 DAY);

Likewise takes about 48 seconds.

Is MySQL running a function every time it makes a comparison? I decided to try using a hard-coded date to find out:

SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < '2015-11-12 12:00:00';

6 seconds! Much faster.

I created a user-defined variable:
SET @sixty_days_ago = DATE_SUB(NOW(), INTERVAL 60 DAY);

Then ran the query:
SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < @sixty_days_ago;

12 seconds. No 6 seconds, but still a fraction of the original time!

Selecting the newest row in one-to-many on mySQL

This one was quite a sticky wicket. So what if you have two tables in mySQL, with a one-to-many relationship (say a forum and comments in that forum), and each of the comments is dated. Now let’s say you want a result set containing each forum’s name, and with it the text of the newest comment in that forum.

The obvious way to do this is with a subselect, which mySQL doesn’t have. So how does one do it?

At first I thought that it might be impossible, but I have figured out the answer:

SELECT f.forum_id,
       max(c.created_on) as last_date,
       c2.created_on as created_on,
       c2.text
FROM   forum f
       LEFT JOIN comment c ON (c.forum_id = f.forum_id)
       LEFT JOIN comment c2 ON (f.forum_id = c2.forum_id) 
GROUP BY f.forum_id, c2.comment_id
HAVING created_on = last_date OR last_date IS NULL;

See, it’s pretty clever actually, though it leans on the “HAVING” option, which is sort of crappy. The trick is to join the comment table with the forum table twice. One of those joins gets grouped by forum id so that you can use the max function on it. The other doesn’t get grouped (by adding its primary key to the group by clause) so that you can still pull data out of it. Then you use the having clause to find only the row that has the max date.

I am also accepting values with a max value of NULL so that if a forum has no comments, it still comes back in the results.

Hope this helps someone, somewhere.
Happy Hacking

Weird little MySQL error.

So I just moved some code onto a new server, and I’m suddenly getting the warning:

mysql_query(): 14 is not a valid MySQL-Link resource in <bla bla bal> on line 47

A few other people seem to have gotten this error, but no one has posted a solution. (though one guy oh-so-annoyingly posted “I figured it out, so never mind” … Grrr. I mean, if you’re going to post a question, the answer should be in that thread if you ever figure it out….

So as soon as I figure out the answer I’m going to post it here.

I’m back with the solution:

I got clued onto it from this page: http://bytes.com/forum/thread638479.html . The error is coming because mysql_close was being called by the destructor, and because I was in safe mode the same MySQL resource was being used for each instance. What threw me even more though was that the destructor was being called at all, because I thought I only _had_ one instance. Turns out that there is a spot in my code where I (accidentaly) passed my DB object by value rather than refrence. This made a new copy of the object, which ran mysql_connect again, because it was in safe mode it returened the _same_ refrence. Then the object got unloaded, the destructor ran and closed the refrence, even though there was another instance of the object out there still using the same refrence.

Icky!