Category Archives: Mysql

Simple strategy for MySQL backups

I now have a good amount of data in my blog that I would be very sad if I lost. As a precautionary measure I decided to build a little system that will backup my data regularly so I’m prepared in case of a disaster.

The strategy

The strategy is going to be very simple. I’m going to create a user in my database that has read permissions on the tables I want to backup. This user will run mysqldump from a different machine and will save the backups there. I will create a cron job that will do this once a day.

Read more »

Showing records from MySQL tables with many columns

Lately I’ve been working on a system that has a lot of big tables (with a lot of columns), and often I want to do something like:

1
SELECT * FROM users WHERE id = 1;

But there are so many columns that it doesn’t look good on a terminal:

1
2
3
4
5
+----+-------------------+---------------------+---------------------+---------------------+-----------------------+--------+-------------------------------------+----------------------------------------------------------------------------------------------------+
| id | username          | created_at          | updated_at          | issuer              | issuer_id             | points | email                               | picture_url                                                                                        |
+----+-------------------+---------------------+---------------------+---------------------+-----------------------+--------+-------------------------------------+----------------------------------------------------------------------------------------------------+
|  1 | carlos            | 2016-01-31 13:03:36 | 2016-11-04 18:15:56 | accounts.google.com | 111394444444498347111 |    100 | NULL                                | https://lh4.googleusercontent.com/-laaaaaajmcc/AAAAAAAAAAA/AAAAAAAAAAA/qwertyuioaa/s96-c/photo.jpg |
+----+-------------------+---------------------+---------------------+---------------------+-----------------------+--------+-------------------------------------+----------------------------------------------------------------------------------------------------+

There is an easy way to tell MySQL to output data in a format that is more easy to read for these scenarios:

1
SELECT * FROM users WHERE id = 1\G;

The output looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
*************************** 1. row ***************************
         id: 1
   username: carlos
 created_at: 2016-01-31 13:03:36
 updated_at: 2016-11-04 18:15:56
     issuer: accounts.google.com
  issuer_id: 111394444444498347111
     points: 100
      email: NULL
picture_url: https://lh4.googleusercontent.com/-laaaaaajmcc/AAAAAAAAAAA/AAAAAAAAAAA/qwertyuioaa/s96-c/photo.jpg
1 row in set (0.00 sec)

ERROR:
No query specified

Since now each value is printed in its own line, the content can be read more easily.

MySQL for production

I’m starting a web project and I decided to save some money by hosting my MySQL database in a cheap instance in Digital Ocean. I was a little concerned about security so I did some research and found some ways to make my installation a little safer.

The first thing any installation must do is run:

1
sudo mysql_secure_installation

This step will allow you to set a root password if you haven’t already done so. This of course is something you must do if you want any kind of security. The script will also remove the default anonymous account, only allow root connections from localhost and remove the test database.

If you expect connections to your mysql database to come from a single host you can restrict this inside /etc/my.cnf by adding something like this:

1
bind-address = 127.0.0.1

This can be any valid IP address. If you want to allow connections from more than one IP addresses then you will have to do this at the network level.

MySQL allows you to load data from the local file system using a LOAD statement. If you are not using this statement, the best thing is to remove the access to local files altogether. You can do this by adding a line to your /etc/my.cnf file:

1
local-infile = 0

Read more »

Create MySQL users

I always forget the syntax to create a new mysql user, so I decided to write it in here for later reference. I am doing this from a mysql terminal, so first we need to login:

1
2
mysql -uroot -p
mysql>

And then we can:

1
2
CREATE USER 'username'@'domain' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.table TO 'username'@'domain'

When issuing the GRANT command you can use wildcards like this:

1
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'

And that is pretty much all there is to it.