Backup
  • PDF

Backup

  • PDF

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 recovery using the backup file that was in storage. In addition to servers using high availability settings, you can also use the backup and recovery features for standalone servers. However, point-in-time recovery is not supported for standalone servers.

To use backup and recovery, 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 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 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.

database-database-5-4_main_vpc_en.png

Area Description
① Menu name Name of the menu currently being checked
② Basic features Check details for Cloud DB for MySQL, refresh the backup page
③ Backup list Configured backup settings per server and settings information

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 the NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus, in that order.
  2. Click the Backup menu.
  3. When the backup settings list appears, check the required information.
    database-database-5-4_backuplistdetails_vpc_en.png
    • 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: The time that the daily backup is performed
    • Backup data size: Size of the completed backup file
    • Latest backup date: Date for the latest backup that was performed
    • View details: Details and recovery 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 execution of backup.

  1. From the NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus, in that order.
  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.
    database-database-5-4_backupdetails_vpc_en.png
    • Backup data: 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 the backup file, as well as easily and quickly create a MySQL Server. Also, you can restore the data up to the desired time within the available range of recovery using the point-in-time recovery feature.

Restore backup file

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 the NAVER Cloud Platform console, click the Products & Services > Database > Cloud DB for MySQL menus, in that order.
  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 recovery.
    database-database-5-4_restorepopup_vpc_en.png
    • Backup time: Range of time available for recovery (up to 7 days)
    • Backup completion time: The point in time to restore to
    • Subnet (VPC environment): Subnet to be used by 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
  6. Click the [Restore] or [Create] button.
  7. Click the [OK] button.
  8. Click the DB Server menu.
  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 (recovery) and configuration of the MySQL Server has been completed with the information entered by user, and access to MySQL Server from the application server is available
Caution

The completion of the server recovery may take several minutes.

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 recovery.

The following describes how to restore data using the point-in-time recovery feature.

Note

However, point-in-time recovery is not supported for standalone type servers.

  1. From the NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus, in that order.
  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 recovery] button.
    database-database-5-4_restorepointhover_vpc_en.png
  5. When the Point-in-time recovery pop-up window appears, check or enter the required information for restoration.
    database-database-5-4_restorepoint_vpc_en.png
    • Available DB recovery time: Range of time available for restoration (up to 7 days)
    • DB recovery time: The point in time to restore to
    • Subnet (VPC environment): Subnet to be used by 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
  6. Click the [Restore] or [Create] button.
  7. Click the [OK] button.
  8. Click the DB Server menu.
  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 (recovery) and configuration of the MySQL Server has been completed with the information entered by user, and access to MySQL Server from the application server is available
Caution

The completion of the server recovery may take several minutes.

Create a new DB service using restored server

You can create a new DB service based on the recovery server created through the recovery 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.

Note

However, data storage encrypted recovery server can only create DB Services with high availability configurations.

  1. From the NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus, in that order.
  2. Click the DB Server menu.
  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.
    database-database-5-4_newService_vpc_en.png
  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 the access to MySQL Server from the application server is available

Save to Object Storage

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

Note
  • Additional fees apply when you request subscription to Object Storage. For the introduction on Object Storage and details about its pricing plans, refer to the Service > Storage > Object Storage menu in the NAVER Cloud Platform portal.

  • In case of a MySQL Server created in Secure Zone, a related policy must be added first in the NAVER Cloud Secure Zone. In the Create policy pop-up window, select MySQL server and Object Storage for the Source IP and Destination IP fields respectively. Please refer to Secure Zone for more information about the Secure Zone.

  1. From the NAVER Cloud Platform console, click the Services > Database > Cloud DB for MySQL menus, in that order.
  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.
    database-database-5-4_objhover_vpc_en.png
  5. When the Send to Object Storage pop-up window appears, click and select the bucket you want to save it to.
    database-database-5-4_objpopup_vpc_en.png
  6. Click the [Send to Object Storage] button.
  7. Click the [OK] button.
Note
  • When sending to Object Storage, unlocking the bucket and setting appropriate access control and ACL are required.
  • Sending to Object Storage may take several minutes to be completed.

Use backup utility

You can backup and restore MySQL Server data using Percona XtraBackup and mysqldump utilities.

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 recovery of backup file exists.
  • The my.cnf file configured with the datadir variable exists in the MySQL Server to be restored.
  • The OS user executing the recovery command is able to access and write in the datadir directory.
  • MySQL is in the Shutdown status in the server to be restored.
Note
  • If the server is using another OS other than CentOS, then you need to download the XtraBackup binary for the OS image you are using from the download link.
  • For descriptions on XtraBackup binary options, refer to Percona official document (English).

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

1. Prepare backup file in Object Storage

  1. By referring to Save to Object Storage, save the backup file of the MySQL Server you want to restore, to Object Storage.
  2. From the NAVER Cloud Platform console, click the Services > Storage > Object Storage > Bucket Management menus in this order, and then click the bucket where you saved the backup file.
  3. Click the saved backup file, and then click the Edit > Manage permissions menus in this order.
  4. Set the Open to all field to Public, and then 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 by referring to the Server access guide.
    • 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 Object Storage.
    # wget [Link]
    
    -- Example
    # 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] .
    
    -- 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_backup file] | ${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
    # ${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
    # 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]
    
    -- Example
    # chown -R mysql:mysql /var/lib/mysql
    
  12. Add the skip-grant-tables statement 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 recovery using mysqldump

You can perform backup and recovery 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.

Note
  • Since the Cloud DB for MySQL uses GTID (Global Transaction Identifier), the --set-gtid-purged=OFF option needs to be added during the backup to restore a MySQL DB that does not use GTID.
  • If you want to create a replication connection with the Cloud DB for MySQL immediately after performing backup, then delete the --set-gtid-purged=OFF option to enable the use of GTID.
  • When restoring a backup copy of Cloud DB for MySQL to another Cloud DB for MySQL service, enable the --set-gtid-purged=OFF option, and then perform the backup only for the user DB.
  1. Access the application server by referring to Access server.
  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 DB to be backed up] > [Name of the backup file to create].sql
    
    -- Example
    # 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
    
    -- Example
    # 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
    
    -- Example
    # 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 by referring to Access server.
  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 the `ERROR 3546 (HY000)` error occurs when performing recovery from a backup file with the `--set-gtid-purged=OFF` option excluded, run the following commands, in order, and then perform the recovery.

# 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.

Note

The master server is a server that uses a private domain, and the slave server is a local server.

  1. Create a user account to be used for the replication connection by referring to 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 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;
  1. 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;
Item Description
Slave_IO_State Current status of the slave server
Master_Host Host name or IP address of the master server that the slave server is connected to
Master_User User account used for the slave server's connection to the master server
Master_Port Port used for the slave server's connection to the master server
Connect_Retry Duration of the reconnection attempt when the connection is lost (default: 60 seconds)
Can be changed through the CHANGE MASTER TO command
Master_Log_File Name of the master server's binary log file that the I/O thread is currently reading
Read_Master_Log_Pos Location of the master server's binary log file that the I/O thread is currently reading
Relay_Log_File Name of the slave server's relay log that the SQL thread is currently reading
Relay_Log_Pos Location of the slave server's relay log that the SQL thread is currently reading
Relay_Master_Log_File Name of the source binary log file that includes the recent events executed by the SQL thread
Slave_IO_Running I/O thread operation status of the slave server, displays the connection status to the master server through one of the three following values
  • No: Thread has not run
  • Connecting: Thread is running, but is not connected to the master server
  • Yes: Thread is running and is connected to the master server
  • Slave_SQL_Running Displays if the SQL thread has started
    Replicate_Do_DB DB list specified with the --replicate-do-db option
    Replicate_Ignore_DB Database list specified with the --replicate-ignore-db option
    Replicate_Do_Table Database list specified with the --replicate-do-table option
    Replicate_Ignore_Table Database list specified with the --replicate-ignore-table option
    Replicate_Wild_Do_Table Database list specified with the --replicate-wild-do-table option
    Replicate_Wild_Ignore_Table Database list specified with the --replicate-wild-ignore-table_Table option
    Last_Errno, Last_Error Same value as the Last_SQL_Errno field, the value can be reset with the RESET MASTER and RESET SLAVE commands
    (When the SQL thread of the slave server receives an error, it reports the error first before stopping the SQL thread. Thus, even if the value of Slave_SQL_Running is YES when the SHOW SLAVE STATUS command is run, the value of this field does not show 0.)
    Skip_Counter Displays the current value of SQL_Slave_Skip_Counter, which is a system variable
    Exec_Master_Log_Pos Location of the current master DB's binary log file read and run by the SQL thread, displays the start of the transaction or event to be processed next
    (When connecting a new slave server, if you specify a value for the MASTER_LOG_POS option in the CHANGE MASTER TO command, then the connected slave server starts reading from the specified location.)
    Relay_Log_Space Sum value of the size of all existing relay log files
    Until_Condition Value specified in the START_SLAVE command, displayed as one of the following values
  • None: The UNTIL clause is not specified
  • Master: The slave server sets the binary log file of the master server
  • Relay: The slave server sets the relay log file
  • Until_Log_File Name of the log file that the SQL thread will run, and then suspend
    Until_Log_Pos Position value of the log file that the SQL thread will run, and then suspend
    Master_SSL_Allowed SSL parameter used for the slave server's connection to the master server, displayed as one of the following values
  • Yes: SSL connection allowed
  • No: SSL connection not allowed
  • Ignored: SSL connection is allowed, but the SSL support is not activated in the slave server
  • Master_SSL_CA_File SSL parameter used for the slave server's connection to the master server
    Master_SSL_CA_Path SSL parameter used for the slave server's connection to the master server
    Master_SSL_Cert SSL parameter used for the slave server's connection to the master server
    Master_SSL_Cipher SSL parameter used for the slave server's connection to the master server
    Master_SSL_Key SSL parameter used for the slave server's connection to the master server
    Seconds_Behind_Master Displays how much the replication speed of the slave server has slowed down
    Master_SSL_Verify_Server_Cert SSL parameter used for the slave server's connection to the master server
    Last_IO_Errno Number of the error that stopped the I/O thread most recently
    Last_IO_Error Message of the error that stopped the I/O thread most recently
    Last_SQL_Errno Number of the error that stopped the SQL thread most recently
    Last_SQL_Error Message of the error that stopped the SQL thread most recently
    Replicate_Ignore_Server_Ids Content from running the IGNOER_SERVER_IDS option in the CHANGE MASTER TO statement for the slave server
    Master_Server_Id Master server's server_id value
    Master_UUID Master server's server_uuid value
    Master_Info_File Location of the master.info file
    SQL_Delay Displays by how many seconds the slave server delays the replication with the master server
    SQL_Remaining_Delay Displays the remaining time when the Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event
    (otherwise, NULL)
    Slave_SQL_Running_State Operation status of the SQL thread
    Master_Retry_Count Number of reconnection attempts when the slave server's connection to the master server is lost
    Master_Bind Binding network interface for the slave server
    Last_IO_Error_Timestamp Time of occurrence for the most recent I/O error
    (YYMMDD HH:MM:SS)
    Last_SQL_Error_Timestamp Time of occurrence for the most recent SQL error
    (YYMMDD HH:MM:SS)
    Retrieved_Gtid_Set GTID group that corresponds to all transactions that the slave server received
    (left empty when the GTID is not used)
    Executed_Gtid_Set GTID group created on the binary log
    (left empty when the GTID is not used)
    Auto_Position 1 if Auto_Position is used, 0 if not used

    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 modifying the Executed_Gtid_Set value.

    Caution

    To prevent data mismatch when resetting the GTID_PURGED value, refer to the official documents of MySQL and Percona.

    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;
      

    Was this article helpful?

    What's Next