Mysql应用MySQL的使用中实现读写分离的教程
《Mysql应用MySQL的使用中实现读写分离的教程》要点: mysql-proxy实现读写分离
tar -zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz mv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy 2、创立好mysql主从复制 master 192.168.216.133:3306 slave 192.168.216.132:3306 3、配置mysql-proxy vim /usr/local/mysql-proxy/mysql-proxy.conf [mysql-proxy] daemon=true #以后台守护进程方式启动 keepalive=true #当进程故障后自动重启 log-level=debug #设置日志级别为debug,可以在调试完成后改成info log-file=/var/log/mysql-proxy.log #设置日志文件路径 basedir=/usr/local/mysql-proxy #设置mysql-proxy的家目录 proxy-address=192.168.216.132:4040 #指定mysql-proxy的监听地址 proxy-backend-addresses=192.168.216.133:3306 #设置后台主服务器 proxy-read-only-backend-addresses=192.168.216.132:3306 #设置后台从服务器 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #设置读写分离脚本路径 admin-address=192.168.216.132:4041 #设置mysql-proxy管理地址,需要家长admin插件 admin-username=admin #设置登录管理地址用户 admin-password=admin #设置管理用户暗码 admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua #设置管理后台lua脚本路径,改脚本默认没有要自动定义 配置完mysql-proxy.conf后需要确保该文件的权限是600,并确保包含个lua脚本 /usr/local/mysql-proxy/bin/mysql-proxy --plugins=proxy --plugins=admin --defaults-file=mysql-proxy.conf --plugins=proxy #指定proxy插件,该配置写入配置文件无法启动 --plugins=admin #指定admin插件 --defaults-file=mysql-proxy.conf #指定配置文件 4、启动测试MYSQL实例
mysql -uadmin -padmin -h192.168.216.132 -P4041 两个后端服务器当前状态为unknown是因为没有用户通过mysql-proxy连接到后端MYSQL实例 mysql-proxy不对用户做身份验证,而是下身份验证交予后端服务器进行验证的,因此必要在后端服务器上对mysql-proxy开放权限MYSQL实例 下面是自界说的admin.luaMYSQL实例 function set_error(errmsg) proxy.response = { type = proxy.MYSQLD_PACKET_ERR,errmsg = errmsg or "error" } end function read_query(packet) if packet:byte() ~= proxy.COM_QUERY then set_error("[admin] we only handle text-based queries (COM_QUERY)") return proxy.PROXY_SEND_RESULT end local query = packet:sub(2) local rows = { } local fields = { } if query:lower() == "select * from backends" then fields = { { name = "backend_ndx",type = proxy.MYSQL_TYPE_LONG },{ name = "address",type = proxy.MYSQL_TYPE_STRING },{ name = "state",{ name = "type",{ name = "uuid",{ name = "connected_clients",} for i = 1,#proxy.global.backends do local states = { "unknown","up","down" } local types = { "unknown","rw","ro" } local b = proxy.global.backends[i] rows[#rows + 1] = { i,b.dst.name,-- configured backend address states[b.state + 1],-- the C-id is pushed down starting at 0 types[b.type + 1],-- the C-id is pushed down starting at 0 b.uuid,-- the MySQL Server's UUID if it is managed b.connected_clients -- currently connected clients } end elseif query:lower() == "select * from help" then fields = { { name = "command",{ name = "description",} rows[#rows + 1] = { "SELECT * FROM help","shows this help" } rows[#rows + 1] = { "SELECT * FROM backends","lists the backends and their state" } else set_error("use 'SELECT * FROM help' to see the supported commands") return proxy.PROXY_SEND_RESULT end proxy.response = { type = proxy.MYSQLD_PACKET_OK,resultset = { fields = fields,rows = rows } } return proxy.PROXY_SEND_RESULT end 5、相关问题办理 可以修改 rw-splitting.lua中的min_idle_connections = 4和max_idle_connections = 8的只,将其调年夜MYSQL实例 (2)、如果遇到乱码必要调整后端mysql的设置的字符集MYSQL实例 [mysqld] skip-character-set-client-handshake init-connect = 'SET NAMES utf8' character_set_server = utf8
tar -zxvf mysqlnd_ms-1.5.2.tgz cd mysqlnd_ms-1.5.2 /usr/local/php/bin/phpize ./configure --with-php-config=/usr/local/php/bin/php-config make && make install 出现以下类似提示,记录下面的路径必要拥有配置php.iniMYSQL实例 Installing shared extensions: /usr/local/php/lib/php/extensions/no-debug-non-zts-20121212/ Installing header files: /usr/local/php/include/php/</span> 2、编纂 /usr/local/php/etc/php.iniMYSQL实例 extension = /usr/local/php/lib/php/extensions/no-debug-non-zts-20121212/mysqlnd_ms.so mysqlnd_ms.enable = On mysqlnd_ms.config_file = /usr/local/php/etc/mysqlnd_ms_plugin.ini 3、创立mysqlnd_ms_plugin.ini配置文件MYSQL实例 { "myapp": { "master": { "master_0": { "host": "192.168.6.135","socket": "/tmp/mysql.sock" } "slave": { "slave_0": { "host": "192.168.6.136","port": "3306" "slave_1": { "host": "192.168.6.137","port": "3306" "filters": { "random": { "sticky": "1" } } } } 这里使用到了1主2从的mysql服务器 4、测试 /** MySQL主机 */ define('DB_HOST','myapp'); #这的myapp是在mysqlnd_ms_plugin.ini中界说的 《Mysql应用MySQL的使用中实现读写分离的教程》是否对您有启发,欢迎查看更多与《Mysql应用MySQL的使用中实现读写分离的教程》相关教程,学精学透。编程之家PHP学院为您提供精彩教程。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |