Mysql5.5主从/读写分离配置

数据库配置

主数据库：172.20.17.211
从数据库：172.20.17.210

一、创建主/从数据同步账户( 主从两台数据库都要执行 )

# 开启远程访问支持
/usr/local/mysql/bin/mysql -h172.20.17.211 -uroot -pwoshishui # 登录数据库
update mysql.user set Host='%' where User='root' and Host='localhost'; # 开启Mysql远程访问支持
select Host,User,Password from mysql.user limit 10 \G; # 查看修改结果
flush privileges; # 刷新权限
# 主/从数据库添加授权账户
CREATE USER 'xzdesk'@'%' IDENTIFIED BY 'woshishui'; # 创建授权账户xzdesk 密码woshishui
GRANT ALL ON *.* TO 'xzdesk'@'127.0.0.1'; # 授权xzdesk用户拥有全部数据库的所有权限
GRANT ALL ON `ceshi`.* TO 'xzdesk'@'%'; # 授权xzdesk用户拥有ceshi数据库的所有权限
# 第二种 授权和添加账户一起操作
# GRANT ALL ON `ceshi`.* TO 'xzdesk'@'127.0.0.1' IDENTIFIED BY 'woshishui';
# 撤销用户授权
REVOKE ALL ON *.* TO 'xzdesk'@'%'; # 撤销xzdesk的所有授权
# REVOKE delete ON databasename.tablename FROM 'username'@'host'; # 没搞明白撒意思，不用执行
# 创建授权用户
#insert into mysql.user(Host,User,Password) values('172.20.17.210','xzdesk',password('woshishui'));
# 删除xzdesk用户( 授权信息无法删除 )
#DELETE FROM user WHERE User="xzdesk" and Host="%";

二、主服务器配置（172.20.17.211）

/usr/local/mysql/bin/mysql -h172.20.17.211 -uroot -pwoshishui
create database ceshi; # 创建ceshi数据库
flush tables with read lock; # 数据库只读锁定命令
# 导出主服务器数据库
/usr/local/mysql/bin/mysqldump -h172.20.17.211 -uroot -pwoshishui ceshi > /data/ceshi_20160609.sql
unlock tables; # 解除锁定
# 主服务器数据导入从服务器
# 第一种方法
/usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui # 进入从服务器MySQL控制台
create database ceshi; # 创建数据库
use ceshi # 进入数据库
/usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui ceshi # 第二种方法
/usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui # 进入从服务器MySQL控制台
create database ceshi; # 创建数据库
use ceshi # 进入数据库
source /data/ceshi_20160609.sql # 导入备份文件到数据库

四、配置MySQL主服务器的my.cnf文件

vim /etc/my.cnf # 编辑配置文件，在[mysqld]部分添加下面内容
server-id=1 # 设置服务器id，为1表示主服务器，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
log_bin=mysql-bin # 启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
binlog-do-db=ceshi # 需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
binlog-ignore-db=mysql # 不同步mysql系统数据库
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
:wq! #保存退出
service mysqld restart # 重启MySQL
mysql -u root -p # 进入mysql控制台
show master status; # 查看主服务器，出现以下类似信息
+------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000006 | 575 | ceshi | mysql,performance_schema,information_schema |
+------------------+----------+--------------+---------------------------------------------+

五、配置MySQL从服务器的my.cnf文件

vim /etc/my.cnf # 编辑配置文件，在[mysqld]部分添加下面内容
server-id=2 # 配置文件中已经有一行server-id=1，修改其值为2，表示为从数据库
log-bin=mysql-bin # 启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
replicate-do-db=ceshi # 需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
replicate-ignore-db=mysql # 不同步mysql系统数据库
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
:wq! #保存退出
service mysqld restart # 重启MySQL
/usr/local/mysql/bin/mysql -h172.20.17.210 -uroot -pwoshishui
slave stop; # 停止slave同步进程
change master to master_host='172.20.17.211',master_user='xzdesk',master_password='woshishui',master_log_file='mysql-bin.000006' ,master_log_pos=575; #执行同步语句
slave start; # 开启slave同步进程
SHOW SLAVE STATUS\G # 查看slave同步信息，出现以下内容
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.17.211
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 341
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 487
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes # 此状态必须YES
Slave_SQL_Running: Yes # 此状态必须YES
Replicate_Do_DB: ceshi
Replicate_Ignore_DB: mysql,performance_schema,information_schema
注意：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
注意：MySQL 5.1.7版本之后，已经不支持把master配置属性写入my.cnf配置文件中了，只需要把同步的数据库和要忽略的数据库写入即可。