mysql cluster解决方案-mysql教程-PHP中文网

首页

数据库

mysql教程

mysql cluster解决方案

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:26 PM

clusterhaproxylinuxmysql集群

Mysqlcluster实验1测试环境：1.1软件系统版本Gentoo3.8.13Mysql5.1.67Haproxy-1.4.241.2架构1.3主机信息hostnameNetworkinterfaceIPaddrnote主机编号Mysq..

Mysql cluster

重启mysql

/etc/init.d/mysql restart

在主机3-4上

Mysql –uroot –p

Change master to

Master_host=’192.168.254.111’,

Master_port=3306,

Master_user=’copy’,

Master_password=’copy’,

Master_log_file=’mysql-bin.000005’,

Master_log_pos=’106’;

Start slave;

Show slave status;

测试

同上，分别在1和2上创建1个数据库，看是否同步

5 安装xinetd

在主机1-6上

Emerge –av xinetd

Rc-update add xinetd default

/etc/init.d/xinetd start

在主机1-2上

Vi /etc/xinetd.d/mysqlchk

# # /etc/xinetd.d/mysqlchk # service mysqlchk_write { flags = REUSE socket_type = stream port = 9200 wait = no user = nobody server = /opt/mysqlchk_status.sh log_on_failure = USERID disable = no only_from =192.168.254.0/24 } service mysqlchk_replication { flags = REUSE socket_type = stream port = 9201 wait = no user = nobody server = /opt/mysqlchk_replication.sh log_on_failure = USERID disable = no only_from = 192.168.254.0/24 }

在主机1上

Vi /opt/mysqlchk_status.sh

#!/bin/bash MYSQL_HOST="localhost" MYSQL_PORT="3306" MYSQL_USERNAME="root" MYSQL_PASSWORD="root" ERROR_MSG=`/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "show databases;" 2>dev/null` if [ "$ERROR_MSG" != "" ] then # mysql is fine, return http 200 /bin/echo -e "HTTP/1.1 200 OKrn" /bin/echo -e "Content-Type: Content-Type:textrn" /bin/echo -e "rn" /bin/echo -e "MySQL is running.rn" /bin/echo -e "rn" else #mysql is down, return http 503 /bin/echo -e "HTTP/1.1 503 Service Unavailablern" /bin/echo -e "Content-Type: Content-Type:text/plainrn" /bin/echo -e "rn" /bin/echo -e "MYSQL is *down*.rn" /bin/echo -e "rn" fi

Vi /opt/mysqlchk_replication.sh

#!/bin/bash MYSQL_HOST="localhost" MYSQL_PORT="3306" MYSQL_USERNAME="root" MYSQL_PASSWORD="root" /usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "show slave status;" > /tmp/check_repl.txt iostat=`grep "Slave_IO_Running" /tmp/check_repl.txt | awk '{print $2}'` sqlstat=`grep "Slave_SQL_Running" /tmp/check_repl.txt | awk '{print $2}'` #echo iostat:$iostat and sqlstat:$sqlstat if [ "$iostat" = "No" ] || [ "$sqlstat" = "No" ]; then #mysql is down,return http 503 /bin/echo -e "HTTP/1.1 503 Service Unavailablern" /bin/echo -e "Content-Type: Content-Type:text/plainrn" /bin/echo -e "rn" /bin/echo -e "MySQL replication is *down*.rn" /bin/echo -e "rn" else #mysql is fine,return http 200 /bin/echo -e "HTTP/1.1 200 OKrn" /bin/echo -e "Content-Type: Content-Type:text/plainrn" /bin/echo -e "rn" /bin/echo -e "MySQL replication is running.rn" /bin/echo -e "rn" fi

在主机2上

Vi /opt/mysqlchk_status.sh

Vi /opt/mysqlchk_replication.sh

#!/bin/bash MYSQL_HOST=“本地主机” MYSQL_PORT =“3306” MYSQL_USERNAME=“根” MYSQL_PASSWORD =“根” /usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e "显示从属状态;" > /tmp/check_repl.txt iostat=`grep "Slave_IO_Running" /tmp/check_repl.txt | awk '{print $2}'` sqlstat=`grep "Slave_SQL_Running" /tmp/check_repl.txt | awk '{print $2}'` #echo iostat:$iostat 和 sqlstat:$sqlstat if [ "$iostat" = "否" ] || [“$sqlstat”=“否”]; 然后 #mysql宕机，返回http 503 /bin/echo -e "HTTP/1.1 503 服务不可用" /bin/echo -e“内容类型：内容类型：text/plainrn” /bin/echo -e“rn” /bin/echo -e "MySQL 复制已 *down*.rn" /bin/echo -e“rn” 别的 #mysql没问题，返回http 200 /bin/echo -e "HTTP/1.1 200 OKrn" /bin/echo -e“内容类型：内容类型：text/plainrn” /bin/echo -e“rn” /bin/echo -e "MySQL 复制正在运行.rn" /bin/echo -e“rn” fi

在主机3-6上

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

mysql：blob和其他无-SQL存储，有什么区别？May 13, 2025 am 12:14 AM

mysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而alenosqloptionslikemongodb，redis和calablesolutionsoluntionsoluntionsoluntionsolundortionsolunsolunsstructureddata.blobobobsimplobissimplobisslowderperformandperformanceperformancewithlararengelitiate;

mySQL添加用户：语法，选项和安全性最佳实践May 13, 2025 am 12:12 AM

toaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith

MySQL：如何避免字符串数据类型常见错误？May 13, 2025 am 12:09 AM

toAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingsefectery.1）usecharforfixed lengengters lengengtings，varchar forbariaible lengength，varchariable length，andtext/blobforlabforlargerdata.2 seterters seterters seterters seterters

mySQL：字符串数据类型和枚举？May 13, 2025 am 12:05 AM

mysqloffersechar，varchar，text，and denumforstringdata.usecharforfixed Lengttrings，varcharerforvariable长度，文本forlarger文本，andenumforenforcingDataAntegrityWithaEtofValues。

mysql blob：如何优化斑点请求May 13, 2025 am 12:03 AM

优化MySQLBLOB请求可以通过以下策略：1.减少BLOB查询频率，使用独立请求或延迟加载；2.选择合适的BLOB类型（如TINYBLOB）；3.将BLOB数据分离到单独表中；4.在应用层压缩BLOB数据；5.对BLOB元数据建立索引。这些方法结合实际应用中的监控、缓存和数据分片，可以有效提升性能。

将用户添加到MySQL：完整的教程May 12, 2025 am 12:14 AM

掌握添加MySQL用户的方法对于数据库管理员和开发者至关重要，因为它确保数据库的安全性和访问控制。1)使用CREATEUSER命令创建新用户，2)通过GRANT命令分配权限，3)使用FLUSHPRIVILEGES确保权限生效，4)定期审计和清理用户账户以维护性能和安全。

掌握mySQL字符串数据类型：varchar vs.文本与charMay 12, 2025 am 12:12 AM

chosecharforfixed-lengthdata，varcharforvariable-lengthdata，andtextforlargetextfield.1）chariseffity forconsistent-lengthdatalikecodes.2）varcharsuitsvariable-lengthdatalikenames，ballancingflexibilitibility andperformance.3）

MySQL：字符串数据类型和索引：最佳实践May 12, 2025 am 12:11 AM

在MySQL中处理字符串数据类型和索引的最佳实践包括：1)选择合适的字符串类型，如CHAR用于固定长度，VARCHAR用于可变长度，TEXT用于大文本；2)谨慎索引，避免过度索引，针对常用查询创建索引；3)使用前缀索引和全文索引优化长字符串搜索；4)定期监控和优化索引，保持索引小巧高效。通过这些方法，可以在读取和写入性能之间取得平衡，提升数据库效率。

See all articles