You deploy InnoDB ReplicaSet in a similar way to
InnoDB Cluster. First you configure some MySQL server instances,
the minimum is two instances, see
Section 6.1, “Using MySQL AdminAPI”. One functions as the
primary, in this tutorial rs-1
; the other
instance functions as the secondary, in this tutorial
rs-2
; which replicates the transactions applied
by the primary. This is the equivalent of the source and replica
known from asynchronous MySQL replication. Then you connect to one
of the instances using MySQL Shell, and create a ReplicaSet. Once
the ReplicaSet has been created, you can add instances to it.
InnoDB ReplicaSet is compatible with sandbox instances, which you can use to deploy locally, for example for testing purposes. See Section 6.2.1, “Deploying Sandbox Instances” for instructions. However, this tutorial assumes you are deploying a production InnoDB ReplicaSet, where each instance is running on a different host.
To use InnoDB ReplicaSet you should be aware of the following prerequisites:
Only instances running MySQL version 8.0 and later are supported
Only GTID-based replication is supported, binary log file position replication is not compatible with InnoDB ReplicaSet
Only Row Based Replication (RBR) is supported, Statement Based Replication (SBR) is unsupported
Replication filters are not supported
Unmanaged replication channels are not allowed on any instance
A ReplicaSet consists of maximum one primary instance, and one or multiple secondaries are supported. Although there is no limit to the number of secondaries you can add to a ReplicaSet, each MySQL Router connected to a ReplicaSet has to monitor each instance. Therefore, the more instances that are added to a ReplicaSet, the more monitoring has to be done.
The ReplicaSet must be entirely managed by MySQL Shell. For example, the replication account is created and managed by MySQL Shell. Making configuration changes to the instance outside of MySQL Shell, for example using SQL statements directly to change the primary, is not supported. Always use MySQL Shell to work with InnoDB ReplicaSet.
AdminAPI and InnoDB ReplicaSet enable you to work with MySQL replication without a deep understanding of the underlying concepts. However, for background information see Replication.
Use
dba.configureReplicaSetInstance(
to configure each instance you want to use in your replica set.
MySQL Shell can either connect to an instance and then
configure it, or you can pass in instance
)instance
to
configure a specific remote instance. To use an instance in a
ReplicaSet, it must support persisting settings. See
Section 6.1.5, “Persisting Settings”.
When you connect to the instance for administration tasks you
require a user with suitable privileges. The preferred method to
create users to administer a ReplicaSet is using the
setupAdminAccount()
operation. See
Section 6.1.7, “Creating User Accounts for AdminAPI”.
Alternatively, the
dba.configureReplicaSetInstance()
operation
can optionally create an administrator account, if the
clusterAdmin
option is provided. The account
is created with the correct set of privileges required to manage
InnoDB ReplicaSet.
The administrator account must have the same user name and password across all instances of the same cluster or replica set.
To configure the instance at rs-1:3306
, with
a cluster administrator named rsadmin
issue:
mysql-js> dba.configureReplicaSetInstance('root@rs-1:3306', {clusterAdmin: "'rsadmin'@'rs-1%'"});
The interactive prompt requests the password required by the specified user. To configure the instance MySQL Shell is currently connected to, you can specify a null instance definition. For example issue:
mysql-js> dba.configureReplicaSetInstance('', {clusterAdmin: "'rsadmin'@'rs-1%'"});
The interactive prompt requests the password required by the specified user. This checks the instance which MySQL Shell is currently connected to is valid for use in an InnoDB ReplicaSet. Settings which are not compatible with InnoDB ReplicaSet are configured if possible. The cluster administrator account is created with the privileges required for InnoDB ReplicaSet.
Once you have configured your instances, connect to an instance
and use dba.createReplicaSet()
to create a
managed ReplicaSet that uses MySQL asynchronous replication, as
opposed to MySQL Group Replication used by InnoDB Cluster. The
MySQL instance which MySQL Shell is currently connected to is
used as the initial primary of the ReplicaSet.
The dba.createReplicaSet()
operation performs
several checks to ensure that the instance state and
configuration are compatible with a managed ReplicaSet and if
so, a metadata schema is initialized on the instance. If you
want to check the operation but not actually make any changes to
the instances, use the dryRun
option. This
checks and shows what actions the MySQL Shell would take to
create the ReplicaSet. If the ReplicaSet is created
successfully, a ReplicaSet
object is
returned. Therefore it is best practice to assign the returned
ReplicaSet
to a variable. This enables you to
work with the ReplicaSet, for example by calling the
operation. To create a ReplicaSet named
ReplicaSet
.status()example
on instance
rs-1
and assign it to the
rs
variable, issue:
mysql-js> \connect root@rs-1:3306
...
mysql-js> var rs = dba.createReplicaSet("example")
A new replicaset with instance 'rs-1:3306' will be created.
* Checking MySQL instance at rs-1:3306
This instance reports its own address as rs-1:3306
rs-1:3306: Instance configuration is suitable.
* Updating metadata...
ReplicaSet object successfully created for rs-1:3306.
Use rs.add_instance() to add more asynchronously replicated instances to this replicaset
and rs.status() to check its status.
To verify that the operation was successful, you work with the
returned ReplicaSet
object. For example this
provides the
operation, which displays information about the ReplicaSet. We
already assigned the returned ReplicaSet
.status()ReplicaSet
to
the variable rs
, so issue:
mysql-js> rs.status()
{
"replicaSet": {
"name": "example",
"primary": "rs-1:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"rs-1:3306": {
"address": "rs-1:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
This output shows that the ReplicaSet named
example
has been created, and that the
primary is rs-1
. Currently there is only one
instance, and the next task is to add more instances to the
ReplicaSet.
When creating an InnoDB ReplicaSet using MySQL Shell 8.0.28 and
later, if you have security requirements that want all accounts
created automatically by AdminAPI to have strict authentication
requirements, you can set a value for the
replicationAllowedHost
configuration option
of the ReplicaSet. The replicationAllowedHost
MySQL Shell option allows you to set internally managed
replication accounts for a ReplicaSet to a strict subnet based
filter instead of the default wildcard value of
%
.The
replicationAllowedHost
option can take a
string value. For example, to set the
replicationAllowedHost
to
192.0.2.0/24
, issue:
mysql-js> var rs = dba.createReplicaSet('example', {replicationAllowedHost:'192.0.2.0/24'})
A new replicaset with instance 'rs-1:3306' will be created.
* Checking MySQL instance at rs-1:3306
This instance reports its own address as rs-1:3306
rs-1:3306: Instance configuration is suitable.
* Updating metadata...
ReplicaSet object successfully created for rs-1:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
An InnoDB ReplicaSet can be modified after creation to set a
replicationAllowedHost
through the
setOption
configuration option, by issuing:
mysql-js> rs.setOption('replicationAllowedHost', '192.0.2.0/24')