centos7 mariadb主从复制配置搭建详解步骤
<p>花了小一天的时间，终于实现了centos7 mariadb主从复制配置搭建，下面记录一下过程</p>
<p>
环境:</p>
<p>
虚拟机:vm8; centos7 版本：7.2.1511; mariadb 版本：centos7.2内置的</p>
<p>
主库服务器： 10.69.5.200，CentOS 7，MariaDB 10已安装，有数据。</p>
<p>
从库服务器1： 10.69.5.201，CentOS 7，MariaDB 10已安装，无应用数据。</p>
<p>
<span><strong>主服务器配置</strong></span></p>
<p>
以下操作在主服务器192.168.71.151的/etc/my.cnf上进行。</p>
<p>
1.修改配置文件，命令：vim /etc/my.cnf，输入下列代码：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_31292">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># cat /etc/my.cnf</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain"></code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">datadir=</code><code class="bash plain">/var/lib/mysql</code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">socket=</code><code class="bash plain">/var/lib/mysql/mysql</code><code class="bash plain">.sock</code>
</div>
<div class="line number5 index4 alt2">
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">`</code><code class="bash comments"># 新添加的部分</code>
</div>
<div class="line number7 index6 alt2">
<code class="bash comments"># 配置主从时需要添加以下信息 start</code>
</div>
<div class="line number8 index7 alt1">
<code class="bash plain">innodb_file_per_table=NO</code>
</div>
<div class="line number9 index8 alt2">
<code class="bash plain">log-bin=</code><code class="bash plain">/var/lib/mysql/master-bin</code> <code class="bash comments">#log-bin没指定存储目录，则是默认datadir指向的目录</code>
</div>
<div class="line number10 index9 alt1">
<code class="bash plain">binlog_format=mixed</code>
</div>
<div class="line number11 index10 alt2">
<code class="bash plain">server-</code><code class="bash functions">id</code><code class="bash plain">=200 </code>
</div>
<div class="line number12 index11 alt1">
<code class="bash comments">#每个服务器都需要添加server_id配置，各个服务器的server_id需要保证唯一性，实践中通常设置为服务器IP地址的最后一位</code>
</div>
<div class="line number13 index12 alt2">
<code class="bash comments">#配置主从时需要添加以下信息 end </code>
</div>
<div class="line number14 index13 alt1">
<code class="bash plain">`</code>
</div>
<div class="line number15 index14 alt2">
<code class="bash comments"># Disabling symbolic-links is recommended to prevent assorted security risks</code>
</div>
<div class="line number16 index15 alt1">
<code class="bash plain">symbolic-links=0</code>
</div>
<div class="line number17 index16 alt2">
<code class="bash comments"># Settings user and group are ignored when systemd is used.</code>
</div>
<div class="line number18 index17 alt1">
<code class="bash comments"># If you need to run mysqld under a different user or group,</code>
</div>
<div class="line number19 index18 alt2">
<code class="bash comments"># customize your systemd unit file for mariadb according to the</code>
</div>
<div class="line number20 index19 alt1">
<code class="bash comments"># instructions in http://fedoraproject.org/wiki/Systemd</code>
</div>
<div class="line number21 index20 alt2">
</div>
<div class="line number22 index21 alt1">
<code class="bash plain"></code>
</div>
<div class="line number23 index22 alt2">
<code class="bash plain">log-error=</code><code class="bash plain">/var/log/mariadb/mariadb</code><code class="bash plain">.log</code>
</div>
<div class="line number24 index23 alt1">
<code class="bash plain">pid-</code><code class="bash functions">file</code><code class="bash plain">=</code><code class="bash plain">/var/run/mariadb/mariadb</code><code class="bash plain">.pid</code>
</div>
<div class="line number25 index24 alt2">
</div>
<div class="line number26 index25 alt1">
<code class="bash comments">#</code>
</div>
<div class="line number27 index26 alt2">
<code class="bash comments"># include all files from the config directory</code>
</div>
<div class="line number28 index27 alt1">
<code class="bash comments">#</code>
</div>
<div class="line number29 index28 alt2">
<code class="bash plain">!includedir </code><code class="bash plain">/etc/my</code><code class="bash plain">.cnf.d</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
最后，:wq!保存退出</p>
<p>
2.重启mariadb服务，输入命令</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_69499">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># systemctl restart mariadb.service</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
3.登录mariadb</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_638337">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># mysql -u root -padmin</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
注：-p后是密码，中间没有空格</p>
<p>
4.创建帐号并赋予replication的权限</p>
<p>
从库,从主库复制数据时需要使用这个帐号进行</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_702597">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> </code><code class="sql keyword">GRANT</code> <code class="sql plain">REPLICATION SLAVE </code><code class="sql keyword">ON</code> <code class="sql plain">*.* </code><code class="sql keyword">TO</code> <code class="sql string">'root'</code><code class="sql plain">@</code><code class="sql string">'10.69.5.%'</code> <code class="sql plain">IDENTIFIED </code><code class="sql keyword">BY</code> <code class="sql string">'admin'</code><code class="sql plain">;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
5.备份数据库数据，用于导入到从数据库中</p>
<p>
加锁</p>
<p>
实际工作中，备份的时候是不让往库中写数据的，所以数据库要加锁，只能读</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_363771">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> FLUSH TABLES </code><code class="sql keyword">WITH</code> <code class="sql keyword">READ</code> <code class="sql plain">LOCK;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
记录主库log文件及其当前位置</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_605784">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> SHOW MASTER STATUS;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">+</code><code class="sql comments">------------------+----------+--------------+------------------+</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">+</code><code class="sql comments">------------------+----------+--------------+------------------+</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">| mysql-bin.000001 | 694 | | |</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">+</code><code class="sql comments">------------------+----------+--------------+------------------+</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
记住File和Position的部分，从服务器会用到</p>
<p>
备份数据，输入命令：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_337827">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># mysqldump -uroot -p --all-databases > /root/db.sql</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
解锁 主库</p>
<p>
数据备份完成后，就可以释放主库上的锁：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_10437">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> UNLOCK TABLES;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>从服务器配置</strong></p>
<p>
以下在从服务器上的操作</p>
<p>
1.导入主库的数据</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_511749">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># mysql -uroot -p < db.sql</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
2.从服务器/etc/my.cnf配置,设置relay-log</p>
<p>
my.cnf文件中添加一行relay_log=relay-bin</p>
<p>
如果不设置，默认是按主机名 + “-relay-bin”生成relay log。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_227742">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># cat /etc/my.cnf</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain"></code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">datadir=</code><code class="bash plain">/var/lib/mysql</code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">socket=</code><code class="bash plain">/var/lib/mysql/mysql</code><code class="bash plain">.sock</code>
</div>
<div class="line number5 index4 alt2">
<code class="bash comments"># Disabling symbolic-links is recommended to prevent assorted security risks</code>
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">symbolic-links=0</code>
</div>
<div class="line number7 index6 alt2">
</div>
<div class="line number8 index7 alt1">
<code class="bash plain">`</code><code class="bash comments">#配置主从时需要添加以下信息 start</code>
</div>
<div class="line number9 index8 alt2">
<code class="bash plain">innodb_file_per_table=NO</code>
</div>
<div class="line number10 index9 alt1">
<code class="bash plain">server-</code><code class="bash functions">id</code><code class="bash plain">=201 </code><code class="bash comments">#一般与服务器ip的最后数字一致</code>
</div>
<div class="line number11 index10 alt2">
<code class="bash plain">relay-log=</code><code class="bash plain">/var/lib/mysql/relay-bin</code>
</div>
<div class="line number12 index11 alt1">
<code class="bash comments">#配置主从时需要添加以下信息 end </code>
</div>
<div class="line number13 index12 alt2">
<code class="bash plain">`</code>
</div>
<div class="line number14 index13 alt1">
<code class="bash comments"># Settings user and group are ignored when systemd is used.</code>
</div>
<div class="line number15 index14 alt2">
<code class="bash comments"># If you need to run mysqld under a different user or group,</code>
</div>
<div class="line number16 index15 alt1">
<code class="bash comments"># customize your systemd unit file for mariadb according to the</code>
</div>
<div class="line number17 index16 alt2">
<code class="bash comments"># instructions in http://fedoraproject.org/wiki/Systemd</code>
</div>
<div class="line number18 index17 alt1">
</div>
<div class="line number19 index18 alt2">
<code class="bash plain"></code>
</div>
<div class="line number20 index19 alt1">
<code class="bash plain">log-error=</code><code class="bash plain">/var/log/mariadb/mariadb</code><code class="bash plain">.log</code>
</div>
<div class="line number21 index20 alt2">
<code class="bash plain">pid-</code><code class="bash functions">file</code><code class="bash plain">=</code><code class="bash plain">/var/run/mariadb/mariadb</code><code class="bash plain">.pid</code>
</div>
<div class="line number22 index21 alt1">
</div>
<div class="line number23 index22 alt2">
<code class="bash comments">#</code>
</div>
<div class="line number24 index23 alt1">
<code class="bash comments"># include all files from the config directory</code>
</div>
<div class="line number25 index24 alt2">
<code class="bash comments">#</code>
</div>
<div class="line number26 index25 alt1">
<code class="bash plain">!includedir </code><code class="bash plain">/etc/my</code><code class="bash plain">.cnf.d</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
3.重启服务</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_612915">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># systemctl restart mariadb.service</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
4.登录mariadb</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_790907">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># mysql -u root -padmin</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
5.设置主从复制</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_301155">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> CHANGE MASTER </code><code class="sql keyword">TO</code> <code class="sql plain">MASTER_HOST=</code><code class="sql string">'10.69.5.200'</code><code class="sql plain">,MASTER_USER=</code><code class="sql string">'root'</code><code class="sql plain">, MASTER_PASSWORD=</code><code class="sql string">'admin'</code><code class="sql plain">, MASTER_LOG_FILE=</code><code class="sql string">'master-bin.000001'</code><code class="sql plain">, MASTER_LOG_POS= 694;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.02 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这个命令完成以下几个任务：</p>
<p>
a.设置当前服务器为主服务器(10.69.5.200)的从库</p>
<p>
b.提供当前数据库(从库)从主库复制数据时所需的用户名和密码，即上面的GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin';设置的</p>
<p>
c.指定从库开始复制主库时需要使用的日志文件和文件位置，即上面主库执行SHOW MASTER STATUS;显示结果中的File和Position</p>
<p>
6.开启主从复制</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_868902">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> START SLAVE;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
7.查看从库状态</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_531800">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
<div class="line number42 index41 alt1">
42</div>
<div class="line number43 index42 alt2">
43</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> show slave status\G</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">*************************** 1. row ***************************</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">Slave_IO_State: Waiting </code><code class="sql keyword">for</code> <code class="sql plain">master </code><code class="sql keyword">to</code> <code class="sql plain">send event</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_Host: 10.69.5.200</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">Master_User: root</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_Port: 3306</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql plain">Connect_Retry: 60</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_Log_File: master-bin.000001</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">Read_Master_Log_Pos: 694</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql plain">Relay_Log_File: relay-bin.000003</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">Relay_Log_Pos: 530</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql plain">Relay_Master_Log_File: master-bin.000001</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql plain">Slave_IO_Running: Yes</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql plain">Slave_SQL_Running: Yes</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Do_DB: </code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Ignore_DB: </code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Do_Table: </code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Ignore_Table: </code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Wild_Do_Table: </code>
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Wild_Ignore_Table: </code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code><code class="sql plain">Last_Errno: 0</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql spaces"> </code><code class="sql plain">Last_Error: </code>
</div>
<div class="line number23 index22 alt2">
<code class="sql spaces"> </code><code class="sql plain">Skip_Counter: 0</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql spaces"> </code><code class="sql plain">Exec_Master_Log_Pos: 694</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code><code class="sql plain">Relay_Log_Space: 818</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql spaces"> </code><code class="sql plain">Until_Condition: None</code>
</div>
<div class="line number27 index26 alt2">
<code class="sql spaces"> </code><code class="sql plain">Until_Log_File: </code>
</div>
<div class="line number28 index27 alt1">
<code class="sql spaces"> </code><code class="sql plain">Until_Log_Pos: 0</code>
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code><code class="sql plain">Master_SSL_Allowed: </code><code class="sql keyword">No</code>
</div>
<div class="line number30 index29 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_SSL_CA_File: </code>
</div>
<div class="line number31 index30 alt2">
<code class="sql spaces"> </code><code class="sql plain">Master_SSL_CA_Path: </code>
</div>
<div class="line number32 index31 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_SSL_Cert: </code>
</div>
<div class="line number33 index32 alt2">
<code class="sql spaces"> </code><code class="sql plain">Master_SSL_Cipher: </code>
</div>
<div class="line number34 index33 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_SSL_Key: </code>
</div>
<div class="line number35 index34 alt2">
<code class="sql spaces"> </code><code class="sql plain">Seconds_Behind_Master: 0</code>
</div>
<div class="line number36 index35 alt1">
<code class="sql plain">Master_SSL_Verify_Server_Cert: </code><code class="sql keyword">No</code>
</div>
<div class="line number37 index36 alt2">
<code class="sql spaces"> </code><code class="sql plain">Last_IO_Errno: 0</code>
</div>
<div class="line number38 index37 alt1">
<code class="sql spaces"> </code><code class="sql plain">Last_IO_Error: </code>
</div>
<div class="line number39 index38 alt2">
<code class="sql spaces"> </code><code class="sql plain">Last_SQL_Errno: 0</code>
</div>
<div class="line number40 index39 alt1">
<code class="sql spaces"> </code><code class="sql plain">Last_SQL_Error: </code>
</div>
<div class="line number41 index40 alt2">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Ignore_Server_Ids: </code>
</div>
<div class="line number42 index41 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_Server_Id: 200</code>
</div>
<div class="line number43 index42 alt2">
<code class="sql plain">1 row </code><code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。</p>
<p>
验证</p>
<p>
主服务器：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_17369">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> show databases;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">| </code><code class="sql keyword">Database</code> <code class="sql plain">|</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">| information_schema |</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">| mysql |</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">| mytest |</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">| performance_schema |</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">| test |</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">+</code><code class="sql comments">--------------------+</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">5 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.04 sec)</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">MariaDB [(none)]> use mytest;</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">Reading </code><code class="sql keyword">table</code> <code class="sql plain">information </code><code class="sql keyword">for</code> <code class="sql plain">completion </code><code class="sql keyword">of</code> <code class="sql keyword">table</code> <code class="sql color1">and</code> <code class="sql keyword">column</code> <code class="sql plain">names</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">You can turn </code><code class="sql keyword">off</code> <code class="sql plain">this feature </code><code class="sql keyword">to</code> <code class="sql plain">get a quicker startup </code><code class="sql keyword">with</code> <code class="sql plain">-A</code>
</div>
<div class="line number16 index15 alt1">
</div>
<div class="line number17 index16 alt2">
<code class="sql keyword">Database</code> <code class="sql plain">changed</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">MariaDB > </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql color2">user</code><code class="sql plain">;</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">| id | </code><code class="sql keyword">name</code> <code class="sql plain">|</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql plain">| 1 | t |</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql plain">| 2 | t2 |</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql plain">| 3 | t3 |</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number26 index25 alt1">
<code class="sql plain">3 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number27 index26 alt2">
</div>
<div class="line number28 index27 alt1">
<code class="sql plain">MariaDB > </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql color2">user</code><code class="sql plain">(</code><code class="sql keyword">name</code><code class="sql plain">) </code><code class="sql keyword">values</code><code class="sql plain">(</code><code class="sql string">'t4'</code><code class="sql plain">);</code>
</div>
<div class="line number29 index28 alt2">
<code class="sql plain">Query OK, 1 row affected (0.01 sec)</code>
</div>
<div class="line number30 index29 alt1">
</div>
<div class="line number31 index30 alt2">
<code class="sql plain">MariaDB > </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql color2">user</code><code class="sql plain">;</code>
</div>
<div class="line number32 index31 alt1">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number33 index32 alt2">
<code class="sql plain">| id | </code><code class="sql keyword">name</code> <code class="sql plain">|</code>
</div>
<div class="line number34 index33 alt1">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number35 index34 alt2">
<code class="sql plain">| 1 | t |</code>
</div>
<div class="line number36 index35 alt1">
<code class="sql plain">| 2 | t2 |</code>
</div>
<div class="line number37 index36 alt2">
<code class="sql plain">| 3 | t3 |</code>
</div>
<div class="line number38 index37 alt1">
<code class="sql plain">| 4 | t4 |</code>
</div>
<div class="line number39 index38 alt2">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number40 index39 alt1">
<code class="sql plain">4 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查看从服务器数据是否变化：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_464969">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> use mytest;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Reading </code><code class="sql keyword">table</code> <code class="sql plain">information </code><code class="sql keyword">for</code> <code class="sql plain">completion </code><code class="sql keyword">of</code> <code class="sql keyword">table</code> <code class="sql color1">and</code> <code class="sql keyword">column</code> <code class="sql plain">names</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">You can turn </code><code class="sql keyword">off</code> <code class="sql plain">this feature </code><code class="sql keyword">to</code> <code class="sql plain">get a quicker startup </code><code class="sql keyword">with</code> <code class="sql plain">-A</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">Database</code> <code class="sql plain">changed</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">MariaDB > </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql color2">user</code><code class="sql plain">;</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">| id | </code><code class="sql keyword">name</code> <code class="sql plain">|</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">| 1 | t |</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">| 2 | t2 |</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">2 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
<div class="line number14 index13 alt1">
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">MariaDB > </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql color2">user</code><code class="sql plain">;</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">| id | </code><code class="sql keyword">name</code> <code class="sql plain">|</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">| 1 | t |</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">| 2 | t2 |</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">| 4 | t4 |</code>
</div>
<div class="line number22 index21 alt1">
<code class="sql plain">+</code><code class="sql comments">----+------+</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql plain">3 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
可以看到，从服务器更新了数据</p>
<p>
<span><strong>搭建过程中遇到的问题及解决方法</strong></span></p>
<p>
<strong>问题1：从服务器设置主从复制出现错误:</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_550880">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">MariaDB > start slave;</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">ERROR 1201 (HY000): Could not initialize master info structure; </code><code class="bash functions">more</code> <code class="bash plain">error messages can be found </code><code class="bash keyword">in</code> <code class="bash plain">the MariaDB error log</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
发现 </p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_495570">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain">Slave_IO_Running: No</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">Slave_SQL_Running: No</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
进一步发现我输入的是：CHANGE MASTER TO MASTER_HOST='192.168.71.151',MASTER_USER='slave_user', MASTER_PASSWORD='bigs3cret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;</p>
<p>
重新输入：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;<br>
报错：ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log</p>
<p>
于是看错误日志：/var/log/mariadb/mariadb.log</p>
<p>
错误日志的位置在/etc/my.cnf中配置：log-error=/</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_635343">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># cat /var/log/mariadb/mariadb.log</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">160915 12:52:02 Failed to </code><code class="bash functions">open</code> <code class="bash plain">the relay log </code><code class="bash string">'./mariadb-relay-bin.000001'</code> <code class="bash plain">(relay_log_pos 4)</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">160915 12:52:02 Could not </code><code class="bash functions">find</code> <code class="bash plain">target log during relay log initialization</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
通过查找答案： 删除/var/lib/mysql/路径下the ‘master.info' ‘mysqld-relay-bin.*' ‘relay-log.info' ‘relay-log-index.*'</p>
<p>
运行命令：rm -rf master.info,rm -rf *relay*</p>
<p>
重启服务：# systemctl restart mariadb.service</p>
<p>
进入mariadb:</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_675899">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># mysql -u root -padmin</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">MariaDB [(none)]> flush logs;</code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">Query OK, 0 rows affected (0.00 sec)</code>
</div>
<div class="line number5 index4 alt2">
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">MariaDB [(none)]> reset slave;</code>
</div>
<div class="line number7 index6 alt2">
<code class="bash plain">Query OK, 0 rows affected (0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
重新设置主从复制关系：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_380276">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> CHANGE MASTER </code><code class="sql keyword">TO</code> <code class="sql plain">MASTER_HOST=</code><code class="sql string">'10.69.5.200'</code><code class="sql plain">,MASTER_USER=</code><code class="sql string">'root'</code><code class="sql plain">, MASTER_PASSWORD=</code><code class="sql string">'admin'</code><code class="sql plain">, MASTER_LOG_FILE=</code><code class="sql string">'master-bin.000001'</code><code class="sql plain">, MASTER_LOG_POS= 694;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.02 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这次成功了。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_23148">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> start slave;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.01 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查看从库状态：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_476331">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">MariaDB [(none)]> show slave status\G</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">*************************** 1. row ***************************</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">Slave_IO_State: Connecting </code><code class="sql keyword">to</code> <code class="sql plain">master</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_Host: 10.69.5.200</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql plain">Master_User: root</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_Port: 3306</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code><code class="sql plain">Connect_Retry: 60</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">Master_Log_File: master-bin.000001</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">Read_Master_Log_Pos: 694</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces"> </code><code class="sql plain">Relay_Log_File: relay-bin.000001</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain">Relay_Log_Pos: 4</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code><code class="sql plain">Relay_Master_Log_File: master-bin.000001</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql plain">Slave_IO_Running: Connecting</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql spaces"> </code><code class="sql plain">Slave_SQL_Running: Yes</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code><code class="sql plain">···</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces"> </code><code class="sql plain">···</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql plain">···</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql plain">Replicate_Ignore_Server_Ids: </code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql plain">Master_Server_Id: 0</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">1 row </code><code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>发现问题2.Slave_IO_Running: Connecting</strong></p>
<p>
问题2.Slave_IO_Running: Connecting</p>
<p>
查看错误日志</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_731497">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># cat /var/log/mariadb/mariadb.log</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">···</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">160915 13:17:56 Slave SQL thread initialized, starting replication </code><code class="bash keyword">in</code> <code class="bash plain">log </code><code class="bash string">'master-bin.000001'</code> <code class="bash plain">at position 694, relay log </code><code class="bash string">'/var/lib/mysql/relay-bin.000001'</code> <code class="bash plain">position: 4</code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">160915 13:17:56 Slave I</code><code class="bash plain">/O</code><code class="bash plain">: error connecting to master </code><code class="bash string">'root@10.69.5.200:3306'</code> <code class="bash plain">- retry-</code><code class="bash functions">time</code><code class="bash plain">: 60 retries: 86400 message: Can</code><code class="bash string">'t connect to MySQL server on '</code><code class="bash plain">10.69.5.200' (113), Error_code: 2003</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这时运行telnet命令</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_368912">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># telnet 10.69.5.200 3306</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
-bash: telnet: 未找到命令</p>
<p>
安装telnet</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_57964">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># yum -y install telnet-server.x86_64</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
安装成功后重启telnet服务</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_178825">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># systemctl start telnet.socket</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain"></code><code class="bash comments"># systemctl enable telnet.socket</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain"></code><code class="bash comments"># telnet 10.69.5.200 3306</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
-bash: telnet: 未找到命令</p>
<p>
还是不行</p>
<p>
这回我reboot重启虚拟机，运行命令</p>
<p>
注意：这回不是"yum -y install telnet-server.x86_64"了，这回没有telnet-server了</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_694147">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># yum install telnet.x86_64</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
运行成功了</p>
<p>
接着</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_870099">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code><code class="bash comments"># systemctl enable telnet.socket</code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain"></code><code class="bash comments"># systemctl start telnet.socket</code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain"></code><code class="bash comments"># firewall-cmd --add-service=telnet --permanent </code>
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">success</code>
</div>
<div class="line number5 index4 alt2">
<code class="bash plain"></code><code class="bash comments"># telnet</code>
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">telnet></code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
telnet终于安装成功了</p>
<p>
从最新版本的centos7系统开始，默认的是 Mariadb而不是mysql！</p>
<p>
使用系统自带的repos安装很简单：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_422904">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">yum </code><code class="bash functions">install</code> <code class="bash plain">mariadb mariadb-server</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<ul>
<li>
systemctl start mariadb ==> 启动mariadb</li>
<li>
systemctl enable mariadb ==> 开机自启动</li>
<li>
mysql_secure_installation ==> 设置 root密码等相关</li>
<li>
mysql -u root -p 123456 ==> 测试登录！</li>
</ul>
<p>
结束！</p>
<p>
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。</p>
<p>
原文链接：http://blog.csdn.net/mingliangniwo/article/details/54606894?locationNum=8&fps=1</p>
頁:
[1]