Let me introduce the group at the beginning. If you are interested in PolarDB, MongoDB, MySQL, PostgreSQL, Redis, Oceanbase, Sql Server, etc. and have questions, you can join the group if you have any needs. There are big names and CTOs in the major database industry in the group, who can solve your problems. . Please contact liuaustin3 to join the group. New friends will be divided into 2 groups (a total of about 1620 people 1 + 2 + 3 + 4). Group 3 has exceeded 490 and has been closed. Free application. If you want to join, please inform in advance. Newcomers will be added to group 4 ( 200), OpenGauss technical staff are also welcome to join.
Everyone’s thoughts
The funny Matthew in "Six People" passed away two days ago at the age of 54. An era has passed, it has come and gone, live every day well, every day is a new beginning, treat it as a good day.
—————————-
How to set up a master-slave in MySQL? I believe it is a very simple task in the hands of a MySQL DBA. xtrabackup the database, then restore it, and then configure the master-slave and start slave start.
Times have changed with MySQL 8. We no longer use this method to establish replication of databases based on MySQL 8. So what do we use? What is this method called innodb replication? Here we use the method to support MySQL replication through adminapi, and Perform manual switch between master and slave.
Based on this operation we need to confirm the following pre-information
1 MySQL version is recommended to be above 8.019
2 Data replication requires the use of GTID technology
3 Cannot use copy filtering
4 The number of slave libraries is not set during operation.
5 Support manual switching
6 The entire operation process uses MySQL Shell
7 Setting a host through the host can clearly identify itself and other hosts to be replicated.
Set the host name in the host where /etc/hosts needs to be used for replicaiton. If DNS supports it, you do not need to set it here.
127.0.0.1 localhost 127.0.0.1 mysql1 192.168.198.100 mysql1 192.168.198.101 mysql2 # The following lines are desirable for IPv6 capable hosts ::1 ip6-localhost ip6-loopback fe00::0 ip6-localnet ff00::0 ip6-mcastprefix ff02::1 ip6-allnodes ff02::2 ip6-allrouters ~ ~
The following is a step-by-step procedure for setting up replication and creating accounts in both databases. Then log in to the database through mysqlsh. It should be noted here that our commands are all based on js, so after entering mysqlshell, you need to operate in the JS command environment.
It is necessary to indicate here that the host used for replication this time is
192.168.198.100
192.168.198.101
[root@localhost][(none)]> create user repl@'%' identified by 'repl'; Query OK, 0 rows affected (10.01 sec) [root@localhost][(none)]> grant all on *.* to repl@'%' with grant option; Query OK, 0 rows affected (0.00 sec)
mysql@mysql1:~$ mysqlsh repl@'192.168.198.100' Please provide the password for '[email protected]': **** Save password for '[email protected]'? [Y]es/[N]o/Ne[v]er (default No): yes MySQL Shell 8.0.34 Copyright (c) 2016, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Creating a session to '[email protected]' Fetching schema names for auto-completion... Press ^C to stop. Your MySQL connection id is 11 (X protocol) Server version: 8.0.34 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL 192.168.198.100:33060 + ssl JS > dba.configureReplicaSetInstance() Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet... This instance reports its own address as mysql1:3306 Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. applierWorkerThreads will be set to the default value of 4. NOTE: Some configuration options need to be fixed: + ---------------------------------------- + -------- ------- + ---------------- + ------------------------- --- + | Variable | Current Value | Required Value | Note | + ---------------------------------------- + -------- ------- + ---------------- + ------------------------- --- + | binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable | + ---------------------------------------- + -------- ------- + ---------------- + ------------------------- --- + Do you want to perform the required configuration changes? [y/n]: y Configuring instance... The instance 'mysql1:3306' was configured to be used in an InnoDB ReplicaSet.
Here we need to pay attention to the host settings, prompting that the configuration in binlog_transaction_dependency_tracking needs to be modified to writeset. The purpose here is that in the source server with multi-thread replication, binlog_transaction_dependency_tracking specifies the way the source mysql generates dependency information. This way will Support MYSQL 8 to use parallel replication to determine which transactions can be replicated in parallel. Parallel replication here mainly uses logical timestamps. Replic_parallel_type and slave_parallel_type need to be set to logcial_clock, which includes sequence_number and last_committed to number the transactions, and pass last_committed Which specifies the sequence_number of the latest transaction in the binary log that conflicts with the current transaction.
It contains three values, commit_order, writset, writeset_session
1. If the time windows of the first transaction and the second transaction in commit_order overlap, the two transactions are considered independent. This is also based on the fact that the last statement of the transaction in the transaction submission ends as the focus. The two transactions are in Overlapping time periods cannot hold resources at the same time, so we think that the rows they update are different.
2 In writeset, each row in the writeset will have its own calculated hash value through commit_order and transaction. If during transmission, the hash values of the two transactions are found to be consistent, indicating a conflict.
3 writeset_session is similar to writeset but adds the SESSION link. It uses session to determine whether there is a transaction conflict in a session.
If the replicaiton method is used here, the minimum value is required to be writeset, so when I set up innodb_replication immediately above, I will judge whether the configuration is feasible. If not, modify the database configuration with permission.
After executing the relevant commands, the mysqld-auto file will be generated
mysql@mysql1:~$ cat /data/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"slave_parallel_workers": {"Value": "4", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809532860 }}, "replica_parallel_workers": {"Value": "4", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809532860}}}, "mysql_dynamic_variables": { "binlog_transaction_dependency_tracking": {"Value": "WRITESET", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809533983}}}}mysql@mysql1:~$</ pre>
<p>Then we need to create the first cluster on the main library. The name of the cluster is repl</p>
<pre>MySQL 192.168.198.100:33060 + ssl JS > dba.createReplicaSet('repl')
A new replicaset with instance 'mysql1:3306' will be created.
* Checking MySQL instance at mysql1:3306
This instance reports its own address as mysql1:3306
mysql1:3306: Instance configuration is suitable.
* Checking connectivity and SSL configuration...
*Updating metadata...
ReplicaSet object successfully created for mysql1:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
<ReplicaSet:repl>
Some students may encounter certain errors during the construction as follows. This error occurs when the host name and IP resolution are consistent and causes an error.
2023-10-30T03:03:50.912684-00:00 14 [ERROR] [MY-013117] [Repl] Replica I/O for channel 'mysqlsh.test': Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it). Error_code: MY-013117
2023-10-30T03:03:50.912755-00:00 14 [Note] [MY-010570] [Repl] Replica I/O thread exiting for channel 'mysqlsh.test', read up to log 'FIRST', position 4< /pre>
<p>The above operations are mainly for the settings before the replicaiton operation for mysql1. The following also requires the settings for mysql2 before the replication operation. This is done through a simple command to add a slave section on the master node. The host CLONE method is used here. Here you need to pay attention to the data space of the master and slave libraries.</p>
<pre>MySQL mysql1:33060 + ssl JS > repl.addInstance('mysql2:3306')
Adding instance to the replicaset...
* Performing validation checks
This instance reports its own address as mysql2:3306
mysql2:3306: Instance configuration is suitable.
* Checking async replication topology...
* Checking connectivity and SSL configuration...
* Checking transaction state of the instance...
NOTE: The target instance 'mysql2:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'mysql2:3306' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod ' option to 'clone'.
WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
*Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: mysql2:3306 is being cloned from mysql1:3306
** Stage DROP DATA: Completed
** Clone Transfer FILE COPY ============================================== ================ 0% Not Started PAGE COPY ============================ ================================ 0% Not Started REDO COPY ============ ================================================ 0% Not Started** Clone Transfer FILE COPY ######################################### ################## 100% Completed PAGE COPY ########################### ################################ 100% Completed REDO COPY ############ ############################################### 100% Completed
NOTE: mysql2:3306 is shutting down...
* Waiting for server restart... ready
* mysql2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.66 MB transferred in about 1 second (~73.66 MB/s)
** Changing replication source of mysql2:3306 to mysql1:3306
** Waiting for new instance to synchronize with PRIMARY...
** Transactions replicated ============================================== ============== 0% ** Transactions replicated ############################# ############################= 98% ** Transactions replicated ############## ############################################ 100%
The instance 'mysql2:3306' was added to the replicaset and is replicating from mysql1:3306.
* Waiting for instance 'mysql2:3306' to synchronize the Metadata updates with the PRIMARY...
** Transactions replicated ============================================== ============== 0% ** Transactions replicated ############################# ###########################== 97% ** Transactions replicated ############## ############################################ 100%</ pre>
<pre>MySQL mysql1:33060 + ssl JS > repl.status()
{
"replicaSet": {
"name": "repl",
"primary": "mysql1:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mysql1:3306": {
"address": "mysql1:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"mysql2:3306": {
"address": "mysql2:3306",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Waiting for an event from Coordinator",
"applierWorkerThreads": 4,
"receiverStatus": "ON",
"receiverThreadState": "Waiting for source to send event",
"replicationLag": null,
"replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3"
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
At this point, MySQL’s innodb_replicaset has been built.
There are many advantages of innodb replicaset here. Among them, the convenience of master-slave switching is mentioned. Here we perform master-slave switching. The following steps directly switch the master-slave database.
MySQL mysql1:33060 + ssl JS > repl.setPrimaryInstance('mysql2:3306')
mysql2:3306 will be promoted to PRIMARY of 'repl'.
The current PRIMARY is mysql1:3306.
* Connecting to replicaset instances
** Connecting to mysql1:3306
** Connecting to mysql2:3306
** Connecting to mysql1:3306
** Connecting to mysql2:3306
* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...
* Synchronizing transaction backlog at mysql2:3306
** Transactions replicated ============================================== ============== 0% ** Transactions replicated ############################# ############################# 100% ** Transactions replicated ############## ############################################ 100%
*Updating metadata
* Acquiring locks in replicaset instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES
*Updating replication topology
** Changing replication source of mysql1:3306 to mysql2:3306
mysql2:3306 was promoted to PRIMARY.
MySQL mysql1:33060 + ssl JS > repl.status()
{
"replicaSet": {
"name": "repl",
"primary": "mysql2:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mysql1:3306": {
"address": "mysql1:3306",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Waiting for an event from Coordinator",
"applierWorkerThreads": 4,
"receiverStatus": "ON",
"receiverThreadState": "Waiting for source to send event",
"replicationLag": null,
"replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3"
},
"status": "ONLINE"
},
"mysql2:3306": {
"address": "mysql2:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
Up to now, MySQL8 has been able to carry out secondary development in the establishment and basic maintenance of innodb replica and centrally manage a large number of master-slave replication function preparations. Simple commands can quickly set up replication or perform master-slave switching.
