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
改變當(dāng)前數(shù)據(jù)庫為mysql：
mysql>use mysql
設(shè)置從本地主機(jī)登錄的root帳號(hào)密碼：
mysql>set password for root@localhost=password('root');
刪除匿名帳號(hào)：
mysql>delete from user where user='';
刪除密碼為空的帳號(hào)：
mysql>delete from user where password='';
刪除允許非localhost主機(jī)登錄的帳號(hào)：
mysql>delete from user where host<>'localhost';
執(zhí)行下面的命令使更改生效：
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主庫數(shù)據(jù)
mysql>flush tables with read lock;
$mysqldump -h127.0.0.1 -p3306 -uroot -p phpdb > /mnt/phpdb.sql
（/mnt目錄是我自己做的NFS，就是方便不同機(jī)器間使用文件，不用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
改變當(dāng)前數(shù)據(jù)庫為mysql：
mysql>use mysql
設(shè)置從本地主機(jī)登錄的root帳號(hào)密碼：
mysql>set password for root@localhost=password('root');
刪除匿名帳號(hào)：
mysql>delete from user where user='';
刪除密碼為空的帳號(hào)：
mysql>delete from user where password='';
刪除允許非localhost主機(jī)登錄的帳號(hào)：
mysql>delete from user where host<>'localhost';
執(zhí)行下面的命令使更改生效：
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下創(chuàng)建文件mysql-proxy.cnf添加如下內(nèi)容：
[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
啟動(dòng)代理：
/usr/local/mysql-proxy/bin/mysql-proxy --daemon --defaults-file=mysql-proxy.cnf