mysql5.5做读写分离_MySQL5.5读写分离配置的详解

读写分可以实现数据库性能的保证了同时也可以做到数据实时备份了，下面我们来看一篇MySQL5.5读写分离配置的教程，希望对各位新手有帮助。

一、服务器配置

主库：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)