Backup

Available in Classic and VPC

In Backup, you can check the backup information configured per server for safe storage of cache data for users using Cloud DB for MySQL. Also, when the cache data is damaged due to a failure, you can perform restoration using the backup file that was in storage. In addition to servers using high availability settings, you can also use the backup and restoration features for standalone servers. However, point-in-time restoration is not supported for standalone servers.

To use the backup and restoration, it is recommended to first understand the basic execution rules for the backup provided by Cloud DB for MySQL. The basic backup execution rules are as follows:

• Backup execution methods
  • Performed daily, once a day
  • Select between automatic settings and user-defined settings
    • Automatic settings: a random time will be specified when an MySQL server is created, and the backup will be performed at a time similar to the time of the first backup.
    • User-defined settings: backups are performed within 15 minutes from the time selected by the user
    • Exceptional situations
      • Backup is performed within 30 minutes from the DB creation
      • When DB settings change, the backup is performed up to 5 minutes after the user-defined backup time
• Backup file
  • Retention period: can be stored up to 30 days according to the user settings
  • Storage location: a separate data storage (Storage contracts are made according to the backup file size)

Backup page

The basics of using the backup are as follows:

Check backup settings list

You can check the backup settings list per MySQL server in operation. The following describes how to check the backup settings list.

1. From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
2. Click the Backup menu.
3. When the backup settings list appears, check the required information.
   
   • DB service name: DB service name specified by user
   • Backup retention period: maximum number of days for the backup file to be stored in data storage
   • Backup start time: time at which the backup is performed once a day
   • Backup data size: size of the completed backup file
   • Latest backup date: date for the latest backup that was performed
   • View details: details and restoration of the backup file list created per server, saved to Object Storage

Check backup file list per server

The following describes how to check the backup file list created for each server after completing the backup:

1. From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
2. Click the Backup menu.
3. Click the [Details] button from View details of the backup settings row you want to check the details for.
4. Check the details for the backup file.
   
   • Backup date: date that the backup was performed
   • Backup start time: time when the backup was started
   • Backup completion time: time when the backup was completed
   • Elapsed time: time it took for the backup to be completed
   • Backup size: size of the created backup file upon completion of backup (MB)
   • Data storage: size of the MySQL's data storage

Restore data from console

Cloud DB for MySQL provides a service that enables you to restore lost data using backup files, and easily and quickly create an MySQL server. Also, you can restore the data up to the desired time within the available range of restoration using the point-in-time restoration feature.

Backup file restoration

You can restore data using the backup file created through automatic backup.
The following describes how to restore data using the created backup file.

1. From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
2. Click the Backup menu.
3. Click the [Details] button of the backup settings row you want to restore.
4. Select the backup file and click the [Restore backup file] button.
5. When the Restore backup file pop-up window appears, check or enter the required information for restoration.
   
   • Backup time: range of time available for restoration (up to 7 days)
   • Backup completion time: the point in time to restore to
   • Private sub domain (VPC environment): the private sub domain to be used on the server to be restored (can't be selected)
   • DB server type: MySQL server type to be restored
   • DB server name: the name of the MySQL server to be restored
   • Create as a new DB service: create the server to be restored as a new service
   • DB service name: new DB service name
   • High availability: when creating a new service, create it as a high availability configuration
   • Multi-zone: create the Standby Master Server and Master Server in different availability zones
   • Subnet: the master subnet and the subnet of another availability zone
6. Click the [Restore] button or the [Create] button.
7. Click the [OK] button.
8. Click DB server.
9. Check the status of the server being restored.
   • Created as Recovery type
   • Creating: status in which the MySQL server is being created (restored) with the information entered by the user
   • Configuring: status in which the MySQL server is being configured after being created (restored) with the information entered by the user
   • Running: status in which the creation (restoration) and configuration of the MySQL server has been completed with the information entered by user, and access to MySQL from the application server is available.

Point-in-time restoration

You can restore data to a desired point in time. You can restore up to the desired minute within the range available for restoration.
The following describes how to restore data using the point-in-time recovery feature.

1. From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
2. Click the Backup menu.
3. Click the [Details] button of the backup settings row you want to restore.
4. Click the [Point-in-time restoration] button.
   
5. When the Point-in-time recovery pop-up window appears, check or enter the required information for restoration.
   
   • Available DB restoration time: range of time available for restoration (up to 7 days)
   • DB restoration time: the point in time to restore to
   • Private sub domain (VPC environment): the private sub domain to be used on the server to be restored (cannot select)
   • DB server type: MySQL server type to be restored
   • DB server name: the name of the MySQL server to be restored
6. Click the [Restore] button or the [Create] button.
7. Click the [OK] button.
8. Click DB server.
9. Check the status of the server being restored.
   • Created as Recovery type
   • Creating: status in which the MySQL server is being created (restored) with the information entered by the user
   • Configuring: status in which the MySQL server is being configured after being created (restored) with the information entered by the user
   • Running: status in which the creation (restoration) and configuration of the MySQL server has been completed with the information entered by user, and access to MySQL from the application server is available.

Create a new DB service using restored server

You can create a new DB service based on the recovery server created through the restoration feature, and then change the server to a standalone or high availability configuration server. The specifications, DB settings, and backup times for the server are retained. When restoring a server whose backup time is not specifically set, the retention period is set as one day.

1. From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
2. Click DB server.
3. Click the recovery server you want to create a new DB service from, and then click the [Manage DB] button.
4. Click the [Create new DB service] button.
5. When the Create new DB service pop-up window appears, select whether to support high availability, enter the DB service name, and then click the [Yes] button.
   
6. Check the status of the server being created.
   • Creating: status in which the MySQL server is being created with the information entered by the user
   • Configuring: status in which the MySQL server is being configured after being created with the information entered by the user
   • Running: status in which the creation and configuration of the MySQL server has been completed with the information entered by user, and access to MySQL from the application server is available

Store in Object Storage

In Cloud DB for MySQL, you can store the created backup file to Object Storage to use for backup. The following describes how to save a stored backup file to Object Storage:

1. From NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus.
2. Click the Backup menu.
3. Click the [Details] button of the backup settings row for the file you want to save to Object Storage.
4. Select the backup file and click the [Send to Object Storage] button.
   
5. When the Send to Object Storage pop-up window appears, click and select the bucket and folder you want to save into.
   
6. Press the [Send to Object Storage] button.
7. Click the [OK] button.

Use backup utility

You can backup and restore the data on a MySQL server using utilities such as Percona XtraBackup and mysqldump.

Restore backup file using XtraBackup

After saving the backup file to NAVER Cloud Platform's Object Storage, you can use XtraBackup to restore data from the server.

The prerequisites for restoring a backup file saved to Object Storage using XtraBackup are as follows.

• The major version of MySQL installed on the user's separate server is the same as the major version of MySQL that was backed up
• The XtraBackup binary required for restoration of backup file exists
• At the MySQL server to be restored, my.cnf file with datadir variable exists
• The OS user executing the restoration command is able to access and write in the datadir directory
• MySQL is in the shutdown status in the server to be restored

To restore data using XtraBackup, proceed with the following steps in order. The description is based on CentOS 7.8 application servers.

1. Prepare the backup file at the Object Storage

1. Store the backup file of the MySQL server to restore to the Object Storage, referring to Save to Object Storage.
2. From NAVER Cloud Platform console, click Services > Storage > Object Storage > Bucket Management menus in this order, and click the bucket you want to save the backup file to.
3. Click the saved backup file, and then click the Edit > Manage permissions menus in order.
4. Set the "Open to fall field" to Public, and click the [OK] button.
5. From the details of the saved backup file, copy the link in the Link field.

2. Restore data using XtraBackup

1. Access the application server, referring to the Getting started with Cloud DB for MySQL.

   • If the server is using MySQL 5.7 version, then add the innodb_undo_tablespaces = 2 statement under the [mysqld] statement of the my.cnf file.

2. Paste the copied link in the command below, and then run it to download the backup file.

   • Once downloading the backup file is completed, change the Public settings to Private from the "Manage permissions" of the Object Storage.

   # wget [Link]
   
   -- Examples
   # wget https://kr.beta-object.ncloudstorage.com/mysql-b/20211013_BACKUP.1634094735
   

3. Run the commands shown below, in order, to download XtraBackup.

   • MySQL 5.7 version

   # cd ~ 
   
   # wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
   
   # tar xzf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
   
   # cd ./percona-xtrabackup-2.4.9-Linux-x86_64
   
   # XTRABACKUP_DIR=`pwd`
   

   • MySQL 8.0 version

   # cd ~ 
   
   # wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.23-16/binary/tarball/percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar.gz
   
   # tar xzf percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar.gz
   
   # cd ./percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17
   
   # XTRABACKUP_DIR=`pwd`
   

4. Run the following command to set the absolute path of the my.cnf file.

   # MYSQL_CONF=/etc/my.cnf
   

5. Run the following commands, in order, to create a temporary directory and copy the backup file.

   # cd ~
   
   # mkdir backup
   
   # cd ./backup/
   
   # cp ~/[Backup file name].
   
   -- Examples
   # cp ~/20211013_BACKUP.1634094735 .
   

6. Run the following commands, in order, to specify the path of BACKUP_DIR as the location of the temporary directory, and check if each path has been specified properly.

   # BACKUP_DIR=`pwd`
   
   # echo $XTRABACKUP_DIR
   
   # echo $MYSQL_CONF
   
   # echo $BACKUP_DIR
   

7. Run the following commands, in order, to end MySQL in operation and extract the file from xbstream.

   # systemctl stop mysqld
   
   # cd $BACKUP_DIR
   
   # cat [uploaded_ backupfile] | ${XTRABACKUP_DIR}/bin/xbstream -x
   
   -- Examples
   # cat 20211013_BACKUP.1634094735 | ${XTRABACKUP_DIR}/bin/xbstream -x
   

8. Run the following command to restore the backup file to the created temporary directory.

   • MySQL 5.7 version

   # ${XTRABACKUP_DIR}/bin/innobackupex --defaults-file=${MYSQL_CONF} --apply-log ${BACKUP_DIR}/
   

   • MySQL 8.0 version

   # ${XTRABACKUP_DIR}/bin/xtrabackup --defaults-file=${MYSQL_CONF} --prepare --target-dir=${BACKUP_DIR}/
   

9. Open the my.cnf file to check the location of datadir, and then run the following command to move the directory for the datadir variable to another temporary backup folder.

   # mv [data directory location] /[new_directory location]
   
   -- Examples
   # mv /var/lib/mysql /new_directory
   

10. Run the following command to move the backup file to the newly created MySQL directory.

    • MySQL 5.7 version

    # ${XTRABACKUP_DIR}/bin/innobackupex --defaults-file=${MYSQL_CONF} --move-back ${BACKUP_DIR}
    

    • MySQL 8.0 version

    # ${XTRABACKUP_DIR}/bin/xtrabackup --defaults-file=${MYSQL_CONF} --move-back --target-dir=${BACKUP_DIR}/
    

11. Enter the following commands, in order, to move to the location of datadir, and then change the datadir permission to mysql.

    • Upon completion of the change, you can run the ll command to check if the permission has been changed properly.

    # cd /var/lib/mysql
    
    # chown -R mysql:mysql [data directory path]
    
    -- Examples
    # chown -R mysql:mysql /var/lib/mysql
    

12. Add 'skip-grant-tables' under the [mysqld] statement of the my.cnf file.

13. Run the following commands, in order, to start MySQL and create a new root account.

    • When running create, the ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost' error may occur. In this case, run the drop user 'root'@'localhost'; command to delete the existing root account, and then run create again.

    # systemctl start mysqld
    
    mysql -u root -p
    
    mysql > flush privileges;
    mysql > create user root@localhost identified by 'password';
    mysql > grant all privileges on *.* to root@localhost with grant option;
    mysql > flush privileges;
    

14. Delete the skip-grant-tables statement added under the [mysqld] statement in the my.cnf file, and then run the following command to restart MySQL.

    # systemctl restart mysqld
    

DB backup and restoration using mysqldump

You can perform backup and restoration of MySQL server in the application server using the mysqldump utility.

DB backup using mysqldump

The following describes how to backup a DB in the server using the mysqldump utility.

1. Access the application server, referring to the Getting started with Cloud DB for MySQL.

2. Run the following command to backup the desired DB.
   You can check the user ID, password, private domain, and DB name from server details of each server in the DB Server menu in the console.

   • Backup only specific DBs

   # mysqldump --set-gtid-purged=OFF -u [User_ID] -p  -h [DB Private domain] -S [mysql.sock location] --single-transaction --databases [name of the database to backup] > [name of the backup file to create].sql
   
   -- Examples
   # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.beta-cdb.ntruss.com -S /var/lib/mysql/mysql.sock --single-transaction --databases dbdb > dumpfile.sql
   

   • Backup all DBs

   --mysqldump 5.7 or earlier versions
   # mysqldump --set-gtid-purged=OFF -u [User_ID] -p  -h [DB Private domain] -S [mysql.sock location] --single-transaction --all-databases > [name of the backup file to create].sql
   
   -- Examples
   # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.beta-cdb.ntruss.com -S /var/lib/mysql/mysql.sock --single-transaction --all-databases  > dumpfile.sql
   
   
   --mysqldump 8.0 version
   # mysqldump --set-gtid-purged=OFF -u [User_ID] -p  -h [DB Private domain] -S [mysql.sock location] --single-transaction --column-statistics=0 --all-databases  > [name of the backup file to create].sql
   
   -- Examples
   # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.beta-cdb.ntruss.com -S /var/lib/mysql/mysql.sock --single-transaction --column-statistics=0  --all-databases  > dumpfile.sql
   

Restore backup file using mysqldump

The following describes how to restore data from the server using the mysqldump utility.

1. Access the application server, referring to the Getting started with Cloud DB for MySQL.
2. Run the following command to restore the desired backup file.

# mysql -u root -p < [backup file name].sql

-- Examples
# mysql -u root -p < dumpfile.sql


--If the `ERROR 3546 (HY000)` error occurs when performing restoration from a backup file with the `--set-gtid-purged=OFF` option excluded, run the following commands in order, and then perform the restoration

# mysql -u root - p

mysql> reset master;

Replication connection

You can create a replication connection between the slave server and master server, and check the replication status.

The following describes how to create a replication connection.

1. Create a user account to be used for the replication connection by referring to the Manage DB user.

   • For HOST (IP), enter the Private IP of the application server where the slave server is located.

2. Add the following settings under the [mysqld] statement of the my.cnf file.

   • The server-id should be set with a unique value to identify each server in the replication configuration, and you can only enter a number between 1 and 4294967295.

   # Replication
   server-id = 2
   log-bin = mysql-bin
   relay-log = relay-bin
   report-host = ncp-mysql-server
   master_info_repository = FILE
   relay_log_info_repository = FILE
   slave_type_conversions = ALL_NON_LOSSY
   log_slave_updates # required! GTID 
   read_only # required!
   
   # Replication GTID
   enforce-gtid-consistency # required! GTID 
   gtid-mode = ON # required! GTID 
   

3. Run the following command to restart MySQL.

   systemctl restart mysqld
   

4. Back up the DB by referring to the Use backup utility.

5. Run the following commands, in order, to access the slave server with the root account and change the master.

mysql -u root -p

mysql> CHANGE MASTER TO
 MASTER_HOST = 'DB Private domain',
 MASTER_USER = 'User_ID',
 MASTER_PASSWORD = 'user password',
 MASTER_PORT = 3306,
 MASTER_AUTO_POSITION = 1;
 
 -- Examples
 mysql> CHANGE MASTER TO
    -> MASTER_HOST = 'db-1qv7d.beta-cdb.ntruss.com',
    -> MASTER_USER = 'replication',
    -> MASTER_PASSWORD = 'password1!',
    -> MASTER_PORT = 3306,
    -> MASTER_AUTO_POSITION = 1;

6. Run the following command to connect to the master server from the slave server.

mysql> START SLAVE;

Confirm connection

After completing the replication connection, run the following command and refer to the table to check the connection status.

mysql> show slave status \G;

Troubleshoot replication errors

If the Executed_Gtid_Set values of the master server and slave server are not identical, then one of the values for Slave_SQL_Running and Slave_IO_Running is displayed as No when the connection status is checked with the show slave status \G; command. In this case, the following errors may be displayed.

Slave_SQL_Running: No

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'e0b74649-41d7-11ec-afb6-f220af895dd6:2' at master log mysql-bin.000001, end_log_pos 751. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Slave_IO_Running: No

Last_IO_Error: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period.

The following describes how to troubleshoot replication errors by editing the Executed_Gtid_Set value.

1. Run each of the following commands to check if the Executed_Gtid_Set values of the master server and slave server are identical.
   • Master Server: show global variables like 'gtid_executed';
   • Slave Server: show slave status \G;
2. Run the following command to reset the GTID_PURGED value.

   ① Reset GTID_PURGED
   mysql> stop slave;
   mysql> reset master;
   mysql> set global GTID_PURGED="master Executed_Gtid_Set value";
   mysql> start slave;
   
   ② Reset root account
   mysql> flush privileges;
   mysql> create user root@localhost identified by 'password';
   mysql> grant all privileges on *.* to root@localhost with grant option;
   mysql> flush privileges;