首页
常用命令
About Me
推荐
weibo
github
Search
1
linuxea:gitlab-ci之docker镜像质量品质报告
48,996 阅读
2
linuxea:如何复现查看docker run参数命令
20,462 阅读
3
Graylog收集文件日志实例
18,023 阅读
4
git+jenkins发布和回滚示例
17,602 阅读
5
linuxea:jenkins+pipeline+gitlab+ansible快速安装配置(1)
17,574 阅读
ops
Openvpn
Sys Basics
rsync
Mail
NFS
Other
Network
HeartBeat
server 08
Code
Awk
Shell
Python
Golang
virtualization
KVM
Docker
openstack
Xen
kubernetes
kubernetes-cni
Service Mesh
Data
Mariadb
PostgreSQL
MongoDB
Redis
MQ
Ceph
TimescaleDB
kafka
surveillance system
zabbix
ELK Stack
Open-Falcon
Prometheus
Web
apache
Tomcat
Nginx
自动化
Puppet
Ansible
saltstack
Proxy
HAproxy
Lvs
varnish
更多
音乐
影视
music
Internet Consulting
最后的净土
软件交付
持续集成
gitops
devops
登录
Search
标签搜索
kubernetes
docker
zabbix
Golang
mariadb
持续集成工具
白话容器
linux基础
nginx
elk
dockerfile
Gitlab-ci/cd
最后的净土
基础命令
jenkins
docker-compose
gitops
haproxy
saltstack
Istio
marksugar
累计撰写
676
篇文章
累计收到
140
条评论
首页
栏目
ops
Openvpn
Sys Basics
rsync
Mail
NFS
Other
Network
HeartBeat
server 08
Code
Awk
Shell
Python
Golang
virtualization
KVM
Docker
openstack
Xen
kubernetes
kubernetes-cni
Service Mesh
Data
Mariadb
PostgreSQL
MongoDB
Redis
MQ
Ceph
TimescaleDB
kafka
surveillance system
zabbix
ELK Stack
Open-Falcon
Prometheus
Web
apache
Tomcat
Nginx
自动化
Puppet
Ansible
saltstack
Proxy
HAproxy
Lvs
varnish
更多
音乐
影视
music
Internet Consulting
最后的净土
软件交付
持续集成
gitops
devops
页面
常用命令
About Me
推荐
weibo
github
搜索到
29
篇与
mariadb
的结果
2020-03-24
linuxea: 基于Galera cluster的MaxScale 2.4读写分离笔记
在此前的两个版本中,不同于数据库集群大小和数据大小做了不同的线上测试,有一系列问题,最终被下架更换到haproxy。在新的环境中使用了2.4,因为2.4有很多变动。仍然保持热情的进行体验。Galera cluster集群模式中节点都可以作为读写,但是通常而言,会在一台节点进行写入,其他节点读取。因此选择读写分离模块readwritesplit路由模式。readwritesplit读写分离readwritesplit旨在提高群集的只读处理能力,同时保持一致性。不修改数据的数据分散在多个节点上,而所有写查询都将发送到单个节点上。那么,除了readwritesplit,maxscale还提供SchemaRouter和SmartRouter,有兴趣可以查看。此外,maxscale提供了新的Clustrix作为监视工具MaxCtrl安装建议下载rpm包,查看相应的版本下载即可。假如打不开,推荐到github下载压缩包进行安装即可下载完成在本地安装即可yum localinstall maxscale-2.4.7-1.centos.7.x86_64.rpm -y他的配置文件默认在/etc下[root@linuxea_Node ~]# rpm -ql maxscale |grep etc /etc/maxscale.cnf.template /etc/prelink.conf.d /etc/prelink.conf.d/maxscale.conf配置我现在有两台数据库是Galera cluster集群,现在我们配置成maxscale读写分离配置文件分几个部分,分别是全局,节点,service,监控模块和listener。相比之前的版本少了监控的端口配置。其他配置参考官网配置。全局配置全局配置中配置一些必要的配置信息节点配置除了必要的信息之外,可以添加priority设置优先级,如果为0,永远不会成为主节点用户请求从4006进入,将请求发给RW-Service,RW-Service根据读写分离策略将请求发给后端,Galera-Monitor作为管理和调度如下:# 全局配置 [maxscale] threads=auto ms_timestamp=1 syslog=0 maxlog=1 log_warning=1 log_notice=0 log_info=0 log_augmentation=1 # service配置 [RW-Service] type=service router=readwritesplit user=maxscale password=F3BF776B14BE7CAB61E83256E1C2B56C cluster=Galera-Monitor #listener [RW-Listener] type=listener service=RW-Service protocol=MariaDBClient address=0.0.0.0 port=4006 # 节点配置 [server1] type=server address=172.25.109.5 port=3306 protocol=MariaDBBackend [server2] type=server address=172.25.109.4 port=3306 protocol=MariaDBBackend # 监控模块 [Galera-Monitor] type=monitor module=galeramon servers=server1,server2 user=maxscale # maxkeys /var/lib/maxscale/ # maxpasswd /var/lib/maxscale KLJMKMXOPQOP1 KLJMKMXOPQOP1是授权的密码 password=F3BF776B14BE7CAB61E83256E1C2B56C monitor_interval=10000 disable_master_failback=true use_priority=true数据库授权授权给maxscaleCREATE USER 'maxscale'@'%' IDENTIFIED BY 'password'; GRANT SELECT ON mysql.user TO 'maxscale'@'%'; GRANT SELECT ON mysql.db TO 'maxscale'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';完成授权后,修改maxscale的配置文件password字段[root@linuxea_node_maxscale-1 ~]# maxkeys /var/lib/maxscale/ [root@linuxea_node_maxscale-1 ~]# maxpasswd /var/lib/maxscale password 70DB8B57832941E87AB14C5BC47C6292 [root@linuxea_node_maxscale-1 ~]# sed -i 's/password=.*/password=70DB8B57832941E87AB14C5BC47C6292/g' /etc/maxscale.cnf而后启动[root@linuxea_node_maxscale-1 ~]# systemctl restart maxscale [root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log MariaDB MaxScale /var/log/maxscale/maxscale.log Sat Mar 21 17:03:08 2020 ---------------------------------------------------------------------------- 2020-03-21 17:03:08 notice : (mxb_log_set_syslog_enabled): syslog logging is disabled. 2020-03-21 17:03:08 notice : (mxb_log_set_maxlog_enabled): maxlog logging is enabled. 2020-03-21 17:03:08.752 notice : (mxb_log_set_highprecision_enabled): highprecision logging is enabled. 2020-03-21 17:03:08.752 notice : (mxb_log_set_priority_enabled): The logging of warning messages has been enabled.监控模块[root@linuxea_node_maxscale-1 ~]# maxctrl list services ┌────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤ │ RW-Service │ readwritesplit │ 0 │ 0 │ server1, server2 │ └────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘[root@linuxea_node_maxscale-1 ~]# maxctrl list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────────────┬───────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤ │ server1 │ 172.25.109.5 │ 3306 │ 0 │ Slave, Synced, Running │ │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤ │ server2 │ 172.25.109.4 │ 3306 │ 0 │ Master, Synced, Running │ 0-1-6 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────────────┴───────┘[root@linuxea_node_maxscale-1 ~]# maxctrl list listeners RW-Service ┌──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┐ │ Name │ Port │ Host │ State │ ├──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ { │ { │ { │ { │ │ "id": "RW-Listener", │ "id": "RW-Listener", │ "id": "RW-Listener", │ "id": "RW-Listener", │ │ "type": "listeners", │ "type": "listeners", │ "type": "listeners", │ "type": "listeners", │ │ "attributes": { │ "attributes": { │ "attributes": { │ "attributes": { │ │ "state": "Running", │ "state": "Running", │ "state": "Running", │ "state": "Running", │ │ "parameters": { │ "parameters": { │ "parameters": { │ "parameters": { │ │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ │ "port": 4006, │ "port": 4006, │ "port": 4006, │ "port": 4006, │ │ "socket": null, │ "socket": null, │ "socket": null, │ "socket": null, │ │ "authenticator_options": "", │ "authenticator_options": "", │ "authenticator_options": "", │ "authenticator_options": "", │ │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ │ "authenticator": null, │ "authenticator": null, │ "authenticator": null, │ "authenticator": null, │ │ "ssl": "false", │ "ssl": "false", │ "ssl": "false", │ "ssl": "false", │ │ "ssl_cert": null, │ "ssl_cert": null, │ "ssl_cert": null, │ "ssl_cert": null, │ │ "ssl_key": null, │ "ssl_key": null, │ "ssl_key": null, │ "ssl_key": null, │ │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ │ }, │ }, │ }, │ }, │ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ │ { │ { │ { │ { │ │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ │ "host": "%" │ "host": "%" │ "host": "%" │ "host": "%" │ │ }, │ }, │ }, │ }, │ │ { │ { │ { │ { │ │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ │ "host": "%" │ "host": "%" │ "host": "%" │ "host": "%" │ │ } │ } │ } │ } │ │ ] │ ] │ ] │ ] │ │ } │ } │ } │ } │ │ } │ } │ } │ } │ └──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┘ [root@linuxea_node_maxscale-1 ~]# 用户授权数据库授权后,每一个登陆的授权用户同时仍然需要授权给maxscale。比如:maxscale的ip是172.25.109.8。假设现在授权一个用户sean用户从172.25.111.6来进行访问。使用maxscale,还需要将sean授权给maxscale的ip,权限及密码。CREATE DATABASE linuxea_com CHARACTER SET utf8mb4; GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password';MariaDB [(none)]> CREATE DATABASE linuxea_com CHARACTER SET utf8mb4; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)假如不授权,是可以登陆,但是会提示没有权限[root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log 2020-03-21 17:10:04.865 error : (129) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES) 2020-03-21 17:10:09.961 error : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server1'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES) 2020-03-21 17:10:09.961 error : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES)如下:[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 124 Server version: 10.0.33-MariaDB-wsrep Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 129 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 132 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.我们在数据库进行授权maxscale的ipMariaDB [(none)]> GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.109.8 IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)回到172.25.111.6登陆[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 124 Server version: 10.0.33-MariaDB-wsrep Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 129 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 132 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 174 Current database: *** NONE *** +--------------------+ | Database | +--------------------+ | information_schema | | linuxea_com | +--------------------+ 2 rows in set (0.01 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | linuxea_com | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> 创建MariaDB [(none)]> use linuxea_com Database changed MariaDB [linuxea_com]> CREATE TABLE MyGuests ( -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> firstname VARCHAR(30) NOT NULL, -> lastname VARCHAR(30) NOT NULL, -> email VARCHAR(50), -> reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -> ) -> ; Query OK, 0 rows affected (0.02 sec) MariaDB [linuxea_com]> show tables; +-----------------------+ | Tables_in_linuxea_com | +-----------------------+ | MyGuests | +-----------------------+ 1 row in set (0.00 sec) MariaDB [linuxea_com]> desc MyGuests; +-----------+-----------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------+------+-----+-------------------+-----------------------------+ | id | int(6) unsigned | NO | PRI | NULL | auto_increment | | firstname | varchar(30) | NO | | NULL | | | lastname | varchar(30) | NO | | NULL | | | email | varchar(50) | YES | | NULL | | | reg_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-----------+-----------------+------+-----+-------------------+-----------------------------+ 5 rows in set (0.00 sec)
2020年03月24日
4,570 阅读
0 评论
0 点赞
2018-06-09
linuxea:MaxScale binlog server2.2.6与读写分离失败实践
MaxScale是一个动态数据的路由平台,位于数据库层和该数据库的客户端之间。但是,这里描述的binlog路由器与原始概念有所不同。在传统的复制中,有一个主和多个从,但添加主机或者恢复主机,需要在从执行一些操作才能成为主在之前的mha中binlog日志是mha通过ssh识别最新的中继日志,配合配置中最新的主节点,同步到其他从站,并且能让从站重新开始复制而在maxscale中是在主从之间引入代理层解决,从只知道代理服务器,并不需要知道实际的主服务器位置,以此来简化复制环节中更换故障主服务器的过程。从服务器只知道中间服务器,也就是代理层。因此将一个现有的从库提升到主只涉及到从服务器和中间主服务器,一旦中间服务器更改完成,从站便是新的主节点。但是需要将中间服务器设置为新服务器日志中的正确的点,才可以继续复制但是,如果中间服务器本身发生故障,就会回到开始的问题。如果用多个中间服务器有会出现二进制日志不能一致的问题。还有一个问题是,如果中间节点故障,从节点是不能从故障节点转移到另外一个新的中间主节点,也就是说如果你双主,则会被认为是从maxscale接受主服务器的二进制日志缓存到本地,并将二进制日志提供给从服务器。这就意味着从站始终获得与主站写入的二进制事件相关的二进制日志。并与从站并行保持。事实上上面一大堆文字都是翻译的(有误处请指正),我本想它是可以这样实现的,如下:maxscale不单单是binlog-server而且还是读写分离,借鉴:https://mariadb.com/resources/blog/mariadb-maxscale-setup-binlog-server-and-sql-query-routing文章中说是测试版本,我测试发现不行,只能做为binlog-server使用,有些扯犊子参考:参考:http://maxscale.readthedocs.io/en/stable/Documentation/Routers/Binlogrouter/ https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-as-a-binlog-server/#binlog-router-compatibility 参考:https://mariadb.com/resources/blog/mariadb-maxscale-setup-binlog-server-and-sql-query-routing1,maxscale安装[root@Linuxea-VM-Node145 ~]# wget https://downloaLinuxea.mariadb.com/MaxScale/2.2.6/rhel/7/x86_64/maxscale-2.2.6-1.rhel.7.x86_64.rpm [root@Linuxea-VM-Node145 ~]# yum localinstall maxscale-2.2.6-1.rhel.7.x86_64.rpm [root@Linuxea-VM-Node145 ~]# mkdir /data/maxscale/data -p [root@Linuxea-VM-Node6 /data]# mkdir -p /data/maxscale/logs/trace/ [root@Linuxea-VM-Node6 /data]# mkdir -p /data/maxscale/cache/ [root@Linuxea-VM-Node145 ~]# maxkeys /data/maxscale/{data,cache} [root@Linuxea-VM-Node145 ~]# maxkeys /data/maxscale/data [root@Linuxea-VM-Node145 ~]# maxpasswd /data/maxscale/data password 63B4C3058D940CD68A6E8E0E11ABE2D7 [root@Linuxea-VM-Node145 ~]# chown -R maxscale.maxscale /data/maxscale/2,在主库上授权这里授权的是maxscale的用户,作为链接后端主机的用户,后面在创建一个用户作为主从同步,也可以使用此用户GRANT SELECT ON *.* TO 'maxscale'@'%' identified by 'password'; GRANT SHOW DATABASES ON *.* TO maxscale@'%'; GRANT REPLICATION SLAVE on *.* to maxscale@'%'; GRANT REPLICATION CLIENT on *.* to maxscale@'%'; flush privileges;slave用户作为复制用户GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password'; flush privileges;主节点:MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 467 | +------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> 3,在maxscale创建配置文件[root@Linuxea-VM-Node145 /data/maxscale]# mysql -umaxscale -ppassword -h10.10.240.145 -P3306 Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MySQL connection id is 4 Server version: 10.2.12 2.2.6-maxscale Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.202',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=467,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; ERROR 1234 (42000): Can not set MASTER_LOG_POS to 467: Permitted binlog pos is 4. Specified master_log_file=mysql-bin.000001 【这里MASTER_LOG_POS=4设置成4即可】 MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.202',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) 此时目录下产生master.ini[root@Linuxea-VM-Node145 /data/maxscale]# ll 总用量 20 drwxr-xr-x 2 maxscale maxscale 4096 6月 8 21:50 cache drwxr-xr-x 13 maxscale maxscale 4096 6月 9 09:23 data -rw-r--r-- 1 maxscale maxscale 4096 6月 9 09:25 gtid_maps.db drwxr-xr-x 3 maxscale maxscale 37 6月 8 20:21 logs -rw------- 1 maxscale maxscale 177 6月 9 09:25 master.ini -rw-r--r-- 1 maxscale maxscale 467 6月 9 09:25 mysql-bin.000001(可以直接用手撸一串配置)[root@Linuxea-VM-Node145 /data/maxscale]# cat master.ini [binlog_configuration] master_host=10.10.240.202 master_port=3306 master_user=slave master_password=password filestem=mysql-bin master_heartbeat_period=2 master_connect_retry=5 [root@Linuxea-VM-Node145 /data/maxscale]# 4,从库同步(两个从库都同步,同步的Ip是maxsclae blog的IP地址)MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.145',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=467,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.240.145 Master_User: slave Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 467 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 535 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes5,查看节点信息[root@Linuxea-VM-Node145 /data/maxscale]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- binlog_router_master_host | 10.10.240.202 | 3306 | 1 | Running server1 | 10.10.240.202 | 3306 | 0 | Master, Running server2 | 10.10.240.203 | 3306 | 0 | Slave, Running server3 | 10.10.240.146 | 3306 | 0 | Slave, Running -------------------+-----------------+-------+-------------+--------------------节点详情查看[root@Linuxea-VM-Node145 /data/maxscale]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 show servers|egrep "Server|Status" Server 0xf95420 (binlog_router_master_host) Server: 10.10.240.202 Status: Running Server Version: 10.0.33-MariaDB-wsrep Server 0xf5dc60 (server1) Server: 10.10.240.202 Status: Master, Running Server Version: 10.0.33-MariaDB-wsrep Server Parameters: Server 0xf5c840 (server2) Server: 10.10.240.203 Status: Slave, Running Server Version: 10.0.33-MariaDB-wsrep Server Parameters: Server 0xf5b420 (server3) Server: 10.10.240.146 Status: Slave, Running Server Version: 10.0.33-MariaDB-wsrep Server Parameters: [root@Linuxea-VM-Node145 /data/maxscale]# 在maxscale上查看从节点信息[root@Linuxea-VM-Node145 /data/maxscale]# mysql -umaxscale -ppassword -h10.10.240.145 -P3306 Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MySQL connection id is 4 Server version: 10.2.12 2.2.6-maxscale Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show slave hosts; +-----------+------+------+-----------+------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+------------+ | 4 | | 3306 | 100 | | | 2 | | 3306 | 100 | | +-----------+------+------+-----------+------------+ 2 rows in set (0.00 sec)6,添加机器添加一台机器就如第4部分同步从节点一样,备份到新从节点,而后接入进来7,模拟一次切换登陆maxscale[root@Linuxea-VM-Node145 /data/maxscale]# mysql -uslave -ppassword -h10.10.240.145 -P3306 Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MySQL connection id is 14 Server version: 10.2.12 2.2.6-maxscale MariaDB Server, wsrep_25.21.rc3fc46e Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.146',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1195,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; ERROR 1234 (42000): Can not set MASTER_LOG_POS to 1195: Permitted binlog pos is 1340. Current master_log_file=mysql-bin.000001, master_log_pos=1340 这里会提示pos节点 MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.146',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1340,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> start slave; Query OK, 0 rows affected (0.09 sec)在将新节点加入进来[root@Linuxea-VM-Node202 ~]# mysql -uroot -plookback Welcome to the MariaDB monitor. CommanLinuxea end with ; or \g. Your MariaDB connection id is 86 Server version: 10.0.33-MariaDB-wsrep MariaDB Server, wsrep_25.21.rc3fc46e Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.10.240.145',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1195,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.240.145 Master_User: slave Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1340 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 680 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes在查看节点数[root@Linuxea-VM-Node145 ~]# maxadmin --user=admin --password=mariadb --host=10.10.240.145 show services|grep Slave_host_port Slave_host_port: [10.10.240.202]:21694 Slave_host_port: [10.10.240.145]:27544 Slave_host_port: [10.10.240.203]:2640 Slave_host_port: [10.10.240.146]:51492
2018年06月09日
3,412 阅读
0 评论
0 点赞
1
2
...
15