【原创】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)log_bin= /var/log/mysql/mysql-bin.log

添加或取消注释 "log_bin= /var/log/mysql/mysql-bin.log" ,主从数据库是基于bin_log来使从数据库与主数据库上的数据保持一致性的，所以配主从必须开启 "log_bin" .

（bin_log记载了数据库执行的增、删、改语句，从数据库也执行这些语句，从而保持一致；所以主从数据库上的数据必须保持一致！！！）

(3)binlog_do_db=databaseName

需要用binlog记录状态的数据库名，也就是需要主从同步的数据库名。

2.为slave创建复制用户并给与复制权限

(1)创建复制用户，并给与权限

每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中。假如，你想创建repl用户，如下：
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
       -> TO repl@'192.168.1.%' IDENTIFIED BY 'p4ssword';

上面这条语句包含，用户名：repl 密码：p4ssword 允许的ip地址: 192.168.1.%(%表示所有这个号段下的都可以连接访问)

(2)重启使变更生效

重启数据库一般时间较长，在工作服务器上请谨慎操作！

三、从数据库配置

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 STATUSG;

*************************** 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 STATUSG;    #查看复制进行情况

*************************** 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 PROCESSLISTG; #也可查看是否开启复制

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

就是说’server-id‘重复，推荐使用ip末段，好记也不容易出现重复错误。

 另：如果单独将从库中的数据（记：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