建设一个网站的过程,html个人网页制作模板,提供网站建设出售,中山百度seoMySQL的复制架构与优化###########原理###########1.主服务器将更新的数据的sql语句(例如#xff0c;insert#xff0c;update#xff0c;delete等)写入到二进制文件中(由log-bin选项开启)。此二进制文件由一个索引文件跟踪维护。2.从服务器连接(使用I/O线程连接)主服务器insertupdatedelete等)写入到二进制文件中(由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.cnfserver-id 1 #设置服务器唯一标识log-binmysql-bin #开启二进制日志功能2. 添加复制用户GRANT REPLICATION CLIENT,REPLICATION SLAVE TO repl192.168.1.103IDENTIFIED BY 123;##从服务器1. 更改/etc/my.cnf:server-id 2 #同主服务器relay-logrelay-bin #开启中继日志relay-log-indexrelay-bin.index #开启跟踪中继日志的索引若未设置此选项系统也会自动生成索引文件。2. 启动mysql并设置为从服务器1. mysql -uroot -p2. CHANGE MASTER TO MASTER_HOST192.168.1.102,MASTER_USERrepl,MASTER_PASSWORD123,MASTER_PORT3306;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_enabled1;SET GLOBAL rpl_semi_sync_master_timeout1000; #单位是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_enabled1;重启slavestop 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-start1##数据库复制过滤主服务器1.[mysqld]binlog-do-dbtest #只复制test数据库相当于白名单。binlog-ignore-dbmysql #除了mysql数据库外不复制外其他的都要复制相当于黑名单。注一般这两项不同时使用若同时存在则白名单生效。不过在主服务器上做过滤有个缺陷就是任何涉及不到的数据库都不会记录在二进制日志中。因此大多情况下不在主服务器上做过滤。从服务器1.[mysqld]replicate-do-dbtest1replicate-ignore-dbtest1replicate-do-tabletest2.t1replicate-ignore-tabletest2.t2replicate-wild-do-tabletest3.ta%replicate-wild-ignore-tabletest3.tb%##防止事务提交和写入日志期间的服务器崩溃问题主服务器1. [mysqld]sync_binlog1 #每次事件后立即同步到磁盘上的二进制日志文件中innodb_flush_logs_at_trx_commit1 ######################################### 主主架构 ########################################说明主主架构即服务器互为主从。配置基本上和主从差不多。此处关键的是如果数据库的表中使用了auto_incremnet 关键字则需要设置auto-increment-increment和auto-increment-offset两项以防止键值冲突。##主服务器1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO t1192.168.1.103IDENTIFIED BY 123;2. [mysqld]server-id10log-binmysql-binauto-increment-increment2auto-increment-offset13. mysql -uroot -p4. CHANGE MASTER TO MASTER_HOST192.168.1.102,MASTER_USERt2,MASTER_PASSWORD123,MASTER_PORT3306;##从服务器1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO t2192.168.1.102IDENTIFIED BY 123;2. [mysqld]server-id10log-binmysql-binauto-increment-increment2auto-increment-offset13. mysql -uroot -p4. CHANGE MASTER TO MASTER_HOST192.168.1.103,MASTER_USERt1,MASTER_PASSWORD123,MASTER_PORT3306;#################MySQL复制架构解决方案###############1.主——从(解决应用程序与耦合度较高的问题)1.分三层1.读写分离器,产品有MySQL Proxy和Amoeba2.主服务器3.从服务器2.分四层1.读写分离器2.主服务器3.伪从服务器(所用引擎BLACKHOLE)4.从服务器2.主——主(解决更新数据时数据不一致的情况)1.主动/被动模式即将两个主机server-id设置为相同值。产品mmmMulti 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操作的次数一次查询只累计加1com_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 id1000;2.优化SQL语句1.查询慢查询日志2.解析查询语句3.判断是否要加索引和索引是否可使用上3.索引优化1.添加索引主要是在WHEREHAVINGGROUP BYOREDER 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_checks0导入数据恢复唯一索引SET unique_checks1注如果能确定数据的唯一性则可以使用关闭唯一索引来提高速度。否则不建议关闭。3.针对InnoDB表类型的数据导入的优化1.将导入的数据按主键的顺序来排列可提高导入速度2.关闭自动提交SET autocommit0导入数据恢复自动提交SET autocommit16.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(136710),--PARTITION p1 VALUES IN(245811)--);HASH类型CREATE TABLE t1(id int,name varchar(30))--PARTITION BY HASH(id)--PARTITIONS 2;##InnoDB引擎修改my.cnf[mysqld]innodb_file_per_table1 #开启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-setutf8服务器和数据库字符集设定[mysqld]character-set-serverutf8校验字符集[mysqld]collation-serverutf8_general_ci注可使用SHOW CHARACTER SET;查看字符集对应的校验字符集。##开启慢查询日志1.使用SHOW VARIABLES LIKE %slow%;查看慢查询日志是否开启开启[mysqld]slow_query_logslow.log慢查询时间[mysqld]long_query_time5##socket问题1.如果mysql.sock丢失则可使用mysql -uroot -p --protocol tcp -h localhost注只是临时的启动解决方法。2. Mysql 密码丢失跳过授权表mysqld_safe --skip-grant-tables --usermysql
