MySQL5.6配置双机互为主备<h2><span style="font-family:宋体">一准备</span></h2>
<p><span style="font-family:宋体">首先需要两个</span>MySQL<span style="font-family:宋体">数据库服务器</span></p>
<p><span style="font-family:宋体">假如</span>:</p>
<p><span style="font-family:宋体">数据库服务器</span>1<span style="font-family:宋体">信息</span></p>
<p>192.168.8.202 3306 root root</p>
<p><span style="font-family:宋体">数据库服务器</span>2<span style="font-family:宋体">信息</span></p>
<p>192.168.8.203 3360 root root</p>
<p> </p>
<h2><span style="font-family:宋体">二、数据库</span>1<span style="font-family:宋体">配置</span></h2>
<h3>2.1<span style="font-family:宋体">修改数据库</span>1<span style="font-family:宋体">配置</span></h3>
<p><span style="font-family:宋体">编辑数据库</span>1<span style="font-family:宋体">的配置文件</span>/etc/my.cnf</p>
<p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">节点下添加</span>:</p>
<pre>
<code>#######主从配置master信息#######
#[必须]服务器唯一ID,默认是1,一般取IP最后一段
server_id=202
#[必须]启用二进制日志
log_bin=mysql-bin
#需要备份的数据库名 多个库写多行
binlog-do-db=test
#忽略备份数据库名 多个库写多行
#binlog-ignore-db=mysql
#若涉及及同步函数或者存储过程需要配置,否则主备会产生异常不能同步
log_bin_trust_function_creators=TRUE
#######主从配置master信息############
</code></pre>
<p> </p>
<p><img alt="1" class="img-thumbnail" src="/assist/images/blog/54fb33900cb949a2aea85ef489156cc6.png" /></p>
<p> </p>
<p><strong><span style="font-family:宋体"><span style="color:red">配置方式为指定数据库</span></span><span style="color:red">test</span></strong><strong><span style="font-family:宋体"><span style="color:red">同步</span></span></strong></p>
<h3>2.2<span style="font-family:宋体">重启</span>MySQL<span style="font-family:宋体">服务</span></h3>
<p><span style="font-family:宋体">重启数据库服务器</span>1<span style="font-family:宋体">的</span>MySQL<span style="font-family:宋体">服务</span></p>
<p><span style="font-family:宋体">命令</span>:</p>
<pre>
<code>service mysqld restart</code></pre>
<h3>2.3<span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></h3>
<p><span style="font-family:宋体">创建一个</span>slave<span style="font-family:宋体">用户</span>,<span style="font-family:宋体">用于数据库服务器</span>2<span style="font-family:宋体">上进行同步</span></p>
<p> </p>
<p>Root<span style="font-family:宋体">登录</span>MySQL<span style="font-family:宋体">数据库</span></p>
<pre>
<code>[root@localhost ~]# mysql -uroot -proot</code></pre>
<p> </p>
<p><span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></p>
<pre>
<code>mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';</code></pre>
<p> </p>
<p><span style="font-family:宋体">切换至</span>mysql<span style="font-family:宋体">数据库</span>,<span style="font-family:宋体">查看刚才创建的用户信息</span></p>
<pre>
<code>mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from user where User='slave' \G;
*************************** 1. row ***************************
Host: %
User: slave
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
ERROR:
No query specified
mysql></code></pre>
<p><img alt="2" class="img-thumbnail" src="/assist/images/blog/d3716e0e60d7453290b3af342cf6878d.png" /></p>
<p><span style="font-family:宋体">注意查看</span>: <strong><span style="color:red">Repl_slave_priv: Y</span></strong></p>
<p><span style="font-family:宋体">是</span>Y<span style="font-family:宋体">标识该账户已经有权限操作同步了。</span></p>
<h3>2.3<span style="font-family:宋体">备份数据库</span></h3>
<p><span style="font-family:宋体">该操作主要是备份<strong>数据库服务器</strong></span><strong>1</strong><span style="font-family:宋体">中需要同步的数据库。(这里的同步数据库为</span>test<span style="font-family:宋体">库)</span></p>
<p> </p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">锁表为只读状态</span></li>
</ol>
<p style="margin-left:18.0pt"><span style="font-family:宋体">锁表命令</span>:<br />
</p>
<pre>
<code>mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.06 sec)
</code></pre>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">备份数据库</span>1<span style="font-family:宋体">数据</span></li>
</ol>
<p><span style="font-family:宋体">退出数据库或者新打开一个</span>ssh<span style="font-family:宋体">连接</span>,<span style="font-family:宋体">执行以下命令备份</span></p>
<pre>
<code>[root@localhost ~]# mysqldump -uroot -proot test > /root/test-db.sql</code></pre>
<p><img alt="3" class="img-thumbnail" src="/assist/images/blog/22d428f5384146fda237607774502347.png" /></p>
<p> </p>
<p><span style="font-family:宋体">如图已经将</span>test<span style="font-family:宋体">库备份至</span>/root<span style="font-family:宋体">目录</span>,<span style="font-family:宋体">名称为</span>test-db.sql</p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">查看数据库服务器</span>1<span style="font-family:宋体">的</span>master<span style="font-family:宋体">信息</span></li>
</ol>
<p style="margin-left:18.0pt"><span style="font-family:宋体">登录数据库执行命令</span>:</p>
<pre>
<code>mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 887 | test | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql></code></pre>
<p><img alt="4" class="img-thumbnail" src="/assist/images/blog/74ce9951b1034a9088bc7daf619e1785.png" /></p>
<p><strong><span style="font-family:宋体">记住这里的</span>file</strong><strong><span style="font-family:宋体">名称和</span>Position</strong><strong><span style="font-family:宋体">值</span></strong></p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">解锁表</span></li>
</ol>
<pre>
<code>mysql> unlock tables;</code></pre>
<p> </p>
<h2><span style="font-family:宋体">三、数据库</span>2<span style="font-family:宋体">配置</span></h2>
<h3>3.1<span style="font-family:宋体">修改数据库</span>2<span style="font-family:宋体">配置</span></h3>
<p><span style="font-family:宋体">编辑数据库</span>1<span style="font-family:宋体">的配置文件</span>/etc/my.cnf</p>
<p><span style="font-family:宋体">在</span>[mysqld]<span style="font-family:宋体">节点下添加</span>:</p>
<pre>
<code>#######主从配置master信息#######
#[必须]服务器唯一ID,默认是1,一般取IP最后一段
server_id=203
#[必须]启用二进制日志
log_bin=mysql-bin
#需要备份的数据库名 多个库以逗号分隔
binlog-do-db=test
#忽略备份数据库名
#binlog-ignore-db=mysql
#若涉及及同步函数或者存储过程需要配置,否则主备会产生异常不能同步
log_bin_trust_function_creators=TRUE
#######主从配置master信息############</code></pre>
<h3>3.2<span style="font-family:宋体">重启</span>MySQL<span style="font-family:宋体">服务</span></h3>
<p><span style="font-family:宋体">重启数据库服务器</span>2<span style="font-family:宋体">的</span>MySQL<span style="font-family:宋体">服务</span></p>
<p><span style="font-family:宋体">命令</span>:</p>
<pre>
<code>#service mysqld restart</code></pre>
<p> </p>
<h3>3.3<span style="font-family:宋体">导入数据</span></h3>
<p><span style="font-family:宋体">将数据库服务器</span>1<span style="font-family:宋体">中导出的库导入进来</span></p>
<p> </p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">远程复制备份文件</span></li>
</ol>
<pre>
<code>[root@bogon ~]# scp root@192.168.8.202:/root/test-db.sql /root/test-db.sql
The authenticity of host '192.168.8.202 (192.168.8.202)' can't be established.
RSA key fingerprint is 70:bc:ba:69:58:77:3c:4b:8e:75:75:91:35:fa:40:f6.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.8.202' (RSA) to the list of known hosts.
root@192.168.8.202's password:
test-db.sql 100% 2575 2.5KB/s 00:00
[root@bogon ~]# ll
total 42432
-rw-------. 1 root root 3330 Jul 19 03:54 anaconda-ks.cfg
-rw-r--r--. 1 root root 41789864 Jul 25 07:10 axshool25.sql
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Desktop
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Documents
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Downloads
drwxr-xr-x. 4 root root 4096 Jul 24 06:08 fastDFS
-rw-r--r--. 1 root root 41364 Jul 19 03:54 install.log
-rw-r--r--. 1 root root 9154 Jul 19 03:52 install.log.syslog
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Music
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Pictures
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Public
drwxrwxr-x. 6 root root 4096 May 17 08:39 redis-3.2.9
-rw-r--r--. 1 root root 1547695 May 17 08:40 redis-3.2.9.tar.gz
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Templates
-rw-r--r--. 1 root root 2575 Jul 26 03:00 test-db.sql
drwxr-xr-x. 2 root root 4096 Jul 19 07:11 Videos
[root@bogon ~]#
导入数据
[root@bogon ~]# mysql -uroot -proot -B test </root/test-db.sql</code></pre>
<p> </p>
<h3>3.4<span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></h3>
<p>Root<span style="font-family:宋体">用户登录数据库</span></p>
<pre>
<code>[root@bogon ~]# mysql -uroot -proot</code></pre>
<p> </p>
<p><span style="font-family:宋体">创建</span>slave<span style="font-family:宋体">用户</span></p>
<pre>
<code>mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';</code></pre>
<p> </p>
<p><span style="font-family:宋体">切换至</span>mysql<span style="font-family:宋体">数据库</span>,<span style="font-family:宋体">查看刚才创建的用户信息</span></p>
<pre>
<code>mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from user where User='slave' \G;
*************************** 1. row ***************************
Host: %
User: slave
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
ERROR:
No query specified
mysql></code></pre>
<p><img alt="5" class="img-thumbnail" src="/assist/images/blog/dc22d4ee5d40435599149de8f38a43cb.png" /></p>
<p> </p>
<h3>3.5<span style="font-family:宋体">配置</span>master</h3>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">首先停止</span>slave</li>
</ol>
<pre>
<code>mysql>stop slave</code></pre>
<p> </p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">配置</span>master<span style="font-family:宋体">指向数据库服务器</span>1:</li>
</ol>
<pre>
<code>mysql> change master to
-> master_host='192.168.8.202',
-> master_user='slave',
-> master_password='123456',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=887;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql></code></pre>
<p> </p>
<p><em><span style="font-family:宋体">命令说明</span>:</em></p>
<p><em>master_host :master</em><em><span style="font-family:宋体">的主机地址</span></em></p>
<p><em>master_user :master</em><em><span style="font-family:宋体">的备份用户</span></em></p>
<p><em>master_password :master</em><em><span style="font-family:宋体">备份用户密码</span></em></p>
<p><em>master_log_file :</em><em><span style="font-family:宋体">日志文件名</span>,</em><em><span style="font-family:宋体">在第二步骤的</span>2.3</em><em><span style="font-family:宋体">备份的最后一步查看的信息里面</span></em></p>
<p><em>master_log_pos :</em><em><span style="font-family:宋体">日志位置</span>,</em><em><span style="font-family:宋体">同上也在</span>2.3</em><em><span style="font-family:宋体">步骤中</span></em></p>
<p> </p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">启动</span>slave</li>
</ol>
<pre>
<code>mysql> start slave;</code></pre>
<p> </p>
<h3>3.6<span style="font-family:宋体">查看数据库服务</span>2<span style="font-family:宋体">的</span>master<span style="font-family:宋体">信息</span></h3>
<pre>
<code>mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | test | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql></code></pre>
<p><img alt="6" class="img-thumbnail" src="/assist/images/blog/8a1bdb08a23d4e6ab2731f9751ba140b.png" /></p>
<p> </p>
<h2><span style="font-family:宋体">四、配置数据库</span>1master</h2>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176">Root<span style="font-family:宋体">用户登录数据库</span>1</li>
</ol>
<pre>
<code>#mysql –uroot –proot</code></pre>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">停止</span>slave</li>
</ol>
<pre>
<code>mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql></code></pre>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">配置</span>master<span style="font-family:宋体">指向数据库服务器</span>2</li>
</ol>
<pre>
<code>mysql> change master to
-> master_host='192.168.8.203',
-> master_user='slave',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
启动slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
</code></pre>
<h2><span style="font-family:宋体">五、测试</span></h2>
<p> </p>
<h3>5.1<span style="font-family:宋体">测试数据库</span>1<span style="font-family:宋体">为</span>master<span style="font-family:宋体">创建表同步至数据库</span>2</h3>
<p><span style="font-family:宋体">测试步骤</span>:</p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176">Root<span style="font-family:宋体">数据库用户登录数据库服务器</span>1,<span style="font-family:宋体">切换至</span>test<span style="font-family:宋体">库</span></li>
</ol>
<p><img alt="7" class="img-thumbnail" src="/assist/images/blog/707f5370f77e4420b0c3cf1ca2279405.png" /></p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">创建一张表</span>,<span style="font-family:宋体">名称为</span>t_data</li>
</ol>
<p><img alt="8" class="img-thumbnail" src="/assist/images/blog/9782fe887f6b42668c646d0f83c9e140.png" /></p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">登录数据库服务器</span>2<span style="font-family:宋体">,切换至数据库</span>test<span style="font-family:宋体">并查看表请情况</span></li>
</ol>
<p> </p>
<p><span style="font-family:宋体">可以看到数据库</span>2<span style="font-family:宋体">中已经存在</span>t_data<span style="font-family:宋体">表格说明表创建已经同步成功</span></p>
<p><img alt="9" class="img-thumbnail" src="/assist/images/blog/ac1062d2c431404c986ccd2f70589649.png" /></p>
<h3>5.2<span style="font-family:宋体">测试数据库</span>1<span style="font-family:宋体">为</span>master<span style="font-family:宋体">数据内容同步</span></h3>
<p><span style="font-family:宋体">测试步骤</span>:</p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">登录数据库</span>1<span style="font-family:宋体">并切换至</span>test<span style="font-family:宋体">库,新增一条数据到上面创建的</span>t_data<span style="font-family:宋体">表中</span></li>
</ol>
<p><img alt="10" class="img-thumbnail" src="/assist/images/blog/632dc5d7655d4baf957eb3c6bb49b891.png" /></p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">登录数据库</span>2<span style="font-family:宋体">,切换至</span>test<span style="font-family:宋体">库查看</span>t_data<span style="font-family:宋体">表数据是否同步</span></li>
</ol>
<p><img alt="11" class="img-thumbnail" src="/assist/images/blog/a2bf6397291f4986b911acb359fcf51e.png" /></p>
<p><span style="font-family:宋体">可以看到数据库</span>2<span style="font-family:宋体">中的表</span>t_data<span style="font-family:宋体">数据内容已经同步。测试数据同步成功。</span></p>
<p> </p>
<h3>5.3<span style="font-family:宋体">测试数据库</span>2<span style="font-family:宋体">为</span>master<span style="font-family:宋体">创建表同步</span></h3>
<p><span style="font-family:宋体">测试步骤</span>:</p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176">Root<span style="font-family:宋体">数据库用户登录数据库</span>2<span style="font-family:宋体">,并切换至</span>test<span style="font-family:宋体">库,创建一个</span>t_data2<span style="font-family:宋体">的表格</span></li>
</ol>
<p><img alt="12" class="img-thumbnail" src="/assist/images/blog/c2e37210f12c4d33940a40a63a59a80f.png" /></p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">登录数据库</span>1<span style="font-family:宋体">切换至</span>test<span style="font-family:宋体">库</span>,<span style="font-family:宋体">查看表</span>t_data2<span style="font-family:宋体">是否创建</span></li>
</ol>
<p><img alt="13" class="img-thumbnail" src="/assist/images/blog/624225953a5d44fca9ff04d18b5caed0.png" /></p>
<p><span style="font-family:宋体">可以看到在数据库</span>1<span style="font-family:宋体">中</span>t_data2<span style="font-family:宋体">表已经同步创建,测试表创建同步成功。</span></p>
<p> </p>
<h3>5.4<span style="font-family:宋体">测试数据库</span>2<span style="font-family:宋体">为</span>master<span style="font-family:宋体">数据内容的同步</span></h3>
<p><span style="font-family:宋体">测试步骤</span>:</p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">登录数据库</span>2<span style="font-family:宋体">,并切换至</span>test<span style="font-family:宋体">库向</span>t_data2<span style="font-family:宋体">表中插入一条数据</span></li>
</ol>
<p><img alt="14" class="img-thumbnail" src="/assist/images/blog/36ca6b577a124f76a1638df072436855.png" /></p>
<ol start="7" style="list-style-type:lower-alpha">
<li value="176"><span style="font-family:宋体">登录数据库</span>1<span style="font-family:宋体">切换至</span>test<span style="font-family:宋体">库查看表</span>t_data2<span style="font-family:宋体">中数据是否同步</span></li>
</ol>
<p><img alt="15" class="img-thumbnail" src="/assist/images/blog/ba2fb40560134b99b3bd58a8f27ab1ad.png" /></p>
<p><span style="font-family:宋体">可以看到数据已经同步至数据库</span>1<span style="font-family:宋体">中的</span>test<span style="font-family:宋体">库的</span>t_data2<span style="font-family:宋体">表。数据同步测试成功。</span></p>