mysql的部署及原理_MySQL原理详解及部署

生产场景MySQL主从复制常用基本架构

图原理过程简述：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.%' identify by 'mima'这样就是允许用户能够从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更新的内容还会重新插入到数据库文件里面吗？

对于数据库主从同步和备份还有好多问题呀！！！

-----------------------------------------------------------------------------------------