使用俩台mysql服务器实现AB，主从复制。
一、在主ＭＡＳＴＥＲ服务器配置
MASTER 172.16.1.3
BACKUP 172.16.1.2
１、编辑my.cnf文件
#在原有基础上添加这俩行
[root@zhaoyun ~]# cat /etc/my.cnf
[mysqld]
log-bin=/mysql/bin #开启binlog
server-id=1 ＃配置不和另一台重复就行
２、重启服务
[root@zhaoyun ~]# service mysqld restart
停止MySQL：[确定]
启动MySQL：[确定]
３、授权用户
mysql> grant replication slave on *.* to zhaoyun@172.16.1.2 identified by '123456'
[root@zhaoyun ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> grant replication slave on *.* to zhaoyun@172.16.1.2 identified by '123456';
Query OK, 0 rows affected (0.15 sec)
mysql>
4、在B服务器测试是否可以登录
[root@BACKUP ~]# mysql -uzhaoyun -p123456 -h172.16.1.3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants ;
+----------------------------------------------------------------------------------------------------+
| Grants for zhaoyun@172.16.1.2 |
+----------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'zhaoyun'@'172.16.1.2' IDENTIFIED BY PASSWORD '565491d704013245' |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5、查看master的状态
mysql> show master status ;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000001 | 315 | | |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
file字段是master的binlog文件名，position是binlog的节点。
二、配置BACKUP
1、编辑配置文件my.cnf,添加4行。
[mysqld]
server-id=2
master-host=172.16.1.3 #MASTER服务器的ip地址
master-user=zhaoyun #连接MASTER服务器的用户名
master-password=123456 #密码
2、重启服务
[root@BACKUP ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3、重启服务后会在
数据库目录下生成几个文件
[root@BACKUP
mysql]# ls
ib_logfile1 mysqld-relay-bin.000001 mysqld-relay-bin.index test
ibdata1 master.info mysql.sock
ib_logfile0 mysql relay-log.info
[root@BACKUP mysql]# pwd
/var/lib/mysql
mysqld-relay-bin.000001 #binload文件，从master复制而来
mysqld-relay-bin.index #binload的信息
master.info #master信息
relay-log.info #中继日志信息
4、查看slave的状态
[root@BACKUP ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_Sta |