mysql5.5.60 配置,MySQL5.5读写分离配置

很早之前搭建过mysql读写分离架构(原文：http://www.webyang.net/Html/web/article_89.html)，然后今天研究mysql数据库主从不同步的问题，要再搭一套这样的环境，发现按照之前写的怎么搭都不成功。经度娘，原来Mysql版本从5.1.7以后开始就不支持“master-host”等参数，于是就只有再记录一套了~

一、服务器配置

主库：192.168.204.132

vim /etc/my.cnf

server-id= 1

log-bin=mysql-bin

binlog_format=mixed

#需要同步的二进制数据库名

binlog-do-db=test

#不同步的二进制数据库名

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

#可选参数

sync-binlog = 1

#日志保留天数

expire_logs_days = 7

从库：192.168.204.134

vim /etc/my.cnf

server-id=2

log-bin=mysql-bin

binlog_format=mixed

#不需要复制的数据库

replicate-ignore-db=mysql

replicate-ignore-db=performance_schema

#需要复制的数据库

replicate-do-db=test

#可选参数

sync-binlog = 1

expire_logs_days = 7

二、设置主从

主库：

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+--------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |

+------------------+----------+--------------+--------------------------+

| mysql-bin.000026 |      302 | test         | mysql,performance_schema |

+------------------+----------+--------------+--------------------------+

mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.204.134' IDENTIFIED BY '123456';

mysql>FLUSH PRIVILEGES;

从库：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.204.132',MASTER_USER='root', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000026',MASTER_LOG_POS=302;

#上面参数注意对照主库

mysql> slave stop;

mysql> slave start;

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Connecting to master

Master_Host: 192.168.204.132

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000026

Read_Master_Log_Pos: 302

Relay_Log_File: localhost-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000026

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql,performance_schema

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: 302

Relay_Log_Space: 107

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: NULL

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: 0

1 row in set (0.00 sec)

参考：http://www.geedoo.info/mysql5-5-master-slave-synchronization.html