How to add nodes to Mysql 02/24 Update SLTechnology News&Howtos

How to add nodes to Mysql

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to increase nodes in Mysql". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

We know that a group of excellent cluster environments have a necessary feature, and that is extensibility. What about the expansibility of Group Replication? Let's set the following scenarios to see if Group Replicaiton is convenient for expansion:

The total number of transactions executed is small, and all binlog remains intact.

The total transaction volume is small, and binlog retains only part of it.

The total transaction volume is very large, and the binlog remains intact.

The total transaction volume is so large that binlog retains only part of it.

We are analyzing the above scenarios.

The total transaction volume is small, and the binlog remains intact. Then we can apply all the binlog directly to create an instance that is the same as the existing environment.

The total transaction volume is small, and the part of binlog is reserved. Binlog is missing in this scenario, and it is not possible to apply all binlog to create an instance that is the same as the existing environment. So to get an instance that is the same as the existing environment, we have to copy an instance from the existing environment and then add this instance to the cluster. There are several ways I can think of to copy:

Mysqldump

Xtrabackup

The total transaction volume is large, and the binlog remains intact. We can apply all the binlog to create a new instance as in the first environment. But with more transactions, it takes a lot of time to apply binlog. In order to improve efficiency, we still use to copy the instance to create a new instance.

There are many total transactions, and binlog only retains some of them. This scenario is similar to the second scenario, and we can only copy the instance to create a new instance.

Therefore, except for the first scenario, it is best to restore the pre-backup data to a new server and then enable synchronization. Let's do this experiment:

Machine name IP role

Qht131 172.17.61.131 primary

Qht132 172.17.61.132 secdnode1

Qht133 172.17.61.133 secdnode2

Qht134 172.17.61.134 secdnode3

1. Check the status of the current mgr:

Mysql > select * from performance_schema.replication_group_members

±--+

±--+

±--+

one

Create a table for test data:

Mysql > drop table test_mgr

Query OK, 0 rows affected (0.05 sec)

Mysql > create table test_mgr (C1 int (11) primary key)

Query OK, 0 rows affected (0.07 sec)

Mysql > insert into test_mgr values (1)

Query OK, 1 row affected (0.01sec)

one

Copy the full library backup to the target new library:

[root@qht131 backup] # mysqldump-uroot-p-all-databases-- triggers-- routines-- events-- master-data=2 > dbdump.db

[root@qht131 backup] # scp dbdump.db 172.17.61.134:/u01/backup

one

Do some operations on the database after the backup:

Mysql > insert into test_mgr values (2)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test_mgr values (3)

Query OK, 1 row affected (0.03 sec)

Mysql > select * from test_mgr

±- +

| | C1 |

±- +

| | 1 |

| | 2 |

| | 3 |

±- +

one

2.qht134 installs the database and restores the backup:

[root@qht134 backup] # mysql-uroot-p

< dbdump.db 3.配置my.cnf，配置文件注意server_id以及loose-group_replication_local_address和loose-group_replication_local_address。 [root@qht134 backup]# cat /etc/my.cnf [client] port = 3306 socket = /u01/mysql/mysql.sock [mysql] no-auto-rehash [mysqld] socket = /u01/mysql/mysql.sock character_set_server= utf8 init_connect= 'SET NAMES utf8' basedir= /usr/local/mysql datadir= /u01/mysql socket = /u01/mysql/mysql.sock log-error= /u01/log/mysql/mysql_3306.err pid-file= /u01/mysql/mysqld.pid lower_case_table_names = 1 sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION secure-file-priv = /u01/backup server-id=10004 log_bin = /u01/mysql/mysql_bin #skip-grant-tables #innodb_flush_log_at_trx_commit=1 #sync_binlog=1 #expire_logs_days=10 #max_binlog_size=1073741824 #autocommit=off #long_query_time=15 #slow_query_log=on log_slave_updates = ON relay_log_info_repository = TABLE master_info_repository = TABLE transaction_write_set_extraction = XXHASH64 binlog_format = ROW binlog_checksum = NONE enforce_gtid_consistency = ON gtid_mode = ON loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.17.61.134:33060" loose-group_replication_group_seeds= "172.17.61.131:33060,172.17.61.132:33060,172.17.61.133:33060,172.17.61.134:33060" loose-group_replication_bootstrap_group= off 1 重启数据库 1 [root@qht134 mysql]# service mysql start Starting MySQL… [ OK ] 1 4.修改原有节点的memeber信息：在qht131,qht132,qht133以分别执行： mysql>

Set global group_replication_group_seeds='172.17.61.131:33060172.17.61.132:33060172.17.61.133:33060172.17.61.134:33060'

Query OK, 0 rows affected (0.02 sec)

one

5. Establish a replication user on the new node

Mysql > set sql_log_bin=0

Query OK, 0 rows affected (0.00 sec)

Mysql > create user mgruser@'%' identified by 'mgruser'

ERROR 1396 (HY000): Operation CREATE USER failed for 'mgruser'@'%'

one

Mysql > set sql_log_bin=1

Query OK, 0 rows affected (0.00 sec)

one

By the way, the information of the replicated user has been recovered from mysqldump, so there is no need to re-establish it.

If the new node is not restored by backup, the replication user needs to be re-established.

6. Install the replication plug-in and start a new replication node

Mysql > install plugin group_replication soname 'group_replication.so'; # first show plugins; to check whether the replication plug-in is installed, and skip this step if it is installed

Mysql > change master to

-> master_user='mgruser'

-> master_password='mgruser'

-> for channel 'group_replication_recovery'

Query OK, 0 rows affected, 2 warnings (0.04 sec)

Mysql > start group_replication

Query OK, 0 rows affected (3.40 sec)

one

7. Query node status:

Mysql > select * from performance_schema.replication_group_members

±--+

±--+

±--+

4 rows in set (0.04 sec)

one

It was found that qht134 has successfully joined the replication group.

Mysql > select * from test_mgr

±- +

| | C1 |

±- +

| | 1 |

| | 2 |

| | 3 |

±- +

3 rows in set (0.00 sec)

one

The data is also synchronized to the latest state.

8. Subsequent operation

In order to keep the gr configuration valid after the next qht131,qht132,qht133 restart, you need to modify the my.cnf configuration:

Oose-group_replication_group_seeds= "172.17.61.131v 33060172.17.61.132v 33060172.17.61.133v 33060172.17.61.134v 33060"

one

This ensures that the configuration of GR is up-to-date after restarting the database.

This is the end of the content of "how to add nodes to Mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.