Category Archives: Database

All things databasey

Backing up and restoring a mongo database to a new server

https://docs.mongodb.org/manual/tutorial/backup-and-restore-tools/

  1. Create a dump of your database:
    1. Make a dump folder, eg “mkdir /data/dump_01/”
    2. Run the dumper: “mongodump –out /data/dump_01/”
  2. Now zip it up
    1. Ensure zip is installed: “apt-get install zip”
    2. Then zip up your folder: “zip -r dump_01.zip /data/dump_01/”
    3. Now get your zipped folder over to the other server, i would recommend Rsync.
  3. At the other end it is basically a reverse of the above
    1. unzip dump_01.zip
    2. This will create a folder of the zip relative to where you are.
    3.  Now just run “mongorestore dump01/”

Providing you had a running instance of mongo on the 2nd server you should now have imported your mongo db.

Get the the top 2 records of each group in a single mysql query

Eg the following result is the 2 players from each team with the highest score:

Natural sort for MySQL

Given the list:

1
2
3
10
11
12a
12b
12c
Alpha
Beta
Charlie

If running a normal ORDER BY it will order the list:
1
10
11
12a
12b
12c
2
3
Alpha
Beta
Charlie

You can CAST the results to UNSIGNED during the ORDER BY to achieve naturally sorted results as shown in the first list.

MySql database replication

First up there are a few guides out there already, eg:

http://www.rackspace.com/knowledge_center/article/set-up-mysql-master-slave-replication

However, if your service is running on something like rackspace you may or may not know there is an internal network you can use to reduce bandwidth costs. However, when you setup the db replication and start the slave, if the connection cannot be established you will find MySQL doesn’t spit out much helpful.

So, why not test the connection separately:

1 – Create the slave user on the master server (from mysql shell, where *** is your users password and 123.123.123.123 is your ip’s password):

2 – Attempt to connect to the master server from the slave via mysql:

Fetch the last 300 rows entered in MySQL

I have taken on a project in which I have inherited a table with a few 1000 entries.

The problem is, there is no auto increment ID field on the table and I have been asked to extract the last 300 rows that were entered into it.

It it possible to extract the last 300 entries from a table? Is there a “systems row id”?

Sound familiar? Well this happened to me not so long ago… I had remembered hearing somewhere about a row id built into mysql.. and wondered if I could use this to extract what I required.

Turns out I was exactly right!

This was my question – http://stackoverflow.com/questions/14386904/select-last-250-rows-from-a-table-with-no-auto-id

And best answer was this:

 SELECT
    col1,
    col2,
    col3
FROM (
        SELECT
            col1,
            col2,
            col3,
            (
                @auto := @auto +1
            )indx
        FROM
            the_table,
            (
                SELECT @auto :=1
            )a
    )
    a
  
ORDER BY
    indx DESC
LIMIT
    300

 Absolute genius! A trick well worth remembering.

Another good post on the subject – http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/