分享程序员开发的那些事...
更新时间：2022-03-16 06:23:07
mysql主从复制的实现
1、MySQL复制的实现原理
MySQL支持单向、双向复制、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入一个二进制日志文件中，并创建一个索引文件以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，日志文件会通知主服务器，从服务器在日志中读取的最后一次成功更新的位置。接着，从服务器在上次成功更新的位置处开始进入更新操作。更新完成后从服务器开始进入等待状态，等待主服务器后续的更新。需要注意的是：在进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，可能发生对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
2、MySQL同步细节
MySQL同步功能由3个线程(master上1个binlogdump，slave上2个，分别是Sql进程和IO进程)来实现。执行“STARTSLAVE”语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。slave上的I/O线程读取master的BinlogDump线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relaylogs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。
slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。
例如，如果slave停止了一段时间，那么I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，但I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。
3、在test1,test2上安装MYSQL
test1的ip：192.168.46.131test2的ip：192.168.46.130
test1为mastertest2为slave
安装MySQL有多种方法，这里仅以源码安装test1为列说明。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@test1 ~]# yum install libtermcap libtermcap-devel imake autoconf automake libtool m4[root@test1 ~]# useradd -M -s /sbin/nologin mysql[root@test1 ~]# mkdir /usr/local/mysql[root@test1 ~]# tar zxvf mysql-5.1.70.tar.gz
[root@test1 ~]# cd mysql-5.1.70
[root@test1 mysql-5.1.70]# ./configure --prefix=/usr/local/mysql --enable-assembler --with-client-ldflags=-all-static --with-unix-socket-path=/tmp --with-charset=utf8 --enable-thread-safe-client --with-pthread --without-debug --with-big-tables --enable-community-features --enable-profiling --enable-local-infile --with-fast-mutexes --with-plugins=partition,federated,ndbcluster,innobase,csv,blackhole,myisam,innodb_plugin
[root@test1 mysql-5.1.70]#make
[root@test1 mysql-5.1.70]#strip sql/mysqld
[root@test1 mysql-5.1.70]#makeinstall
[root@test1 mysql-5.1.70]# cp support-files/my-medium.cnf /etc/my.cnf
[root@test1 mysql-5.1.70]# /usr/local/mysql/bin/mysql_install_db --user=mysql
[root@test1 mysql-5.1.70]# echo "PATH=\"/usr/local/mysql/bin:\$PATH\"">> /etc/profile
[root@test1 mysql-5.1.70]# export PATH="/usr/local/mysql/bin:$PATH"
|
4、在主服务器test1上编辑配置文件my.cnf
编辑主服务器的配置文件/etc/my.cnf，在[mysqld]中添加如下内容即可：
server-id=1
#服务器ID。服务器之间不能有重复ID，一般master是1
log-bin=mysql-bin
#打开mysql的binlog功能，后面的名字可以自己指定，如果不改名字的话，默认是以主机名字命名
binlog-do-db=test
#test是需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
binlog-ignore-db=mysql
#不需要备份的数据库名称，如果需要忽略备份多个数据库，重复设置这个选项即可。
5.在主服务器test1上建立复制用户mysql>GRANTREPLICATIONSLAVEON*.*TO'chenzhongyang'@'%'IDENTIFIEDBY'chenzhongyang';
这里一定将chenzhongyang用户设置为远程任意节点可以登录。
备份Master数据备份Master上的数据，首先执行如下SQL语句：
mysql>FLUSHTABLESWITHREADLOCK;QueryOK,0rowsaffected(0.00sec)
mysql>resetmaster;QueryOK,0rowsaffected(0.00sec)
不要退出这个终端，否则这个锁就失效了；在不退出终端的情况，再开一个终端直接打包压缩数据文件或使用mysqldump工具来导出数据。
[root@test1~]#cd/var/lib/#进入mysql的数据目录，根据自己情况而定。
[root@test1lib]#tarzcvfmysql.tar.gzmysql
[root@test1lib]#scpmysql.tar.gz192.168.46.130:/var/lib/
用scp命令把打包的数据传到其他几台Slave机器上。数据传输完成之后，在上面的命令终端上执行：
mysql>UNLOCKTABLES;
6.设置辅服务器test2
编辑/etc/my.cnf文件，在[mysqld]中添加如下内容即可：
server-id=2
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
其他的Slave以此类推，保证server-id全局唯一即可。
在slave上执行如下命令：
mysql>CHANGEMASTERTOMASTER_HOST='192.168.46.131',
->MASTER_USER='chenzhongyang',
->MASTER_PASSWORD='chenzhongyang',
->MASTER_LOG_FILE='mysql-bin.000001',
->MASTER_LOG_POS=98;
执行完之后执行：mysql>slavestart;
QueryOK,0rowsaffected(0.00sec)
mysql>showslavestatus\G
从输出可以看到：Slave_IO_Running和Slave_SQL_Running如果都为Yes时，表示配置成功。
7.需要注意
如果在my.cnf里面定义了log-bin、relay-log参数，那么要保证定义与hostname无关，因为如果这两类log的文件名与主机名有关，切换过程会导致slave主机不能继续同步的问题。例如可以做下设置：log-bin=mysql-binrelay-log=mysql-relay-bin保证在两台主机上两种文件的名字一样。
8,排错Slave_IO_Running:No
mysql>showslavestatus\G;-----察看从数据库服务器的状态
***************************1.row***************************
Slave_IO_State:
Master_Host:192.168.10.129
Master_User:qing
Master_Port:3306
Connect_Retry:10
Master_Log_File:mysql_bin.000006
Read_Master_Log_Pos:106
Relay_Log_File:mysqld-relay-bin.000001
Relay_Log_Pos:4
Relay_Master_Log_File:mysql_bin.000006
Slave_IO_Running:No------------显示为no，证明没有成功，
Slave_SQL_Running:Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:106
Relay_Log_Space:106
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:NULL
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:1236
last_IO_Error:Gotfatalerror1236frommasterwhenreadingdatafrombinarylog:'Couldnotfindfirstlogfilenameinbinarylogindexfile'----无法同步的原因
Last_SQL_Errno:0
Last_SQL_Error:
1rowinset(0.00sec)
ERROR:
Noqueryspecified
Slave_IO_Running:No证明同步没有成功，上面一行的
红色字体显示了错误的原因，一般出现这种情况，使用如下方法解决：
先进入slave中执行:slavestop来停止从库同步；
再去master中执行:flushlogs来清空日志；
然后在master中执行:showmasterstatus查看下主库的状态，主要是日志的文件和position；
然后回到slave中，执行:changemastertomaster_log_file='mysql-bin.000008',master_log_pos=106，文件和位置对应master中的;
最后在slave中执行:slavestart来启动同步。
9,排错Slave_SQL_Running:No
mysql>showslavestatus\G
Slave_IO_Running:Yes
Slave_SQL_Running:No
Last_Errno:1062
....
Seconds_Behind_Master:NULL
原因：
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.
本文转自陈仲阳0 51CTO博客，原文链接:http://blog.51cto.com/wolfword/1290938