实现apache服务的读写mysql在两台服务器上

原创

b715127126 2012-11-30 17:39:19 ©著作权

文章标签 mysql apache 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者b715127126的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、首先配置mysql 的ab 复制备份如下 a(master) b(slave)

1修改配置文件

master:
vim /etc/my.cnf
[mysqld]
server_id = 1
log-bin=binlog
log-bin-index=binlog.index

并重启服务
service mysqld restart

slave:
    vim /etc/my.cnf
    [mysqld]
    server_id = 2
    relay_log = /var/lib/mysql/mysql-relay-bin
    relay_log_index=/var/lib/mysql/mysql-relay-bin.index

删除原有的数据文件

cd /var/lib/mysql
    rm -fr *
并重启服务
service mysqld restart

2新建远程用户并给b 导入a 的现有数据
    master:
mysql> GRANT replication slave ON *.* TO 'kyo'@'%' identified by '123';
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

master
    mysqldump -A -x > /tmp/full.sql
    scp /tmp/full.sql root@salveip:/tmp/

slave:
    mysql < /tmp/full.sql

3锁住现的 a 并记下 file 和 Position,解锁

master:
    mysql> flush tables with read lock;
    mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 |     1124 |              |                  |

mysql>unlock tables;

4 告诉 b a的当时的at ,并启动 b
slave:
mysql> change master to master_host='192.168.18.81', master_port=3306, master_user='kyo', master_password='123', master_log_file='binlog.000001',master_log_pos=1124;

mysql> start slave;

5 确认 ab 是否成功
slave:
    mysql> show slave status \G
    Slave_IO_Running: Yes
     Slave_SQL_Running: Yes