How to Create a Database in MySQL

Introduction

When you build up an application, you need a database (db) to save your data. It could be about your order, member, or transactional data. It really depends on business needs from the application that you build. Another purpose is you can initiate improvements based on huge data that you’ve already saved.

Based on Wikipedia, a database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques Wikipedia.

There are many great databases these days, one of it is MySQL. In this section, we will learn from the beginning how to create a database, tables, and query data with MySQL.

Why MySQL:

  • It is open source. However, there are a personal and enterprise version.
  • Fast. Of course with the right indexes when you have huge amount of rows data.
  • Scalability, maintainability.
  • Suitable for web-based application. E-commerce, warehouse, logging, and many more.

Before we start, to create or manage your MySQL database, you need database client/IDE.

Three IDE options:

Personally, I find Sequel Pro is very helpful and powerful for my day-to-day use.

Start and Login to MySQL on your local machine (Mac OS X).

  1. Go to your System Preferences
  2. Find MySQL
  3. Choose to Start MySQL Server

After the MySQL database started, you can log in.

  1. Go to your database client, in this example I am using Sequel Pro.
  2. Connect to your localhost. You need to provide the username and password before login.
  3. Once you connect, you will be able to create your database.

Create new Database:

Create Database Statements
CREATE DATABASE database_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name];

Example:

CREATE DATABASE learning_mysql 
    CHARACTER SET utf8
    COLLATE utf8_general_ci;

Using Functionality Provided by IDE
  • Go to Database menu, select Add Database…

  • Then fill in the database name

For common cases and non latin, use UTF-8 for character set, and you can use utf8_general_ci for the collation.

Your database is now ready to use. Ensure you choose the right database that you want to manage. The second step is to prepare tables as per your business needs, to save the data from your application.

Happy exploring!

How do I backup MySQL databases in Ubuntu?

What is MySQL

MySQL is an open-source RDBMS (Relational Database Management System). As the name implied it uses SQL (Structured Query Language) to access and manipulate data. MySQL has been widely used to store and manage data ranging from a simple web application to an enterprise class application.

The importance of data in every application require us to regularly back up the data to prevent data loss, for example caused by hardware crashes. In this post I will show you how to back up the database manually and using a script combined with a cron job to run the process automatically.

Using mysqldump

To create a database backup in MySQL we can use the mysqldump command. The example syntax of using this command is:

mysqldump -u username -p database_to_backup > backup_file_name.sql

If you need to restore the database you can use the following command:

mysql -u username -p database_to_restore < backup_file_name.sql

Before you can execute the command you might need to create the database if you don’t already have it.

saturn@ubuntu:~$ mysql -u root -p
CREATE DATABASE database_to_restore;

Creating Backup Script

To start let’s create MySQL user account that we are going to use to do the backup process. Login to MySQL using mysql -u root -p command. Type and execute the following command to create backupuser.

grant lock tables, select, show view on kodejava.* to 'backupuser'@'localhost' identified by 'backuppasswd';
flush privileges;

Exit from the MySQL using the exit command and create the following backup script called backup.sh using your favorite editor. For example, you can use nano or vim to create the file.

#!/bin/sh
BACKUP_HOME="/home/saturn/backup"

cd $BACKUP_HOME
directory="$(date +%Y%m%d)"

if [ ! -d "$directory" ]; then
    mkdir $directory
fi

backupdir="$BACKUP_HOME/$directory"
backup="kodejava-$(date +%Y%m%d%H%M%S)"

mysqldump -ubackupuser -pbackuppasswd --opt kodejava > $backupdir/$backup.sql

cd $directory
tar -czf $backup.tar.gz $backup.sql
rm $backup.sql

To make the backup.sh file executable you need to run the chmod +x backup.sh command.

Creating Scheduler Using Crontab

The crontab command is used to schedule commands to be executed periodically at a predetermined time. It will run as a background process without needing user intervention. These kinds of jobs are generally referred to as cron jobs and the jobs will run as the user who creates the cron jobs.

In the example below we register a cron job to execute the script at 12:00AM every day. To edit the cron jobs type crontab -e, this will open the crontab file.

saturn@ubuntu:~$ crontab -e
no crontab for saturn - using an empty one

Select an editor.  To change later, run 'select-editor'.
  1. /bin/ed
  2. /bin/nano        <---- easiest
  3. /usr/bin/vim.basic
  4. /usr/bin/vim.tiny

Choose 1-4 [2]:

Select an editor to edit the crontab, choose by entering the number of the editor. The easiest one is nano but you can also use vim if you comfortable with it.

And you will see an empty crontab file will the following commented messages:

# Edit this file to introduce tasks to be run by cron.
#
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
#
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use '*' in these fields (for 'any').#
# Notice that tasks will be started based on the cron's system
# daemon's notion of time and timezones.
#
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
#
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
#
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command

Go to the end of the file and write the following entry to register a cron job. In the example below we register a cron job to execute the backup.sh script at 12:00M every day.

# m h  dom mon dow   command
  0 0   *   *   *    /home/saturn/backup.sh

After you save the file you can use the crontab -l command to list the registered cron job. If you want to know more about crontab you can visit crontab guru website.

How to create a read-only MySQL user?

Introduction

There are times when you need to create a user only to have read-only access to a database. The user can view or read the data in the database, but they cannot make any changes to the data or the database structure.

Creating a New User Account

To create a read-only database user account for MySQL do the following steps:

  • First, login as a MySQL administrator from your terminal / command prompt using the following command:
mysql -u root -p
  • You’ll be prompted to enter the password. Type the password for the root account.
  • Create a new MySQL user account.
CREATE USER 'report'@'%' IDENTIFIED BY 'secret';

The % in the command above means that user report can be used to connect from any host. You can limit the access by defining the host from where the user can connect. Omitting this information will only allow the user to connect from the same machine.

  • Grant the SELECT privilege to user.
GRANT SELECT ON kodejava.* TO 'report'@'%';
  • Execute the following command to make the privilege changes saved and take effect.
FLUSH PRIVILEGES;
  • Type quit to exit from the MySQL shell.

Test the New User Account

  • Now we can try the newly created user account. Start by login with the new user account and provide the corresponding password.
mysql -u report -p
  • Try executing the DELETE command:
mysql> USE kodejava;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM authors;
ERROR 1142 (42000): DELETE command denied to user 'report'@'localhost' for table 'authors'
mysql> UPDATE authors SET name = 'Wayan Saryada' WHERE id = 1;
ERROR 1142 (42000): UPDATE command denied to user 'report'@'localhost' for table 'authors'
mysql>

How do I insert a document into MongoDB collection?

In the last MongoDB example, How documents are represented in MongoDB Java Driver?, we’ve seen how MongoDB JSON documents are represented in MongoDB Java driver.

Using this knowledge it is time for us to learn how to insert documents into MongoDB collections. We will create a code snippet that will insert documents into the teachers collections in the school database. We will see the complete code snippet first followed by a detail description of the code snippet. So, let’s begin with the code snippet.

package org.kodejava.mongodb;

import com.mongodb.*;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;
import org.bson.json.JsonMode;
import org.bson.json.JsonWriterSettings;
import org.bson.types.ObjectId;

import java.util.Arrays;

public class MongoDBInsertDocument {
    public static void main(String[] args) {
        // Creates MongoDB client instance.
        MongoClient client = new MongoClient(new ServerAddress("localhost", 27017));

        // Gets the school database from the MongoDB instance.
        MongoDatabase database = client.getDatabase("school");

        // Gets the teachers collection from the database.
        MongoCollection<Document> teachers = database.getCollection("teachers");
        teachers.drop();

        // Creates a document to be stored in the teachers collections.
        Document document = new Document("firstName", "John")
                .append("lastName", "Doe")
                .append("subject", "Computer Science")
                .append("languages", Arrays.asList("Java", "C", "C++"))
                .append("email", "john.doe@school.com")
                .append("address",
                        new Document("street", "Main Apple St. 12")
                                .append("city", "New York")
                                .append("country", "USA"));

        // Prints the value of the document.
        JsonWriterSettings settings = JsonWriterSettings.builder()
                .indent(true)
                .outputMode(JsonMode.RELAXED)
                .build();

        System.out.println(document.toJson(settings));

        // Inserts the document into the collection in the database.
        teachers.insertOne(document);

        // Prints the value of the document after inserted in the collection.
        System.out.println(document.toJson(settings));
    }
}

The snippet should be easy to understand. But I will explain about it a little more down here. In the beginning of the code snippet we begin with the following lines:

// Creates MongoDB client instance.
MongoClient client = new MongoClient(new ServerAddress("localhost", 27017));

This is how we bootstrap / start the MongoDB Java Driver. It connects to MongoDB server at localhost port 27017. If you omit using this ServerAddress class it will also connect to localhost port 27017 as the default. On the next lines you can see the following codes.

// Gets the school database from the MongoDB instance.
MongoDatabase database = client.getDatabase("school");

// Gets the teachers collection from the database.
MongoCollection<Document> teachers = database.getCollection("teachers");
teachers.drop();

This code snippet tells you how to get the database, the school database. We get the database using the client.getDatabase() method call and passing the database name as the argument. The reference to this database then stored in a variable called database. After having the database we can then access the teachers collections by calling the database.getCollection() method.

You also notice that we call collection.drop(), which will clear the collection. We use this for our example purpose only, just to make sure that every time we execute our code snippet the collection will be cleaned before we insert some document.

Next, we create the document to be stored in the teachers collections. We define a variable called document with Document type which refer to an instance of org.bson.Document type. And we add some fields in the document, and array type field and another embedded document.

// Creates a document to be stored in the teachers collections.
Document document = new Document("firstName", "John")
        .append("lastName", "Doe")
        .append("subject", "Computer Science")
        .append("languages", Arrays.asList("Java", "C", "C++"))
        .append("email", "john.doe@school.com")
        .append("address",
                new Document("street", "Main Apple St. 12")
                        .append("city", "New York")
                        .append("country", "USA"));

In the last three lines we do the following:

// Prints the value of the document.
JsonWriterSettings settings = JsonWriterSettings.builder()
        .indent(true)
        .outputMode(JsonMode.RELAXED)
        .build();

System.out.println(document.toJson(settings));

// Inserts the document into the collection in the database.
teachers.insertOne(document);

// Prints the value of the document after inserted in the collection.
System.out.println(document.toJson(settings));

In the first print out we will see the document as defined in the previous lines using the org.bson.Document with all the defined field values. Then it followed by calling the collection.insertOne() method which will insert the document into the collections.

In the last line we print out the document once again. You might see that the result is almost the same as the first print out, but you will notice that after inserted into the collection the document now have another field, which is the _id field assigned by the Java Driver as the object id of the document. The _id is added automatically if we didn’t define the _id field in the document. It is essentially the same as if we define the document using the following code, where _id it a type of org.bson.types.ObjectId.

Document document = new Document("_id", new ObjectId());

And these are the actual output of the code above:

{
  "firstName": "John",
  "lastName": "Doe",
  "subject": "Computer Science",
  "languages": [
    "Java",
    "C",
    "C++"
  ],
  "email": "john.doe@school.com",
  "address": {
    "street": "Main Apple St. 12",
    "city": "New York",
    "country": "USA"
  }
}
{
  "firstName": "John",
  "lastName": "Doe",
  "subject": "Computer Science",
  "languages": [
    "Java",
    "C",
    "C++"
  ],
  "email": "john.doe@school.com",
  "address": {
    "street": "Main Apple St. 12",
    "city": "New York",
    "country": "USA"
  },
  "_id": {
    "$oid": "6191261ad2c0ec541c3edba2"
  }
}

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.mongodb</groupId>
        <artifactId>mongo-java-driver</artifactId>
        <version>3.12.11</version>
    </dependency>
</dependencies>

Maven Central

How documents are represented in MongoDB Java Driver?

MongoDB’s documents are stored inside a collections as a JSON (JavaScript Object Notation) document. It’s a string of key-value pairs data. JSON is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate.

When we are working in the MongoDB shell we can type in this document as a string that follow JSON data format. But how do we create this JSON document when working within a Java programming. This post will show you how to represent a document using Java Driver for MongoDB.

If you recall a key-value pairs data type you will remember that Java has a java.util.Map that can represent data structure in this format. So you might think that you can use a generic type of Map<String, Object> to store this data. But, because in MongoDB’s document the order of keys in a document is quite important to make sure the operations such as find, insert, update and remove work correctly, using a Map to represent a document can be quite dangerous.

MongoDB has a special interface called as com.mongodb.DBObject and its implementation class in com.mongodb.BasicDBObject that can be used to create or represent a document in MongoDB database. The DBObject is actually a map like structure with a key-value pairs. If you look up to the class hierarchy you can actually see that a BasicDBObject is inherited from the java.util.LinkedHashMap class.

The code snippet below will show you how to create a BasicDBObject to represent a MongoDB document.

package org.kodejava.mongodb;

import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;

import java.util.Arrays;

public class MongoDBDocument {
    public static void main(String[] args) {

        // Creates an empty document.
        DBObject emptyDoc = new BasicDBObject();
        System.out.println("emptyDoc = " + emptyDoc);

        // Creates a simple document with a given key and value.
        DBObject simpleDoc = new BasicDBObject("name", "John Doe");
        System.out.println("simpleDoc = " + simpleDoc);

        // Creates a document with embedded document and arrays.
        DBObject document = new BasicDBObject("firstName", "Foo")
                .append("lastName", "Bar")
                .append("age", 25)
                .append("email", "foo@bar.com")
                .append("address",
                        new BasicDBObject("street", "Sunset Boulevard 123")
                                .append("city", "New York")
                                .append("country", "USA"))
                .append("hobbies", Arrays.asList("Swimming", "Cycling", "Running"));
        System.out.println("document = " + document);
    }
}

In the code above we have created three documents as an example. The first one is an empty document which created by instantiating a BasicDBObject class with no arguments specified. The second one we create a document with a single key and value. This key and value is passed as an argument when we create the BasicDBObject.

The last example show you how to create a document with multiple keys, embedded document and an arrays. To add more fields to the BasicDBObject we can call a chain of the append() method with a specified key and value. The key will be a string and the value is a type of java.lang.Object.

An embedded document is created simply by instantiating another BasicDBObject and assign it as a value of a document key. In the example above the address field is an embedded document inside the document. Which contains another fields such as street, city and country.

If you want to see how the JSON string of this document is look like, you can run the code above. You will see something like the output below as the result.

emptyDoc = {}
simpleDoc = {"name": "John Doe"}
document = {"firstName": "Foo", "lastName": "Bar", "age": 25, "email": "foo@bar.com", "address": {"street": "Sunset Boulevard 123", "city": "New York", "country": "USA"}, "hobbies": ["Swimming", "Cycling", "Running"]}

That’s the basic that you need to know on how to create a document using MongoDB Java Driver. You will use this document when doing some database operation in MongoDB such as finding a document, inserting, updating and removing document from collection in the database.

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.mongodb</groupId>
        <artifactId>mongo-java-driver</artifactId>
        <version>3.12.11</version>
    </dependency>
</dependencies>

Maven Central