Centos7.2下安装mysql-group-replication数据库集群

最新推荐文章于 2024-03-17 11:43:57 发布

weixin_34284188

最新推荐文章于 2024-03-17 11:43:57 发布

阅读量268

点赞数

文章标签：数据库 python 运维

原文链接：https://my.oschina.net/u/2477500/blog/1830407

版权

2019独角兽企业重金招聘Python工程师标准>>>

系统版本：Centos7.2 x64

数据库版本：mysql5.7.17

1.首先安装环境：

yum -y install gcc gcc-c++ libaio-devel boost-devel autoconf automake zlib-devel libxml2-devel ncurses-devel libgcrypt-devel libtool-devel openssl-devel bison-devel unzip numactl-devel

2.关闭selinux和防火墙

setenforce 0
systemctl stop firewalld
systemctl disabled firewalld

3.下载mysql安装包

wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar

解压：

tar xf mysql-5.7.17-linux-glibc2.5-x86_64.tar

tar zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

将解压后的文件夹移动到/user/local并且修改为mysql5.7

mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql5.7


配置环境变量
mysql-5.7.17-linux-glibc2.5-x86_64.tar

source /etc/profile

5.创建data目录和conf文件

进入mysql5.7

mkdir data/3306 data/3307 data/3308

mkdir conf

分别创建3306.cnf 3307.cnf 3308.cnf

6.实例化并且启动mysql

实例化：

/usr/local/mysql5.7/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data/3306

/usr/local/mysql5.7/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data/3307

/usr/local/mysql5.7/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data/3308

会报一些警告信息：

2018-06-14T03:17:32.915314Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-14T03:17:33.980961Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-06-14T03:17:34.192176Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-06-14T03:17:34.256360Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7b29387c-6f81-11e8-abac-00163e06e59d.
2018-06-14T03:17:34.259013Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-06-14T03:17:34.259401Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

分别启动三个实例：

/usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/conf/3306.cnf &

/usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/conf/3307.cnf &

/usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/conf/3308.cnf &

查看mysql三个实例是否启动成功

[root@iZm5e7kkxs23uutsa9dbl5Z mysql5.7]# ps -ef | grep mysqld
root     22236  9200  1 11:18 pts/0    00:00:00 /usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/conf/3306.cnf
root     22265  9200  2 11:18 pts/0    00:00:00 /usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/conf/3307.cnf
root     22294  9200  3 11:18 pts/0    00:00:00 /usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/conf/3308.cnf
root     22326  9200  0 11:18 pts/0    00:00:00 grep --color=auto mysqld

说明启动成功

4.数据库相关配置

进入3306节点，作为主节点

执行以下操作：

mysql -S /usr/local/mysql5.7/data/3306/mysqld.sock

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)


mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START group_replication;
Query OK, 0 rows affected (1.07 sec)

mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;

进入3307节点

mysql -S /usr/local/mysql5.7/data/3307/mysqld.sock

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.00 sec)

mysql> START group_replication;
Query OK, 0 rows affected (5.59 sec)

mysql> exit;

进入3308节点：

mysql -S /usr/local/mysql5.7/data/3308/mysqld.sock

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.25 sec)

进入三个任意一个节点验证：

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST             | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------------------+-------------+--------------+
| group_replication_applier | 7b29387c-6f81-11e8-abac-00163e06e59d | iZm5e7kkxs23uutsa9dbl5Z |        3306 | ONLINE       |
| group_replication_applier | 8badfa8f-6f81-11e8-aec3-00163e06e59d | iZm5e7kkxs23uutsa9dbl5Z |        3307 | ONLINE       |
| group_replication_applier | 90f24e32-6f81-11e8-aef5-00163e06e59d | iZm5e7kkxs23uutsa9dbl5Z |        3308 | ONLINE       |
+---------------------------+--------------------------------------+-------------------------+-------------+--------------+
3 rows in set (0.00 sec)

5.数据同步测试

进入到3306节点

mysql> create table tb_stu(id int not null primary key auto_increment,name varchar(50));
Query OK, 0 rows affected (0.02 sec)

mysql> select * from tb_stu;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

进入3307节点：

mysql> select * from tb_stu;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

同样进入3308节点验证：

mysql> select * from tb_stu;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

说明三个节点能够进行数据同步。

加入我们在3307或者3308节点插入数据：

mysql>  insert tb_stu(id,name) values (1,'tom');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

报错原因是：3306是主节点，3307和3308是从节点，单主模式下只能在主节点写入，从节点只能查询数据。

下面我们在进行多主节点修改和增加节点。

转载于:https://my.oschina.net/u/2477500/blog/1830407