MySQL复制的概述、安装、故障、技巧、工具

很文博客hinven.com 2021年9月29日08:36:28数据库评论1946101字阅读模式

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

同MongoDB,Redis这样的NoSQL数据库的复制相比,MySQL复制显得相当复杂!

概述

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

MySQL复制的概述、安装、故障、技巧、工具

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

  1. mysql> SHOW SLAVE STATUS;

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

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

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

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

安装

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

  1. mysql> GRANT REPLICATION SLAVE ON *.*
  2. TO '<SLAVE_USER>'@'<SLAVE_HOST>'
  3. IDENTIFIED BY '<SLAVE_PASSWORD>';

注:出于安全性和灵活性的考虑,不要把root等具有SUPER(http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_super)权限用户作为复制账号。然后设置主服务器配置文件(缺省:/etc/my.cnf):

  1. [mysqld]
  2. server_id = 100
  3. log_bin = mysql-bin
  4. log_bin_index = mysql-bin.index
  5. sync_binlog = 1
  6. innodb_flush_log_at_trx_commit = 1
  7. innodb_support_xa = 1

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

注:如果没有指定log_bin(http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_log_bin)的话,缺省会使用主机名作为名字,如此一来一旦主机名发生改变,就会出问题,所以推荐指定log_bin(从服务器的relay_log存在一样的问题)。

注:sync_binlog(http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_sync_binlog)innodb_flush_log_at_trx_commit(http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit)innodb_support_xa(http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_support_xa)三个选项都是出于安全目的设置的,不是复制的必须选项。

接着设置从服务器配置文件(缺省:/etc/my.cnf):

  1. [mysqld]
  2. server_id = 200
  3. log_bin = mysql-bin
  4. log_bin_index = mysql-bin.index
  5. relay_log = mysql-relay-bin
  6. relay_log_index = mysql-relay-bin.index
  7. read_only = 1
  8. skip_slave_start = 1
  9. log_slave_updates = 1

注:如果用户有SUPER权限,则read_only(http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only)无效。

注:有了skip_slave_start(http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_skip-slave-start),除非使用START SLAVE(http://dev.mysql.com/doc/refman/5.5/en/start-slave.html)命令,否则从服务器不会开始复制。

注:设置log_slave_updates(http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_log_slave_updates),让从服务器记录日志,有助于在必要时把从切换成主。

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

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

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

注:master-data(http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_master-data)选项缺省会打开lock-all-tables(http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_lock-all-tables),并写入CHANGE MASTER TO(http://dev.mysql.com/doc/refman/5.5/en/change-master-to.html)语句。

如果数据库使用的是InnoDB表类型的话,则应该使用single-transcation(http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction)

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

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

  1. shell> mysql < data.sql

如果数据量很大的话,mysqldump会非常慢,此时直接拷贝数据文件能节省不少时间:
在拷贝之前要先锁定数据,然后再获得相关的日志信息(FILE & POSITION):

  1. mysql> FLUSH TABLES WITH READ LOCK;
  2. mysql> SHOW MASTER STATUS;

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

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

  1. mysql> CHANGE MASTER TO
  2. MASTER_HOST='<MASTER_HOST>',
  3. MASTER_USER='<SLAVE_USER>',
  4. MASTER_PASSWORD='<SLAVE_PASSWORD>',
  5. MASTER_LOG_FILE='<FILE>',
  6. MASTER_LOG_POS=<POSITION>;

注:不要在my.cnf配置文件里设置MASTER_USER和MASTER_PASSWORD,因为最终生效的是CHANGE MASTER TO生成的master.info文件里的信息。

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

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

  1. mysql> SHOW SLAVE STATUS;

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

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

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

  1. mysql> CHANGE MASTER TO
  2. MASTER_HOST='<MASTER_HOST>',
  3. MASTER_USER='<SLAVE_USER>',
  4. MASTER_PASSWORD='<SLAVE_PASSWORD>',
  5. MASTER_LOG_FILE='<Relay_Master_Log_File>',
  6. MASTER_LOG_POS=<Exec_Master_Log_Pos>;

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

  1. mysql> START SLAVE;
  2. mysql> SHOW SLAVE STATUS;

如果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命令:

  1. shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null
  2. shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null

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

如果是主日志错误,则需要在从服务器使用SET GLOBAL sql_slave_skip_counter(http://dev.mysql.com/doc/refman/5.5/en/set-global-sql-slave-skip-counter.html),如下:

  1. mysql> SET GLOBAL sql_slave_skip_counter = 1;
  2. mysql> START SLAVE;

注:如果有多个错误,可能需要执行多次(提醒:主从服务器数据可能因此不一致)。

如果是中继日志错误,只要在从服务器使用SHOW SLAVE STATUS结果中的日志信息重新CHANGE MASTER TO即可,系统会抛弃当前的中继日志,重新下载:

  1. mysql> CHANGE MASTER TO
  2. MASTER_LOG_FILE='<Relay_Master_Log_File>',
  3. MASTER_LOG_POS=<Exec_Master_Log_Pos>;
  4. mysql> START SLAVE;

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

问题:主服务器宕机了,如何把从服务器提升会主服务器?

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

MySQL复制的概述、安装、故障、技巧、工具

提升从服务器为主服务器

在一主(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有静态表一说。

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

工具

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

Multi-Master Replication Manager for MySQL(http://mysql-mmm.org/)

Percona XtraBackup(http://www.percona.com/software/percona-xtrabackup/)

Maatkit(http://www.maatkit.org/)

Tungsten-replicator(http://code.google.com/p/tungsten-replicator/)

此外,Google Project Hosting里还有很多有趣的项目,可用mysql+replication(http://code.google.com/hosting/search?q=label:mysql+replication)标签搜索。

说明:本文参考了下面列出的书籍中相关的内容:

High Performance MySQL: Optimization, Backups, Replication, and More(http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716/)

MySQL High Availability: Tools for Building Robust Data Centers(http://www.amazon.com/MySQL-High-Availability-Building-Centers/dp/0596807309/)

weinxin
扫一扫更精彩
大家的支持是我更新的动力!!!
MySQL,大小写 数据库

MySQL,大小写

修改MySql Server安装目录下的 my.ini 文件,在mysqld节下加入下面一行 set-variable=lower_case_table_names=0 (0:大小写敏感;1:大小写不...
广告也精彩
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定