- Print
- PDF
Using MySQL server images
- Print
- PDF
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.
Property | Description |
---|---|
expire_logs_days = 7 | To prevent the log size set by log-bin from increasing, save it for 7 days. |
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 | Increase the innodb_log_file_size option to prevent checkpoint bottlenecks caused by heavy traffic. DB restart is required to adjust the variable. |
innodb_log_files_in_group | Specify the number of innodb_log_files. Two or more are recommended for rotation. |
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. |
log_timestamps = SYSTEM (5.7 only) | In MySQL 5.7, change LogTime, which is set to UTC, to SYSTEM Local TIME. |
The following table shows the directories where MySQL is installed.
Catetory | Directory |
---|---|
DATADIR | /var/lib/mysql/ |
SLOW_LOG | /var/lib/mysql/*slow.log |
ERROR_LOG | /var/log/mysqld.log - CentOS /var/log/mysql/error.log - Ubuntu |
my.cnf | /etc/my.cnf - CentOS /etc/mysql/my.cnf - Ubuntu. |
sqlstatus_nCloud | /usr/bin/sqlstatus_nCloud |
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';
- MySQL 5.6
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
Metric | Description |
---|---|
Port | DB port information connected from the sqlstatus_nCloud. |
Time | Monitoring time. |
Conn | The current connection count to the DB. |
Run | Number of running threads in the DB. Indicates the number of threads that are currently processing queries in the DB. |
Abort | Number of failed connections to the DB. Displays the number of times when max_connection is exceeded, or when a user who is not registered in the DB account table attempts to connect to the DB. |
Select | Number of SELECT queries processed in the DB |
Update | Number of UPDATE queries processed in the DB |
Insert | Number of INSERT queries processed in the DB |
Delete | Number of DELETE queries processed in the DB |
Replace | Number of REPLACE queries processed in the DB |
Qcache | Number of queries processed through the Query Cache |
Call | Number of queries processed using the Procedure/Function |
QPS | Total number of queries processed in the DB |
Slave | Replication delay time of replicated SlaveDB. Default is 0 if not replicated |
user | CPU normal processes executing in user mode |
system | CPU processes executing in kernel mode |
iowait | CPU waiting for I/O to complete |
Slow_queries | Number of queries that run longer than the number specified in long_query_time |
Sum | Number of SlowQueries that occur after running sqlstatus. Default is 10 seconds and the value can be changed with the following command (unit: sec.). mysql> set global long_query_time=1 |