日期:2014-05-16  浏览次数:20757 次

mysql的主从模式配置
1、改主库配置文件:
[mysqld]
log-bin=mysql-bin
server-id=1

2、授权,导数据:
grant replication slave on *.* TO 'replication'@'192.168.234.123' identified by 'replication';

mysqldump --master-data=2 --single-transaction -uroot -p --all-databases >dumpfile
//记下导出瞬间的日志文件和偏移(--master-data=2)

主库:
FLUSH TABLES WITH READ LOCK;--先把主库设置成只读,然后导出SQL或者直接复制数据文件
SHOW MASTER STATUS; --记下日志文件和偏移
UNLOCK TABLES;  --恢复主库写入

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      250 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3、备库:
复制主库的/etc/my.cnf和dumpfile。
把主库配置里的 server-id 改成2(或者3、4,多个备库保存互不相同),再加上

relay-log=slave-relay.log
relay-log-index=slave-relay-log.index
导入数据: mysql <dumpfile

SLAVE STOP;
CHANGE MASTER TO
MASTER_HOST='192.168.234.124',
MASTER_PORT=3306,
MASTER_USER='replication',
MASTER_PASSWORD='replication',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=321;
SLAVE START;

SHOW PROCESSLIST;
查看同步的状态,如果此命令的输出里有两个“system user”的进程,并且Command都是“Connect”就差不多OK。
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+-----------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------+------------------+
|  5 | root        | localhost | test | Query   |    0 | NULL                                                                  | SHOW PROCESSLIST |
|  6 | system user |           | NULL | Connect |  226 | Waiting for master to send event                                      | NULL             |
|  7 | system user |           | NULL | Connect |   99 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+------+---------+------+-----------------------------------+------------------+