西安cms模板建站,购物网站的推广,如何建立本站站点,全球网站排行榜下载mysql安装包#xff1a;
https://dev.mysql.com/downloads/mysql/5.7.html#downloads
准备环境
1、准备三台服务器并设置hosts
192.168.236.143 mysql1
192.168.236.144 mysql2
192.168.236.145 mysql32、设置免密登陆
#生成秘钥
ssh-keygen -t rsa
#一直按Enter即可…下载mysql安装包
https://dev.mysql.com/downloads/mysql/5.7.html#downloads
准备环境
1、准备三台服务器并设置hosts
192.168.236.143 mysql1
192.168.236.144 mysql2
192.168.236.145 mysql32、设置免密登陆
#生成秘钥
ssh-keygen -t rsa
#一直按Enter即可
#将秘钥分别拷贝到另外两个机器
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.236.144
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.236.145
#测试
ssh 192.168.236.1433、关闭防火墙
#关闭防火墙
systemctl stop firewalld
#禁用防火墙
systemctl disable firewalld4、关闭selinux
vi /etc/selinux/config
#设置
SELINUXdisabled重启服务器
安装mysql
#解压
[rootmysql1 data]# tar -xvf mysql-cluster-community-8.0.34-1.el7.x86_64.rpm-bundle.tar
mysql-cluster-community-client-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-client-plugins-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-common-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-data-node-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-debuginfo-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-devel-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-embedded-compat-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-icu-data-files-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-java-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-libs-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-libs-compat-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-management-server-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-ndbclient-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-ndbclient-devel-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-nodejs-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-server-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-server-debug-8.0.34-1.el7.x86_64.rpm
mysql-cluster-community-test-8.0.34-1.el7.x86_64.rpm1、依次安装 rpm -ivh mysql-cluster-community-common-8.0.34-1.el7.x86_64.rpm --nodeps --forcerpm -ivh mysql-cluster-community-libs-8.0.34-1.el7.x86_64.rpm --nodeps --forcerpm -ivh mysql-cluster-community-client-8.0.34-1.el7.x86_64.rpm --nodeps --forcerpm -ivh mysql-cluster-community-server-8.0.34-1.el7.x86_64.rpm --nodeps --force2、配置mysql
#初始化mysql
mysqld --initialize
#给mysql分组中的mysql用户赋权限
chown mysql:mysql /var/lib/mysql -R
#启动mysql服务器
systemctl start mysqld.service
#设置开机启动mysql
systemctl enable mysqld3、查看mysql初始化的密码
[rootmysql1 data]# cat /var/log/mysqld.log | grep password
2023-08-09T05:11:28.204159Z 6 [Note] [MY-010454] [Server] A temporary password is generated for rootlocalhost: )c)ea3,O密码即是最后面的)c)ea3,O 4、修改数据库密码
#进入mysql
mysql -u root -p
#然后输入密码即可进入mysql
#修改密码
ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 1234;
#退出mysql
exit;
#创建账号名和密码
create user admin% identified with mysql_native_password by admin;
#授权远程链接
grant all privileges on *.* to admin% with grant option;
#刷新权限
flush privileges;5、mysql服务命令
#查看mysql版本
mysql -V
#启动mysql服务
systemctl start mysqld.service
#停止mysql服务
systemctl stop mysqld.service
#重启mysql服务
systemctl restart mysqld.service
#查看mysql服务当前状态
systemctl status mysqld.service
#设置mysql服务开机自启动
systemctl enable mysqld.service
#停止mysql服务开机自启动
systemctl disable mysqld.service安装mysql shell
下载https://downloads.mysql.com/archives/shell/
#安装
rpm -ivh mysql-shell-8.0.33-1.el7.x86_64.rpm集群配置
1、修改配置文件
vi /etc/my.cnf
#mysql1配置文件增加
server-id1
gtid_modeon
enforce_gtid_consistencyon
binlog_checksumnone
transaction_write_set_extraction XXHASH64
loose-group_replication_recovery_use_ssl ON
loose-group_replication_group_name 5dbabbe6-8050-49a0-9131-1de449167446
loose-group_replication_start_on_boot OFFloose-group_replication_local_address mysql1:24901
loose-group_replication_group_seeds mysql1:24901,mysql2:24902,mysql3:24903
loose-group_replication_bootstrap_group OFF#mysql2配置文件增加
server-id2
gtid_modeon
enforce_gtid_consistencyon
binlog_checksumnone
transaction_write_set_extraction XXHASH64
loose-group_replication_recovery_use_ssl ON
loose-group_replication_group_name 5dbabbe6-8050-49a0-9131-1de449167446
loose-group_replication_start_on_boot OFFloose-group_replication_local_address mysql2:24901
loose-group_replication_group_seeds mysql1:24901,mysql2:24902,mysql3:24903
loose-group_replication_bootstrap_group OFF#mysql3配置文件增加
server-id3
gtid_modeon
enforce_gtid_consistencyon
binlog_checksumnone
transaction_write_set_extraction XXHASH64
loose-group_replication_recovery_use_ssl ON
loose-group_replication_group_name 5dbabbe6-8050-49a0-9131-1de449167446
loose-group_replication_start_on_boot OFFloose-group_replication_local_address mysql3:24901
loose-group_replication_group_seeds mysql1:24901,mysql2:24902,mysql3:24903
loose-group_replication_bootstrap_group OFF2、启动mysql并使用shell连接
#启动mysql
systemctl start mysqld.service
#在每个服务器上都是用shell连接mysql
mysqlsh adminlocalhost:3306
#每个服务器都执行
dba.configureInstance();
dba.checkInstanceConfiguration(adminlocalhost:3306);3、进入mysql安装组复制插件
mysql INSTALL PLUGIN group_replication SONAME group_replication.so;4、进入mysql shell创建集群(注意只在其中一台服务器上执行即可) MySQL localhost:3306 ssl JS var cluster dba.createCluster(myCluster);5、添加节点 MySQL localhost:3306 ssl JS cluster.addInstance(adminmysql1:3306);#创建节点即添加可以不执行MySQL localhost:3306 ssl JS cluster.addInstance(adminmysql2:3306);MySQL localhost:3306 ssl JS cluster.addInstance(adminmysql3:3306);6、查看当前节点状态 MySQL localhost:3306 ssl JS cluster.status()
{clusterName: myCluster, defaultReplicaSet: {name: default, primary: mysql1:3306, ssl: REQUIRED, status: OK, statusText: Cluster is ONLINE and can tolerate up to ONE failure., topology: {mysql1:3306: {address: mysql1:3306, memberRole: PRIMARY, mode: R/W, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.0.34}, mysql2:3306: {address: mysql2:3306, memberRole: SECONDARY, mode: R/O, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.0.34}, mysql3:3306: {address: mysql3:3306, memberRole: SECONDARY, mode: R/O, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.0.34}}, topologyMode: Single-Primary}, groupInformationSourceMember: mysql1:3306
}7、查看节点信息任意节点即可
mysql select * from performance_schema.replication_group_members;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| group_replication_applier | 2ef5a678-3673-11ee-b7fb-000c29d4f7b1 | mysql2 | 3306 | ONLINE | SECONDARY | 8.0.34 | MySQL |
| group_replication_applier | 2efc06f0-3673-11ee-8284-000c29b47d6f | mysql1 | 3306 | ONLINE | PRIMARY | 8.0.34 | MySQL |
| group_replication_applier | 2f144a98-3673-11ee-b663-000c2973412e | mysql3 | 3306 | ONLINE | SECONDARY | 8.0.34 | MySQL |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)安装mysql router
下载地址https://downloads.mysql.com/archives/router/
# 安装
rpm -ivh mysql-router-community-8.0.33-1.el7.x86_64.rpm
#查看mysqlrouter版本
mysqlrouter --version配置router
vim /etc/mysqlrouter/mysqlrouter.conf
#增加如下配置
[routing:secondary]
bind_address 0.0.0.0
bind_port 7001
destinations mysql1:3306,mysql2:3306,mysql3:3306
routing_strategy round-robin[routing:primary]
bind_address 0.0.0.0
bind_port 7002
destinations mysql1:3306,mysql2:3306,mysql3:3306
routing_strategy first-available启动mysql router
mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf 测试连接情况
#连接mysql
mysql -h 192.168.236.145 -P 7001 -uadmin -padmin
mysql -h 192.168.236.145 -P 7002 -uadmin -padmin
#执行命令获取Mysql中配置的server_id每次执行返回的server_id不一样因为使用轮询策略
mysql -h192.168.236.145 -uadmin -padmin -e select server_id -P 7001
#执行命令获取Mysql中配置的server_id每次执行返回的server_id都一样
mysql -h192.168.236.145 -uadmin -padmin -e select server_id -P 7002查看监听状态
[rootmysql3 data]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp6 0 0 ::1:7001 :::* LISTEN 13179/mysqlrouter
tcp6 0 0 ::1:7002 :::* LISTEN 13179/mysqlrouter 测试
使用工具连接测试关掉其中一台数据库仍然可以连接 通过mysql命令查询primary已经切换了
mysql select * from performance_schema.replication_group_members;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| group_replication_applier | 2ef5a678-3673-11ee-b7fb-000c29d4f7b1 | mysql2 | 3306 | ONLINE | PRIMARY | 8.0.34 | MySQL |
| group_replication_applier | 2efc06f0-3673-11ee-8284-000c29b47d6f | mysql1 | 3306 | ONLINE | SECONDARY | 8.0.34 | MySQL |
| group_replication_applier | 2f144a98-3673-11ee-b663-000c2973412e | mysql3 | 3306 | ONLINE | SECONDARY | 8.0.34 | MySQL |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.05 sec)
