您所在的位置:首页 > 新闻中心 > 配置Mysql主从服务实现实例_Mysql_脚本之家,主从复制澳门新濠登录网址
公司要闻
配置Mysql主从服务实现实例_Mysql_脚本之家,主从复制澳门新濠登录网址
发布时间:2019-12-22 06:36
访问量:359

配置Mysql主从服务实现实例

前言

构建一个Mysql分布式集群需要对数据库的原理有较深的认识,而深奥复杂的数据库原理往往让很多读者中途放弃。本系列尝试用最简单的语言介绍Mysql分布式集群涉及到的原理,并结合实践的方式给读者介绍如何构建一个Mysql分布式集群。

配置思路

### 配置主数据库 my.cnf

目录

1.Mysql分布式集群(一)主从复制
2.Mysql分布式集群(二)主主复制和高可用

  1. 修改master和slave的配置文件,使用二进制日志,指定serverid
    目的是让各自都有了自己的唯一标示,并以二进制文件格式进行交流
  2. master中创建授权用户,查看二进制日志文件名,及最新位置
    让slave知道用哪个用户信息访问master,知道读取哪个日志文件,及从哪儿开始读
  3. slave中使用被授权用户信息及日志文件信息,进行指向master
    这时已经建立了和master的联系,明确了从哪儿读取日志文件
  4. 执行启动slave的命令,开始主从复制,并查看复制状态信息
    准备条件
    停止对master数据库的操作
    把master中的数据库全部导入到slave,使两边数据库完全一致
    配置步骤
  5. 修改配置文件 my.cnf

    vi /etc/my.cnf

server-id=1log-bin=master-binlog-bin-index=master-bin.index

主从复制

主从复制是为了完成一个和主数据库一致的数据库,当主数据数据改变的时候,从数据库自动保持和主数据库一致的改变。主从复制主要应用于读写分离,数据库备份

master:
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=222 //[必须]服务器唯一ID,默认是1,一般取IP最后一段
slave:
[mysqld]
log-bin=mysql-bin 
server-id=226 

### 在主数据库上创建同步账号

实验环境:

Master ip :192.168.178.128
Slave ip :192.168.179.129
操作系统:Ubuntu14.04
mysql版本:5.7.20

  1. 重新启动两台服务器的mysql
  2. master创建授权用户
    登陆主服务器mysql命令行,创建一个用于从服务器复制的用户

    CREATE USER '*'@'192.168.2.113' IDENTIFIED BY '**'; GRANT REPLICATION SLAVE ON . TO '***'@'192.168.2.113';

create user backup;GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.%.%' IDENTIFIED BY 'mysql';

主从复制原理

主从复制既能在程序端完成也能在Mysql端完成,本系列主要介绍通过配置Mysql 完成主从复制。具体原理如下图所示:

澳门新濠登录网址 1

主从复制原理

1.Master对数据改变之后,会将记录改变的事件(如:delete,update等)在Master中的Binary log(以下称:bin log);
2.Slave会按照一定的频率使用I/Othread读取Master上的bin log,并将该日志中的最近改变事件,写到Relay log(中继日志)中;
3.Salve通过SQL Thread 读取Relay log ,将改变事件同步到自己的Mysql中;

"*.*"表示对所有库的所有操作,“%”表示所有客户端都可能连,也可用具体客户端IP代替,如192.168.145.226,加强安全

### 配置从数据库 my.cnf

Master端配置

根据以上的原理可以,Slave端需要读取Master端的bin log日志,因此需要再Master端完成以下工作:

  1. 查看master状态
    登陆主服务器mysql命令行
    mysql>show master status;
server-id=2relay-log-index=slave-relay-bin.indexrelay- log=slave-relay-bin

1.对远程访问Master数据库的用户授权。

默认情况下root只能在本地登录,只有授权了,Slave端才能读取Master端bin log日志。

mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
mysql> grant all on *.* to 'slave'@'192.168.179.129' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select user,host from mysql.user;
+---------------+-----------------+
| user          | host            |
+---------------+-----------------+
| slave         | 192.168.179.129 |
| mysql.session | localhost       |
| mysql.sys     | localhost       |
| root          | localhost       |
+---------------+-----------------+
mysql> show grants for 'salve'@192.168.179.129;
+----------------------------------------------------------+
| Grants for salve@192.168.179.129                         |
+----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'salve'@'192.168.179.129' |
+----------------------------------------------------------+
1 row in set (0.00 sec)

以上我们授予slave用户所有库所有表的权限.
然后可以在Slave端使用用户slave远程登录一下Master端的数据库,检查是否能远程登录:

root@ubuntu:~# mysql -uslave -p -h192.168.179.128;
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.179.128' (111)

如果遇到以上的问题,在能ping通Master的情况下,请检查两个方面:
1.Master端的防火墙是否关闭

root@ubuntu:~# ufw status;
Status: inactive

2.如果Master端的防火墙已关,则查看Mysql的bind_address变量

mysql> show variables like '%bind_address%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| bind_address  | 127.0.0.1 |
+---------------+-----------+
1 row in set (0.02 sec)

可以发现bind_address 地址只允许本地访问,因此需要修改它,可以讲mysqld.cnf中注释掉bind-address,重启Mysql之后,在Slave端就可以正常连接了。

root@ubuntu:~# vim  /etc/mysql/mysql.conf.d/mysqld.cnf 
[mysqld]
# By default we only accept connections from localhost
#bind-address    = 127.0.0.1
root@ubuntu:~# /etc/init.d/mysql restart
 * Stopping MySQL Community Server 5.7.20
....
 * MySQL Community Server 5.7.20 is stopped
 * Re-starting MySQL Community Server 5.7.20
.........
 * MySQL Community Server 5.7.20 is started

澳门新濠登录网址 2

### 重启mysql服务

2.打开bin log日志

默认情况下Mysql是不开启这个日志的,如下:

mysql> show global variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+

打开bin log 可以通过在mysqld.cnf中指定log-bin路径完成,需要注意的是如果打开bin log 一定要指定server-id,否则重启Mysql会报错。

root@ubuntu:~# vim  /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
......
#bind-address   = 127.0.0.1
server-id=1
log-bin =/var/log/mysql/mysql-bin.log
......

重启Mysql之后,查看bin log日志状态

mysql> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/log/mysql/mysql-bin       |
| log_bin_index                   | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
+---------------------------------+--------------------------------+
5 rows in set (0.03 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.01 sec)

此时可以看到log_bin已经开启了。
我们可以使用mysqlbinlog命令对bin log日志进行操作,这里就不展开讲了。

mysql-bin.000001 是用于主从复制的文件名
619 是日志文件内的最新位置

service mysqld restart

Slave端配置

更改Slave的配置文件

root@ubuntu:~# vim  /etc/mysql/mysql.conf.d/mysqld.cnf 
[mysqld]
.......
server-id=2
relay_log = mysql-relay-bin
.......

需要注意的是server-id一定不要和Master中的server-id一样

mysql> change master to master_host='192.168.179.128',
    -> master_user='slave',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

通过以上的方式指定Master,这种方式的好处是不用重启服务器,也可以动态改变指向的Master。接下来启动Slave端

mysql> start slave;
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.179.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 171028 03:44:54
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

启动Slave的时候发现,Slave_IO_Running:No,在上文的原理中已经介绍了,Slave需要Slave_IO和Slave_SQL线程,但是此时IO线程没有开启。检查Slave状态发现了错误:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

原来是Slave 和Master使用了一样的UUID
Master端UUID

mysql> show variables like '%UUID%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | d7c00798-b94e-11e7-8656-000c29f0d4cf |
+---------------+--------------------------------------+
1 row in set (0.02 sec)

Slave端UUID

mysql> show variables like '%UUID%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | d7c00798-b94e-11e7-8656-000c29f0d4cf |
+---------------+--------------------------------------+
1 row in set (0.02 sec)

可以发现,二者的UUID确实一样,此时可以更改其中一个的UUID,本文中选择修改Slave的UUID,如下:

root@ubuntu:~# vim /var/lib/mysql/auto.cnf 
[auto]
server-uuid=d7c00798-b94e-11e7-8656-000c29f0d4ce

重启Slave端的Mysql后,发现Slave的IO线程也开启了。其中Id:1为SQL线程,Id:2位IO线程

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3706
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3706
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 6
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
3 rows in set (0.00 sec)

以上完成了Slave端的配置

5. slave指向master
登陆从服务器mysql命令行,使用之前创建的用户和master的日志文件及其位置

### 连接Master服务

验证主从是否成功

CHANGE MASTER TO
MASTER_HOST='192.168.2.113',
MASTER_USER='root',
MASTER_PASSWORD='*****',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154;
change master to master_host='192.168.91.128',master_port=3306,master_user='backup',master_password='mysql',master_log_file='master-bin.000001',master_log_pos=0;

1.创建数据库

澳门新濠登录网址 3

### 启动slave服务

Master端创建数据库

mysql> create database test;
Query OK, 1 row affected (0.28 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.02 sec)

6.启动slave
mysql>start slave;

start slave;

Slave 端查询数据库是否同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

可以发现Slave端也创建了test库

7. 查看slave状态
mysql> show slave status\G;
结果中有两个重要数据项:
常见的问题是SQL线程没有正常工作 Slave_SQL_Running: No
通常是两边的数据库不是完全对应的,需要确保master上的库及到目前为止的最新记录都复制到slave上了

### 查看Master服务状态

2.创建表并插入数据

澳门新濠登录网址 4

SHOW MASTER STATUS;

Master端

mysql> use test;
Database changed
mysql> create table user(id int(3),name varchar(10));
Query OK, 0 rows affected (1.84 sec)
mysql> insert into user values(001,'kevin');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+------+-------+
| id   | name  |
+------+-------+
|    1 | kevin |
+------+-------+
1 row in set (0.04 sec)

8.

### 查看Slave服务状态

Salve端

mysql> use test;
Database changed
mysql> create table user(id int(3),name varchar(10));
Query OK, 0 rows affected (1.84 sec)
mysql> insert into user values(001,'kevin');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+------+-------+
| id   | name  |
+------+-------+
|    1 | kevin |
+------+-------+
1 row in set (0.04 sec)

以上可以发现Slave已经自动完成了同步。此时已经完成了Mysql 主从复制。
补充:
如果在同步的过程中发现Slave节点不同步的话,可以通过以下操作完成同步
在Master端查看日志的状态,例如

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1026 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

此时Master的Position指向1026,我们需要在Slave节点中重新指向该位置

mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> change master to master_host='192.168.179.128',
    -> master_user='slave',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001', master_log_pos=1026;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

通过以上的重新指定,则可以重新同步。

在“Slave_SQL_Running_State”中出现了“Fatal error: The slave I/O thread stopsbecause master and slave have equal MySQL server UUIDs; these UUIDs must bedifferent for replication to work.”这个错误。

SHOW slave STATUS \G;

当时以为是主节点服务器的UUID和从节点服务器网卡的UUID重复了,经过查看,发现他们并没有重复,然后重启服务器以后发现仍然没有成功。有点摸不着头脑,所以通过上网搜索。

### 检查server_id是否一样,需要不一样

发现原来是Mysql的一个配置文件auto.cnf里面记录了mysql服务器的uuid。

show variables like 'server_id';
vi /var/lib/mysql/auto.cnf

常见错误

澳门新濠登录网址 5

错误: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

澳门新濠登录网址 6

解决方式: 检查data目录中的auto.cnf中的server-uuid=******************** 是否一样的,要改为不一样

 

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

server_uuid:服务器身份ID。在第一次启动Mysql时,会自动生成一个server_uuid并写入到数据目录下auto.cnf文件里。

原来是这个uuid和主服务器的uuid重复了。(从节点服务器上的mysql安装包是直接从主节点服务器里面拷过来的),然后经过修改auto.cnf文件中的server-uuid,重启mysql服务器,再查看mysql从节点的状态,终于成功了。

 

9.测试
当IO线程和SQL线程都正常后,到master中随意测试下插入、修改、删除操作,同时到slave中检查

1) Slave_IO_Running: Yes

IO线程状态,必须YES

2) Slave_SQL_Running: Yes

SQL线程状态,必须YES