MySQL主从复制的常见问题和有效解决方案

下面讲讲关于MySQL主从复制的常见问题和有效解决方案，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL主从复制的常见问题和有效解决方案这篇文章你一定会有所受益。

一、复制的问题和解决方案：

（1）数据损坏或丢失：

            某个slave上损坏：在slave上重放二进制日志，各种原因，导致数据不一致。例如硬件故障        

            //使用其他master上的备份的数据集，在新的slave上进行还原，从备份的位置开始从master上进行复制。

    重新复制即可//下线故障的slave，还原然后重新复制即可

    master数据损坏或丢失：

        //在各个slave上根据其gtid，找出最新的salve，然后提升为master

        //MHA+semi repl //MHA和一个node半同步复制，提升半同步node为新的master即可

//最后方案：

    二进制日志分析，然后还原

    单个故障了：活该！

（2）混合使用存储引擎；//坚决杜绝

    MyISAM不支持事务//不能回滚的

    InnoDB支持事务

（3）不唯一的server id 

    整个复制集群，可能导致错乱。

    //例如某个slave的server id和master的server id一样，那么他就不会重放master发来的server-id 

    修改server id 重新复制 

（4）复制延迟

    从node落后于master

    自己写脚本，不断监控，发现落后的时间不等于0，后，可以重启slave的io线程

    需要额外的监控工具来辅助实现。

    在master上事务是可以并发的，但是往同一个二进制日志写入是单线程的

    因此slave在本地应用也是单线程的

    master上有多个库，每一个库的事务是可以并行的，因为对A库的锁，对B库是没有影响的，但是反映到二进制日志中必然有先后的顺序

    因此slave落后是没有办法避免的，因此mysql 5.5之后引入了多线程机制。

    支持一从多主和多线程复制

    多线程复制//每一个数据库只启用一个线程

     //但是主库是不能一样的

    多线程复制：还是有用的，依赖于gtid 

        //MySQL 5.6.3之后，MariaDB10.0.5之后

    复制线程：Master上的IO线程和Slave上的IO、SQL线程，

数据库服务的衡量指标：

qps:query per second 

tps:事务per second 

数据库压力测试工具：

sysbench 

二、Galera Cluster:多用于MySQL的数据复制

http://galeracluster.com/

更底层的复制机制

需要单独编译实现。

安装方式

msyql官方或者galera //官方提供的支持galera 的版本

percona-cluster //整合过mysql版本

mariadb-cluster //专用的复制

base和epel都没有提供galera-cluster的源

默认的mariadb版本是不支持galera的

需要用到的包：

MariaDB-5.5.46-centos7-x86_64-client.rpm

MariaDB-5.5.46-centos7-x86_64-common.rpm

MariaDB-Galera-5.5.46-centos7-x86_64-common.rpm

//至少需要3个node

1.前提：hosts文件和时间同步

node1:192.168.1.67

node2:192.168.1.68

node3:192.168.1.69

node1: 

建议在本地配置yum源

yum remove mariadb //卸载client和server端

yum install MairaDB-Galera-server 

node2: 

建议在本地配置yum源

yum remove mariadb //卸载client和server端

yum install MairaDB-Galera-server 

node3: 

建议在本地配置yum源

yum remove mariadb //卸载client和server端

yum install MairaDB-Galera-server 

2.配置文件说明/etc/my.cnf.d/server.cnf 

...

[galera] 

# Manatory settings //强制配置

#wresp_provider = /usr/lib64/galera/libgalera_smm.so 

#wresp_cluster_address = "gcomm://192.168.1.67, 192.168.1.68, 192.168.1.69"  

#wresp_cluster_name = "mycluster" 

#wresp_node_name = 'node1'

#wresp_node_address = '192.168.1.67' //这两个可以省略

#binlog_format = row 

#default_storage_engine=InnoDB

#bind-address=0.0.0.0

# Optional setting 

#wsrep_slave_threads=1

#innodb_flush_logs_at_trx_commit=0 

...

首次启动：需要初始化集群，在其中一个node上执行如下命令

/etc/init.d/mysql start --wsreq-new-cluster 

而后正常启动其他节点即可

3.修改配置文件

node1: 

[galera] 

wresp_provider=/usr/lib64/galera/libgalera_smm.so 

wresp_cluster_address="gcomm://192.168.1.67, 192.168.1.68, 192.168.1.69"  

binlog_format=row 

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2 

bind-address=0.0.0.0

wresp_cluster_name="mycluster" 

node2: 和 node3: 配置文件相同 

node1：任意一个node都可以

/etc/rc.d/init.d/mysql start --wsrep-new-cluster

node2: 

service mysql start 

node3:

service mysql start 

3.测试 

mysql> 任何一个node创建数据

在其他node上可以看到有数据

node1：

> use mydb;

> create table tb2 (id int unsigned auto_increment not null primary key,name char(30));

> insert into tb2 (name) values ('hi'),('hello');

node2:

> select * from tb2; 

id | name 

---------

1 | h2 

4 | hello  

> insert into tb2 (name) values ('to'),('from');

> select * from tb2 

1 hi 

4 hello 

5 to 

8 from 

使用全局id生成器

r/w分离器：

要么自己写

要么使用开源的工具

或者在应用层配置实现 //一旦故障需要手动修改或者使用vrrp

亲测方法：

1.复制所有的rpm文件

2.createrepot /testrepo 

3.修改yum文件指向该yum源即可

4.使用yum进行安装即可

三、MySQL 5.6的多线程复制、GTID

注意：任何服务或者主机都应该被监控

mysql落后的原因：单线程复制，

5.5 半同步

5.6 GTID、多线程复制,

MariaDB在配置文件的mysqld选项组中添加：

slave-parallel-threads

MASTER: my.cnf添加以下参数 

binlog_format = row 

gtid_mode = ON 

enforce-gtid-consistency = ON 

slave_parallel_workers=4           --开启基于库的多线程复制默认0不开启 

binlog_cache_size = 8M 

max_binlog_size = 50M 

max_binlog_cache_size = 100M 

sync_binlog = 1 

expire_logs_days = 1 

log-slave-updates=true 

SLAVE: my.cnf添加以下参数  

binlog_format = row 

gtid_mode = ON 

enforce-gtid-consistency = ON 

binlog_cache_size = 8M 

max_binlog_size = 50M 

max_binlog_cache_size = 100M 

sync_binlog = 1 

expire_logs_days = 1 

slave_parallel_workers=4 

max_relay_log_size = 50M 

relay_log_purge = 1 

relay_log_recovery = 1  

master_verify_checksum = 1        --主事件校验 

slave_sql_verify_checksum = 1     --从事件校验 

slave_allow_batching = 1 

log-slave-updates=true 

对于以上MySQL主从复制的常见问题和有效解决方案相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。