mysql主从复制和mysql主主复制搭建_MySQL
A. 主从复制。
两台主机 IP:
1) 192.168.11.141 (master)
2) 192.168.11.142 (slave)
依次在141和142上安装mysql并启动服务。拷贝/usr/share/mysql/下自带的默认配置(选择合适的一个,例如my-huge.conf),并改名为my.conf,复制到/etc/下。
修改master(141)的/etc/my.conf文件,追加如下代码:
server-id=1
log-bin=mysql-bin
binlog_format=mixed
log-bin=/var/lib/mysql/mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1
修改master(142)的/etc/my.conf文件,追加如下代码:
server-id=2
log-bin=mysql-bin
binlog_format=mixed
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
replicate-do-db=test
datadir=/var/lib/mysql
注意:replicate-do-db代表需要复制的数据库,如果为多个可以逗号连接。
不过我在实践过程中发现需要重复设置replicate-do-db来增加需要复制的数据库。
replicate-do-db=test
replicate-do-db=test1
replicate-do-db=test2
.....
在主机Master(141)上创建一个有复制权限的用户:
[sql] view plaincopy
mysql>create user slave@192.168.11.142;
mysql>grant replication slave on *.* to slave@192.168.11.142 identified by '123456';
锁住主机,记录二进制日志的位置(后面设置从机的时候会用到)
[sql] view plaincopy
mysql>flush tables with read lock;
mysal>show master status;
File:mysql-bin.000003 Position:750
复制主机数据库(test)到从机
[sql] view plaincopy
shell>/usr/bin/mysqldump test -uroot -pxxxx --opt | mysql test -uroot -pxxxx -h 192.168.11.142
从机Slave(142)初始化复制
[sql] view plaincopy
mysql>stop slave;
mysql>change master to master_host='192.168.11.141',
->master_user='slave',
->master_password='123456',
->master_log_file='mysql-bin.000003',
->master_log_pos=750;
此刻可以unlock主机的了:
[sql] view plaincopy
mysql>unlock tables;
开启复制
[sql] view plaincopy
mysql>start slave;
查看复制状态
[sql] view plaincopy
mysql>show slave status
当看见如下内容的时候证明我们复制搭建成功,复制开始运行了。Slave_IO_Running: YesSlave_SQL_Running: Yes
===================================================================================================================
http://isadba.com/?p=159
http://blog.csdn.net/mydeman/article/details/6845567
http://liuyu.blog.51cto.com/183345/98867
http://mysql-mmm.org/
主从复制:http://tech.it168.com/a2011/0718/1219/000001219244.shtml
http://chinaapp.sinaapp.com/thread-1817-1-1.html