Mysql中主从复制的原理、及配置过程

1.什么是主从复制?

原理:主从分离,什么意思呢?我们不妨画个图看看。如图1所示: 

冷暖自知一抹茶ck

主从复制原理: 

    写进主服务器中的数据在从服务器中原模原样的也有。

实现原理

     在主服务器中执行例如:insert、update等语句的时候把执行的这些语句的所有操作都给记录到一个叫binlog(二进制日志)中,记录下来之后呢,那么这个从服务器来读这个主服务器的binlog,在这里要特别注意一下,这个主服务器直接把binlog给从服务器是无法直接使用的,这个时候,从服务器把binlog读过来之后首先分析一下,形成能够为自己所用的relaylog然后再施加到自己身上,这样整个流程OK了!所以我们根据以上原理可以分析出以下几点:

1、主服务器要配置binlog

2、从服务器要配置replaylog

3、因为binlog是比较敏感的,那么从服务器如何拥有权限读取master呢?

答案:使用授权方式,主服务器要授予从服务器账号。

4、从服务器使用账号连接主服务器。


2.准备工作:

     预备两台服务器,我这里使用虚拟机安装了两个Centos7_64位操作系统,并分别在两台服务器上安装mysql。我的IP地址分别为:192.168.216.157/192.168.216.158,这里我定义157为主服务器,158为从服务器。 

    

首先,我们编辑主服务器中mysql配置文件。

在编辑mysql配置文件之前你要考虑,在一个机房里,一主多从,或者是主主复制或者是被动模式的主主复制等多种情况,那么这多台服务器之间相互他们日志复制来binlog,复制来复制去会不会乱了呢?真有可能会乱,那么碰到这样情况你怎么办?因此,我们要给每一台服务器起一个独特的server-id。那么这个id呢,正常情况下做主从服务器都会在同一个局域网内,因同意个局域网内IP前三段都一样,只有最后一段不一样,所以我们一般都是拿ip的末尾一段作为server-id,当然这不是硬性的规定,只是习惯而已。因此我们开始配置my.cnf文件。

#编辑主服务器 (192.168.216.157)的mysql配置文件
[root@localhost ~]# vim /etc/my.cnf
#给服务器骑一个独特的id
server-id=157
#声明二进制日志的文件为mysql-bin
log-bin=mysql-bin
max_binlog_size = 100M
expire_logs_days = 7
#binary logging format - mixed recommended
#二进制日志的格式
binlog_format=mixed

冷暖自知一抹茶ck


对于配置中主binlog-format=row/statement的解释:

statement:记录执行语句,例如update...

row:记录的是磁盘变化

到底哪个好?

update age=age+1 where id=3;//语句长而磁盘变化少,宜用row

update set money=money+1000;//语句短而变化多,宜用statement

如果以上两个你拿不准,请使用mixed,由系统根据语句来决定。


进入/data/mysql目录并查看以前mysql日志信息,删除以前的log主服务器日志信息 

[root@localhost ~]# cd /data/mysql/
[root@localhost mysql]# rm -rf ./mysql-bin.*
如下图所示:这里我们不删除,先保存副本迁移到其他目录,以防修改错误

冷暖自知一抹茶ck


至此,我们的主服务器就配置好了,接下来我们来配置一下从服务器...

首先我们需要配置/data/mysql目录下my.cnf文件。

#编辑从服务器 (192.168.216.158)的mysql配置文件
[root@localhost ~]# vim /etc/my.cnf
#给服务器骑一个独特的id
server-id=158
#声明二进制日志的文件为mysql-bin
log-bin=mysql-bin
max_binlog_size = 100M
expire_logs_days = 7
#binary logging format - mixed recommended
#二进制日志的格式
binlog_format=mixed
relay-log=mysql-relay

冷暖自知一抹茶ck


同样,进入/data/mysql目录并查看以前mysql日志信息,删除以前的log主服务器日志信息 

[root@localhost mysql]# cd /data/mysql/
[root@localhost mysql]# rm -rf ./mysql-bin.*
如下图所示:这里我们不删除,先保存副本迁移到其他目录,以防修改错误

冷暖自知一抹茶ck


更改了mysql配置文件,所以需要    分别重新启动    主从服务器的mysql服务 

[root@localhost mysql]# systemctl restart mysqld.service            #主服务器
[root@localhost mysql]# systemctl restart mysqld.service            #从服务器


以上主从服务器相关配置已经完成,那么我们开始正式测试这个功能! 

首先登陆主服务器,登陆mysql  ( 因为我的mysql已经写到了环境变量里面直接登录就好 )
[root@localhost mysql]# mysql -u root -p
Enter password:                                                
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.11-MariaDB-log Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show variables like 'server_id';            #配置文件修改server-id成功
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 157   |
+---------------+-------+
1 row in set (0.00 sec)

用同样的方式登陆从服务器

[root@localhost mysql]# mysql -uroot -p
Enter password:                                                
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.11-MariaDB-log Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show variables like 'server_id';            #配置文件修改server-id成功
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 158   |
+---------------+-------+
1 row in set (0.00 sec)

在主服务器上为从服务器账号授予读取权限

授权test用户拥有testDB数据库的所有权限(某个数据库的所有权限):

MariaDB [(none)]> grant replication client,replication slave on *.* to ck@'192.168.216.158' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

在从服务器通过语句指定要复制的主服务器(注意,可以一主多从,不可一丛多主),在使用下面语句之前,我们需要只要当前主服务器日志文件走到哪了,使用show master status查看. 

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      342 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在从服务器执行下面语句

MariaDB [(none)]> change master to
    -> master_host='192.168.216.157',
    -> master_user='ck',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=342;
Query OK, 0 rows affected (0.03 sec)

在从服务器上查看两台服务器状态有么有打通

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.216.157
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 342
               Relay_Log_File: mysql-relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 342
              Relay_Log_Space: 256
              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: 0
                Last_IO_Error:

启动从服务器功能,再次查看状态。

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.216.157
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 342
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            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: 342
              Relay_Log_Space: 860
              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:


3.实战测试

分别查看主从服务器都有哪些数据库

主服务器:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
从服务器:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

由此,我们先在主服务器上创建数据库test,并再次查看主服务器有哪些数据库。

主服务器:
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| test               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
从服务器:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| test               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>

看见没有?从服务器是不是多了一个demo数据库?

不信咱再测试一下...

我们使用主服务器的test,然后建立一张student表,并插入两条数据,看看从服务器是不是自动的为我们新建sutent表,表中并包含我们刚从主服务器中插入的两条数据。我们测试一下:

主服务器:
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table stutent
    -> (
    -> id int primary key AUTO_INCREMENT,
    -> name varchar(20),
    -> age varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)
MariaDB [test]> insert into stutent  values(1,'徐守威1','24');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> insert into stutent values(2,'徐守威2','23');
Query OK, 1 row affected (0.01 sec)
从服务器:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.02 sec)
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stutent        |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from stutent;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | 徐守威1    | 24   |
|  2 | 徐守威2    | 23   |
+----+------------+------+
2 rows in set (0.01 sec)
MariaDB [test]>

处理完后,我们紧接着查看一下从服务器是不是多了一张表,并且里面有两条数据。

好了,mysql主从复制的问题到此就结束了!


冷暖自知一抹茶ck
请先登录后发表评论
  • 最新评论
  • 总共0条评论