Install and Configure mysqlbackup in Mysql Enterprise Edition

-

Install and Configure mysqlbackup in Mysql Enterprise Edition

When you are using MySQL Enterprise Edition ,You can check online utility mysqlbackup for taking online backup for the MySQL backup. In this blog, We will discuss about installation and configuration of the mysqlbackup. 

1) Download the mysqlbackup rpm from the oracle.support.com

2) Install mysqlbackup on the server using below command

dpkg -i mysql-commercial-backup_*.deb



3) Create backup admin user for taking backup.

CREATE USER 'backupadmin'@'localhost' IDENTIFIED BY '**************';

GRANT SELECT ON *.* TO 'backupadmin'@'localhost';

GRANT BACKUP_ADMIN ON *.* TO 'backupadmin'@'localhost';

GRANT SELECT ON performance_schema.variables_info TO 'backupadmin'@'localhost';

GRANT SELECT ON performance_schema.log_status TO 'backupadmin'@'localhost';

GRANT RELOAD ON *.* TO 'backupadmin'@'localhost';

GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'backupadmin'@'localhost';

GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history TO 'backupadmin'@'localhost';

GRANT REPLICATION CLIENT ON *.* TO 'backupadmin'@'localhost';

GRANT SUPER ON *.* TO 'backupadmin'@'localhost';

GRANT PROCESS ON *.* TO 'backupadmin'@'localhost';

GRANT SELECT ON performance_schema.replication_group_members TO 'backupadmin'@'localhost';

GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO 'backupadmin'@'localhost';

GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'backupadmin'@'localhost';

GRANT ENCRYPTION_KEY_ADMIN ON *.* TO 'backupadmin'@'localhost';

GRANT SELECT ON non-InnoDB_tbl TO 'backupadmin'@'localhost';

GRANT INNODB_REDO_LOG_ARCHIVE ON *.* TO 'backupadmin'@'localhost';

grant show view on *.* TO 'backupadmin'@'localhost';

grant event on *.* TO 'backupadmin'@'localhost';

GRANT ALTER ON mysql.backup_progress TO 'mysqlbackup'@'localhost';

GRANT CREATE, INSERT, DROP ON mysql.backup_progress_old TO 'mysqlbackup'@'localhost';

GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_progress_new TO 'mysqlbackup'@'localhost';

4) Configure Login patch for mysal backup admin user.
Most of the time backup is configured from the cron or from some scheduling tool. So , We cannot provide password in the script. In order to overcome this problem , We can configure login path for the MySQL backup admin user.

mysql_config_editor set --login-path=local --host=localhost --user=username --password

mysql_config_editor print –all


Some use full command for taking MySQL backup:


Full Backup:


mysqlbackup --login-path=mysqlbackup --host=localhost --with-timestamp --backup-image=/dbbackup/rise/$(date '+%Y-%m-%d')/my_full_$(date +'%Y-%m-%d-%H%M').mbi --backup-dir=/dbbackup/rise/$(date '+%Y-%m-%d')  backup-to-image

Incremental Backup:


mysqlbackup --login-path=myslbackup --host=localhost --with-timestamp --incremental --incremental-base=history:last_backup --backup-image=/dbbackup/rise/$(date '+%Y-%m-%d')/my_inc_$(date +'%Y-%m-%d-%H%M').mbi --backup-dir=/dbbackup/rise/$(date '+%Y-%m-%d')  backup-to-image


Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Add or Remove a node in MySQL Innodb Cluster

-
Add or Remove a node in MySQL Innodb Cluster In any database Cluster Solution ,it's regular activity to add or remove node from the cluster. In this blog , We will discuss about addition and removal of the cluster node from the MySQL Innodb cluster. Remove a node from the MySQL Innodb cluster . 1) Connect to primary node and check status. mysqlsh --uri clusteradmin@mysqlhost01:3306 MySQL  mysqlhost01:3306 ssl  JS > cluster.status() {     "clusterName": "mysqlcluster",     "defaultReplicaSet": {         "name": "default",         "primary": "mysqlhost01:3306",         "ssl": "REQUIRED",         "status": "OK",         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",         "topology": {     ...
Read more