Using MySQL server images

Available in Classic and VPC

About self-hosted Ncloud MySQL service

This page describes the concept and basic structure of NAVER Cloud Platform’s Ncloud MySQL service. This service supports MySQL, the world’s most popular open source relational database management system.

MySQL

MySQL is the world’s most popular open source relational database management system (RDBMS). Like other RDBMSes, it creates a database and uses tables and columns to view, change, add and delete records. Usually, MySQL databases are managed by using the Command Line Interface (CLI). You can use Workbench provided by MySQL or third-party tools for GUI-based management.

After Sun acquired MySQL AB in 2008, Oracle acquired Sun in 2010. All rights to MySQLEnterprise/Community Version are now owned by Oracle.

The self-hosted Ncloud MySQL service provided by NAVER Cloud Platform offers the MySQL Community Edition (GPL) to support pre-installed images at Basic Install level.

NAVER Cloud Platform’s Ncloud MySQL configuration

Self-hosted service

The self-hosted Ncloud MySQL service provides an environment file, MySQL Daemon and Startup status. You can check the operating standards for DB installation through the environment file, and you can see the DB status including the CLI-type monitoring binary through the MySQL Daemon and Startup status. Therefore, our technical support covers the installation of databases only.

RPM/APT

There are three types of MySQL installation: installation by compiling source, RPM/APT installation and binary installation. NAVER Cloud Platform adopts the second method, by providing the RPM (APT) installation image. The .repo file used by RPM (APT) installs MySQL using the files officially provided by MySQL. It is included in the server image based on the Latest Version provided by Repo.

Install RPM

The package installed by RPM for CentOS 7.x with MySQL consists of the following:

This package is installed in addition to the OS default settings related to MySQL installation and execution.

• mysql-server
• mysql-devel
• libmysqlclient-dev
• build-essential

Install APT

The package installed by APT for CentOS 6.x/7.x with MySQL consists of the following:

This package is installed in addition to the OS default settings related to MySQL installation and execution.

• mysql-server
• libmysqlclient-dev

Recommended parameters (for 5.6 and 5.7)

The list below shows recommended parameters which are standardized in the self-hosted Ncloud MySQL image in NAVER Cloud Platform:

• innodb_file_per_table
• innodb_log_file_size = 512M
• innodb_log_files_in_group = 2
• default_storage_engine=InnoDB
• query_cache_type = ON
• query_cache_size = 0M
• sysdate-is-now

innodb_file_per_table

When the InnoDB global tablespace is used to create an InnoDB table, the physical space that has been increased will not be reduced even after the table is deleted.
If this option is enabled, an InnoDB table is created or deleted by creating or deleting a file, so as to secure the physical space.

Innodb_log_file_size/innodb_log_files_in_group

Increase the innodb_log_file_size option to prevent checkpoint bottlenecks caused by heavy traffic.
DB restart is required to adjust these variables.

default_storage_engine

Specify InnoDB that enables online-DDL and transactions.

query_cache_type/query_cache_size

Set these parameters to ON/0 to allow users to dynamically turn ON or OFF query_cache after DB startup. Then, adjust them to suit your service situation.

sysdate-is-now

In case of using sysdate, the time type data may be different if the data processing time is different between the master and the slave in replication configuration. Therefore, sysdate automatically replaces now.

self-hosted Ncloud MySQL service management

This page describes how to manage the self-hosted Ncloud MySQL service.

Create MySQL image server

If you create an instance server with MySQL installed, the MySQL Daemon with my.cnf environment standardized for NAVER CLOUD PLATFORM is created and started.

my.cnf

The following table shows the parameters of my.cnf.

The following table shows the directories where MySQL is installed.

How to create server

This section describes the process of creating a MySQL server.

Connect to www.ncloud.com and log in

① Click Console in the upper-right corner of the page.

② Click [Create server].

Create server

• Create server, referring to Server Creation Guide

Step 1. Select server image

① Select DBMS > MySQL.

② Select the MySQL version that matches your OS version and click [Next].

③ Check the popup window for the instructions.

④ Click [OK] to close the popup window.

Step 2. Set server

Select a server type and pricing plan.

Step 3. Set login key

Select a login key.

Step 4. Set firewall

Select an existing ACG or create a new one.

• Proceed by referring to ACG User Guide..

Step 5. Confirm

Confirm the settings and create a MySQL server.

Server connection settings

Set port forwarding

• Proceed referring to Port Forwarding Guide.

1. Click [Set port forwarding].

2. In the Set port forwarding popup window, enter a port number for remote access using SSH.

3.Add an external port and click [Add].

4. Click [Apply].

Get admin password

1. Click [Manage servers].

2. Click [Get admin password].

3. Add a login key file in the Get admin password window.

4. Click [Get admin password].

5. Get your admin password.

Connect to server via terminal

Connect to your server by using a terminal program.

• Port: A port number for port forwarding
• Public IP: Public IP address for connecting to the server, displayed on the port forwarding screen.
• Password: The password shown as the administrator's password (The initial password must be changed.)

ssh -p [PORT] root@[Public IP]

After creating server image

1. If you do not use the server immediately after creating it, shutdown MySQL daemon to prevent security risks.

2. You must set the password for your MySQL because no password is specified.

How to start, stop and access MySQL

• Execute the following commands to start or stop the MySQL daemon.

• CentOS

  shell> /sbin/service mysqld start
  shell> /sbin/service mysqld stop
  

• Ubuntu

  Shell> /usr/sbin/service mysql start
  Shell> /usr/sbin/service mysql stop
  

• To check whether the MySQL daemon is running, use the following command.

  Shell> ps -ef | grep mysqld
  

  • Result: The mysqld/mysqld process should be shown.

• You can access MySQL created in NAVER Cloud Platform with the following command.

  Shell> mysql -uroot
  

• The initial password is not specified according to the MySQL Password policy.

• Be sure to change the root password when you first connect to MySQL to prevent unauthorized access.

• Use the following command to change the root password.

  • MySQL 5.6

    mysql> set password=password('Password');
    

  • MySQL 5.7

    mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password';
    

• The basic policy of MySQL Built-in Password is as follows.

  • At least 8 characters in length
  • At least 1 special character
  • At least 1 number
  • At least 1 case combination

Terminate MySQL image server

When terminating a MySQL server in NAVER Cloud Platform, we recommend that you terminate it after the following procedure.

1. Back up the data stored in MySQL

• Download the backed up data and save it in a safe place.

2. Stop MySQL daemon

3. Delete the data directory where mysql is stored.

• Example) rm ‐rf /var/lib/mysql

4. In NAVER Cloud Platform’s Console, Stop the server and then Terminate it.

Allow remote access to MySQL

If you use MySQL on Ubuntu, only access from localhost is allowed.
To allow remote access to MySQL, follow the procedure below.

1. Connect to the MySQL DB.

2. Grant account permissions
Example) GRANT ALL PRIVILEGES ON *.* to 'Account'@'%' IDENTIFIED BY 'Password';

3. Edit the environment file.
Example) sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

4. Comment out bind-address.

5. Restart MySQL.

SQLSTATUS

NAVER Cloud Platform’s Ncloud MySQL service provides a CLI-based monitoring program.

• Any unauthorized distribution or modification of, or tampering with the program is prohibited.

Overview

The internal status of MySQL is provided in binary format so that it can be viewed in seconds.
You can display the MySQL status in seconds to view the status changes of your DB in real-time.

How to use

Create an account to use in sqlstatus_nCloud (including this as the default account).

 GRANT PROCESS, SHOW DATABASES, SUPER, EXECUTE, REPLICATION CLIENT ON *.* TO 'sqlstatus_ncloud'@'127.0.0.1' IDENTIFIED BY PASSWORD '*AB9C7542BD3FE96F5342F2A616D030CF4494EB44';

• Installation path: /usr/bin/sqlstatus_nCloud

• When using the default 3306 port: sqlstatus_nCloud

• When using a custom port other than 3306: sqlstatus_nCloud port number

If you do not use it, you can delete the default account (DROP USER sqlstatus\_ncloud@127.0.0.1;).

Monitoring results

Monitoring metrics