Restore MySQL InnoDB Cluster from mysqlbackup(MySql Enterprise Backup)

-

Restore MySQL InnoDB Cluster from mysqlbackup(MySql Enterprise Backup)

Now a days HA(High Availability) and DR(Disaster Recovery) Solutions are mandatory and required for mission critical databases. In MySQL world , HA solution are provided using the InnoDB or NDB Clustering Solution. 

In todays blog , We will discuss about the restoration of the full InnoDB Cluster restoration.

InnoDB MySQL cluster restoration is done in two steps:

1)  Restore MySQL DB Backup on the first node of the Cluster. For that you can check our blog Restore MySQL Database from mysqlbackup.

2) Recreate and Start Cluster.
We will follow below steps for Recreation of MySQL InnoDB Cluster.

2.1) Check if Cluster Admin user existing on the restore node in Step 1

select  User, Host from  user where User like '%cluster%';
 
2.2)  Perform MySQL InnoDB Cluster Prechecks

mysqlsh --log-level=DEBUG3
dba.verbose=2
dba.checkInstanceConfiguration('clusteradmin@mysqlhost01:3306')
dba.checkInstanceConfiguration('clusteradmin@mysqlhost02:3306')
dba.checkInstanceConfiguration('clusteradmin@mysqlhost03:3306')



For node mysqlhost02 & mysqlhost03 prechecks output will be same as that of the above.

2.3) Configure all Cluster node for the MySQL InnoDB.

dba.configureLocalInstance('clusteradmin@mysqlhost01:3306')
dba.configureLocalInstance('clusteradmin@mysqlhost02:3306')
dba.configureLocalInstance('clusteradmin@mysqlhost03:3306')


For node mysqlhost02 & mysqlhost03 MySQL InnoDB Configuration output should be same as above.
After Local node configuration, Recheck all node configuration using below steps:

mysqlsh --log-level=DEBUG3
dba.verbose=2
dba.checkInstanceConfiguration('clusteradmin@mysqlhost01:3306')
dba.checkInstanceConfiguration('clusteradmin@mysqlhost02:3306')
dba.checkInstanceConfiguration('clusteradmin@mysqlhost03:3306')

2.4) Recreate Cluster.

mysqlsh --uri clusteradmin@mysqlhost01:3306
var cluster = dba.createCluster('testdbcluster')


NOTE : If you get error 
"Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000) " with CreateCluster Command then follow our blog.

2.5) Check MySQL InnoDB Cluster Status
var cluster = dba.getCluster()
cluster.status()




2.6) Add Other two node to the cluster using below command.
cluster.addInstance('clusteradmin@mysqlhost02:3306');
cluster.addInstance('clusteradmin@mysqlhost03:3306');


Similarly, Add mysqlhost03 node to cluster. 

2.6) Check MySQL Cluster Status.
cluster.status()



NOTE: If you get error during node addition . Please follow our blog 

 Now, MySQL InnoDB restored completely and successfully.

At this stage , You can try to connect with MySQL InnoDB cluster via MySQL Router. If you are getting any issue you can configure the MySQL Router using our blog Install & Configure MySQL Router -  MySQL InnoDB Cluster 


=========================================================================

You can also , Visit our Blogs for related topic on Backup & Restore:

2)Improve & Optimizing Performance of the MySQL Restore with mysqldump
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

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --d...
Read more

Oracle Database 19C Installation on Windows Server 2016

-
Image
Oracle Database 19C Installation on Windows Server 2016 In Database world, Its important to be on the latest version of the database flavor. Though Oracle have release 21c Database as well but by far Oracle 19C is most stable version.( You also check our similar blog for  Installation of Microsoft SQL Server 2016 ) Please do also check out blog for  Oracle 19c New Features to know about Oracle 19c features.  In this blog, We will be installing Oracle 19C DB on Windows Server. 1) Create required directory for Oracle Home and Oracle Base. Oracle Base: E:\app\oracle Oracle Home: E:\app\oracle\db\19.0.0\dbhome_1 2) Move the binary to E:\app\oracle\product\19.0.0 directory and unzip 3) Check unzipped directory. 4)Right Click on setup and  Run Setup as Administrator: 5)   On Configuration Screen, Select Set up software only 6)   On Database Installation Option page, Select Single instance Database installation for stand alone database server. 7)  O...
Read more

MySQL InnoDB Cluster Restore/Create Issue : - Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000)

-
Image
MySQL InnoDB Cluster Restore Issue : - Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000) Recently ,We have been restoring and recreating MySQL InnoDB Cluster and faced below issue after firing : MySQL mysqlhost01:3306 ssl JS > var cluster = dba.createCluster('testdbCluster') .. ..... ... Creating InnoDB cluster 'testdbCluster' on 'mysqlhost01:3306'... Adding Seed Instance... ERROR: Unable to start Group Replication for instance 'mysqlhost01:3306'. Please check the MySQL server error log for more information. Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000): mysqlhost01:3306: The START GROUP_REPLICATION command failed as the applier module failed to start. (RuntimeError) MySQL mysqlhost01:3306 ssl JS > Below is full command output with error message: Below are the message in MySQL error log: This issue is Caused by the old(original server) relay log information exiting in metadata tables ....
Read more