利用本机环境搭建MySQL主从数据库_MySQL

bitsCN.com

利用本机环境搭建MySQL主从数据库

首先来介绍一下我的配置环境

本机是XP系统 搭载MySQL5.5  IP地址为192.168.1.101（这个地址是自适用的 会随着你的工作地点的改变而改变 但不管怎么变 只要让你的虚拟机——保证能PING通就OK）

由于我没有多余的电脑 所以我决定在虚拟机里再搭建一个MySQL

虚拟机的os是CentOS5.5  MySQL为5.1.18

接下来保证主机和虚拟机相互能通信 我们需要知道虚拟机的IP 在虚拟机的linux里运行ifconfig命令查看eth0的ip 找到第二行 inet addr:192.168.1.115  Bcast:255.255.255.255  Mask:255.255.255.0

到主机XP中 ping 192.168.1.115  再到虚拟机中ping 192.168.1.101 ok 通信成功

现在来修改主从MySQL的配置文件

我将主机XP作为主数据库 虚拟机centos作为从数据库

主数据库配置文件  

在MySQL安装目录下的my.ini 不知道安装目录的就在控制台里运行show variables like 'basedir';即可

找到#SERVER SECTION [mysqld] 这一项 前面的#SERVER SECTION 表明下面的配置都是针对MySQL服务器端的

从数据库配置文件

是/etc/my.cnf    /etc这个文件夹存放的是linux的各种配置文件  apache php的配置文件也存于此

同样找到[mysqld]

现在我们已经同时打开了主从数据库的配置文件 并找到了合适的写配置项的位置

我们在my.ini里写上server-id=1，在my.cnf里写上server-id=2 对这些配置项的含义不理解的可以另行查阅资料。在这里 server-id 表示给服务器分配一个独一无二的编号 主数据库设为1 从数据库设为2

接下来继续在my.ini里添加如下选项

log-bin=filename.n                   //开启二进制日志功能 filename.n是日志文件名 要保证可写

binlog-do-db=dbname           //只把给定数据库的变化情况写进日志 即需要同步的数据库

binlog-ignore-db=dbname    //不把给定数据库的变化情况写进日志 即不需要同步的数据库

在继续往my.cnf里写配置项前 我们需要在主数据库上创建一个同步用户 命令如下

grant replication slave,reload,super on *.* to  'yongbaolinux'@'%'  identified by '123456';

这是一个创建数据库用户及相应权限的命令 具体用法可以查阅手册和百度

接下来继续在my.cnf里添加如下选项

master_host=192.168.1.101

master_user=yongbaolinux

master_password=123456

然后将主从数据库分别重启

以root身份进入虚拟机的从数据库 运行mysql>start slave;mysql>show slave status/G

运气好点的话你能看到如下的关键信息：

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.101

Master_User: yongbaolinux

Master_Port: 3306

.............

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

运气不好的话 就只能看到这样

Slave_IO_State:

Master_Host: 192.168.1.101

Master_User: yongbaolinux

Master_Port: 3306

..............

Slave_IO_Running: NO

Slave_SQL_Running: NO

第一栏是空的 也就是说没有连接上master,slave_io也没有运行 我开始以为是配置文件的问题 因为机器重启 路由器重新分配了个ip 192.168.1.102给我  于是我在my.cnf里修改master_host 为102

但是重启数据库之后发现输出信息没有任何变化 第一行依然为空 第二行的master_host依旧是101

难道my.cnf这个配置文件不起作用?后来我把它删了 MySQL依然正常启动了 我不得不说——我凌乱了

我的世界观人生观爱情观在这一瞬间彻底崩溃了 经过多方验证 MySQL的确可以脱离my.cnf的依赖 因为MySQL可以依靠默认启动参数而存在 

这下肿么办 于是乎 只能这样了 在console里修改master信息（后来得知 其实用刷新命令flush也行）

mysql>stop slave;

mysql>change master to

          >master_host='192.168.1.102',

          >master_user='yongbaolinux',

          >master_password='123456'; 

mysql>start slave;

mysql>show slave status/G

ok 现在一切正常了 输出信息被修改了

关于Slave_IO_Running和Slave_SQL_Running，下面还有很多废话要说

每一对master/slave系统中  都会有三个相关线程来互动完成同步工作  其中主上有一个 从上有两个 就是这个slave_io和那个slave_sql。如果一台master与多个slave相连，那么这台master上肯定有与从机数量相同的主线程  而每台slave上都只有一个slave_io和一个slave_sql.我说明白了吧 再不明白 我也么办法了

上述的输出信息显示slave_io_running为NO 就表明这个线程未启动

这三个线程是这样互动的：首先io被创建后 会连接到master上 并要求master发送二进制日志里的语句 这个二进制日志留到后面再长篇大论 master的主线程便会处理这个合理的要求  然后slave_io会读取master传递过来的语句并把它们复制到数据目录下的中继日志(relay logs)中  可见这个slave_io要做两件事 一件是发送请求（如果可以这样理解的话） 另一件是读取并保存数据   最后 slave_sql 出场了 它会读取中继日志(delay logs)中的语句并执行它们而达到更新数据的目的

现在来说说这个二进制日志，mysql有多种日志格式，二进制是其中一种，无论是win还是linux，二进制日志默认都是关闭的  要开启很简单 只要在my.cnf或者my.ini里写上log-bin=path,path就是日志存放路径 如果不写路径只写一个文件名 那么日志文件会被存进datadir，win是C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/data，linux是/var/lib/mysql。配置文件修改后重启 mysql 你到上述两个文件夹下会找到xxx.index 和xxx.000001,xxx是你自定义的文件名,xxx.index是日志索引文件,xxx.000001是第一个日志文件，以后会按照序号递增。（如果你啥也不指定，那么默认日志文件名为mysql-bin)

二进制文件无法正常查看，需要mysqlbinlog工具（linux下是命令）

win下打开DOS控制台，进入C:/Program Files/MySQL/MySQL Server 5.5/bin目录运行mysqlbinlog xxx.000001(linux下直接运行mysqlbinlog命令，[root@localhost xxxx]#mysqlbinlog xxxxx.000001;)

在同步操作前 里面没有sql语句 如果你进行过主数据库的操作 你会发现里面有对应的SQL语句

在打完收工前还有几句废话要说

如果主从数据库的数据表结构不一样  比如从机少一张表或者少某个字段之类的 那么主机进行数据的操作 即DML语句的操作 从机是没反应的 道理显而易见  都不存在那张表 怎么添加数据进去  但是由于中继日志中包含这些DML语句 所以 如果你把从机的数据库结构弄得跟主机一样后 数据便会自动同步上去——需要重启从机数据库

蛋似 对主机进行DDL 即数据对象的定义操作 比如加一张表 删一张表之类的 从机是会自动进行的 道理还是显而易见 因为DDL在从机上本来就是可以执行的

好了 现在你在主机上增删改查 从机上的数据库会自动变化 达到了主从复制的目的

PS：网上有神说 一主多从的架构并不是最好的架构 但目前我也不知道啥是最好的架构了 希望各位大神不吝指教 现在只是主从同步 后面还有用mysql-proxy进行读写分离