mysql主从复制环境搭建

╰+攻爆jí腚メ 2022-09-24 00:29 238阅读 0赞

[http://blog.163.com/zhaoxin\_hope/blog/static/17243722120126114039748/][http_blog.163.com_zhaoxin_hope_blog_static_17243722120126114039748]

从网上转载的一篇文章，步骤很详细，实验过，可以直接拿来用，我是window下低版本的[MySQL][]做主服务器，linux下高版本的mysql做从服务器做主从复制结构的，主从复制之间要求主从mysql版本一致或从的版本高与主的版本均可成功搭建主从复制环境。至于主版本高、从版本低的情况没有实验过，大家如果试验过可以告诉我结论。

在实际企业应用环境当中，单台mysql[数据库][MySQL]是不足以满足日后业务需求的。譬如服务器发生故障，没有备份服务器来提供服务的话，业务就得停止。介于这种情况，我们来学习一下mysql主从复制。

使用mysql主从复制的好处有：

1、采用主从服务器这种[架构][Link 1]，稳定性得以提升。如果主服务器发生故障，我们可以使用从服务器来提供服务。

2、在主从服务器上分开处理用户的请求，可以提升数据处理效率。

3、将主服务器上的数据复制到从服务器上，保护数据免受意外的损失。

环境描述：

新企业要搭建架构为主从复制的mysql数据库。

主服务器（mysql-master）：IP地址：192.168.48.128，mysql已安装，没有用户数据。

从服务器（mysql-slave）：IP地址：192.168.48.130，mysql已安装，没有用户数据。

主从服务器均可正常提供服务。

建立mysql用户并授权：

一, 创建用户:  
  
       命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';  
  
       说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.  
  
      例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';  
               CREATE USER 'pig'@'192.168.1.101\_' IDENDIFIED BY '123456';  
               CREATE USER 'pig'@'%' IDENTIFIED BY '123456';  
               CREATE USER 'pig'@'%' IDENTIFIED BY '';  
               CREATE USER 'pig'@'%';  
  
 二,授权:  
  
      命令:GRANT privileges ON databasename.tablename TO 'username'@'host'  
  
     说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用\*表示, 如\*.\*.  
  
      例子: GRANT SELECT, INSERT ON test.user TO 'pig'@'%';  
               GRANT ALL ON \*.\* TO 'pig'@'%';  
  
      注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:  
           GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

[![01][]][01 1]

主从复制配置如下：

在主服务器上操作：

1)、确保/etc/my.cnf中有如下参数，没有的话需手工添加，并重启mysql服务。

\[mysqld\]

log-bin=mysql-bin 启动二进制文件

server-id=1 服务器ID

2)、登录mysql，在mysql中添加一个backup的账号，并授权给从服务器。

\[root@localhost ~\]\# mysql -uroot –p123456 登录mysql

mysql> grant replication slave on \*.\* to 'backup'@'192.168.48.130' identified by 'backup'; 创建backup用户，并授权给192.168.48.130使用。

[![02][]][02 1]

3)、查询主数据库状态，并记下FILE及Position的值，这个在后面配置从服务器的时候要用到。

mysql> show master status;

[![03][]][03 1]

在从服务器上操作：

1)、确保/etc/my.cnf中有log-bin=mysql-bin和server-id=1参数，并把server-id=1修改为server-id=10。修改之后如下所示：

\[mysqld\]

log-bin=mysql-bin 启动二进制文件

server-id=10 服务器ID

2）、重启mysql服务。

\[root@localhost ~\]\# mysqladmin -p123456 shutdown   
\[root@localhost ~\]\# mysqld\_safe --user=mysql &

[![04][]][04 1]

3)、登录mysql，执行如下语句

\[root@localhost ~\]\# mysql -uroot –p123456

mysql> change master to master\_host='192.168.48.128',master\_user='backup',master\_password='backup',master\_log\_file='mysql-bin.000003',master\_log\_pos=401;

[![05][]][05 1]

4)、启动slave同步。

mysql> start slave;

[![06][]][06 1]

5)、检查主从同步，如果您看到Slave\_IO\_Running和Slave\_SQL\_Running均为Yes，则主从复制连接正常。

mysql> show slave status\\G

[![07][]][07 1]

验证配置是否正常，mysql主从能否正常复制。

在主数据库上新建一个库，并且在库中写一个表和一些数据。

\[root@localhost ~\]\# mysql -uroot –p123456

mysql> create database mysqltest;

mysql> use mysqltest;

mysql> create table user(id int(5),name char(10));

mysql> insert into user values (00001,'zhangsan');

[![08][]][08 1]

在从数据库中验证一下，是否正常复制到数据。

\[root@localhost ~\]\# mysql -uroot –p123456

mysql> show databases;

[![09][]][09 1]

mysql> select \* from mysqltest.user;

[![10][]][10 1]

从上图中的结果，我们可以看到mysql主从复制已经在起作用了，我们在主数据库中写入的数据已经复制到我们的从数据库中了。

环境如下：

单台数据库存有数据，要升级为主从复制的架构。

主数据库：mysql-master：IP192.168.48.128，数据库正常提供服务，有数据。

从数据库：mysql-slave：IP192.168.48.130，数据库正常提供服务，无数据。

[![01][01 2]][01_01 2]

升级大致步骤如下：

1、修改主数据库配置文件，开启总从复制必要的功能。

2、在主数据库中创建一个账号并授权给从数据库使用。

3、导出主数据库中的数据作。

4、修改从数据库配置文件，开启总从复制必要的功能。

5、把刚才主数据库中导出的数据导入到从数据库。

6、在从数据库中配置连接主数据库要用到的账户、密码等。

7、启动主从复制功能，并检查是否正常复制。

开始升级:

在主数据库上操作：

1)、确保/etc/my.cnf中有如下参数，没有的话需手工添加，并重启mysql服务。

\[mysqld\]

log-bin=mysql-bin 启动二进制文件

server-id=1 服务器ID

2)、登录mysql，在mysql中添加一个backup的账号，并授权给从服务器。

\[root@localhost ~\]\# mysql -uroot –p123456 登录mysql

mysql> grant replication slave on \*.\* to 'backup'@'192.168.48.130' identified by 'backup'; 创建backup用户，并授权给192.168.48.130使用。

[![02][02 2]][02_02 2]

3)、查看已有的数据库有哪些；

mysql> show databases;

[![02-1][]][02-1 1]

4)、进行锁表操作，不让数据进行写入动作，这么做事为了防止从数据库的原始数据和主数据库的原始数据不一致。

mysql> flush tables with read lock;

[![03][03 2]][03_03 2]

5)、查询主数据库状态，并记下FILE及Position的值，这个在后面配置从服务器的时候要用到。

mysql> show master status;

[![04][04 2]][04_04 2]

6)、切换一个终端，使用mysqldump命令将刚才查询到的两个库导出来。

mysql> mysqldump –uroot –p123456 mysqltest > mysqltest.sql

mysql> mysqldump –uroot –p123456 test1234 > test1234.sql

[![05][05 2]][05_05 2]

7)、将导出来的库文件传送到从数据库的/root目录下。

\[root@localhost ~\]\#scp mysqltest.sql test1234.sql [root@192.168.48.130:/root/][root_192.168.48.130_root]

这中间需要输入一次验证口令。见下图

[![06][06 2]][06_06 2]

8)、切换回之前的终端，进行表解锁操作。

mysql> unlock tables;

[![07][07 2]][07_07 2]

主数据库服务器上的操作告一段落。

在从数据库上操作：

1)、确保/etc/my.cnf中有log-bin=mysql-bin和server-id=1参数，并把server-id=1修改为server-id=10。修改之后如下所示：

\[mysqld\]

log-bin=mysql-bin 启动二进制文件

server-id=10 服务器ID

2)、重启mysql服务。

\[root@localhost ~\]\# mysqladmin -p123456 shutdown   
\[root@localhost ~\]\# mysqld\_safe --user=mysql &

[![08][08 2]][08_08 2]

3)、登录数据库，确认要同步的库名不存在。

\[root@localhost ~\]\# mysql -uroot –p123456

mysql> show databases;

[![09][09 2]][09_09 2]

4)、创建名为mysqltest和test1234的库。

mysql> create database mysqltest;

mysql> create database test1234;

[![10][10 2]][10_10 2]

4)、切换一个终端，将传过来的两个数据文件分别导入对应的数据库下。

\[root@localhost ~\]\# mysql -uroot –p123456 mysqltest < mysqltest.sql

\[root@localhost ~\]\# mysql -uroot –p123456 test1234 < test1234.sql

[![11][]][11 1]

5)、切换回之前的终端，执行如下语句

mysql> change master to master\_host='192.168.48.128',master\_user='backup',master\_password='backup',master\_log\_file='mysql-bin.000003',master\_log\_pos=1650;

5)、切换回之前的终端，执行如下语句

mysql> change master to master\_host='192.168.48.128',master\_user='backup',master\_password='backup',master\_log\_file='mysql-bin.000003',master\_log\_pos=1650;

6)、启动主从复制功能。

mysql> start slave;

[![13][]][13 1]

7)、

检查主从同步，如果您看到Slave\_IO\_Running和Slave\_SQL\_Running均为Yes，则主从复制连接正常。

mysql> show slave status\\G

[![14][]][14 1]

验证主从是否正常配置：

在主数据库上插入数据：

mysql> show databases;

mysql> show tables;

mysql> use mysqltest;

mysql> select \* from user;

mysql> insert into user values (4,'ergou');

mysql> insert into user values (5,'sanwazi');

[![16][]][16 1]

在从数据库上查看是否正常同步：

mysql> show databases;

mysql> use mysqltest;

mysql> select \* from user;

[![17][]][17 1]

上图可以看出，主从之间已经可以正常复制了。

今天就先到这里吧O(∩\_∩)O~

mysql主从复制碰到的小问题

一个朋友在做mysql主从复制的时候碰到了一些问题，他已经按照我之前的教程一步步做了。但是就是不知道是怎么回事。从数据库中使用命令show slave status\\G查看到的结果如下图:

[![000][]][000 1]

我们知道，要想mysql主从复制成功的先决条件是Slave\_IO\_Running和Slave\_SQL\_Running均为Yes，而上图所示的结果显然是有一个不满足条件。

再根据Last\_IO\_Error的错误代码2013及下面的错误提示我们可以知道，从数据库无法正常连接到主数据库。

这里原因有三：

1、主数据库没有给从数据库登录授权

2、网络不通

3、有防火墙

明白这三点之后我们就可以针对原因依次来解决它们：

登录主数据库，给从数据库授权

\[root@localhost ~\]\# mysql -uroot –p123456 登录主数据库

mysql> grant replication slave on \*.\* to 'backup'@'192.168.48.130' identified by 'backup'; 创建backup用户，并授权给从数据库连接使用。

[![02][02 3]][02_02 3]

检查网络是否通畅

\[root@localhost ~\]\# ping -c 4 192.168.48.128 再从服务器上ping主服务器。

[![QQ截图20111013104639][QQ_20111013104639]][QQ_20111013104639_QQ_20111013104639]

分别在主从服务器上检查防火墙状态

我们可以直接关闭防火墙

运行命令：\[root@localhost ~\]\# setup

[![111111111111][]][111111111111 1]

选择防火墙（Firewall configuretion）这一项.

[![001][]][001 1]

将它Disabled掉。

[![002][]][002 1]

或者你可以直接使用命令：

\[root@localhost ~\]\# service iptables stop 即时生效，系统重启后失效

\[root@localhost ~\]\# chkconfig iptables off 当前状态无效，系统重启后生效

大家可以结合使用

[![222222222][]][222222222 1]

以上情况都排除完毕，我们来再测试一下主从是否能正常连接。

在从数据库中执行如下命令：

mysql>stop slave;

mysql>start slave;

[![000-1][]][000-1 1]

然后查看主从连接状态

mysql>show slave status\\G

[![07][07 3]][07_07 3]

经过上述操作，我那位朋友成功排除了主从不能复制的故障。

故障2：报错如下:

![@MF2K\}FVZ7%Y9QEQ\_E$2INQ][MF2K_FVZ7_Y9QEQ_E_2INQ]

意思就是从上的server\_id和主的一样的，经查看发现从上的/etc/my.cnf中的server\_id=1这行我没有注释掉（在下面复制部分我设置了server\_id），于是马上把这行注释掉了，然后重启mysql，发现还是报同样的错误。

使用如下命令查看了一下server\_id

show variables like 'server\_id';

发现，mysql并没有从my.cnf文件中更新server\_id，既然这样就只能手动修改了

mysql> set global server\_id=10; \#此处的数值和my.cnf里设置的一样就行   
mysql> slave start;

如此执行后，slave恢复了正常。

不过稍后蚊子使用/etc/init.d/mysqld restart重启了mysql服务，然后查看slave状态，发现又出现了上面的错误，然后查看server\_id发现这个数值又恢复到了1。

之后蚊子又重新查看了一下/etc/my.cnf的内容，确认应该不是这个文件的问题，于是去google查了一下，看到mysql在启动的时候会查找/etc/my.cnf、DATADIR/my.cnf，USER\_HOME/my.cnf。

[http_blog.163.com_zhaoxin_hope_blog_static_17243722120126114039748]: http://blog.163.com/zhaoxin_hope/blog/static/17243722120126114039748/
[MySQL]: http://lib.csdn.net/base/14
[Link 1]: http://lib.csdn.net/base/16
[01]: /images/20220720/7c4e747946a8465ba1f552fec0ec1a92.png
[01 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402240rO4d.jpg
[02]: /images/20220720/68e5e6e9e8cd411b8baded16030ace2d.png
[02 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402241r9qz.jpg
[03]: /images/20220720/9ea3a98c10a3445a98b60f7b10451516.png
[03 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402242lERD.jpg
[04]: /images/20220720/ddcac142b63d406783a7db6f91821f29.png
[04 1]: http://img1.51cto.com/attachment/201110/12/714067_13184022428IEi.jpg
[05]: /images/20220720/6e3f3bdd000f4525b4e7630c6a202580.png
[05 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402242EMYp.jpg
[06]: /images/20220720/17db3b30b82b477f9105ec61ab65bdf5.png
[06 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402243ENPO.jpg
[07]: /images/20220720/e3a45efd9f7c438f907ffdcf9923b9d2.png
[07 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402244bZU7.jpg
[08]: /images/20220720/4e7349282c944c1cb680d419d06cc2d2.png
[08 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402247c5yY.jpg
[09]: /images/20220720/9b8afcd109684eb5a9545c49e47e9361.png
[09 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402248isUE.jpg
[10]: /images/20220720/98145415f4d04283848a851e75dda9f0.png
[10 1]: http://img1.51cto.com/attachment/201110/12/714067_1318402248gjlO.jpg
[01 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573255c8C2.jpg
[01_01 2]: /images/20220720/8a96be86ca98415b9154c34c84c263a4.png
[02 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573255YINP.jpg
[02_02 2]: /images/20220720/50e8df90e7e446ca8028028dc60e3c19.png
[02-1]: /images/20220720/83663c2ba6d845d395b9de0ddd3a92d8.png
[02-1 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573255je6n.jpg
[03 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573255TU4m.jpg
[03_03 2]: /images/20220720/816cc76287ec4fb19e88f0fbbf025f0e.png
[04 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573256ppm8.jpg
[04_04 2]: /images/20220720/f9eb6774ff3f4fe485571b30398f2832.png
[05 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573256nWRv.jpg
[05_05 2]: /images/20220720/44da52ab81d948319b0c758e8eac3406.png
[root_192.168.48.130_root]: mailto:root@192.168.48.130:/root/
[06 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573256a9Q6.jpg
[06_06 2]: /images/20220720/7979c7522b0b41f4a296e5934cb7be0a.png
[07 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573256KiQ4.jpg
[07_07 2]: /images/20220720/2df4ec491df742fdbcaa5b411e16a0fa.png
[08 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573256w2El.jpg
[08_08 2]: /images/20220720/090fba9a55a5485b9910d762bfa6a524.png
[09 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573257rBlx.jpg
[09_09 2]: /images/20220720/7daee5430742433a9cdd49e0ef716d0b.png
[10 2]: http://img1.51cto.com/attachment/201110/14/714067_1318573257PxAb.jpg
[10_10 2]: /images/20220720/ec3bb169d2ab47359092ddee2b142f9f.png
[11]: /images/20220720/8c869416fa1a44a49a6356917e6b0607.png
[11 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573257knho.jpg
[13]: /images/20220720/3a2caa5aeca9471096c7986f8ca46ba5.png
[13 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573257FXFt.jpg
[14]: /images/20220720/07391722b95c4faaa15a25aeb13b9273.png
[14 1]: http://img1.51cto.com/attachment/201110/14/714067_13185732589COL.jpg
[16]: /images/20220720/df7fd0602bf7407dbf4331d3ae756f0f.png
[16 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573258Y0AX.jpg
[17]: /images/20220720/824d0ac577ae4ff7989b9b33ebf10672.png
[17 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573259orhH.jpg
[000]: /images/20220720/e9ae097d0f0b4a81b76c35883e275b48.png
[000 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573765G886.jpg
[02 3]: http://img1.51cto.com/attachment/201110/14/714067_13185737664NOG.jpg
[02_02 3]: /images/20220720/90f0d57a3c1640469090352a1887c174.png
[QQ_20111013104639]: http://img1.51cto.com/attachment/201110/14/714067_1318573766f3i5.jpg
[QQ_20111013104639_QQ_20111013104639]: /images/20220720/27d70ba9aa174f0f8f554ab51b67faea.png
[111111111111]: /images/20220720/85a1a6a0818a488a8f500a8728229d1e.png
[111111111111 1]: http://img1.51cto.com/attachment/201110/14/714067_13185737667SNf.jpg
[001]: /images/20220720/effffd8403be4d9b927203f9dd5465f8.png
[001 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573767X3yL.jpg
[002]: /images/20220720/8a0aa30461b2426faaca9f2cafa52e04.png
[002 1]: http://img1.51cto.com/attachment/201110/14/714067_13185737686R0J.jpg
[222222222]: /images/20220720/cb8bb99e239348d5aa78f2bf619a07f4.png
[222222222 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573768k0gi.jpg
[000-1]: /images/20220720/50aadc277c6746fd86f0583907f15739.png
[000-1 1]: http://img1.51cto.com/attachment/201110/14/714067_1318573768RhxV.jpg
[07 3]: http://img1.51cto.com/attachment/201110/14/714067_1318573769XwJ8.jpg
[07_07 3]: /images/20220720/0af4223ac93641ca881e202615130a6c.png
[MF2K_FVZ7_Y9QEQ_E_2INQ]: