心雨在线科技--专业提供APP开发|合成游戏开发|公众号小程序分销商城｜多级别制度营销商城|高端网站建设及改版|公众号开发｜H5页面制作|软件平台开发

详细讲解MySQL复制

发布时间：2019-04-13浏览次数：1034 <table style="BORDER-RIGHT: #cccccc 1px dotted; TABLE-LAYOUT: fixed; BORDER-TOP: #cccccc 1px dotted; BORDER-LEFT: #cccccc 1px dotted; BORDER-BOTTOM: #cccccc 1px dotted" cellspacing="0" cellpadding="6" width="95%" align="center" border="0"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#fdfddf"> WebjxCom提示：MySQL复制的概述、安装、故障、技巧、工具. </td> </tr></tbody></table> 同<a href="http://www.mongodb.org/" target="_blank">MongoDB</a>，<a href="http://redis.io/" target="_blank">Redis</a>这样的NoSQL数据库的复制相比，<a href="http://dev.mysql.com/doc/refman/5.5/en/replication.html" target="_blank">MySQL复制</a>显得相当复杂！ 概述 首先主服务器把数据变化记录到主日志，然后从服务器通过I/O线程读取主服务器上的主日志，并且把它写入到从服务器的中继日志中，接着SQL线程读取中继日志，并且在从服务器上重放，从而实现MySQL复制。具体如下图所示： <img border="0" alt="MySQL复制" src="http://www.webjx.com/files/allimg/110406/1657080.png" width="510" height="330"> MySQL复制 整个过程反映到从服务器上，对应三套日志信息，可在从服务器上用如下命令查看： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> SHOW SLAVE STATUS;</code> </td> </tr></tbody></table> Master_Log_File & Read_Master_Log_Pos：下一个传输的主日志信息。 Relay_Master_Log_File & Exec_Master_Log_Pos：下一个执行的主日志信息。 Relay_Log_File & Relay_Log_Pos：下一个执行的中继日志信息。 理解这些日志信息的含义对于解决故障至关重要，后文会详细阐述。 安装 先在主服务器上创建复制账号： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> GRANT REPLICATION SLAVE ON *.*        TO '<SLAVE_USER>'@'<SLAVE_HOST>'        IDENTIFIED BY '<SLAVE_PASSWORD>';</code> </td> </tr></tbody></table> 注：出于安全性和灵活性的考虑，不要把root等具有<a href="http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_super" target="_blank">SUPER</a>权限用户作为复制账号。 然后设置主服务器配置文件（缺省：/etc/my.cnf）： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>[mysqld] server_id = 100 log_bin = mysql-bin log_bin_index = mysql-bin.index sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 1</code> </td> </tr></tbody></table> 注：一定要保证主从服务器各自的server_id唯一，避免冲突。 注：如果没有指定<a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_log_bin" target="_blank">log_bin</a>的话，缺省会使用主机名作为名字，如此一来一旦主机名发生改变，就会出问题，所以推荐指定log_bin（从服务器的relay_log存在一样的问题）。 注：<a href="http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_sync_binlog" target="_blank">sync_binlog</a>，<a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit" target="_blank">innodb_flush_log_at_trx_commit</a>，<a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_support_xa" target="_blank">innodb_support_xa</a>三个选项都是出于安全目的设置的，不是复制的必须选项。 接着设置从服务器配置文件（缺省：/etc/my.cnf）： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>[mysqld] server_id = 200 log_bin = mysql-bin log_bin_index = mysql-bin.index relay_log = mysql-relay-bin relay_log_index = mysql-relay-bin.index read_only = 1 skip_slave_start = 1 log_slave_updates = 1</code> </td> </tr></tbody></table> 注：如果用户有SUPER权限，则<a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only" target="_blank">read_only</a>无效。 注：有了<a href="http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_skip-slave-start" target="_blank">skip_slave_start</a>，除非使用<a href="http://dev.mysql.com/doc/refman/5.5/en/start-slave.html" target="_blank">START SLAVE</a>命令，否则从服务器不会开始复制。 注：设置<a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_log_slave_updates" target="_blank">log_slave_updates</a>，让从服务器记录日志，有助于在必要时把从切换成主。 下面最重要的步骤是如何克隆一份主服务器的数据： 如果数据库使用的是MyISAM表类型的话，可按如下方式操作： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>shell> mysqldump --all-databases --master-data=1 > data.sql</code> </td> </tr></tbody></table> 注：<a href="http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_master-data" target="_blank">master-data</a>选项缺省会打开<a href="http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_lock-all-tables" target="_blank">lock-all-tables</a>，并写入<a href="http://dev.mysql.com/doc/refman/5.5/en/change-master-to.html" target="_blank">CHANGE MASTER TO</a>语句。 如果数据库使用的是InnoDB表类型的话，则应该使用<a href="http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction" target="_blank">single-transcation</a>： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql</code> </td> </tr></tbody></table> 有了数据文件，传输到从服务器上并导入： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>shell> mysql < data.sql</code> </td> </tr></tbody></table> 如果数据量很大的话，mysqldump会非常慢，此时直接拷贝数据文件能节省不少时间： 在拷贝之前要先锁定数据，然后再获得相关的日志信息（FILE & POSITION）： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> FLUSH TABLES WITH READ LOCK;</code> <code>mysql> SHOW MASTER STATUS;</code> </td> </tr></tbody></table> 接下来拷贝数据文件时，如果是MyISAM表类型的话，直接拷贝即可；如果是InnoDB表类型的话，一定要先停止MySQL服务再拷贝，否则拷贝文件可能无法使用。把拷贝的数据文件直接复制到从服务器的数据目录。 最后还需要再指定一下日志信息： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> CHANGE MASTER TO        MASTER_HOST='<MASTER_HOST>',        MASTER_USER='<SLAVE_USER>',        MASTER_PASSWORD='<SLAVE_PASSWORD>',        MASTER_LOG_FILE='<FILE>',        MASTER_LOG_POS=<POSITION>;</code> </td> </tr></tbody></table> 注：不要在my.cnf配置文件里设置MASTER_USER和MASTER_PASSWORD，因为最终生效的是CHANGE MASTER TO生成的master.info文件里的信息。 在主服务器上直接拷贝数据文件虽然很快，但需要锁表或者停止服务，这会影响线上服务。如果先前已经有了从服务器，那么可以用旧的从服务器做母本来克隆新的从服务器： 先在旧的从服务器上查询日志信息： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> SHOW SLAVE STATUS;</code> </td> </tr></tbody></table> 我们需要的是其中的Relay_Master_Log_File & Exec_Master_Log_Pos。 然后在旧的从服务器上按照前面的方法得到数据，并在新的从服务器上还原。 接着在新的从服务器上设置日志信息： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> CHANGE MASTER TO        MASTER_HOST='<MASTER_HOST>',        MASTER_USER='<SLAVE_USER>',        MASTER_PASSWORD='<SLAVE_PASSWORD>',        MASTER_LOG_FILE='<Relay_Master_Log_File>',        MASTER_LOG_POS=<Exec_Master_Log_Pos>;</code> </td> </tr></tbody></table> 不管用那个方法，最后记得在从服务器上启动复制，并检查工作是否正常： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> START SLAVE;</code> <code>mysql> SHOW SLAVE STATUS;</code> </td> </tr></tbody></table> 如果IO线程和SQL线程都显示Yes，就可以感谢上帝了： Slave_IO_Running 对应：Master_Log_File & Read_Master_Log_Pos Slave_SQL_Running 对应：Relay_Master_Log_File & Exec_Master_Log_Pos 如果显示No，则说明前面某些配置步骤出错，或者对应的日志文件有问题。 故障 问题：主从复制不止何故停止了，我该怎么办？ 答案：复制错误多半是因为日志错误引起的，所以首先要搞清楚是主日志错误还是中继日志错误，从错误信息里一般就能判断，如果不能可以使用类似下面的mysqlbinlog命令： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null</code> <code>shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null</code> </td> </tr></tbody></table> 如果没有错误，则不会有任何输出，反之如果有错误，则会显示出来。 如果是主日志错误，则需要在从服务器使用<a href="http://dev.mysql.com/doc/refman/5.5/en/set-global-sql-slave-skip-counter.html" target="_blank">SET GLOBAL sql_slave_skip_counter</a>，如下： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> SET GLOBAL sql_slave_skip_counter = 1;</code> <code>mysql> START SLAVE;</code> </td> </tr></tbody></table> 注：如果有多个错误，可能需要执行多次（提醒：主从服务器数据可能因此不一致）。 如果是中继日志错误，只要在从服务器使用SHOW SLAVE STATUS结果中的日志信息重新CHANGE MASTER TO即可，系统会抛弃当前的中继日志，重新下载： <table style="BORDER-BOTTOM: #0099cc 1px solid; BORDER-LEFT: #0099cc 1px solid; TABLE-LAYOUT: fixed; BORDER-TOP: #0099cc 1px solid; BORDER-RIGHT: #0099cc 1px solid" border="0" cellspacing="0" cellpadding="6" width="95%" align="center"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#ddedfb"> <code>mysql> CHANGE MASTER TO        MASTER_LOG_FILE='<Relay_Master_Log_File>',        MASTER_LOG_POS=<Exec_Master_Log_Pos>; mysql> START SLAVE;</code> </td> </tr></tbody></table> 至于为什么使用的是Relay_Master_Log_File & Exec_Master_Log_Pos，参见概述。 问题：主服务器宕机了，如何把从服务器提升会主服务器？ 答案：在一主多从的环境总，需选择数据最新的从服务器做新的主服务器。如下图所示： <img border="0" alt="提升从服务器为主服务器" src="http://www.webjx.com/files/allimg/110406/1657081.png" width="500" height="470"> 提升从服务器为主服务器 在一主（Server1）两从（Server2，、Server3）的环境中，Server1宕机后，等到Server1和Server2把宕机前同步到的日志都执行完，比较Master_Log_File和Read_Master_Log_Pos就可以判断出谁快谁慢，因为Server2从 Server1同步的数据（1582）比Server3从Server1同步的数据（1493）新，所以应该提升Server2为新的主服务器，那么 Server3在CHANGE MASTER TO到Server2的时候应该使用什么样的参数呢？1582-1493=89，而Server2的最后的二进制日志位置是8167，所以答案是 8167-89=8078。 技巧 主从服务器中的表可以使用不同的表类型。比如主服务器可以使用InnoDB表类型，提供事务，行锁等高级特性，从服务器可以使用MyISAM表类型，内存消耗少，易备份等优点。还有一个例子，一台主服务器如果同时带很多个从服务器的话，势必会影响其性能，此时可以拿出一台服务器作为从服务器代理，使用BLACKHOLE表类型，只记录日志，不写数据，由它带多台从服务器，从而提升性能。 主从服务器中的表可以使用不同的键类型。比如主服务器用InnoDB，键用VARCHAR的话节省空间，从服务器使用MyISAM，键用CHAR提高速度，因为MyISAM有静态表一说。 主从服务器中的表可以使用不同的索引。主服务器主要用来应付写操作，所以除了主键和唯一索引等保证数据关系的索引一般都可以不加，从服务器一般用来应付读操作，所以可以针对查询特征设置索引，再进一步，不同的从服务器可以针对不同的查询设置不同的索引。 工具 有一些优秀的工具可以让你的复制工作得到事半功倍的效果，详细内容请参考各自文档： <a href="http://mysql-mmm.org/" target="_blank">Multi-Master Replication Manager for MySQL</a> <a href="http://www.percona.com/software/percona-xtrabackup/" target="_blank">Percona XtraBackup</a> <a href="http://www.maatkit.org/" target="_blank">Maatkit</a> <a href="http://code.google.com/p/tungsten-replicator/" target="_blank">Tungsten-replicator</a> 此外，Google Project Hosting里还有很多有趣的项目，可用<a href="http://code.google.com/hosting/search?q=label:mysql+replication" target="_blank">mysql+replication</a>标签搜索。 说明：本文参考了下面列出的书籍中相关的内容： <a href="http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716/" target="_blank">High Performance MySQL： Optimization， Backups， Replication， and More</a> <a href="http://www.amazon.com/MySQL-High-Availability-Building-Centers/dp/0596807309/" target="_blank">MySQL High Availability： Tools for Building Robust Data Centers</a> （来源：<a href="http://huoding.com/2011/04/05/59" target="_blank">火丁笔记</a>）

返回列表上一篇 | MySql数据库插入式的多存储引擎机制下一篇 | SQL Server允许重复空字段不空字段值唯一

【本站公告】

选择以下客服马上在线沟通：

详细讲解MySQL复制

深圳公司