MYSQL 教程 §16, 复制
时间：2008-03-17 来源：oychw
§16, 复制
§16.1 复制的原理
Mysql的复制采取主从模式。一个master可以对应多个slaves。依靠master上面的binary log。Mysql最好使用一样的版本。同步机制和UT的CMM/MDR/MDRA极其相似。
§16.2 安装配置复制
＊ 创建复制用户
如果使用LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER来初始化slave，授权类似如下：
grant replication slave, reload, super, select
on logs.*
to replication@" %" identified by 'password';
初始化之后可以减小权限：
grant replication slave on logs.* to replication@"%" identified by 'password';
＊ 主机配置
mysql> show variables like "log_bin" ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
配置：
[mysqld]
log-bin
server-id=1
＊ 创建主机快照
n A complete, consistent snapshot of the current database
n The name of the master server's binary log file
n The offset into the binary log where the server is currently
MyISAM可以通过LOAD DATA FROM MASTER获取快照，但是效率不高，对主机数据有加锁。通过文件系统效率会更高点。
InnoDB可以使用hot backup tool. http://www.innodb.com/hotbackup.html
文件系统快照的制作：
flush tables with read lock;
show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+-------------------+----------+--------------+------------------+
| server-bin.000007 | 211 | | |
+-------------------+----------+--------------+------------------+
tar -cvf /tmp/snapshot.tar /path/mysql/data/logs
unlock tables;
＊ 从机配置
server-id=2
＊ 从机启动
change master to master_host='server',
master_user='replication',
master_password='password',
master_log_file='server-bin.000007',
master_log_pos=211;
start slave;
MyISAM表：load table logs.logJan2003 from master; load data from master;
[mysqld]
server-id = 2
master-host = server
master-user = replication
master-password = password
replicate-do-db = logs
§16.3 高级拓朴
其他的参考资料
www.lukelaura.com
www.emicnetworks.com.
§16.4 小结
· Replication is not suitable for all occasions, but if you have a busy application with a high read-to-write ratio, it can be an excellent scaling tool.
· Remember that updates are not applied to slaves in real time. Each copy of a table should move from one consistent state to another, but reads from different servers may give dated results while a slave updates.
· The most useful replication-related queries are START SLAVE, LOAD TABLE name FROM MASTER, LOAD DATA FROM MASTER, SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW SLAVE HOSTS.
相关阅读 更多 +
