初学者必备的关于mysql笔记

　　今天给大家系统介绍下MySQL的一些基础知识，包括mysql的概述，故障，使用的工具，复制。

　　同mongodb，Redis这样的NoSQL数据库的复制相比，MySQL复制显得相当复杂！下文中将为大家讲解详细的MySQL复制的概述、安装、故障、技巧、工具的有关内容，希望大家能够从中有所收获。

　　概述

　　首先主服务器把数据变化记录到主日志，然后从服务器通过I/O线程读取主服务器上的主日志，并且把它写入到从服务器的中继日志中，接着SQL线程读取中继日志，并且在从服务器上重放，从而实现MySQL复制。具体如下图所示：

　　MySQL复制

　　整个过程反映到从服务器上，对应三套日志信息，可在从服务器上用如下命令查看：

　　mysql>SHOWSLAVESTATUS;

　　Master_Log_File&Read_Master_Log_Pos：下一个传输的主日志信息。

　　Relay_Master_Log_File&Exec_Master_Log_Pos：下一个执行的主日志信息。

　　Relay_Log_File&Relay_Log_Pos：下一个执行的中继日志信息。

　　理解这些日志信息的含义对于解决故障至关重要，后文会详细阐述。

　　安装

　　先在主服务器上创建复制账号：

　　mysql>GRANTREPLICATIONSLAVEON*.*

　　TO'<SLAVE_USER>'@'<SLAVE_HOST>'

　　IDENTIFIEDBY'<SLAVE_PASSWORD>';

　　注：出于安全性和灵活性的考虑，不要把root等具有SUPER权限用户作为复制账号。

　　然后设置主服务器配置文件（缺省：/etc/my.cnf）：

　　[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

　　注：一定要保证主从服务器各自的server_id唯一，避免冲突。

　　注：如果没有指定log_bin的话，缺省会使用主机名作为名字，如此一来一旦主机名发生改变，就会出问题，所以推荐指定log_bin（从服务器的relay_log存在一样的问题）。

　　注：sync_binlog，innodb_flush_log_at_trx_commit，innodb_support_xa三个选项都是出于安全目的设置的，不是复制的必须选项。

　　接着设置从服务器配置文件（缺省：/etc/my.cnf）：

　　[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

　　注：如果用户有SUPER权限，则read_only无效。

　　注：有了skip_slave_start，除非使用STARTSLAVE命令，否则从服务器不会开始复制。

　　注：设置log_slave_updates，让从服务器记录日志，有助于在必要时把从切换成主。

　　下面最重要的步骤是如何克隆一份主服务器的数据：

　　如果数据库使用的是MyISAM表类型的话，可按如下方式操作：

　　shell>mysqldump–all-databases–master-data=1>data.sql

　　注：master-data选项缺省会打开lock-all-tables，并写入CHANGEMASTERTO语句。

　　如果数据库使用的是InnoDB表类型的话，则应该使用single-transcation：

　　shell>mysqldump–all-databases–single-transaction–master-data=1>data.sql

　　有了数据文件，传输到从服务器上并导入：

　　shell>mysql<data.sql

　　如果数据量很大的话，mysqldump会非常慢，此时直接拷贝数据文件能节省不少时间：

　　在拷贝之前要先锁定数据，然后再获得相关的日志信息（FILE&POSITION）：

　　mysql>FLUSHTABLESWITHREADLOCK;

　　mysql>SHOWMASTERSTATUS;

　　接下来拷贝数据文件时，如果是MyISAM表类型的话，直接拷贝即可；如果是InnoDB表类型的话，一定要先停止MySQL服务再拷贝，否则拷贝文件可能无法使用。把拷贝的数据文件直接复制到从服务器的数据目录。

　　最后还需要再指定一下日志信息：

　　mysql>CHANGEMASTERTO

　　MASTER_HOST='<MASTER_HOST>',

　　MASTER_USER='<SLAVE_USER>',

　　MASTER_PASSWORD='<SLAVE_PASSWORD>',

　　MASTER_LOG_FILE='<FILE>',

　　MASTER_LOG_POS=<POSITION>;

　　注：不要在my.cnf配置文件里设置MASTER_USER和MASTER_PASSWORD，因为最终生效的是CHANGEMASTERTO生成的master.info文件里的信息。

　　在主服务器上直接拷贝数据文件虽然很快，但需要锁表或者停止服务，这会影响线上服务。如果先前已经有了从服务器，那么可以用旧的从服务器做母本来克隆新的从服务器：

　　先在旧的从服务器上查询日志信息：

　　mysql>SHOWSLAVESTATUS;

　　我们需要的是其中的Relay_Master_Log_File&Exec_Master_Log_Pos。

　　然后在旧的从服务器上按照前面的方法得到数据，并在新的从服务器上还原。

　　接着在新的从服务器上设置日志信息：

　　mysql>CHANGEMASTERTO

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

　　不管用那个方法，最后记得在从服务器上启动复制，并检查工作是否正常：

　　mysql>STARTSLAVE;

　　mysql>SHOWSLAVESTATUS;

　　如果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，则说明前面某些配置步骤出错，或者对应的日志文件有问题。

　　故障

　　问题：主从复制不止何故停止了，我该怎么办？

#p#分页标题#e#

　　答案：复制错误多半是因为日志错误引起的，所以首先要搞清楚是主日志错误还是中继日志错误，从错误信息里一般就能判断，如果不能可以使用类似下面的mysqlbinlog命令：

　　shell>mysqlbinlog<MASTER_BINLOG_FILE>>/dev/null

　　shell>mysqlbinlog<SLAVE_BINLOG_FILE>>/dev/null

　　如果没有错误，则不会有任何输出，反之如果有错误，则会显示出来。

　　如果是主日志错误，则需要在从服务器使用SETGLOBALsql_slave_skip_counter，如下：

　　mysql>SETGLOBALsql_slave_skip_counter=1;

　　mysql>STARTSLAVE;

　　注：如果有多个错误，可能需要执行多次（提醒：主从服务器数据可能因此不一致）。

　　如果是中继日志错误，只要在从服务器使用SHOWSLAVESTATUS结果中的日志信息重新CHANGEMASTERTO即可，系统会抛弃当前的中继日志，重新下载：

　　mysql>CHANGEMASTERTO

　　MASTER_LOG_FILE='<Relay_Master_Log_File>',

　　MASTER_LOG_POS=<Exec_Master_Log_Pos>;

　　mysql>STARTSLAVE;

　　至于为什么使用的是Relay_Master_Log_File&Exec_Master_Log_Pos，参见概述。

　　问题：主服务器宕机了，如何把从服务器提升会主服务器？

　　答案：在一主多从的环境总，需选择数据最新的从服务器做新的主服务器。如下图所示：

　　提升从服务器为主服务器

　　在一主（Server1）两从（Server2，、Server3）的环境中，Server1宕机后，等到Server1和Server2把宕机前同步到的日志都执行完，比较Master_Log_File和Read_Master_Log_Pos就可以判断出谁快谁慢，因为Server2从Server1同步的数据（1582）比Server3从Server1同步的数据（1493）新，所以应该提升Server2为新的主服务器，那么Server3在CHANGEMASTERTO到Server2的时候应该使用什么样的参数呢？1582-1493=89，而Server2的最后的二进制日志位置是8167，所以答案是8167-89=8078。

　　技巧

　　主从服务器中的表可以使用不同的表类型。比如主服务器可以使用InnoDB表类型，提供事务，行锁等高级特性，从服务器可以使用MyISAM表类型，内存消耗少，易备份等优点。还有一个例子，一台主服务器如果同时带很多个从服务器的话，势必会影响其性能，此时可以拿出一台服务器作为从服务器代理，使用BLACKHOLE表类型，只记录日志，不写数据，由它带多台从服务器，从而提升性能。

　　主从服务器中的表可以使用不同的键类型。比如主服务器用InnoDB，键用VARCHAR的话节省空间，从服务器使用MyISAM，键用CHAR提高速度，因为MyISAM有静态表一说。

　　主从服务器中的表可以使用不同的索引。主服务器主要用来应付写操作，所以除了主键和唯一索引等保证数据关系的索引一般都可以不加，从服务器一般用来应付读操作，所以可以针对查询特征设置索引，再进一步，不同的从服务器可以针对不同的查询设置不同的索引。

　　工具

　　有一些优秀的工具可以让你的复制工作得到事半功倍的效果，详细内容请参考各自文档：

　　Multi-MasterReplicationManagerforMySQL

　　PerconaXtraBackup

　　Maatkit

　　Tungsten-replicator

　　本文介绍了MySQL复制的概述、安装、故障、技巧、工具的内容，对于初学者来说，最好的领悟方法就是在实际的运用中具体领悟他们，得到自己的体会。