Mysql 5.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 < /data/ceshi_20160609.sql

    # 第二种方法
    /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配置文件中了，只需要把同步的数据库和要忽略的数据库写入即可。