Embedded Files
This page provides some worked examples of data refresh scenarios in an InnoDB Cluster environment.
Incompatible Source Schema
Incompatible Source Schema
mysource - Standalone Source Database
MySQL 5.7
Stored Procedures without DETERMINISTIC assertion
Table without a Primary Key
mynode1 - InnoDB Cluster Primary
mynode2 - InnoDB Replica
mynode3 - InnoDB Replica
MySQL 8.0
The easiest way to resolve this is to dissolve the cluster, recreate the database, import the data, fix/workaround the issues and recreate the cluster....
mynode1
mkdir /backup/mydumpdir
rm -rf /backup/mydumpdur/*
Create a directory to hold a backup of the source database
Remove any existing backup files
mynode1
mysqlsh
\c icadmin@mynode1
\js
var cluster = dba.getCluster("mycluster")
cluster.dissolve( { interactive:true } )
Start MySQL Shell
Connect to the cluster admin account on mynode1
Javascript
Define the cluster variable
Dissolve the cluster
mynode1
\c root@localhost
\sql
STOP GROUP_REPLICATION;
SET PERSIST group_replication_start_on_boot = OFF;
SET PERSIST group_replication_bootstrap_group = OFF;
RESET REPLICA ALL;
RESET MASTER;
Connect to the local MySQL root account on mynode1
SQL
Explicitly stop Group Replication
Disable auto-start of Group Replication
Remove replication channels
Fresh GTID state
mynode1root
sudo rm -f /var/lib/mysql/data/auto.cnf
systemctl restart mysql
mynode2
\c root@localhost
\sql
STOP GROUP_REPLICATION;
SET PERSIST group_replication_start_on_boot = OFF;
SET PERSIST group_replication_bootstrap_group = OFF;
RESET REPLICA ALL;
RESET MASTER;
Connect to the local MySQL root account on mynode2
SQL
Explicitly stop Group Replication
Disable auto-start of Group Replication
Remove replication channels
Fresh GTID state
mynode2root
sudo rm -f /var/lib/mysql/data/auto.cnf
systemctl restart mysql
mynode3
\c root@localhost
\sql
STOP GROUP_REPLICATION;
SET PERSIST group_replication_start_on_boot = OFF;
SET PERSIST group_replication_bootstrap_group = OFF;
RESET REPLICA ALL;
RESET MASTER;
Connect to the local MySQL root account on mynode3
SQL
Explicitly stop Group Replication
Disable auto-start of Group Replication
Remove replication channels
Fresh GTID state
mynode3 root
sudo rm -f /var/lib/mysql/data/auto.cnf
systemctl restart mysql
mynode1
\c root@localhost
\sql
SET GLOBAL local_infile = ON;
SET GLOBAL log_bin_trust_function_creators = 1;
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;
DROP DATABASE mydatabase;
CREATE DATABASE mydatabase;
Connect to the local MySQL root account on mynode1
SQL
Allow local files to be loaded
Allow stored procedures without DETERMINISTIC assertion
Make sure that the database is writable
DROP the database
ReCREATE the database
mynode1
\c root@mysource
\js
util.dumpInstance("/backup/mydumpdir")
Connect to mysource database
Javascript
Dump the instance to the local dump directory on mynode1
mynode1
\c root@localhost
\js
util.loadDump("/backup/mydumpdir",
{ ignoreVersion: true,
threads: 4,
loadData: true,
resetProgress: true } )
Connect to the local MySQL root account on mynode1
Javascript
Load the database from the dump directory on mynode1
Allow a MySQL 5.7 database to load into MySQL 8.0
Run four parallel threads
Load the Data
Start from the beginning (don't pick up from a previous load)
mynode1
\sql
ALTER TABLE mydatabase.mytable
ADD COLUMN _autopk BIGINT UNSIGNED NOT NULL
AUTO_INCREMENT INVISIBLE,
ADD PRIMARY KEY (_autopk);
SQL
Add an invisble auto_increment primary key to the table
mynode1
\js
\c icadmin@mynode1
dba.configureInstance()
dba.checkInstanceConfiguration()
var cluster = dba.createCluster("mycluster");
cluster.status()
cluster.rescan()
cluster.status()
cluster.addInstance("icadmin@mynode2:3306",
{ recoveryMethod: "clone" } )
cluster.status()
cluster.addInstance("icadmin@mynode3:3306",
{ recoveryMethod: "clone" } )
cluster.status()
cluster.status( { extended: 1 } )
Javascript
Reconnect to the cluster admin account on mynode1
Configure the instance for clustering
Check nothing will prevent the Instance from being clustered
Create cluster
Check Cluster status
Rescan to remove things like lingering internal cluster users
Check Cluster status
Add mynode2 (clone from mynode1)
Check Cluster status
Add mynode3 (clone from mynode1 or mynode2)
Check Cluster status
mynode1
\sql
SET GLOBAL local_infile = OFF;
SET GLOBAL log_bin_trust_function_creators = 0;
SQL
Reset parameters (as necessary)
Page updated
Google Sites
Report abuse