Backup

Available in VPC

In Backup, you can check the backup information configured per server for safe storage of data for users using Cloud DB for MySQL. Also, when the 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 Stand Alone Server. However, point-in-time recovery is not supported for Stand Alone 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: Random time is specified when a MySQL Server is created, and the backup is performed at a time similar to the time of the first backup.
    • User-defined settings: Backup starts within 15 minutes after the user-selected time.
    • 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 retained for up to 30 days based on user settings.
  • Storage location: Stored in separate data storage (storage contracts depend on backup file size).

Backup interface

The basics of using backups are as follows:

Check backup settings list

You can check the backup settings list per MySQL Server in operation. To view the backup settings list:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
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 date: 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

To check the backup file list created for each server after completing the backup:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the Backup menu.
3. Click [Details] 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 Server 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 a MySQL Server. Also, you can restore the data up to the desired time within the available range of restoration using the point-in-time recovery feature.

Restore backup file

You can restore data using the backup file created through automatic backup.
To restore data using the created backup file:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the Backup menu.
3. Click [Details] in the backup settings row you want to restore.
4. Select the backup file and click [Restore backup file].
5. When the Restore backup file popup appears, check or enter the required information for restoration.
   
   • Backup time: Range of time available for restoration (up to 7 days).
   • Backup completion time: 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 restore.
   • DB Server name: MySQL Server name to restore.
   • Create as a new DB service: Create the server you want to restore as a new DB service.
   • DB Service name: New DB Service name.
   • High availability: Create it as a high availability configuration when creating a new service.
   • Multi Zone: Create the Standby Master Server and Master Server in different availability zones.
   • Subnet: Master Subnet and the subnet of another availability zone.
   • Data storage type: The type of data storage to restore.
6. Click [Restore] or [Create].
7. Click [OK].
8. Click the DB Server menu.
9. Check the status of the server restoration.
   • Created as Recovery type.
   • Creating: Status in which the MySQL Server is being created (restoration) with the information you entered.
   • Configuring: Status in which the MySQL Server is being configured after being created (restoration) with the information you entered.
   • Running: Status in which the creation (restoration) and configuration of the MySQL Server have been completed with the information you entered, and access to the MySQL Server from the application server is available.

Point-in-time recovery

You can restore data to a desired point in time. You can restore up to the desired minute within the range available for restoration.
To restore data using the point-in-time recovery feature:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the Backup menu.
3. Click [Details] in the backup settings row you want to restore.
4. Click [Point-in-time recovery].
   
5. When the Point-in-time recovery popup appears, check or enter the required information for restoration.
   
   • Available DB restoration time: Time specified in the binlog_expire_logs_seconds setting.
   • DB restoration time: 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 restore.
   • DB Server name: MySQL Server name to restore.
   • Create as a new DB service: Create the server you want to restore as a new DB service.
   • DB Service name: New DB Service name.
   • High availability: Create it as a high availability configuration when creating a new service.
   • Multi Zone: Create the Standby Master Server and Master Server in different availability zones.
   • Subnet: Subnet information of MySQL Server to restore.
   • Data storage type: The type of data storage to restore.
6. Click [Restore] or [Create].
7. Click [OK].
8. Click the DB Server menu.
9. Check the status of the server restoration.
   • Created as Recovery type.
   • Creating: Status in which the MySQL Server is being created (restoration) with the information you entered.
   • Configuring: Status in which the MySQL Server is being configured after being created (restoration) with the information you entered.
   • Running: Status in which the creation (restoration) and configuration of the MySQL Server have been completed with the information you entered, and access to the MySQL Server 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 change the server to a Stand Alone 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 1 day.

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the DB Server menu.
3. Click the Recovery Server you want to create a new DB Service from and click [Manage DB].
4. Click [Create new DB Service].
5. When the Create new DB Service popup appears, select whether to support high availability, enter the DB Service name, and click [Yes].
   
6. Check the status of the server being created.
   • Creating: Status in which the MySQL Server is being created with the information you entered.
   • Configuring: Status in which the MySQL Server is being configured after being creating with the information you entered.
   • Running: Status in which the creation and configuration of the MySQL Server has been completed with the information you entered, and access to the MySQL Server 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. To save a stored backup file to Object Storage:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the Backup menu.
3. Click [Details] of the backup settings row for the file you want to save to Object Storage.
4. Select the backup file and click [Export to Object Storage].
   
5. When the Export to Object Storage popup appears, click and select the bucket and folder you want to save into.
   
6. Click [Export to Object Storage].
7. Click [OK].

Use backup utility

You can back up 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 by referring to Store in Object Storage.
2. Set up access control by selecting one of two methods according to security requirements.
   • Method A (Access from anywhere): Files can be downloaded from anywhere using the link address in the file details, without any separate access control settings.
   • Method B (Access only from specific servers): Add the servers to download backup files to the allow list by referring to Object Storage bucket access control settings.
3. Click the saved backup file, then go to Edit > Manage permissions and change all public items to Public.
4. From the details of the saved backup file, copy the link in the Link item.

2. Restore data using Xtrabackup

1. Access the application server by referring to Getting started with Cloud DB for MySQL.
   • If the Server is using MySQL 5.7 version, add the innodb_undo_tablespaces = 2 syntax under the [mysqld] syntax of the my.cnf file.

   # vi /etc/my.cnf
   
   [mysqld]
   innodb_undo_tablespaces = 2
   

2. Paste the copied link in the following command and run it to download the backup file.

   # wget [Link]
   
   -- public url example:
   # wget https://kr.object.ncloudstorage.com/mysql-b/20211013_BACKUP.1634094735
   
   -- private url example:
   # wget https://kr.object.private.ncloudstorage.com/mysql-b/20211013_BACKUP.1634094735
   

3. Run the following commands 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`
   

   • MySQL 8.4 version

   # cd ~
   
   # wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.4/Percona-XtraBackup-8.4.0-4/binary/tarball/percona-xtrabackup-8.4.0-4-Linux-x86_64.glibc2.34.tar.gz
   
   # tar xzf percona-xtrabackup-8.4.0-4-Linux-x86_64.glibc2.34.tar.gz
   
   # cd ./percona-xtrabackup-8.4.0-4-Linux-x86_64.glibc2.34
   
   # 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].
   
   -- Example
   # 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
   
   -- Example
   # 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 or higher

   # ${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 run the following command to move the directory for the datadir variable to another temporary backup folder.

   # mv [data directory location] /[new_directory location]
   
   -- Example
   # mkdir /new_directory
   # 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 or higher

    # ${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 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]
    
    -- Example
    # chown -R mysql:mysql /var/lib/mysql
    

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

    # vi /etc/my.cnf
    
    [mysqld]
    skip-grant-tables
    

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 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 syntax added under the [mysqld] syntax in the my.cnf file, and run the following command to restart MySQL.

    # systemctl restart mysqld
    

Back up and restore DB using mysqldump

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

Back up DB using mysqldump

To back up a DB from a server using the mysqldump utility:

1. Access the application server by referring to 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.
   • Back up only specific DB

   # mysqldump --set-gtid-purged=OFF -u [User_ID] -p -h [DB Private domain] -P [DB access port] --single-transaction --databases [name of the database to back up] > [name of the backup file to create].sql
   
   -- Example
   # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.cdb.ntruss.com -P 3306 --single-transaction --databases dbdb > dumpfile.sql
   

   • Back up all DB

   --mysqldump 5.7 or earlier versions
   # mysqldump --set-gtid-purged=OFF -u [User_ID] -p -h [DB Private domain] -P [DB access port] --single-transaction --all-databases > [name of the backup file to create].sql
   
   -- Example
   # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.cdb.ntruss.com -P 3306 --single-transaction --all-databases  > dumpfile.sql
   
   
   --mysqldump 8.0 version or higher
   # mysqldump --set-gtid-purged=OFF -u [User_ID] -p -h [DB Private domain] -P [DB access port] --single-transaction --column-statistics=0 --all-databases  > [name of the backup file to create].sql
   
   -- Example
   # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.cdb.ntruss.com -P 3306 --single-transaction --column-statistics=0  --all-databases  > dumpfile.sql
   

Restore backup file using mysqldump

To restore data from the server using the mysqldump utility:

1. Access the application server by referring to 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

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


-- If a `ERROR 3546 (HY000)` error occurs while restoring from a backup file that excludes the `--set-gtid-purged=OFF` option, first run the commands below in order, then proceed with 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.

To create a replication connection:

1. Create a user account to be used for the replication connection by referring to DB user management.
   • 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] syntax 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 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;
 
 -- Example
 mysql> CHANGE MASTER TO
    -> MASTER_HOST = 'db-1qv7d.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.

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;
   

Manual Backup

You can create a snapshot-based backup of MySQL Server at any time.
You can create up to 30 snapshots per DB service. To create a snapshot:

Back up

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. From the Cloud DB for MySQL menu, navigate to DB Server > Manage DB > Manual Backup.
3. When the Manual Backup popup appears, click [OK].
   

View Manual Backup list

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the Backup menu.
3. Click the [Manual Backup] tab.
   
   • DB Service name: DB Service name specified by user.
   • Backup type: Backup method category.
   • 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.

View Manual Backup list by server

To check the backup file list created for each server after completing the backup:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.

2. Click the Backup menu.

3. Click [Details] from View details of the backup settings row you want to check the details for.

4. Click the [Manual Backup] tab.

5. Check the details for the backup file.
   

   • Backup date: Date that the backup was performed.
   • Snapshot name: Name of the Manual Backup snapshot you created.
   • Snapshot creation time: Time when the Manual Backup was created.
   • Snapshot size: Size of the snapshot backup.
   • Zone: Zone containing the snapshot.

Restore a snapshot

Manual Backup creates a snapshot that you can use to restore a new DB service.

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the Backup menu.
3. Click the [Manual Backup] tab.
4. Click [Details] in the backup settings row you want to restore.
5. Select the backup file and click [Restore Snapshot].
6. When the Snapshot recovery popup appears, check or enter the required information for restoration.

• Snapshot creation time: Time when the backup was performed.
• Zone: Zone where the snapshot is located.
• DB Server type: MySQL Server type to restore.
• DB Server name: MySQL Server name to restore.
• DB Service name: New DB Service name.
• High availability: Create it as a high availability configuration when creating a new service.
• Multi Zone: Create the Standby Master Server and Master Server in different availability zones.
• Subnet: Subnet information of MySQL Server to restore.

Delete a snapshot

Manual Backup creates snapshots that you can delete.

1. In the VPC environment of the NAVER Cloud Platform console, navigate to Menu > Services > Database > Cloud DB for MySQL.
2. Click the Backup menu.
3. Click the [Manual Backup] tab.
4. Click [Details] in the backup settings row you want to restore.
5. Select the backup file and click [Delete Snapshot].