主从复制,配置Mysql主从服务实现实例

布局Mysql主从服务实现实例

前言

创设一个Mysql分布式集群须要对数据库的原理有较深的认知,而深邃复杂的数据库原理往往让广大读者中途扬弃。本类别尝试用最简便的言语介绍Mysql布满式集群涉及到的原理,并结成实践的措施给读者介绍怎么样营造二个Mysql布满式集群。

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

目录

1.Mysql布满式集群(意气风发)主从复制
2.Mysql布满式集群(二)主主复制和高可用

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

主从复制

主从复制是为着变成贰个和主数据库后生可畏致的数据库,当主数据数据变动的时候,从数据库自动保持和主数据库生龙活虎致的更动。主从复制主要利用于读写抽离,数据库备份

### 在主数据库上成立同步账号

试验境况:

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

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中;

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

Master端配置

基于上述的规律能够,Slave端要求读取Master端的bin
log日志,因而需求再Master端达成以下专门的学业:

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

### 重启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日志进行操作,这里就不进行讲了。

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 STATUSG
*************************** 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 processlistG
*************************** 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端的配置

### 连接Master服务

表明主从是还是不是中标

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.开立数据库

### 启动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)
start slave;

Slave 端查询数据库是不是同步

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

能够开掘Slave端也开创了test库

### 查看Master服务情况

2.开立表并插入数据

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)

### 查看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)

通过以上的双重钦点,则能够重新联合。

SHOW slave STATUS G;

### 检查server_id是或不是生龙活虎致,需求不均等

show variables like 'server_id';

分布错误

错误: 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.

减轻情势:
检查data目录中的auto.cnf中的server-uuid=********************
是不是后生可畏致的,要改为不相近

谢谢阅读,希望能支援到我们,谢谢我们对本站的扶助!

发表评论

电子邮件地址不会被公开。 必填项已用*标注