mysql5.5+19_mysql 5.5.19 主从配置+读写分离

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