From:http://hi.baidu.com/yzy888/blog/item/98784fd074381d84a0ec9cc3.html
mysql主从复制 主服务器IP:192.168.99.10 从服务器IP:192.168.99.20 (一)安装mysql(主从服务器操作相同) yum -y install gcc gcc-c++ ncurses-devel 增加组和用户 #/usr/sbin/groupadd mysql #/usr/sbin/useradd -g mysql mysql -s /sbin/nologin 解压、编译、安装 #tar xvf mysql-5.1.35.tar.gz #cd mysql-5.1.35 #./configure --prefix=/usr/local/mysql --localstatedir=/data/mysql/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client #make && make install 拷贝配置文件 #cp support-files/my-large.cnf /etc/my.cnf 修改权限 #chown -R mysql.mysql /usr/local/mysql 初始化数据库 #/usr/local/mysql/bin/mysql_install_db --user=mysql 改变权限 #chown -R mysql:mysql /data/mysql/data (二)修改配置文件 主服务器 server-id = 1 从服务器 server-id = 2..3..4依次增加 (三)启动服务(主从相同) #/usr/local/mysql/bin/mysqld_safe --user=mysql& (四)授权(仅主服务器) mysql>GRANT REPLICATION SLAVE ON *.* to 'rep'@'192.168.99.%' identified by 'sasasa'; (五)查询主数据库状态(主服务器上) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 771 | | | +------------------+----------+--------------+------------------+ 记住file和position的值 (六)配置从服务器 mysql> change master to master_host='192.168.99.10', master_user='rep', master_password='sasasa1', master_log_file='mysql-bin.000003', master_log_pos=771; master_host主服务器ip,master_user连接帐户,master_password连接密码,maser_log_file填入上面的file值,master_log_pos填入上面的position值 mysql> start slave; 启用了复制功能 mysql> show slave status\G; 检查slave状态 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.99.10 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 771 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 771 Relay_Log_Space: 410 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:
注意:Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 如果这两个状态不是yes,那么请检查上面的配置是否正确,主要是file和position设置是否正确,从库是否能用主库的账号登陆 如果主服务器还有数据传输,那么先进行锁表操作 mysql> FLUSH TABLES WITH READ LOCK; 然后按照第五步的进行操作
安装mysql-proxy分发请求 (一)安装依赖包 lua-5.1 #tar zxvf lua-5.1.tar.gz #cd lua-5.1 #sed -i 's#INSTALL_TOP= /usr/local#INSTALL_TOP= /usr/local/lua#' Makefile #make posix && make install libevent-1.1a #tar zxvf libevent-1.1a.tar.gz #cd libevent-1.1a #./configure --prefix=/usr/local/libevent #make && make install check-0.8.4 tar zxvf check-0.8.4.tar.gz cd check-0.8.4 ./configure make make install (二)安装mysql客户端 #tar zxvf mysql-5.1.35.tar.gz #cd mysql-5.1.35 #./configure --prefix=/usr/local/mysql --without-server #make && make install (三)设置环境变量 export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm" export CPPFLAGS="-I/usr/local/libevent/include" export CFLAGS="-I/usr/local/libevent/include" (四)安装mysql-proxy #tar xvf mysql-proxy-0.6.1.tar.gz #cd mysql-proxy-0.6.1 #./configure --prefix=/usr/local/mysql-proxy --with-mysql=/usr/local/mysql --with-lua #Make && Make install (五)创建启动脚本 #!/bin/bash LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" /usr/local/mysql-proxy/sbin/mysql-proxy --proxy-backend-addresses=192.168.99.10:3306 --proxy-read-only-backend-addresses=192.168.99.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua >> /var/log/mysql-proxy.log &
#chmod a+x /usr/local/bin/mysql-proxy.sh 执行命令/usr/local/bin/mysql-proxy.sh启动服务 主要的命令行选项 --help-all显示所有的帮助选项 --admin-address=host:port 管理主机及端口,默认是4041 --proxy-address=host:port 代理服务器的监听地址及端口,默认4040 --proxy-read-only-address=host:port 只读连接时,代理服务器的监听地址及端口 --proxy-backend-addresses=host:port 连接真实服务器的地址及监听端口,默认是3306 --proxy-lua-script=file 指定lua脚本的名称 使用方法 mysql-proxy --proxy-backend-addresses=<master_ip> :3306\ --proxy-read-only-backend-addresses=<slave_ip1>:3306 \ --proxy-read-only-backend-addresses=<slave_ip2>:3306 测试 略。。。
|