网站首页 文章专栏 部署mysql主从服务

部署mysql主从服务

编辑时间:2022-06-01 15:52:58 作者:苹果 浏览量:1114




什么是mysql主从


所谓mysql主从就是建立两个完全一样的数据库,其中一个为主要使用的数据库,另一个为次要的数据库,一般在企业中,存放比较重要的数据的数据库服务器需要配置主从,这样可以防止因数据库服务器宕机导致数据丢失,还能保证业务量太多、数据太多和访问人数太多时服务的质量(服务器响应速度),还能提供故障切换、读写分离、和备份等等功能。


- 一主一从

- 主主复制:当作备份使用,当主服务器出现故障时,另一个主服务器会自动顶上。

- 一主多从:用来实现读写分离,当写操作较少时,读操作较多时使用,主服务器用来实现写操作,从服务器用来实现读操作。

- 多主一从:用来实现读写分离,当写操作较多时,读操作较少时使用,主服务器用来实现写操作,从服务器用来实现读操作。

-


为何要做主从


1.为实现服务器负载均衡/读写分离做铺垫,提升访问速度


   - 什么是读写分离

     有了主从保持数据一致作为大前提,我们便可以可以分离读写操作,其中Master负责写操作的负载,也就是说一切写的操作都在Master上进行,而读的操作则分摊到Slave上进行。那么读写分离的作用是什么呢?


   - 读写分离的作用

     先说答案:读写分离可以大大提高读取的效率。


     在一般的互联网应用中,经过一些数据调查得出结论,读/写的比例大概在 10:1左右 ,也就是说写操作非常少,大量的数据操作是集中在读的操作(如某些应用,像基金净值预测的网站。其数据的更新都是有管理员更新的,即更新的用户比较少。而查询的用户数 量会非常的多。)

     此时我们可以制作一主多从,因为写操作很少,所以由一个主库负责即可,而大量的读操作则分配给多个从库,这样占据比例最大的读操作的压力就被负载均衡了,因此读效率会得到了极大的提升,另外,还有一个原因就是:

     熟悉DB的研发人员都知道,写操作涉及到锁的问题,不管是行锁还是表锁还是块锁,都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上,而读操作其其他的N个节点上进行,这从另一个方面有效的提高了读的效率,保证了系统的性能及高可用性。


   - 具体做法

     方案一:

     就是主库写,从库读


     方案二:

     主库负责写,还有部分读,从库只负责读,而且是读操作的主力


     即当主服务器比较忙时,部分查询请求会自动发送到从服务器中,以降低主服务器的工作负荷。


     


2. 通过复制实现数据的异地备份,保障数据安全


    可以定期的将数据从主服务器上复制到从服务器上,这实现了数据的异地备份。

   在传统的备份体制下,是将数据备份在本地。此时备份作业与数据库服务器运行在同一台设备上,当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时,将备份数据存放在本地,也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃,此时由于备份文件仍然存放在硬盘上,数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。


   


3. 提高数据库系统的可用性


   数据库复制功能实现了主服务器与从服务器之间数据的同步,增加了数据库系统的可用性。主库宕机后,从库尚可用,即当主服务器出现问题时,数据库管理员可以马上让从服务器作为主服务器,用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员会采用两种手段。


   -   从库临时取代主库,只用来读


     主服务器故障之后,虽然从服务器取代了主服务器的位置,但是对于主服务器可以采取的操作仍然做了一些限制,例如仍然只能够进行数据的查询,而不能够进行数据的更新、删除等操作。这主要是基于从数据的安全性考虑。如现在一些银行系统的升级,在升级的过程中,只能够查询余额而不能够取钱。这是同样的道理。


   - 从库永久取代主库,负责读和写

     从服务器真正变成了主服务器。当从服务器切换为主服务器之后,其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等




主从复制的原理


主库将用户所有的写操作(增删改,查除外)记录到binlog日志当中并且生成一个log dump线程,从库生成I/O和SQL线程,从库的I/O线程向主库的log dump线程以I/O流的形式发送请求,主库的log dump线程收到从库I/O线程的请求后将binlog日志发送给从库,从库I/O线程收到binlog日志后将其写道relay log(中继日志)文件中,再由从库的SQL线程将relay log文件中的日志解析成sql脚本,最后执行生成的sql脚本文件,来实现主从的操作一致,达到最终数据一致的目的。






主从复制配置


- 须知事项:


1. 主从数据库版本一致


2. 保证主从两台数据库所在服务器上的3306端口打开


3. MySQL的主从复制有两种复制方式,分别是异步复制和半同步复制,一般选用异步复制


4. 配置时可以主库停服配置,也可以不停服,推荐使用停服(简单,安全)


5. 配置时可以主库之前,请同步一次数据,确保两个库的数据一样

   


测试的两台机器:


主库:192.168.5.220


从库:192.168.5.223


- 主库配置:


  1. 在主库上创建一个用于复制的账号,并赋予replication slave权限,这里必须 `*.*` 不能指定库授权,因为 replication slave 是全局的



     mysql> CREATE USER 'master_slave'@'192.168.5.223' IDENTIFIED BY 'Yavk@HV!CRZhBP5K';
     mysql> grant replication slave on *.* to 'master_slave'@'192.168.5.223';
     mysql> flush privileges;

     


  2. 修改主库配置文件,开启主库的Binlog,并设置server-id

 

     vim /etc/my.cnf
     [mysqld]
     # 节点ID,确保唯一
     server-id = 1        
     
     #开启mysql的binlog日志功能
     log-bin = mysql-bin
     #控制数据库的binlog刷到磁盘上去 , 0 不控制,性能最好,1每次事物提交都会刷到日志文件中,性能最差,最安全
     sync_binlog = 1     
     #binlog日志格式
     binlog_format = row   
     #binlog过期清理时间
     expire_logs_days = 7
     #binlog每个日志文件大小
     max_binlog_size = 100m
     #binlog缓存大小
     binlog_cache_size = 4m   
     #最大binlog缓存大小
     max_binlog_cache_size= 512m         
     
     #不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,或者 复制黏贴下述配置项,写多行
     binlog-ignore-db=mysql 
     
     # 表中自增字段每次的偏移量
     auto-increment-offset = 1 
     # 表中自增字段每次的自增量
     auto-increment-increment = 1  
     #跳过从库错误
     slave-skip-errors = all



  3. 重启主库

     systemctl restart mysql



  4. 备份主库,备份时锁表保证备份一致


 mysqldump -uroot -p -A -E -R --triggers --triggers   --single-transaction > /tmp/all.sql

   


  5. 将备份文件发送给从库

     scp /tmp/all.sql root@192.168.5.223:/tmp


     


  6. 查看主库的binlog日志名与位置

  

     [root@slave1 ~]# mysql -uroot -p # 登录然后执行
     mysql> show master status;
     +------------------+----------+--------------+------------------+-------------------+
     | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
     +------------------+----------+--------------+------------------+-------------------+
     | mysql-bin.000001 |      156 |              | mysql            |                   |
     +------------------+----------+--------------+------------------+-------------------+
     1 row in set (0.00 sec)

     

     

   


     


- 从库配置:


  1. 在从库服务器上测试连接下主库

      mysql -u master_slave -p  -h 192.168.5.220

 


  2. 将主库数据同步到从库上

      mysql -uroot -p < /tmp/all.sql


  3. 修改从库配置文件,增加server-id,注意这个值是唯一的,要区别于主库和其他从库


     vim /etc/my.cnf
     [mysqld]
     #这个id必须大于主库
     server-id = 2 
     
     relay-log = mysql-relay-bin
     replicate-wild-ignore-table=mysql.%
     replicate-wild-ignore-table=test.%
     replicate-wild-ignore-table=information_schema.%
     
     # 从库也可以开启binlog,但通常关闭
     # log-bin=mysql-bin



  4. 重启从库

     systemctl restart mysqld


  5. 配置从库的复制项


     [root@slave1 ~]# mysql -uroot -p # 登录然后执行
     change master to
     master_host='192.168.5.220',  -- 库服务器的IP
     master_port=3306,              -- 主库端口
     master_user='master_slave', -- 主库用于复制的用户
     master_password='Yavk@HV!CRZhBP5K', -- 密码
     master_log_file='mysql-bin.000001', -- 主库日志名
     master_log_pos=156;



  6. 从库启动slave线程,并检查


     mysql> start slave;
     Query OK, 0 rows affected, 1 warning (0.00 sec)
     
     mysql> show slave status \G
     *************************** 1. row ***************************
                    Slave_IO_State: Connecting to source
                       Master_Host: 192.168.15.220
                       Master_User: master_slave
                       Master_Port: 3306
                     Connect_Retry: 60
                   Master_Log_File: mysql-bin.000001
               Read_Master_Log_Pos: 4
                    Relay_Log_File: msq1-relay-bin.000001
                     Relay_Log_Pos: 4
             Relay_Master_Log_File: mysql-bin.000001
                  Slave_IO_Running: Connecting
                 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: 4
                   Relay_Log_Space: 156
                   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: 
                    Last_SQL_Errno: 0
                    Last_SQL_Error: 
       Replicate_Ignore_Server_Ids: 
                  Master_Server_Id: 0
                       Master_UUID: 
                  Master_Info_File: mysql.slave_master_info
                         SQL_Delay: 0
               SQL_Remaining_Delay: NULL
           Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
                Master_Retry_Count: 86400
                       Master_Bind: 
           Last_IO_Error_Timestamp: 
          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: 
            Master_public_key_path: 
             Get_master_public_key: 0
                 Network_Namespace: 
     1 row in set, 1 warning (0.00 sec)
     
     mysql>



  

测试


  出现异常


  -    Last_IO_Errno: 13117


     Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).


     主库和从库的server-id一样了,刚刚配置文件中的server-id 配置的的确是不同的。

    mysql> show variables like 'server_id';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 1     |
    +---------------+-------+
    1 row in set (0.22 sec)
    ###在数据库里看居然是一样的
    mysql>set global server_id=2; #此处的数值和my.cnf里设置的一样就行

    

 - Last_SQL_Errno: 13124

    Last_SQL_Error: Slave failed to initialize relay log info structure from the repository


    之前的日志仍然保留着slave的信息


    mysql> reset slave;
    Query OK, 0 rows affected, 1 warning (0.32 sec)
    
    ##重新调整一次配置项 change master to...
    
    mysql> start slave;
    Query OK, 0 rows affected, 1 warning (0.04 sec)



开启GTID复制模式


        MySQL 5.6 的新特性之一,全局事务标识符(GTID)是创建的唯一标识符,并与在源(主)服务器上提交的每个事务相关联。此标识符不但是唯一的,而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系 。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一,在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单,以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次,避免重复执行导致数据混乱或者主从不一致。


    在主从my.conf中添加配置



    gtid_mode=ON     #开启GTID复制模式    
    enforce_gtid_consistency=ON      #强制GTID一致性
    slave-parallel-type=LOGICAL_CLOCK
    slave-parallel-workers=16
    master_info_repository=TABLE     #将master-info信息记录到表中
    relay_log_info_repository=TABLE    #将relay-info信息记录到表中
    relay_log_recovery=ON



    出自:何冰华个人网站

    地址:http://www.hebinghua.com/

    转载请注明出处


来说两句吧
最新评论