MySQL的复制架构与优化

MySQL的复制架构与优化


###########原理###########
1.主服务器将更新的数据的sql语句(例如,insert,update,delete等)写入到
  二进制文件中(由log-bin选项开启)。此二进制文件由一个索引文件跟踪维护。
  
2.从服务器连接(使用I/O线程连接)主服务器,将自己最后一次更新的位置通知
  主服务器。然后,主服务器将把从‘从服务器’得知的位置开始之后的所有更新发
  送给‘从服务器’(使用Binlog Dump线程来发送),而后‘从服务器’再次使用I/O
  线程读取由Binlog Dump线程发送过来的数据,并将数据拷贝到本地的‘中继二进
  制文件‘中。最后,再由SQL线程读取’中继二进制文件‘并执行其中的更新。
  
  注:mysql的复制由三个线程来完成,一是,主服务器上的Binlog Dump线程;二
  是,从服务器上的I/O线程(用来连接和读取主服务更新,并拷贝到中继二进制文
  件)和SQL线程(用来读取中继二进制日志和执行更新)。
  
#######################################
#        主从架构      #
#######################################

#############配置#############
注:此处使用的是 mysql-5.5.28的二进制包。安装过程略。直接进行主从复制配置
##主服务器
 1. 更改/etc/my.cnf:
   server-id = 1     #设置服务器唯一标识
   log-bin=mysql-bin #开启二进制日志功能
 2. 添加复制用户:
  GRANT REPLICATION CLIENT,REPLICATION SLAVE TO ‘repl‘@‘192.168.1.103‘ 
   IDENTIFIED BY ‘123‘;
##从服务器
 1. 更改/etc/my.cnf:
  server-id = 2     #同主服务器
  relay-log=relay-bin     #开启中继日志
  relay-log-index=relay-bin.index #开启跟踪中继日志的索引,若未设置此选
          项系统也会自动生成索引文件。
 2. 启动mysql并设置为从服务器
  1. mysql -uroot -p
  2. CHANGE MASTER TO MASTER_HOST=‘192.168.1.102‘,
       MASTER_USER=‘repl‘,
       MASTER_PASSWORD=‘123‘,
       MASTER_PORT=‘3306‘;
  3. START SLAVE;
  4. SHOW SLAVE STATUS \G; 若Slave_IO_Running:和Slave_SQL_Running: 均显示
         Yes则说明从服务器配置成功。
   注: SHOW SLAVE STATUS \G;显示信息中的Seconds_Behind_Master: 表示从服务
   器和主服务器数据相差的时间间隔。
  5. 测试:在主服务上创建表或数据库,查看是否在从服务器上有相同的表和数据库。
   若有,则主从复制搭建成功。
#############安全############
##阻止写从服务器
 1.修改/etc/my.cnf
  [mysqld]
  read-only = 1 # 此选项只对普通用户起作用,对有SUPER权限的用户无效。
 2. FLUSH TABLES WITH READ LOCK;#为全局读锁命令,此时除了读操作,其他操作无法执行
##实现半同步
说明:主——>从,为异步模式。mysql从5.5开始支持半同步模式复制,半同步插件为semisync,存储
在/usr/local/mysql/plugin下。
 1. 在主服务器,安装semisync插件
  CHANGE INSTALL rpl_semi_sync_master SONAME ‘semisync_master.so‘;
    查看是否安装成功:
  SHOW PLUGINS; #若有rpl_semi_sync_master 则安装成功。
    启用半同步功能和设置超时时间:
  SET GLOBAL rpl_semi_sync_master_enabled=1;
  SET GLOBAL rpl_semi_sync_master_timeout=1000; #单位是ms,如果半同步在此设置的
                时间内无法同步,则自动降回异步模式。
 注:若使设置永久有效,把以上两项写入my.cnf的[mysqld]下即可。
 
 2. 在从服务器,安装semisync插件
  CHANGE INSTALL rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;
    查看是否安装成功:
  SHOW PLUGINS; #若有rpl_semi_sync_slave 则安装成功。
    启用半同步功能和设置超时时间:
  SET GLOBAL rpl_semi_sync_slave_enabled=1;
    重启slave:
  stop slave;
  start slave;
  
 3. 检测半同步功能是否已经生效
  SHOW STATUS LIKE ‘rpl_%‘;
    若Rpl_semi_sync_master_clients 的值不为0,则说明半同步功能已经生效。

##如何让从服务器的mysql服务在启动的时候,不自动启动从服务线程?
说明:从服务器之所以在启动的时候会自动启动线程,是因为master.info和relay-log.info文件的存在。
   master.info记录的是CHANGE MASTER TO命令传递的参数;relay-log.info记录的是当前从服务器所使用的
   中继日志的位置和从主服务器复制的二进制文件和所处的位置。
   
 1. 在从服务器上,禁止自动启动线程
  更改my.cnf,加入以下选项:
  [mysqld]
  skip-slave-start=1
 
##数据库复制过滤
 主服务器:
 1.[mysqld]
   binlog-do-db=test   #只复制test数据库,相当于白名单。
   binlog-ignore-db=mysql #除了mysql数据库外不复制外,其他的都要复制,相当于黑名单。
   注:一般这两项不同时使用,若同时存在,则白名单生效。不过,在主服务器上做过滤有个缺陷,就是任何
   涉及不到的数据库,都不会记录在二进制日志中。因此,大多情况下不在主服务器上做过滤。

 从服务器:
 1.[mysqld]
    replicate-do-db=test1
    replicate-ignore-db=test1
    
    replicate-do-table=test2.t1
    replicate-ignore-table=test2.t2
    
    replicate-wild-do-table=test3.ta%
    replicate-wild-ignore-table=test3.tb%

##防止事务提交和写入日志,期间的服务器崩溃问题
 主服务器:
 1. [mysqld]
  sync_binlog=1 #每次事件后立即同步到磁盘上的二进制日志文件中
  innodb_flush_logs_at_trx_commit=1 #

#######################################
#        主主架构      #
####################################### 
说明:主主架构,即服务器互为主从。配置基本上和主从差不多。此处关键的是如果
   数据库的表中使用了auto_incremnet 关键字,则需要设置auto-increment-increment
   和auto-increment-offset两项以防止键值冲突。
##主服务器
 1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO ‘t1‘@‘192.168.1.103‘ 
    IDENTIFIED BY ‘123‘;
 2. [mysqld]
  server-id=10
  log-bin=mysql-bin
  auto-increment-increment=2
  auto-increment-offset=1
 3. mysql -uroot -p
 4. CHANGE MASTER TO MASTER_HOST=‘192.168.1.102‘,
      MASTER_USER=‘t2‘,
      MASTER_PASSWORD=‘123‘,
      MASTER_PORT=‘3306‘;
  
##从服务器
 1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO ‘t2‘@‘192.168.1.102‘ 
    IDENTIFIED BY ‘123‘;
 2. [mysqld]
  server-id=10
  log-bin=mysql-bin
  auto-increment-increment=2
  auto-increment-offset=1
 3. mysql -uroot -p
 4. CHANGE MASTER TO MASTER_HOST=‘192.168.1.103‘,
      MASTER_USER=‘t1‘,
      MASTER_PASSWORD=‘123‘,
      MASTER_PORT=‘3306‘;   
  
  
#################MySQL复制架构解决方案###############
 1.主——>从(解决应用程序与耦合度较高的问题)
      1.分三层:
    1.读写分离器,产品有:MySQL Proxy和Amoeba
    2.主服务器
    3.从服务器
    
    2.分四层:
     1.读写分离器
     2.主服务器
     3.伪从服务器(所用引擎BLACKHOLE)
     4.从服务器
    
 2.主——>主(解决更新数据时,数据不一致的情况)
  1.主动/被动模式
   即,将两个主机server-id设置为相同值。
   产品:mmm,Multi Master Manager
  
#####################故障解决################
##解决:出现错误时,不能启动从服务器
 1. SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #此语句可以跳过来自主服务的下一个语句
    START SLAVE;
或 2. 使用pt-slave-restart工具,来自percona-toolkit包。 
 
##解决:数据出现不一致
 1. 检查一致性使用:
  pt-table-checksum #此工具四种功能:1.校验主从数据
             2.监控复制延迟时间
             3.系统开销很小
             4.检查数据一致性
 2. 修复不一致性使用:
  pt-table-sync
 
 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
######################MySQL的优化#######################
##技巧
 1.使用正则表达式REGEXP,取出匹配数据
  例:SELECT name,email FROM t WHERE email REGEXP ‘@126[.,]com$‘;
   如果使用like方式查询
  例:SELECT name,email FROM t WHERE email LIKE ‘%126.com‘ or email LIKE ‘%126,com‘;
 注:使用正则比使用like的一个缺点是系统资源的开销会更大一下。
 
 2.使用RAND()随机取出数据
  例:SELECT * FROM t ORDER BY RAND();
   SELECT * FROM t ORDER BY RAND() LIMIT 3;
 
 3.使用GROUP BY的WITH ROLLUP,进一步分组聚合数据。
  例:SELECT cname,pname,COUNT(cname) FROM demo GROUP BY cname,pname WITH ROLLUP;
 注:WITH ROLLUP 不能与ORDER BY 同时使用
 
##优化
 一.优化SQL语句常用命令
  1.通过SHOW STATUS命令查询各种SQL的执行频率。
   SHOW [SESSION|GLOBAL] STATUS;
   其中:SESSION(默认)表示当前连接。
      GLOBAL表示自数据库启动至今。
    @@主要查询以com开头的参数:
   SHOW STATUS LIEK ‘com_%‘; #Com_XXX表示每个XXX语句执行的次数
    @@需要查看的主要的以com开头的参数
   com_select:执行select操作的次数,一次查询只累计加1
   com_update:执行update操作的次数
   com_insert:执行insert操作的次数,对批量插入只算一次
   com_delete:执行delete操作的次数
    注:以上参数是对所有引擎的。
    @@以下是只针对InnoDB存储引擎的。
   InnoDB_rows_read:执行select操作的次数
   InnoDB_rows_updated:执行update操作的次数
   InnoDB_rows_inserted:执行insert操作的次数
   InnoDB_rows_deleted:执行delete操作的次数
     注:以上针对InnoDB的操作次数是影响的数据的“行”数,而不是相应语句的次数。
    @@其他重要参数
   connections:连接mysql的次数,包括成功和不成功的。
   uptime:服务器已经工作的秒数。
   slow_queries:慢查询的次数。#可通过SHOW VARIABLES LIKE ‘%slow_queries%‘;查看是否开启
  2.定位执行效率较低的SQL语句
   1.explain(或describe) select * from table where id=1000;
 2.优化SQL语句
  1.查询慢查询日志
  2.解析查询语句
  3.判断是否要加索引和索引是否可使用上 
 
 3.索引优化
  1.添加索引,主要是在WHERE,HAVING,GROUP BY,OREDER BY后所使用的字段上。
  2.使用LIKE时,不要把%通配符放在前面,否则索引就无法使用的到。
  3.在使用OR和AND时,前后的两个条件都要使用索引,否则索引就用不到
  4.如果给定的条件表达式的值的数据类型和定义的不一样,则无法用到索引
  5.查看索引使用情况:SHOW STATUS LIKE ‘Handler_read%‘;
   其中所显示的参数:Handler_read_key的值,表示读取索引的次数。
         Handler_read_rnd_next的值越高则,需要添加索引的列越多。
        
 4.表优化
  1.分析和检查表
   CHECK TABLE t1; #检查表t1是否有错误
  2.优化表空间
   OPTIMIZE TABLE t1; #最好在非工作时间使用
 5.常用SQL优化
  1.导入导出优化
   @@导出使用:SELECT * FROM table INTO OUTFILE ‘/tmp/table.txt‘;
   @@导入使用:LOAD DATA INFILE ‘/tmp/table.txt‘ INTO TABLE table;
  2.关闭索引使导入速度更快
   1.@@关闭索引:ALTER TABLE tbl_name DISABLE KEYS;
     @@导入数据
     @@开启索引:ALTER TABLE tbl_name ENABLE KEYS;
    注:以上只对MyISAM表的数据导入能提高速度,对InnoDB无效
   2.@@关闭唯一索引:SET unique_checks=0
     @@导入数据
     @@恢复唯一索引:SET unique_checks=1
    注:如果能确定数据的唯一性,则可以使用关闭唯一索引来提高速度。否则不建议关闭。
  3.针对InnoDB表类型的数据导入的优化
   1.将导入的数据按主键的顺序来排列,可提高导入速度
   2.@@关闭自动提交:SET autocommit=0
     @@导入数据
     @@恢复自动提交:SET autocommit=1
 6.INSERT语句的优化
  1.插入数据时,使用INSERT INTO tbl_name VALUES(‘aa‘),(‘bb‘)......(‘zz‘);
 7.GROUP BY语句的优化
  1.禁用分组排序,使用SELECT * FROM tbl_name GROUP BY cloumn ORDER BY NULL;
 8.嵌套优化查询
  1.使用嵌套查询,内部嵌套的查询会用到索引,而外层的用不到。
    将嵌套查询改为,内连接或是外连接,则可优化查询。
 二.数据库优化
  1.使用中间表
   @@创建新表。#不够灵活
   @@创建视图。#推荐做法
  2.分区(海量数据的优化,在Mysql5.1及以后提供)
   ##MyISAM引擎:
    @@RANGE类型:
    CREATE TABLE t1(id int,name varchar(30))
    -->PARTITION BY RANGE(id)(
    -->PARTITION p0 VALUES LESS THAN (11),
    -->PARTITION p1 VALUES LESS THAN (21)
    -->);
    @@LIST类型:
    CREATE TABLE t1(id int,name varchar(30))
    -->PARTITION BY LIST(id)(
    -->PARTITION p0 VALUES IN(1,3,6,7,10),
    -->PARTITION p1 VALUES IN(2,4,5,8,11)
    -->);
    @@HASH类型:
    CREATE TABLE t1(id int,name varchar(30))
    -->PARTITION BY HASH(id)
    -->PARTITIONS 2;
   ##InnoDB引擎
    @@修改my.cnf
     [mysqld]
     innodb_file_per_table=1 #开启InnoDB的独立存储空间
    @@其他的和MyISAM相同
 三. Mysql服务器优化
  ##锁机制
   1.MyISAM读锁定
    @@命令:LOCK TABLE tbl_name READ #所有用户只能读,不能更新,删除等。
   2.MyISAM写锁定
    @@命令:LOCK TABLE tbl_name WRITE #只有当前用户可增删改查,其他用户无法进行任何操作。
   3.解锁:UNLOCK TABLES;
  ##字符集
   1.@@使用:STATUS或\s,可查看基本信息和字符集。
    其中,有服务器字符集、数据库字符集、客户端字符集、连接字符集,可设置。
     @@客户端和连接字符集设定
    [client]
    default-character-set=utf8
     @@服务器和数据库字符集设定
    [mysqld]
    character-set-server=utf8
     @@校验字符集
    [mysqld]
    collation-server=utf8_general_ci 
    注:可使用SHOW CHARACTER SET;查看字符集对应的校验字符集。
  ##开启慢查询日志
   1.@@使用:SHOW VARIABLES LIKE ‘%slow%‘;查看慢查询日志是否开启
     @@开启:[mysqld]
       slow_query_log=slow.log
     @@慢查询时间:[mysqld]
       long_query_time=5
  ##socket问题
   1.如果mysql.sock丢失,则可使用mysql -uroot -p --protocol tcp -h localhost 
    注:只是临时的启动解决方法。
 
   2. Mysql 密码丢失
    @@跳过授权表:mysqld_safe --skip-grant-tables --user=mysql &


本文出自 “一切皆有可能” 博客,请务必保留此出处http://noican.blog.51cto.com/4081966/1656574

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。