Using MySQL server images
    • PDF

    Using MySQL server images

    • PDF

    Article Summary

    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.

    PropertyDescription
    expire_logs_days = 7To prevent the log size set by log-bin from increasing, save it for 7 days.
    innodb_file_per_tableWhen 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_sizeIncrease 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_groupSpecify the number of innodb_log_files. Two or more are recommended for rotation.
    default_storage_engineSpecify 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-nowIn 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.

    CatetoryDirectory
    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

    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.

    Step 5. Confirm

    Confirm the settings and create a MySQL server.

    Server connection settings

    Set port forwarding

    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.
    database-1-115_en

    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

    database-1-113_en

    Monitoring metrics

    MetricDescription
    PortDB port information connected from the sqlstatus_nCloud.
    TimeMonitoring time.
    ConnThe current connection count to the DB.
    RunNumber of running threads in the DB.
    Indicates the number of threads that are currently processing queries in the DB.
    AbortNumber 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.
    SelectNumber of SELECT queries processed in the DB
    UpdateNumber of UPDATE queries processed in the DB
    InsertNumber of INSERT queries processed in the DB
    DeleteNumber of DELETE queries processed in the DB
    ReplaceNumber of REPLACE queries processed in the DB
    QcacheNumber of queries processed through the Query Cache
    CallNumber of queries processed using the Procedure/Function
    QPSTotal number of queries processed in the DB
    SlaveReplication delay time of replicated SlaveDB.
    Default is 0 if not replicated
    userCPU normal processes executing in user mode
    systemCPU processes executing in kernel mode
    iowaitCPU waiting for I/O to complete
    Slow_queriesNumber of queries that run longer than the number specified in long_query_time
    SumNumber 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

    Was this article helpful?

    Changing your password will log you out immediately. Use the new password to log back in.
    First name must have atleast 2 characters. Numbers and special characters are not allowed.
    Last name must have atleast 1 characters. Numbers and special characters are not allowed.
    Enter a valid email
    Enter a valid password
    Your profile has been successfully updated.