Backup

Available in 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 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 will be specified when a MySQL Server is created and the backup will be 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
      • Perform a backup within 30 minutes after creating the DB.
      • 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 the backup 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 (can't be selected).
   • 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 to be restored as a new service.
   • DB Service name: New DB Service name.
   • High availability: Creates it as a high availability configuration when creating a new service.
   • Multi Zone: Creates 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 entered by the user
   • Configuring: Status in which the MySQL Server is being configured after being created (restoration) 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 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 window appears, check or enter the required information for restoration.
   
   • DB restoration time: Within the time specified by 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 to be restored as a new service.
   • DB Service name: New DB Service name.
   • High availability: Creates it as a high availability configuration when creating a new service.
   • Multi Zone: Creates 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 entered by the user
   • Configuring: Status in which the MySQL Server is being configured after being created (restoration) 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 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 then 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 then click [Manage DB].
4. Click [Create new DB Service].
5. When the Create new DB Service popup window appears, select whether to support high availability, enter the DB Service name, and then 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 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 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 [Send to Object Storage].
   
5. When the Send to Object Storage popup window appears, click and select the bucket and folder you want to save into.
   
6. Click [Send to Object Storage].
7. Click [OK].

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. See save to 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. See 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, 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 syntax under the [mysqld] statement of the my.cnf file.

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

2. Paste the copied link in the following command, and then 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 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`
   

   • 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 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]
   
   -- 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 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]
    
    -- 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 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 syntax added under the [mysqld] syntax 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

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 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 backup] > [name of the backup file to create].sql
   
   -- Example
   # mysqldump --set-gtid-purged=OFF -u person -p -h db-1qv7d.beta-cdb.ntruss.com -P 3306 --single-transaction --databases dbdb > dumpfile.sql
   

   • Backup 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.beta-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.beta-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, 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

-- 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 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;
 
 -- Example
 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.

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;