Tag: database

Posted on

Creating an AWS Aurora Serverless database

I’m looking for a low cost managed db in the cloud for a small project, so thought I’d take a look at setting up an Aurora Serverless db, as depending on usage (and my usage will be very low) it looks like it’s definitely the cheapest of all AWS RDS options.

From the Console, from RDS, I pressed the ‘Create Database’ button:

If you select Aurora, the Serverless option is way down the page here:

I kept all the defaults, but changes the capacity to the smallest options:

After taking note of generated credentials and pressing the last ‘Create Database’ button, the dialog said it would take a couple of minutes to provision, and it sure did. I wasn’t timing it but it was at least 10 minutes before it was ready. This was probably the longest I’ve every waiting to provision anything on AWS.

Once it was ready, I tried to use the online query editor, but looks like there’s an additional step to create a user:

This option is under Network and Security:

After applying the change with the immediate option, I created a test table:

Inserted a row:

And then selected all rows:


Looks good so far!

Posted on

mysql ‘select … into outfile’ access denied

mysql’s ‘select … into outfile …’ is an easy way to export the results of a query to a file, but to execute this command you need the additional FILE privilege. The privilege is not granted by default, even with ALL, e.g.

grant ALL on exampledb.* to exampleuser;

The ALL privilege does not include FILE, so you’ll see this error:

select * from example into outfile 'test.txt'
ERROR 1045 (28000): Access denied for user 'testuser'@'%' (using password: YES)

To grant the FILE privilege,

grant FILE on *.* to exampleuser;

Note that databasename.* doesn’t appear to work, you have to grant on *.*.

 

Posted on

Changing root password after initial mysql 5.7 install

It’s been a while since I’ve installed mysql, and it seems with most recent versions root is set a default password on install which you need to logon and change before you can do anything else.

Logon with:

mysql -u root -p

and enter the temporary password, then change the default with:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

This is described in the docs here.

Posted on

Useful Database Maven dependencies

MySql Connector:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.21</version>
</dependency>

HSQL:

        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.2.8</version>
        </dependency>

H2:

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.3.168</version>
        </dependency>
Proudly powered by WordPress