当前位置:天才代写 > tutorial > 数据库教程 > Mysql进阶之Mysql主从复制和binlog日志(下)

Mysql进阶之Mysql主从复制和binlog日志(下)

2021-02-17 12:00 星期三 所属: 数据库教程 浏览:23

主从复制实际操作

两部版本号同样的mysql连接点

从服务器 204.175.124.51
主网络服务器 54.22.37.2
总体目标:同歩主网络服务器的hst数据库查询,但不对别的数据库同步

# 先查询2个网络服务器是不是都打开了binlog日志
show variables like “%log_bin%”;

================== 对主网络服务器的实际操作 ======================

# 先将hst库备份数据(在开展同歩以前,从连接点要插装式入hst库,不然在主连接点有hst而从连接点沒有hst的状况下,主连接点对hst开展实际操作,会造成从连接点同步控制不成功,而且终断以后的同步控制)

# 对主网络服务器打开binlog

server-id=100   # 特定server id
log-bin=/var/lib/mysql/mysql-bin    # 特定名字
binlog-do-db=hst        # 特定只对hst数据备份

重新启动服务项目

# 查询是不是打开binlog日志

show variables like "%log_bin%";

結果以下:
 --------------------------------- -------------------------------- 
| Variable_name                   | Value                          |
 --------------------------------- -------------------------------- 
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
 --------------------------------- -------------------------------- 

結果已打开

# 对客户开展受权(假如客户沒有建立会全自动建立)

grant replication slave on *.* to "cjq"@"204.175.124.51" identified by "xxxxx";

这儿留意下,有些人很有可能会那样写来表明只同歩hst这一数据库查询:

grant replication slave on hst.* to "cjq"@"204.175.124.51" identified by "xxxxx";

結果会出错 Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

因此 只有在环境变量限制要同歩的库名才行。

自然,主连接点可以不设定binlog-do-db选择项,可是从连接点就需要设定replicate-do-db=hst ,表明从连接点总是对主连接点的hst库开展同歩
何时会用replicate-do-db而不容易用binlog-do-db:
倘若有一台主连接点A,两部从连接点:B,C; B要同歩A的d1库,C要同歩A的d2库,这时也不适合用binlog-do-db,而合适用replicate-do-db。

# 查询主连接点的二进制日志情况

show master status    
 ------------------ ---------- -------------- ------------------ ------------------- 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 ------------------ ---------- -------------- ------------------ ------------------- 
| mysql-bin.000002 |      154 | hst          |                  |                   |
 ------------------ ---------- -------------- ------------------ ------------------- 

纪录下 File 和 Position 这两个字段名的值,待会儿在从服务器虚拟机偏向主网络服务器的情况下会采用。

================== 对从服务器的实际操作 ======================

最先,试着联接主网络服务器

mysql -h54.22.37.2 -ucjq -pxxxxx

假如联接没取得成功,表明主网络服务器限制了联接的IP只有时主网络服务器该设备,或是是由于开过服务器防火墙。把主连接点和从连接点的服务器防火墙关掉,在主服务器的配置文档的[mysqld]下改动bind-address主要参数为从服务器IP和127.0.0.1就可以,还记得重新启动主网络服务器。
而且再次查询show master status,纪录下File和Position字段名,由于每重新启动一次服务项目,便会转化成一个新的binlog日志。

撤出联接

联接从连接点该设备的手机客户端

# 导进hst库

# 查询是不是打开二进制日志:

show variables like "%log_bin%";
 --------------------------------- ------- 
| Variable_name                   | Value |
 --------------------------------- ------- 
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
 --------------------------------- ------- 

这儿已打开,假如没打开则到环境变量加上
server-id = 99      # 和上边的server-id不一样就可以
log-bin= 二进制日志的途径和名字
随后重新启动服务项目

# 配备同歩日志:在环境变量的[mysqld]中加上:

relay-log=/var/lib/mysql/relay-bin               #同歩日志文档的途径
relay-log-index=/var/lib/mysql/relay-bin.index   #同歩日志的数据库索引文档
slave-skip-errors=all                            #绕过同歩sql时产生的出错,以避免 因一两句sql不正确造成同歩终断

重新启动服务项目

# 从服务器偏向要同歩的主网络服务器

change master to master_host="54.22.37.2",master_user="cjq",master_password="xxxxx",master_log_file="mysql-bin.000002",master_log_pos=154;

# 运行从连接点并查询从连接点的同歩是不是早已打开

start slave

show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 54.22.37.2
                  Master_User: cjq
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000002
             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: 154
              Relay_Log_Space: 695
              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: 0
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: 86
1 row in set (0.00 sec)

假如下边二项:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
全是Yes那麼同歩取得成功

如果有No,同歩不成功,它是能够查询字段名Last_IO_Error 和 Last_SQL_Error
Last_IO_Error一般是无法连接,受权难题等出错
Last_SQL_Error一般是在主网络服务器实行删改改实际操作时,从服务器没法同歩句子的出错

================== 主从复制的留意点和普遍出错 ======================

1. 远程控制数据库连接不成功
查验方法: 在主连接点受权客户给从连接点以后,在从连接点网络服务器上试着远程桌面连接一下主连接点: mysql -h主连接点ip -u受权登录名 -p登陆密码
假如联接取得成功则没什么问题,不然就有什么问题

连接失败关键缘故有 : 
a.主连接点的环境变量设定了 bind-address = 127.0.0.1 只容许服务器联接;
b.主连接点或是从连接点设定服务器防火墙

解决方案:
a.在主连接点和从连接点的环境变量的[mysqld]下改动 bind-address=0.0.0.0 容许一切ip联接;或是 bind-address = 127.0.0.1 别的ip 那样就只容许该设备和特定的别的ip联接本网络服务器的mysql
b.将主从关系网络服务器的服务器防火墙都关掉

2. 主从关系连接点的mysql版本号不一样造成同歩sql不成功

查验方法和缘故:
a.最先,同歩sql不成功,我们可以在从连接点根据 show slave status\G; 查询有关不成功信息内容。

会见到 Slave_SQL_Running: No
随后寻找 Last_SQL_Error 字段名,会表明不正确缘故。
假如该字段名表明的不正确缘故不详尽,能够查询mysql的不正确日志查看详细的缘故。

b.mysql版本号不一样的状况下,并不是不可以开展主从复制;
这时 高版本号做为从连接点,低版做为主连接点能够开展同歩;相反不好。
缘故是 不一样版本号的mysql,其mysqlbinlog的版本号不一样,5.5及下列,其mysqlbinlog的版本号为3.3,之上版本号为3.4;
高版本号的mysqlbinlog能够兼容低版的mysqlbinlog指令,换句话说,高版本号的mysqlbinlog指令能够分析低版mysql的二进制日志,可是相反低版的mysqlbinlog指令不可以分析高版本号mysql转化成的二进制日志。

而我们知道,主从复制的基本原理就是以连接点同歩主连接点的二进制日志,随后实行所同歩的二进制日志中删改改指令;
当高版本号做为从连接点,高版本号的mysqlbinlog指令就可以分析低版mysql转化成的二进制日志并实行里边的指令进而同歩。
当低版做为主连接点,那麼便会出错,便是由于没法分析高版本号的日志內容。

倘若 低版做为从连接点,高版本号做为主连接点,那麼查询不正确日志会发觉出错:
Error in Log_event::read_log_event(): ‘Found invalid event in binary log’, data_len: xx, event_type: xx

便是由于mysqlbinlog的版本号不一样。

因此 mysql版本号不一样造成同歩sql不成功的实质缘故是:mysqlbinlog指令版本号不一样;像mysql5.6和5.7版本号的mysqlbinlog的版本号全是3.4 ,那麼也不会出現这个问题。

查询mysqlbinlog指令的版本号:
mysqlbinlog -V

c.在主从关系连接点不一致的状况下,强烈推荐应用mixed文件格式的二进制日志文档,在主从关系连接点的环境变量下另外设定 binlog_format=mixed;那样能够避免 由于二进制日志文件格式不一样造成的同歩sql不成功

自然,那样做依然不可以处理mysql版本号不一样造成同歩sql不成功这个问题

解决方案:
a.高版本号做为从连接点,低版做为主连接点
b.假如非得高版本号做为主连接点,低版做为从连接点,只有升級从连接点的mysql版本号。

结果:
主从关系连接点尽量全是同样版本号的mysql

3.一部分同歩的难题

假如从连接点只同歩主连接点的某一个库或是一部分库,能够应用一下四个配备:
binlog-do-db=库名            # 在主连接点配备,表明只纪录特定库的实际操作到二进制日志,結果是只同歩该库
binlog-ignore-db=库名        # 在主连接点配备,表明不纪录特定库的实际操作到二进制日志,結果不是同歩该库
replicate-do-db=库名         # 在从连接点配备,表明只同歩某库的binlog日志,結果是只同歩该库
replicate-ignore-db=库名     # 在从连接点配备,表明不同歩某库的binlog日志,結果不是同歩该库

replicate-do-db和binlog-do-db只需配备在其中一个就可以,不用2个网络服务器另外配备。提议视作replicate-do_db

假如要特定好几个数据库查询,只需反复设定这一选择项就可以。

4.主从关系连接点数据信息不一致难题
主从复制目地是让主网络服务器只做读实际操作,让从服务器做读实际操作,缓解数据库查询的压力。

因此 从服务器自身不是应当载入数据信息的,因此 能够在从服务器的配备中载入 read-only主要参数

留意: 
a.read_only=1写保护方式,限制的是单用户开展数据信息改动的实际操作,但不容易限制具备super管理权限的客户的数据信息改动实际操作 
b.一旦设定了read_only,全部库的全部表都不可以写,因此 针对只同歩一部分库或是一个库的状况不适合
c.read_only不容易危害主从关系连接点的同歩

5.从服务器因同歩sql不成功而停止运行

举个事例,有一种状况:
主连接点:A
从连接点:B
同歩的库:d1

如今 A连接点有库d1,d1有一个表t1
这时,B连接点开展同歩A连接点
随后,A连接点往d1.t1载入一条数据信息,B连接点同歩不成功,查询show slave status
缘故是,B都还没创建数据库d1

这个时候,因为上边的一个不正确,造成A和B立即停止了同歩,假如想再同歩,就需要再次实行 change master to 指令
而因为A作出了实际操作,因此 A的二进制日志作出了更改,因此 master_log_pos 不一样了,因此想再同歩要作出下列指令

在A连接点: show master status;    并纪录下 File和Position字段名
在B连接点: stop slave;   # 务必先终止slave
         change master to master_host=”A的ip”,master_password=”xxx”,master_user=”user”,master_log_file=”xxx”,master_log_pos=”xxx”;
         start slave;  
         
如今A和B又同歩了,这个时候,B连接点自身建立了d1库,殊不知还没有等B自身建立t1表,A连接点又往t1表格中载入一条数据信息。
这时 A,B的同歩再一次不成功,缘故非常简单,B沒有t1表,怎能在t1表格中插进数据信息呢。
因此B又要再反复一次上边的流程才可以将A,B再同歩起來。

为了更好地处理这个问题,能够在从连接点应用 slave-skip-errors 配备 
slave-skip-errors=all

表明从服务器绕过同歩不正确,那样就不容易由于一两句不正确造成同歩断掉。

设定了slave-skip-errors=all 以后,即便B沒有t1表,A往t1表插进数据信息,B也不会有出错,只是转化成提醒纪录在不正确日志中。

自然,最好是的解决是,一开始先将A的d1备份数据到B以后,再对AB开展同歩。
另外还要在环境变量中设定slave-skip-errors=all,重新启动服务项目

同时进行

6. 主连接点或是从连接点重新启动,是不是要再次实行change master to和start slave指令?
从连接点重新启动会全自动实行change master to 和start slave 因此 无需自身实行

主连接点重新启动得话,我们知道,每一次重新启动便会转化成一个新的binlog文件,而且应用这一新的binlog文件。
那麼是否从连接点要再次实行change master to 来精准定位到主连接点这一新的binlog文件呢
也无需,主连接点重新启动时,主从关系连接点的联接断掉,查询show slave status;
Slave_IO_Running: Connecting
表明从连接点正在连接主连接点。

当主连接点重新启动进行,从连接点会再次偏向主连接点的新的binlog文件和其Position

7.二进制日志文档太多过大该怎么办
expire_logs_days        = 10    # 10天前的二进制日志全自动删掉
max_binlog_size   = 100M        # 超出100M就自动生成新的二进制日志

它是对于二进制日志而不是对于同歩日志relay-bin的,relay-bin会自动清理的。

8.start slave日报错:
Slave failed to initialize relay log info structure from the repository

它是因为以前开展过主从复制,沒有清整洁;

reset slave 

随后再start slave就可以。

主主拷贝实际操作

在上面主从复制的基本以上再开展主主拷贝

主网络服务器1 204.175.124.51
主网络服务器2 54.22.37.2
总体目标:同歩主网络服务器的test数据库查询(test库在204.175.124.51上),但不对别的数据库同步

# 已经知道两部连接点都早已打开binlog,并且在上一个案例中主连接点1时主连接点2的从连接点;如今只需将54.22.37.2也变为主连接点1的从连接点,那麼就完成了主主拷贝

=============== 主连接点1的实际操作 ================

# 改动环境变量

server-id=1
log-bin=/var/lib/mysql/mysql-bin
binlog_format=mixed
relay-log=/var/lib/mysql/relay-bin
relay-log-index=/var/lib/mysql/relay-bin.index
expire_logs_days=7
max_binlog_size=100M
slave-skip-errors=all
replicate-do-db=hst             # 上边这种配备全是上一个案例中做主从复制时设定的

replicate-do-db=test            # 这一时作主主拷贝时加上的,连接点1只同歩连接点2的test库

# 受权客户给连接点2:

grant replication slave on *.* to "repl"@"54.22.37.2" identified by "xxxxx"

# 重新启动服务项目

# 再进到手机客户端

show master status;  纪录下File和position字段名
 ------------------ ---------- -------------- ------------------ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 ------------------ ---------- -------------- ------------------ 
| mysql-bin.000008 |     4498 |              |                  |
 ------------------ ---------- -------------- ------------------ 

=============== 主连接点2的实际操作 ================

# 试着远程桌面连接连接点1,联接取得成功再做下边的实际操作

# 先将test库备份数据到主连接点2上边# 改动主连接点2的环境变量,运行同歩日志

server-id=86                                    # server-id
expire_logs_days    = 10                        # 二进制日志到期日数
max_binlog_size   = 100M                        # 二进制日志较大 的尺寸
log-bin=/var/lib/mysql/mysql-bin                # 打开二进制日志,供从连接点拷贝
binlog_format=mixed                             # 二进制日志应用statement和row的混合模式

replicate-do-db=test                            # 连接点2做为从连接点只同歩test库
relay-log=/var/lib/mysql/relay-bin              # 打开同歩日志
relay-log-index=/var/lib/mysql/relay-bin.index  # 特定同歩日志的数据库索引文档
slave-skip-errors=all                           # 绕过同歩全过程中的sql不正确

# 重新启动服务项目

# 进到手机客户端

show master status;  纪录下File和position字段名
 ------------------ ---------- -------------- ------------------ ------------------- 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 ------------------ ---------- -------------- ------------------ ------------------- 
| mysql-bin.000006 |   997126 |              |                  |                   |
 ------------------ ---------- -------------- ------------------ ------------------- 

# 逐渐同歩连接点1

stop slave;
reset slave;
change master to master_host="204.175.124.51",master_user="repl",master_password="xxxxx",master_log_file="mysql-bin.000006",master_log_pos=4498;
start slave 

# 查询slave情况

show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 204.175.124.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 4498
               Relay_Log_File: relay-bin.000006
                Relay_Log_Pos: 4695
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 4498
              Relay_Log_Space: 5195
              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: 0
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: 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:
     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)

====================== 返回连接点1 =====================

以前设定了连接点1配备(加上了一个要同歩的库test),应当无需再对连接点1实行change master to 指令,由于重新启动连接点1服务项目的情况下应当会全自动再次同歩连接点2的,为了更好地保证连接点1再次同歩了连接点2,能够查询

show slave status;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test,hst

假如Slave_IO_Running,Slave_SQL_Running全是Yes,并且Replicate_Do_DB为test,hst,那表明早已同歩好啦
无需再同歩了。否则的话还得再同歩一次。

下面开展检测,便是在连接点1和连接点2都插进一些数据信息看一下是否都是有相对转变 。

这时 针对test库时主主拷贝,针对hst库是主从复制,即连接点1同歩连接点2的hst库。

 

    关键字:

天才代写-代写联系方式