mysql 主从配置+读写分离

master:10.13.120.236
slave:10.13.120.233
步骤一：安装master
下载mysql-5.1.60-linux-x86_64-glibc23.tar.gz 放到/home/a/soft/下
shell>sudo groupadd mysql 
shell>sudo useradd -r -g mysql mysql 
shell>cd /home/a/soft/ 
shell>tar zxvf mysql-5.1.60-linux-x86_64-glibc23.tar.gz
shell>cd /usr/local/ 
shell>sudo ln -s /home/a/soft/mysql-5.1.60-linux-x86_64-glibc23 mysql 
shell> cd mysql 
shell>sudo chown -R mysql . 
shell>sudo chgrp -R mysql . 
shell>sudo scripts/mysql_install_db --user=mysql 
shell>sudo chown -R root . 
shell>sudo chown -R mysql data 
# Next command is optional 
shell>sudo cp support-files/my-medium.cnf /etc/my.cnf 
shell>sudo bin/mysqld_safe --user=mysql & 
# Next command is optional 
shell>sudo cp support-files/mysql.server /etc/init.d/mysql.server
#配置my.cnf
####################################
binlog-do-db=phpdb
binlog-ignore-db=mysql
log-bin=/usr/local/mysql/log/updatelog
log-slave-updates
slave-skip-errors=all
#####################################
$sudo service mysql.server start
$mysql -uroot -p
改变当前数据库为mysql：
mysql>use mysql
设置从本地主机登录的root帐号密码：
mysql>set password for root@localhost=password('root');
删除匿名帐号： 
mysql>delete from user where user='';
删除密码为空的帐号：
mysql>delete from user where password='';
删除允许非localhost主机登录的帐号：
mysql>delete from user where host<>'localhost';
执行下面的命令使更改生效：
mysql>flush privileges; 
mysql>update user set host = '%' where user = 'root';
mysql>create database phpdb;
mysql>use phpdb;
mysql>create table person(id int(10) primary key auto_increment,name varchar(100) not null);
#dump主库数据
mysql>flush tables with read lock;
$mysqldump -h127.0.0.1 -p3306 -uroot -p phpdb > /mnt/phpdb.sql 
（/mnt目录是我自己做的NFS，就是方便不同机器间使用文件，不用nfs也可以）
mysql> unlock tables;
mysql>use mysql
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'10.13.120.233' IDENTIFIED BY 'root';
$sudo service mysql.server restart
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |     2366 | phpdb        | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

步骤二：安装slave
下载mysql-5.1.60-linux-x86_64-glibc23.tar.gz 放到/home/a/soft/下
shell>sudo groupadd mysql 
shell>sudo useradd -r -g mysql mysql 
shell>cd /home/a/soft/ 
shell>tar zxvf mysql-5.1.60-linux-x86_64-glibc23.tar.gz
shell>cd /usr/local/ 
shell>sudo ln -s /home/a/soft/mysql-5.1.60-linux-x86_64-glibc23 mysql 
shell> cd mysql 
shell>sudo chown -R mysql . 
shell>sudo chgrp -R mysql . 
shell>sudo scripts/mysql_install_db --user=mysql 
shell>sudo chown -R root . 
shell>sudo chown -R mysql data 
# Next command is optional 
shell>sudo cp support-files/my-medium.cnf /etc/my.cnf 
shell>sudo bin/mysqld_safe --user=mysql & 
# Next command is optional 
shell>sudo cp support-files/mysql.server /etc/init.d/mysql.server
#配置my.cnf
#####################################################
server-id       = 2
#master-host     =   mysql_master
#master-user     =   slave1_user
#master-password =   root
#master-port     = 3306
#master-connect-retry=60
replicate-ignore-db=mysql
replicate-do-db=phpdb
log-slave-update
slave-skip-errors=all
#####################################################
$sudo service mysql.server start
$mysql -uroot -p
改变当前数据库为mysql：
mysql>use mysql
设置从本地主机登录的root帐号密码：
mysql>set password for root@localhost=password('root');
删除匿名帐号： 
mysql>delete from user where user='';
删除密码为空的帐号：
mysql>delete from user where password='';
删除允许非localhost主机登录的帐号：
mysql>delete from user where host<>'localhost';
执行下面的命令使更改生效：
mysql>flush privileges; 
mysql>update user set host = '%' where user = 'root';
mysql>create database phpdb;
mysql>use phpdb;
$mysqldump -h127.0.0.1 -p3306 -uroot -p phpdb < /mnt/phpdb.sql   
（mysql>source /mnt/phpdb.sql）
mysql>change master to master_host='10.13.120.236', 
mysql>master_user='slave1_user', 
mysql>master_password='root',
mysql>master_port=3306,
mysql>master_connect_retry=60,
mysql>MASTER_LOG_FILE='mysql-bin.000006',
mysql>MASTER_LOG_POS=107;
mysql>slave start;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.13.120.236
Master_User: slave1_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 2366
Relay_Log_File: canggu-OptiPlex-760-relay-bin.000002
Relay_Log_Pos: 2512
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: phpdb
Replicate_Ignore_DB: mysql
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: 2366
Relay_Log_Space: 2682
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: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR: 
No query specified

mysql>slave stop;
mysql>change master to master_host='10.13.120.236', 
master_user='slave1_user', 
master_password='root',
master_port=3306,
master_connect_retry=60,
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=107;



配置读写分离

下载mysql-proxy-0.8.2-linux-glibc2.3-x86-32bit.tar.gz
解压并cp到/usr/local/mysql-proxy
在/usr/local/mysql-proxy/bin下创建文件mysql-proxy.cnf添加如下内容：
[mysql-proxy]
daemon=true
admin-address=127.0.0.1:4041
proxy-address=:3306
admin-username=admin
admin-password=admin
admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin-sql.lua
proxy-read-only-backend-addresses=10.13.120.233:3306
proxy-backend-addresses=10.13.120.236:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/usr/local/mysql-proxy/logs/log-debug.log
log-level=debug



启动代理：
/usr/local/mysql-proxy/bin/mysql-proxy  --daemon --defaults-file=mysql-proxy.cnf