search

Home >Database >Mysql Tutorial >MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解

MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解

WBOY
WBOYOriginal
2016-06-07 14:50:151180browse

安装环境 操作系统 ：CentOS 6.5 数据库版本：MySQL 5.6 . 27 主机 A ： 192.168 . 1.1 （Master）主机B： 192.168 . 1.2 （Slave） 这里强调的数据库的版本，是因为MySQL在5.6之前和之后的安装方式是不一样的。 本人在进行配置的时候，也遇到了这个坑，这里

安装环境

<code class=" hljs autohotkey">操作系统 ：CentOS <span class="hljs-number">6.5</span> 
数据库版本：MySQL <span class="hljs-number">5.6</span>.<span class="hljs-number">27</span>
主机<span class="hljs-literal">A</span>：<span class="hljs-number">192.168</span>.<span class="hljs-number">1.1</span> （Master）
主机B：<span class="hljs-number">192.168</span>.<span class="hljs-number">1.2</span> （Slave）</code>

这里强调的数据库的版本，是因为MySQL在5.6之前和之后的安装方式是不一样的。
本人在进行配置的时候，也遇到了这个坑，这里提前说明，希望大家不要采坑。

注：这里有一篇CentOS安装MySQL的文章，在这里引出，过程亲测，希望对大家有帮助：http://blog.csdn.net/xlgen157387/article/details/49964557

基本环境配置

首先，要保证防火墙对3306端口的开启，（开启方式，请参考：[http://blog.csdn.net/xlgen157387/article/details/49964557]），如果只是为了学习数据库的主从配置，可以使用service iptables stop 命令直接关闭防火墙。

然后可以在两台机子之间进行 ping操作，确保两台机器之间能够相同。

Master的配置

在Linux环境下MySQL的配置文件的位置是在 /etc/my.cnf ，在该文件下指定Master的配置如下：

<code class=" hljs lasso"><span class="hljs-keyword">log</span><span class="hljs-attribute">-bin</span><span class="hljs-subst">=</span>mysql<span class="hljs-attribute">-bin</span>
server<span class="hljs-attribute">-id</span><span class="hljs-subst">=</span><span class="hljs-number">2</span>
binlog<span class="hljs-attribute">-ignore</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>information_schema
binlog<span class="hljs-attribute">-ignore</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>cluster
binlog<span class="hljs-attribute">-ignore</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>mysql
binlog<span class="hljs-attribute">-do</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>ufind_db </code>

这里的server-id用于标识唯一的数据库，这里设置为2，在设置从库的时候就需要设置为其他值。

binlog-ignore-db：表示同步的时候ignore的数据库
binlog-do-db：指定需要同步的数据库

完整配置截图如下：

这里写图片描述

1、然后重启mysql：service mysqld restart

2、进入mysql：[root@VM_221_4_centos ~]# mysql -u root -p 回车，输入mysql密码进入。

3、 赋予从库权限帐号，允许用户在主库上读取日志，赋予192.168.1.2也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。

在Master数据库命令行中输入：

<code class=" hljs oxygene"> >GRANT FILE <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'root'</span>@<span class="hljs-string">'192.168.1.2'</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">'mysql password'</span>;

 >GRANT REPLICATION SLAVE <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'root'</span>@<span class="hljs-string">'192.168.1.2'</span> IDENTIFIED <span class="hljs-keyword">BY</span> <span class="hljs-string">'mysql password'</span>;
>FLUSH PRIVILEGES</code>

这里使用的仍是 root 用户作为同步的时候使用到的用户，可以自己设定。

4、重启mysql，登录mysql，显示主库信息

<code class=" hljs ">mysql> show master status;</code>
<code class=" hljs asciidoc"><span class="hljs-header">mysql> show master status;
+------------------+----------+--------------+----------------------------------+-------------------+</span>
<span class="hljs-header">| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+</span>
<span class="hljs-header">| mysql-bin.000004 |    28125 | ufind_db     | information_schema,cluster,mysql |                   |
+------------------+----------+--------------+----------------------------------+-------------------+</span>
1 row in set (0.00 sec)

mysql> </code>

这里写图片描述

这里的 File 、Position 是在配置Salve的时候要使用到的，Binlog_Do_DB表示要同步的数据库，Binlog_Ignore_DB 表示Ignore的数据库，这些都是在配置的时候进行指定的。

另外：如果执行这个步骤始终为Empty set(0.00 sec)，那说明前面的my.cnf没配置对。

Slave的配置

1、从库的配置，首先也是修改配置文件：/etc/my.cnf 如下：

<code class=" hljs lasso"><span class="hljs-keyword">log</span><span class="hljs-attribute">-bin</span><span class="hljs-subst">=</span>mysql<span class="hljs-attribute">-bin</span>
server<span class="hljs-attribute">-id</span><span class="hljs-subst">=</span><span class="hljs-number">3</span>
binlog<span class="hljs-attribute">-ignore</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>information_schema
binlog<span class="hljs-attribute">-ignore</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>cluster
binlog<span class="hljs-attribute">-ignore</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>mysql
replicate<span class="hljs-attribute">-do</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>ufind_db
replicate<span class="hljs-attribute">-ignore</span><span class="hljs-attribute">-db</span><span class="hljs-subst">=</span>mysql
<span class="hljs-keyword">log</span><span class="hljs-attribute">-slave</span><span class="hljs-attribute">-updates</span>
slave<span class="hljs-attribute">-skip</span><span class="hljs-attribute">-errors</span><span class="hljs-subst">=</span><span class="hljs-literal">all</span>
slave<span class="hljs-attribute">-net</span><span class="hljs-attribute">-timeout</span><span class="hljs-subst">=</span><span class="hljs-number">60</span></code>

这里写图片描述

2、这里可以看到，在MySQL5.6之后的版本中没有指定：

<code class=" hljs lasso">master<span class="hljs-attribute">-host</span><span class="hljs-subst">=</span><span class="hljs-number">192.168</span><span class="hljs-number">.1</span><span class="hljs-number">.1</span> <span class="hljs-variable">#Master</span>的主机IP
master<span class="hljs-attribute">-user</span><span class="hljs-subst">=</span>root
master<span class="hljs-attribute">-password</span><span class="hljs-subst">=</span>mysql password <span class="hljs-variable">#Master</span>的MySQL密码</code>

3、这也是在网上很多搜索的配置过程，他们也都指定了数据库的版本，但是并没有说出来新版本的配置这种方式是不适用的。

4、如果，你在MySQL5.6和之后的版本中配置从库的时候，设置到了上边的内容，即指定了master-host、master-user等信息的话，重启MySQL的时候就回报错，错误信息如下：

<code class=" hljs coffeescript">[root<span class="hljs-property">@VM_128_194_centos</span> bin]<span class="hljs-comment"># service mysqld restart</span>
Shutting down MySQL... SUCCESS! 
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysqldb/VM_128_194_centos.pid).
[root<span class="hljs-property">@VM_128_194_centos</span> bin]<span class="hljs-comment"># </span></code>

此时，查看数据库的报错信息（数据库的目录, /data/mysqldb/VM_128_194_centos.err ），可以看到：

<code class=" hljs livecodeserver"><span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">06</span> <span class="hljs-number">13</span>:<span class="hljs-number">12</span>:<span class="hljs-number">04</span> <span class="hljs-number">13345</span> [Note] InnoDB: Waiting <span class="hljs-keyword">for</span> purge <span class="hljs-built_in">to</span> start
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">06</span> <span class="hljs-number">13</span>:<span class="hljs-number">12</span>:<span class="hljs-number">04</span> <span class="hljs-number">13345</span> [Note] InnoDB: <span class="hljs-number">5.6</span><span class="hljs-number">.27</span> started; <span class="hljs-built_in">log</span> sequence <span class="hljs-built_in">number</span> <span class="hljs-number">2850211</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">06</span> <span class="hljs-number">13</span>:<span class="hljs-number">12</span>:<span class="hljs-number">04</span> <span class="hljs-number">13345</span> [ERROR] /data/home/server/mysql-<span class="hljs-number">5.6</span><span class="hljs-number">.27</span>/bin/mysqld: unknown <span class="hljs-built_in">variable</span> <span class="hljs-string">'master-host=192.168.1.1'</span>
<span class="hljs-number">2016</span>-<span class="hljs-number">05</span>-<span class="hljs-number">06</span> <span class="hljs-number">13</span>:<span class="hljs-number">12</span>:<span class="hljs-number">04</span> <span class="hljs-number">13345</span> [ERROR] Aborting
</code>

可以看出master-host 被检测数是一个未知的变量，因此会出现错误。

5、在5.6以及后续版本的配置如下：

修改完/etc/my.cnf 文件之后，重启一下MySQL（service mysqld restart）

进入Slave mysql控制台，执行：

这里写图片描述

<code class=" hljs r">
mysql> <span class="hljs-keyword">stop</span> slave;  <span class="hljs-comment">#关闭Slave</span>
mysql> change master to master_host=<span class="hljs-string">'192.168.1.1'</span>,master_user=<span class="hljs-string">'root'</span>,master_password=<span class="hljs-string">'123456'</span>,master_log_file=<span class="hljs-string">'mysql-bin.000004'</span>, master_log_pos=<span class="hljs-number">28125</span>;

mysql> start slave;  <span class="hljs-comment">#开启Slave</span></code>

在这里指定Master的信息，master_log_file是在配置Master的时候的File选项， master_log_pos是在配置Master的Position 选项，这里要进行对应。

然后可以通过mysql> show slave status; 查看配置的信息：

<code class=" hljs lasso">mysql<span class="hljs-subst">></span> show slave status <span class="hljs-subst">\</span>G;
<span class="hljs-subst">***************************</span> <span class="hljs-number">1.</span> row <span class="hljs-subst">***************************</span>
               Slave_IO_State: Waiting for master <span class="hljs-keyword">to</span> send event
                  Master_Host: <span class="hljs-number">192.167</span><span class="hljs-number">.1</span><span class="hljs-number">.1</span>
                  Master_User: root
                  Master_Port: <span class="hljs-number">3306</span>
                Connect_Retry: <span class="hljs-number">60</span>
              Master_Log_File: mysql<span class="hljs-attribute">-bin</span><span class="hljs-number">.000004</span>
          Read_Master_Log_Pos: <span class="hljs-number">28125</span>
               Relay_Log_File: VM_128_194_centos<span class="hljs-attribute">-relay</span><span class="hljs-attribute">-bin</span><span class="hljs-number">.000004</span>
                Relay_Log_Pos: <span class="hljs-number">26111</span>
        Relay_Master_Log_File: mysql<span class="hljs-attribute">-bin</span><span class="hljs-number">.000004</span>
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: ufind_db
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: <span class="hljs-number">0</span>
                   Last_Error: 
                 Skip_Counter: <span class="hljs-number">0</span>
          Exec_Master_Log_Pos: <span class="hljs-number">28125</span>
              Relay_Log_Space: <span class="hljs-number">26296</span>
              Until_Condition: <span class="hljs-literal">None</span>
               Until_Log_File: 
                Until_Log_Pos: <span class="hljs-number">0</span>
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: <span class="hljs-number">0</span>
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: <span class="hljs-number">0</span>
                Last_IO_Error: 
               Last_SQL_Errno: <span class="hljs-number">0</span>
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: <span class="hljs-number">2</span>
                  Master_UUID: <span class="hljs-number">8</span>ac3066a<span class="hljs-subst">-</span><span class="hljs-number">9680</span><span class="hljs-subst">-</span><span class="hljs-number">11e5</span><span class="hljs-attribute">-a2ec</span><span class="hljs-subst">-</span><span class="hljs-number">5254007529</span>fd
             Master_Info_File: /<span class="hljs-built_in">data</span>/mysqldb/master<span class="hljs-built_in">.</span>info
                    SQL_Delay: <span class="hljs-number">0</span>
          SQL_Remaining_Delay: <span class="hljs-built_in">NULL</span>
      Slave_SQL_Running_State: Slave has read <span class="hljs-literal">all</span> relay <span class="hljs-keyword">log</span>; waiting for the slave I/O <span class="hljs-keyword">thread</span> <span class="hljs-keyword">to</span> update it
           Master_Retry_Count: <span class="hljs-number">86400</span>
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: <span class="hljs-number">0</span>
<span class="hljs-number">1</span> row <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)

ERROR: 
No query specified

mysql<span class="hljs-subst">></span> 
</code>

可以看到，已经配置成功。

添加需要同步的从库Slave

由于种种原因，测试的时候使用test库，这里我按照上述的方式，修改Master的my.cnf的配置文件，新增同步的数据库test，重启MySQL,执行Master的：show master status如下：

这里写图片描述

相应的,要修改Slave从库的信息在my.cnf 增加 replicate-do-db=test，重启Mysql，根据上述的show master status，在Slave从库中执行下边的内容：

<code class=" hljs ocaml">
>stop slave
>change master <span class="hljs-keyword">to</span> master_host=<span class="hljs-string">'192.168.1.1'</span>,master_user=<span class="hljs-string">'root'</span>,master_password=<span class="hljs-string">'123456'</span>,master_log_file=<span class="hljs-string">'mysql-bin.000005'</span>, master_log_pos=<span class="hljs-number">120</span>;
>start slave</code>

然后使用：show slave status；

<code class=" hljs lasso">mysql<span class="hljs-subst">></span> show slave status<span class="hljs-subst">\</span>G;
<span class="hljs-subst">***************************</span> <span class="hljs-number">1.</span> row <span class="hljs-subst">***************************</span>
               Slave_IO_State: Waiting for master <span class="hljs-keyword">to</span> send event
                  Master_Host: <span class="hljs-number">192.168</span><span class="hljs-number">.1</span><span class="hljs-number">.1</span>
                  Master_User: root
                  Master_Port: <span class="hljs-number">3306</span>
                Connect_Retry: <span class="hljs-number">60</span>
              Master_Log_File: mysql<span class="hljs-attribute">-bin</span><span class="hljs-number">.000005</span>
          Read_Master_Log_Pos: <span class="hljs-number">1422</span>
               Relay_Log_File: VM_128_194_centos<span class="hljs-attribute">-relay</span><span class="hljs-attribute">-bin</span><span class="hljs-number">.000004</span>
                Relay_Log_Pos: <span class="hljs-number">283</span>
        Relay_Master_Log_File: mysql<span class="hljs-attribute">-bin</span><span class="hljs-number">.000005</span>
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: ufind_db,test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: <span class="hljs-number">0</span>
                   Last_Error: 
                 Skip_Counter: <span class="hljs-number">0</span>
          Exec_Master_Log_Pos: <span class="hljs-number">1422</span>
              Relay_Log_Space: <span class="hljs-number">468</span>
              Until_Condition: <span class="hljs-literal">None</span>
               Until_Log_File: 
                Until_Log_Pos: <span class="hljs-number">0</span>
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: <span class="hljs-number">0</span>
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: <span class="hljs-number">0</span>
                Last_IO_Error: 
               Last_SQL_Errno: <span class="hljs-number">0</span>
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: <span class="hljs-number">2</span>
                  Master_UUID: <span class="hljs-number">8</span>ac3066a<span class="hljs-subst">-</span><span class="hljs-number">9680</span><span class="hljs-subst">-</span><span class="hljs-number">11e5</span><span class="hljs-attribute">-a2ec</span><span class="hljs-subst">-</span><span class="hljs-number">5254007529</span>fd
             Master_Info_File: /<span class="hljs-built_in">data</span>/mysqldb/master<span class="hljs-built_in">.</span>info
                    SQL_Delay: <span class="hljs-number">0</span>
          SQL_Remaining_Delay: <span class="hljs-built_in">NULL</span>
      Slave_SQL_Running_State: Slave has read <span class="hljs-literal">all</span> relay <span class="hljs-keyword">log</span>; waiting for the slave I/O <span class="hljs-keyword">thread</span> <span class="hljs-keyword">to</span> update it
           Master_Retry_Count: <span class="hljs-number">86400</span>
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: <span class="hljs-number">0</span>
<span class="hljs-number">1</span> row <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)

ERROR: 
No query specified

mysql<span class="hljs-subst">></span> 
</code>

已经新增加了test。

真正的测试

在主库中新增数据库表，user，观察从库变化如下：

创建数据库的时候：

这里写图片描述

新增数据的时候：

这里写图片描述

删除Master数据库表的时候：
这里写图片描述

配置过程，到此为止，希望能够帮助大家，如有疑问 欢迎留言。

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：sql server一个查询语句引发的死锁Next article：sql server中select语句需要申请的锁

Related articles

See more