mysql原理详解及部署

xuanhao

　　一、MySQL主从复制原理
　　什么是mysql主从复制介绍
　　Mysql支持几种不同形式的复制如：单双向、链式级联、异步复制。在复制过程中，一个服务器充当主服务器（master）而一个或多个其他的服务器充当从服务器（slave）如果设置了链式级联复制，那么从slave服务器除了当从服务器，还会作为其他服务器的主服务器。
　　当配好主从复制后，数据库的内容更新应该都在master服务器上，避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新发生了冲突。生产环境中一般会忽略授权表的同步，对在从服务器上的用户仅授权select读权限。或在my.cnf配置文件中加read-only参数，来确保从数据库只读，当然二者同时操作效果更佳。
　　一般生产环境中会有一个M-->S 或者是一个M-->多个S。在同步的时候还可以双向同步MM.而级联就是A-->B-->C-->D.在从库的my.cnf配置文件中加read-only参数来确保从库只读。常规情况是不允许的，当然如果能够保证不对同一个表写数据可以了。或者我们在从库上只对用户授权select权限。
　　mysql主从结构的作用：
　　1）主从服务器架构的设置，可以大大加强数据架构的健壮性，当主服务器出现问题时，我们可以切换到从服务器继续提供服务。
　　这类似我们说的NFS存储数据通过inotify+rsync实时同步到备份的nfs上的过程。只不是这里是同步mysql数据库的内容而已，用的是mysql自带的工具。
　　2）主从服务器价格可实现对用户的请求实现读写分离，即通过在从服务器上处理用户select查询请求，降低用户查询响应时间。及读写给主服务器带来的压力。对于更新的数据（update，insert，delete）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。
　　如果网站是以非更新为主的业务，如blog，www首先展示等业务，读请求比较多，这时从服务器的负载均衡策略就很有效了，这就是读写分离数据库结构。
　　3）可以把几个不同的从服务器，根据公司的业务进行拆分，比如有为外部用户提供查询服务的从服务器，有用来备份的从服务器，还有提供公司内部后台、脚本、日志分析及开发人员服务的从服务器。这样拆分除了减轻主服务器的压力外。使得对外用户浏览，对内处理公司内部用户业务，及DBA备份业务互不影响。具体可以看下面的简单架构来说明：

　　我们现在是一主5从，只要我们向这1主写数据，那么5从就都会同时更新。提示：其中s1 2 3 可以通过lvs做成负载均衡的模式。这样第4台从库给我们的后台，内部访问。
　　后台的数据库独立出来，安装在备份数据库上，这样我们测试使用的数据就不会影响到线上的环境，也能够保证真实情况。而我们的备份数据库，要开启binlog功能，做数据库的增量备份功能。
　　-------小结----------------------------------------------------------------
　　数据库主从同步跟sersync实时同步是一样的道理。只是我们的sersync是同步文件，而我们这次是同步数据库数据而已。MySQL同步是利用自带的工具来进行同步。主从架构我们可以在从库上执行select查询请求，降低用户查询响应时间及读写给服务器带来的压力。在数据库里面，还有这样的问题：当数据库在更新的时候是没办法做其他操作的如查询。对于更新数据一般都是通过update inserter delete。
　　如果网站是以非更新的业务为主（浏览为主），读请求比较多，那么这时候从服务器的负载均衡策略就很有效了。这就是传说中的读写分离结构。从库的作用还有很多不同的用途。有为外部用户提供查询服务的从服务器，有用来备份的从服务器，还有提供公司内部后台，脚本，日志分析及开发服务的从服务器。这样拆分能够减轻主服务器的压力。
　　-------小结----------------------------------------------------------------
　　主从复制过程简述图：

　　图原理过程简述：0-1当对master执行插入更新动作会插入数据，0-2并同时生成binlog日志记录。1IO线程请求，2 Master的IO线程获取信息，3返回信息，4、5IO线程填入对应的表，6、7SQL线程获取binlog语句，并执行SQL语句插入数据，8IO线程定期获取master-info信息，然后回到1重新向Master请求binlog信息，然后一直重复。0-1当对master执行insert更新动作会insert数据，0-2并同时生成binlog日志记录。
　　mysql主从库同步的标准实践
　　定义服务器角色
　　主库（mysql master）IP 192.168.1.1  端口3306
　　从库（mysql slave）  IP 192.168.1.2  端口 3306
　　主从也可以是一台机器上的不同mysql实例进行主从同步，或者是两台不同设备之间的mysql进行主从同步。当我们掌握了单机器多实例的同步方法后可自行扩展。
　　实际操作
　　[mysqld]模块内的配置内容。在[mysqld]模块下进行添加。log-bin=/mysql/mysql-bin当然这个后面也可以不指定路径的，但为了规范和方便还是要自定义指定。
　　2）主库上创建同步账号
　　在主库上简历用户从库同步的账号rep
　　#mysql –uroot –p’mima’
　　#grantreplication slave on *.* to ‘rep’@’192.168.1.2’ indentified by ‘miam’
　　replicationslave（从复制）这个就是开启从库复制的权限。

　　授权的仅仅只是replication slave权限。并且是所有库所有表，‘rep’@'10.10.10.%'>　　>show  grants for rep@192.168.1.2来查看用户权限如何。
　　3）对数据库库锁表只读。
　　生产环境中，操作主从复制，需要申请停机，锁表会影响业务。因为我们的有可能主库里面已经有数据了，而我们的从库才刚刚开始，那么我们就需要对主库进行锁表，把主库的数据先导出来，然后导入到从库上，让主从两者之间的数据是一模一样的。然后再开始主从之间的复制，通过log-bin完成.
　　提示：锁表命令的时间，在不同的引擎的情况，会受下面参数的控制，锁表时如果超过设置时间不操作会自动解锁。默认的时长是8小时。
　　注意：锁表的时候一定不能够退出我们当前的连接窗口。当然了，我们后面有一键完成主从的方案。
　　flush talbles with read lock;（用读锁定重新刷表）
　　当我们锁表之后我们就插入不了数据库了。锁表的时候有时间限制，一旦超过这个时间没操作表就会自动解开。
　　4）查看主库的状态
　　查看主库状态，即当前日志文件名和二进制日志偏移量。这个也就是binlog日志已经有多少条记录的意思。
　　>show master status

　　并且我们要记住这个点，因为在我们slave打开同步的时候，我们一开始就要配置我们要从哪个bin log中的哪个偏移量来进行读取。所以我们要记录这个主库的偏移量。当然我们的生产环境肯定是比这个要多的。
　　5)导出数据库数据
　　我们一定要保留当前窗口不能退出，退出锁状态就没有了。我们再重新开一个窗口，来做数据备份。
　　然后在把备份的数据导入到从库上面。
　　#mysqldump  -uroot  -p‘’  -A  -B | gzip >/tmp/backup.sql
　　当然我们也可以对物理文件进行打包，速度会快一些。mysqldump效率会比较低。
　　如果主从不在同一台设备上我们还需要通过rsync来进行数据的传输。
　　首先我们再查看下bin-log数据，看看是否是一样的，如果不一样的话，那表示你锁表的时候还有数据插入到你库里面下。这一步可以在我们对主库进行备份的时候进行查看，要不然等我们备份完成了之后，才发现有数据不对，那岂不是白白浪费了时间。
　　6）解锁主库
　　>unlock tables;
　　说明:我们是先开启bin-log之后，再锁表查看状态，备份数据，查看状态，期间bin-log确实是已经有记录了。（是否我们执行完数据库备份的命令之后就把表解开呢？这时候可能会备份到已经在我们记录的状态点后的数据，并且binlog也是有记录的。到时候数据在从库恢复之后，然后再指定我们的从库从之前的binlog去执行，那binlog插入的数据就会和现有的数据有冲突了。）
　　但我们是按照偏移量来进行同步的，所以也就是说我们会从bin-log里面的某个点来进行同步。所以bin-log里面有和导出的数据重复的数据
　　也是没有关系的，因为我们通过指定偏移量来修正了。
　　7)把主库导出的mysql数据迁移到从库
　　这步常用scp，rsync等来同步。
　　8）从库上进行操作
　　8.1)设置server-id并关闭binglog参数
　　数据库的server-id一般不能够一样的。如果从库只做备份的话，那么log-bin就可以注释掉。
　　如果开着也是会影响性能。如果需要对binlog也进行备份的话那最好也开启从库的binglog日志功能。（因为这样我们就可以在我们的slave数据库上对我们的binlog日志进行备份。这样首先我们的数据库数据有定时的做备份。然后这些binlog就是我们下次做数据库备份的增量备份。如果没有对binlog进行备份的话，那如果是主库的数据丢失我们要进行恢复的话，我就可以使用数据备份+binlog的形式进行恢复到完整的数据。）
　　执行 vi my.cnf编辑配置文件
　　[mysqld]
　　Server-id = 2
　　Log-bin = /usr/local/mysql/mysql-bin
　　修改完成之后要重启数据库。
　　8.2)恢复数据库数据
　　ll -lrt
　　gzip -d  backup.sql.gz
　　mysql还原，因为我们导出的时候加上了-B ，所以恢复的时候直接接上文件就可以了不用做任何操作。
　　从库的数据最好是要保持是空的，这样才不会让从库有一些多余的数据。
　　8.3）配置同步参数
　　要确定我们登录的是从库。
　　我们先查看下我们从库的状态shwo  master  status;
　　登录数据库，配置如下参数
　　CHANGE MASTER TO
　　MASTER_HOST=’192.168.1.1’,
　　MASTER_PORT=3306，
　　MASTER_USER=’rep’,
　　MASTER_PASSWORD=’mima’,
　　MASTER_LOG_FILE=’mysql-bin.0000008’,
　　MASTER_LOG_POS=6399,
　　然后在我们的从库登录，并执行以上的命令参数，这个其实就是在设置master-info数据。
　　我们也可以不登录通过命令行来进行命令的执行。
　　此时，我们的同步参数已经算是设置完成了。接下来就是启动slave start。这样才算是建立两者之间的同步。
　　不登陆数据库，在命令行快速执行CHANGE MASTER的语句（适合在脚本中批量建slave库）
　　Cat | mysql –uroot–p’password’
　　CHANGEMASTER  TO
　　MASTER_HOST=’192.168.1.1’,
　　MASTER_PORT=’3306’,
　　MASTER_USER=’rep’,
　　MASTER_PASSWORD=’password’,
　　MASTER_LOG_FILE=’mysql-bin.000008’,
　　MASTER_LOG_POS=’6639’;
　　EOF
　　8.4）开启从库&查看从库状态
　　start slave;
　　slave start;
　　show slavestatus \G
　　我们知道我们的从库有两个线程，我们查看下两个线程的是否是yes状态。
　　下面还有一个seconds_behind_Master 表示我们的从库落后主库多少，会以秒的形式显示在此处。
　　8.5)主库上做操作，从库验证备份
　　在主库上删除某个数据库，创建某个数据库可以验证主从是否能够备份。
　　可以看到两个线程也是yes状态
　　判断复制是否搭建成功就看下IO和SQL两个线程是否显示为YES状态，
　　Slave_IO_Running:YES  #负责从库去主库读取binlog日志，并写入从库的中继日志中。
　　Slave_sql_running:yes  #负责读取并执行中继日志中的binlog，转换sql语句后应用到数据库汇总。
　　特别提示：有关show slave status的结果，大家查看mysql手册吧。
　　----------------------------后续自己小结------------------------------------------------
　　mysql的主从同步应该到这步算是大致完成了。同步原理，就记住原理图。然后实际的实践过程，自己多操作一下。这个东西我自己目前能比较清楚搞懂的就是上面这些部分。还有从库的binlog同步。但是自己还有一些疑惑就是，如果没有锁表进行备份，那备份的同时会有新的数据插入。那么备份时会值备份我运行命令时间点及之前的数据，还是会把新插入的数据也同时备份进来？那binlog应该是按照哪个时间点来？
　　另：如果说只有一个数据库的话，使用mysqldump进行备份的时候，是否也一定要锁表呢？假设不锁表的时候进行备份。那如果要用binlog来做增量备份的话，确定我们完成全beife备份后binlog的偏移量呢？
　　如果说有主从数据库，在从数据库上做锁表，全备份。那锁表期间，binlog会更新吧？那表解锁之后，binlog更新的内容还会重新插入到数据库文件里面吗？
　　对于数据库主从同步和备份还有好多问题呀！！！
　　-----------------------------------------------------------------------------------------