【原创】mysql主从数据库配置
一、首先,你要有两个数据库
1.sudo apt-get install mysql-server (不需要加版本号,否则都是不能正常工作的),这个下载安装一条龙,安完直接能用!
配置文件位于 /etc/mysql/my.cnf
2.按照上述 1 的方法,为主从两台服务器分别安装数据库。
我的master:ubuntu:ip:192.168.1.100;
slave:ubuntu:ip:192.168.1.101;
3.从高层来看,复制分成三步:
(1) master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
(2) slave将master的binary log events拷贝到它的中继日志(relay log);
(3) slave重做中继日志中的事件,将改变反映它自己的数据。
二、主数据库配置
1.主数据库配置文件修改 /etc/mysql/my.cnf
(1)server-id=100
添加或取消注释 “server-id=100” 我这里选用100,为主数据库ip末位,这个server-id必须是唯一的。
2.为slave创建复制用户并给与复制权限
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
-> TO repl@'192.168.1.%' IDENTIFIED BY 'p4ssword';
三、从数据库配置
1.从数据库配置文件修改 /etc/mysql/my.cnf
(1)与主数据库配置类似
server-id=100
log_bin= /var/log/mysql/mysql-bin.log #开启做记录、备份。方便其切换为主库。
binlog_do_db=databaseName
(2)slave端多出的配置
relay_log= /var/log/mysql/mysql-relay-bin.log
#relay log就是数据库的中继日志;slave将master的binary log events拷贝到它的中继日志(relay log) ;
log_slave_updates = 1
#通常情况,从服务器从主服务器接收到的更新不记入它的二进制日志。该选项告诉从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志。为 了使该选项生效,还必须用--logs-bin选项启动从服务器以启用二进制日志。如果想要应用链式复制服务器,应使用--logs-slave- updates。
#read_only=1
#有些人开启了slave的二进制日志,却没有设置log_slave_updates,然后查看slave的数据是否改变,这是一种错误的配置。所以,尽量使用read_only,它防止改变数据(除了特殊的线程)。但是,read_only并是很实用,特别是那些需要在slave上创建表的应用。
2.slave连接master
(1)为slave配置master
接下来就是让slave连接master,并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作,而应该使用CHANGE MASTER TO语句,该语句可以完全取代对配置文件的修改,而且它可以为slave指定不同的master,而不需要停止服务器。如下:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.100',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='p4ssword',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;
MASTER_LOG_POS的值为0,因为它是日志的开始位置。然后,你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确:
配置如下两项时:
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;
建议先执行:-> SHOW MASTER STATUS; 查看当前master的状态,MASTER_LOG_FILE配置为下图中“File”对应值(否则也是不能同步数据的),下图中“Position”表示二进制文件(bin_log)当前位置;
想要change to master 必须先停止复制:stop slave;
(2)查看slave状态
mysql>SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
...omitted...
Seconds_Behind_Master: NULL
Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running表明slave还没有开始复制过程。日志的位置为4而不是0,这是因为0只是日志文件的开始位置,并不是日志位置。实际上,MySQL知道的第一个事件的位置是4。
(3)开始和关闭复制
mysql>start slave; #开启复制
mysql> SHOW SLAVE STATUS\G; #查看复制进行情况
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 164
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 164
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...omitted...
Seconds_Behind_Master: 0
注意,slave的I/O和SQL线程都已经开始运行,而且Seconds_Behind_Master不再是NULL。日志的位置增加了,意味着一些事件被获取并执行了。如果你在master上进行修改,你可以在slave上看到各种日志文件的位置的变化,同样,你也可以看到数据库中数据的变化。
mysql>SHOW PROCESSLIST\G; #也可查看是否开启复制
mysql>stop slave; #关闭复制
四、错误及解决
1.ERROR:Could not find target log during relay log initialization
需要确认两点,Change to mysql用户名的正确性,log_file的正确。
2.Slave I/O: error connecting to master 'rep1@192.168.0.130:3306' - retry-time: 60 retries: 86400, Error_code: 2013
在从库机器,mysql命令可以通过rep1用户链接到主库。(记得bind-address 注释掉,否则没法在第三方机器访问。)
然后主库执行flush logs;从库重新执行change to ;最后start slave就可以了。
3.Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids
另:如果单独将从库中的数据(记:A)删除,主库的变更影响到此条数据(记:A)时,从库中此条(记:A)数据不存在,则会导致复制无法继续,Slave_SQL_Running就挂起了,变成NO,导致复制停止。
(1)从库read_only=1
(2)互为主数据库:master-master
参考:http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html
链式主从:http://zhumeng8337797.blog.163.com/blog/static/1007689142011994357908/
MySql-flush:http://www.cnblogs.com/zfying/archive/2011/11/30/2268835.html