mysql主从原理与配置

最新推荐文章于 2024-04-29 00:04:44 发布

zlb-linux

最新推荐文章于 2024-04-29 00:04:44 发布

阅读量406

点赞数

文章标签： mysql主从的配置 mysql主从的命令 mysql主从的监控

本文链接：https://blog.csdn.net/weixin_44433165/article/details/87971255

版权

mysql主从

用主从的原因：
用一台数据库存放数据，若此数据库服务器宕机了数据会丢失
业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量

主从的作用

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从的形式

一主一从
主主复制
一主多从，拓展了系统读取性能，因为读是在从库中读取的
多主一从
联级复制

主从复制过程

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

主从复制配置

步骤：
确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件

示例配置

zlb10是主库
zlb9是从库


[root@zlb10 ~]# mysql -e 'show databases;'   //查看主库中有哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
| zlb                |
| zlb1               |
+--------------------+
[root@zlb10 ~]# 


[root@zlb9 ~]# mysql -e 'show databases;'    //查看从库中的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@zlb9 ~]# 

首先要全备主库，但是之前要锁死主库，避免别人在备份期间写入数据导致数据不一致

mysql> flush tables with read lock;   //在主库上进行，记住此锁表的终端必须在备份完成以后才能退出
Query OK, 0 rows affected (0.00 sec)

[root@zlb10 ~]# mysqldump --all-databases > all001.sql   //备份主库
[root@zlb10 ~]# ls
1.20190221.sql  3.2019.sql  anaconda-ks.cfg  httpd-2.4.34          pass
2.20190221.sql  all001.sql  dead.letter      httpd-2.4.34.tar.bz2  zlb.sh
[root@zlb10 ~]# 

[root@zlb10 ~]# scp all001.sql root@192.168.192.9:/root/
root@192.168.192.9's password: 
all001.sql                                                         100% 5962KB  44.9MB/s   00:00    
[root@zlb10 ~]# 

//现在可以退出锁表状态，这里直接退出交互式界面就可以了
mysql> quit
Bye
[root@zlb10 ~]# 

[root@zlb9 ~]# mysql < all001.sql    //在从库上恢复主库的备份，保证与主库一致
[root@zlb9 ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
| zlb                |
| zlb1               |
+--------------------+
[root@zlb9 ~]# 


在主数据库中创建一个同步账号授权给从数据库使用
mysql> 
mysql> create user 'repl004'@'192.168.192.9' identified by 'zlb';   //这里的@后面要加从库的IP地址
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl004'@'192.168.192.9';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

配置主数据库


[root@zlb10 ~]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve


log-bin=mysql-bin      //启用binlog日志
server-id=1     //数据库服务器唯一标识符，注意主库的必须比从库小

symbolic-links=0

log-error=/var/log/mysqld.log


[root@zlb10 ~]# service mysqld restart      //重启服务查看端口
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@zlb10 ~]# ss -antl
State       Recv-Q Send-Q     Local Address:Port                    Peer Address:Port              
LISTEN      0      128                    *:22                                 *:*                  
LISTEN      0      128                   :::22                                :::*                  
LISTEN      0      80                    :::3306                              :::*                  
[root@zlb10 ~]# 

mysql> show master status;   //查看主库的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

配置从数据库

[root@zlb9 ~]# vim /etc/my.cnf

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

server-id=2     
relay-log=mysql-relay-bin     //启用中继日志relay-log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
~                       

[root@zlb9 ~]# systemctl restart mysqld    //重启从库的服务
[root@zlb9 ~]# ss -antl
State       Recv-Q Send-Q     Local Address:Port                    Peer Address:Port              
LISTEN      0      128                    *:22                                 *:*                  
LISTEN      0      100            127.0.0.1:25                                 *:*                  
LISTEN      0      128                   :::22                                :::*                  
LISTEN      0      100                  ::1:25                                :::*                  
LISTEN      0      80                    :::3306                              :::*                  
[root@zlb9 ~]# 

//在从库上配置并启动主从复制。
**注意，这里的MySQL登录密码变了，是在主库设置的密码，注意坑**
[root@zlb9 ~]# mysql -uroot -pzlb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.25 MySQL Community Server (GPL)

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

Oracle is 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> 
 

mysql> change master to
    -> master_host='192.168.192.10',
    -> master_user='repl004',
    -> master_password='zlb',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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


查看从服务器的状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.10
                  Master_User: repl004
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes           //这里的2行必须要为yes才可以做接下来的
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table:

测试运行


//在从库中查看一下
mysql> select * from test0;
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | haha |   12 |
|  6 | xixi |   23 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> 


//在主服务器上添加数据
mysql> insert test0 values(1,'hehe',18),(2,'lala',21);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test0;
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | haha |   12 |
|  6 | xixi |   23 |
|  1 | hehe |   18 |
|  2 | lala |   21 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> 


//在从服务器上可以看到数据同步过来了
mysql> select * from test0;
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | haha |   12 |
|  6 | xixi |   23 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from test0;
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | haha |   12 |
|  6 | xixi |   23 |
|  1 | hehe |   18 |
|  2 | lala |   21 |
+----+------+------+
4 rows in set (0.00 sec)

mysql>

通过zabbix监控主从是否完成

通过脚本来实现
[root@zlb9 ~]# vim check001.sh 

#!/bin/bash

a=$(mysql -e 'show slave status \G;' | grep Slave_IO_Running: | awk -F : '{print $2}')
b=$(mysql -e 'show slave status \G' | grep Slave_SQL_Running: | awk -F: '{print $2}')
if [ $a == Yes ] && [ $b == Yes ]; then
        echo 0          
else
        echo 1
fi
~

在这里插入图片描述

当出现问题的时候，就会报错并发送邮件在这里插入图片描述

mysql主从的POS判定

在配置文件中添加的内容（客户端）
[root@zlb9 ~]# tail -5 /usr/local/etc/zabbix_agentd.conf
UserParameter=check_apache,/scripts//check_process.sh httpd
UserParameter=check_httpd_error,python /scripts/log.py /var/log/httpd/error_log
UserParameter=test002,/scripts/test002.sh httpd
UserParameter=mysqlzc,/root/check001.sh          //记住这里的路径在root下，要把脚本放在root下并且修改root的权限为755，完成之后要重启服务
UserParameter=poscheck,/root/poscheck001.sh     
[root@zlb9 ~]# ll 
total 5980
-rw-r--r--. 1 root   root   6105418 Feb 28 00:45 all002.sql
-rw-------. 1 root   root      1450 Jan 13 16:24 anaconda-ks.cfg
-rwxr-xr-x. 1 zabbix zabbix     253 Mar  1 00:11 check001.sh
-rw-r--r--. 1 root   root         5 Feb 28 04:04 enen
-rwxr-xr-x. 1 zabbix zabbix     242 Mar  1 01:11 poscheck001.sh
[root@zlb9 ~]# 
[root@zlb9 ~]# ll -d /root/
drwxr-xr-x. 3 root root 271 Mar  1 02:45 /root/
[root@zlb9 ~]#

配置的监控项和触发器

在这里插入图片描述

通过上面的监控就可以很清楚的看到主从同步是否出现问题，并判定能够同步，以及同步是否出现了延迟。

GTID的工作原理与配置

GTID的概念

GTID是一个基于原始mysql服务器生成的一个已经被成功执行的全局事务ID，它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器器上唯一，在所有存在主从关系的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠

GTID的原理

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描

GTID的优点

一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次
GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机

GTID的限制

1.不支持非事务引擎
2.不支持create table … select 语句复制(主库直接报错)
原理：（会生成两个sql，一个是DDL创建表SQL，一个是insert into 插入数据的sql。
由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID ）
3.不允许一个SQL同时更新一个事务引擎表和非事务引擎表
4.在一个复制组中，必须要求统一开启GTID或者是关闭GTID
5.开启GTID需要重启（5.7除外）
6.开启GTID后，就不再使用原来的传统复制方式
7.对于create temporary table 和 drop temporary table语句不支持
8.不支持sql_slave_skip_counter

GTID的基本配置

在主库中的配置文件

[root@zlb10 ~]# tail -20 /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve


log-bin=mysql-bin 
server-id=10

log-error=/var/log/mysqld.log



gtid-mode = on
enforce-gtid-consistency = on

[root@zlb10 ~]#

在从库中的配置文件

[root@zlb9 ~]# tail -15 /etc/my.cnf
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=15 
relay-log=mysql-relay-bin 

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

gtid-mode = on
enforce-gtid-consistency = true
port = 3306
[root@zlb9 ~]#



//在从库上的配置
mysql> change master to
    -> master_host='192.168.192.10',
    -> master_user='repl004',
    -> master_password='zlb',
    -> master_auto_position=1;

查看GTID的同步

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.10
                  Master_User: repl004
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 177023
               Relay_Log_File: mysql-relay-bin.000011
                Relay_Log_Pos: 177236
        Relay_Master_Log_File: mysql-bin.000018
             Slave_IO_Running: Yes        //两个线程的状态都变成YES了
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table:

//在主库上面添加
mysql> insert test0 value(7,'GTID',12);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test0;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  5 | haha   |   12 |
|  6 | xixi   |   23 |
|  1 | hehe   |   18 |
|  2 | lala   |   21 |
|  3 | ennnnn |   19 |
|  4 | qqqqq  |   21 |
|  7 | GTID   |   12 |
+----+--------+------+
7 rows in set (0.00 sec)

mysql>

//在从库中添加
mysql> select * from test0;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  5 | haha   |   12 |
|  6 | xixi   |   23 |
|  1 | hehe   |   18 |
|  2 | lala   |   21 |
|  3 | ennnnn |   19 |
|  4 | qqqqq  |   21 |
|  7 | GTID   |   12 |
+----+--------+------+
7 rows in set (0.00 sec)

mysql>

zlb-linux

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql主从原理与配置

mysql主从用主从的原因：用一台数据库存放数据，若此数据库服务器宕机了数据会丢失业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量主从的作用实时灾备，用于故障切换读写分离，提供查询服务备份，避免影响业务主从的形式一主一从主主复制一主多从，拓展了系统读取性能，因为读是在从库中读取的多主一从联级复制主从复制过程主库将所有的写操作记录到binlog...
复制链接

扫一扫