mysql热备搭建服务_MySQL建立主－从服务器双机热备配置

一、准备环境

1.主服务器A(192.168.0.176)，从服务器B(192.168.0.13)。

2.两台服务器上都部署了mysql数据库。(两台服务器上的数据库版本必须是一致的)

3.再两台服务器上分别创建rb-test数据库，并创建数据表user。

二、主服务器A双机热备配置步骤

1、创建同步用户

进入mysql操作界面，在主服务器A上为从服务器建立一个连接帐户，该帐户必须授予REPLICATION SLAVE权限。因为从mysql版本3.2以后就可以通过REPLICATION对其进行双机热备的功能操作。

mysql> grant replication slave on *.* to 'zhutong'@'192.168.0.13' identified by 'zhutong#123';

Query OK,0 rows affected, 1 warning (0.00sec)

mysql>flush privileges;

Query OK,0 rows affected (0.00 sec)

2、创建好同步连接帐户后，我们可以通过在从服务器B上用zhutong帐户对主服务器A(Master)数据库进行访问下，看下是否能连接成功。

[root@ecs ~]# mysql -h192.168.0.176 -uzhutong -pzhutong#123mysql: [Warning] Using a password on the command lineinterfacecan be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 11Server version:5.7.21MySQL Community Server (GPL)

Copyright (c)2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c'to clear the current input statement.

mysql>

3、修改主服务器A数据库的配置

vi /etc/my.cnf

#加上

server-id = 1log-bin=mysql-bin

binlog-do-db =rb

binlog-ignore-db = mysql

4、重启mysql数据库

service restart mysql

5、查看主服务器的状态

[root@ecs-d68e-0003 use-tools]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 765Server version:5.7.21-log MySQL Community Server (GPL)

mysql> flush table with read lock;

Query OK,0 rows affected (0.00sec)

mysql>show master status\G;*************************** 1. row ***************************File: mysql-bin.000006Position:154Binlog_Do_DB: rb

Binlog_Ignore_DB: mysql

Executed_Gtid_Set:1 row in set (0.00sec)

ERROR:

No query specified

注意看里面的参数，特别前面两个File和Position，在从服务器(Slave)配置主从关系会有用到的。

6、锁表

注：这里使用了锁表，目的是为了产生环境中不让进新的数据，好让从服务器定位同步位置，初次同步完成后，记得解锁。

unlock tables;

三、从服务器B双机热备配置步骤

1、修改配置文件

vi /etc/my.cnf

#添加如下内容

[mysqld]

server-id = 2log-bin=mysql-bin

replicate-do-db =rb

replicate-ignore-db = mysql,information_schema,performance_schema

2、重启数据库

service restart mysql

3.用change mster 语句指定同步位置

这步是最关键的一步了，在进入mysql操作界面后，输入如下指令：

[root@ecs-d68e-0002 DB]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 2Server version:5.7.21-log MySQL Community Server (GPL)

mysql>stop slave;

Query OK,0 rows affected, 1 warning (0.00sec)

mysql> change master to master_host='192.168.0.176',master_user='zhutong',master_password='zhutong#123',master_log_file='mysql-bin.000001',master_log_pos=154;

Query OK,0 rows affected, 2 warnings (0.02sec)

#成功后，则可以开启slave线程了。mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

4、查看从服务器(Slave)状态

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:192.168.0.176Master_User: zhutong

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000006Read_Master_Log_Pos:154Relay_Log_File: ecs-d68e-0002-relay-bin.000007Relay_Log_Pos:367Relay_Master_Log_File: mysql-bin.000006Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: rb

Replicate_Ignore_DB: mysql,information_schema,performance_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:154Relay_Log_Space:795Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1Master_UUID: 31e0c365-7e73-11e8-b6ee-fa163ee46a49

Master_Info_File:/home/zhutong/DB/datas/mysql/master.info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingformore updates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:1 row in set (0.00sec)

ERROR:

No query specified

查看下面两项值均为Yes，即表示设置从服务器成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

四、测试同步

1、查看主从数据库中rb数据库user表的数据(我们rb数据库中只有一张表)；

主-A

mysql>use rb;

Database changed

mysql>show tables;+--------------+

| Tables_in_rb |

+--------------+

| user |

+--------------+

1 row in set (0.01sec)

mysql> select * fromuser;

Emptyset (0.00sec)

mysql>

从-B

mysql>use rb;

Database changed

mysql>show tables;+--------------+

| Tables_in_rb |

+--------------+

| user |

+--------------+

1 row in set (0.00sec)

mysql> select * fromuser;

Emptyset (0.00 sec)

现在可以在Master服务器中插入数据看下是否能同步。

2、在主服务器上数据库写数据，看能不能同步。

mysql>use rb;

Database changed

mysql> insert into user values('zbbiex','15352453201');

ERROR1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into user values(1,'zbbiex','15352453201');

Query OK,1 row affected (0.00sec)

mysql> select * fromuser;+----+--------+-------------+

| id | name | mobile |

+----+--------+-------------+

| 1 | zbbiex | 15352453201 |

+----+--------+-------------+

1 row in set (0.00sec)

mysql>

3、在从服务器B上查看是不是数据已经同步过去了

mysql>use rb;

Database changed

mysql> select * fromuser;+----+--------+-------------+

| id | name | mobile |

+----+--------+-------------+

| 1 | zbbiex | 15352453201 |

+----+--------+-------------+

1 row in set (0.00sec)

mysql>

可以看到数据已经同步过去，完全没问题；