mysql主从配置

一.前提了解

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

二.实现mysql主从复制需要进行的配置

如果单单是主从配置(主影响从，从不影响主)，只需要主配置文件开启二进制日志功能即可。

主服务器
- 开启二进制日志
- 配置唯一的server-id
- 获得master二进制文件名及其位置
- 创建一个用于master和slave通信的用户账号
从服务器
- 配置唯一的server-id
- 使用master分配的账号读取master二进制日志
- 启用slave服务

1.master配置

1.master配置文件

数据库配置文件/etc/my.cnf

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

2.重启mysql，创建用于同步的用户账号

创建用户并授权：用户：rel1密码：slavepass 从服务ip：“123.57.44.85”

mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'slavepass';#创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'123.57.44.85';#分配权限
mysql> flush privileges;   #刷新权限
# mysql8需要注意:create user 'admin'@'%' identified with mysql_native_password by 'your_password';

3.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

2.slave配置

1.slave配置文件

数据库配置文件/etc/my.cnf

[mysqld]
server-id=2 #设置server-id，必须唯一

2.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='182.92.172.80',
    ->     MASTER_USER='rep1',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000003',
    ->     MASTER_LOG_POS=73;

3.启动slave同步进程：

mysql>start slave;

4.查看slave状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.92.172.80
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 11662
               Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 11765
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

三.其他参数

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
  
# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game

四.互为主从配置

1.主配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

#主从配置(主)
symbolic-links=0
skip-name-resolve   #不再进行反解析（ip不反解成域名）
auto_increment_offset = 1 #在使用双主互为主从的模式前提是互为主从的两个数据库，表的主键必须是自增的。
auto_increment_increment = 2

server-id=8 #设置一个不同的id、注意这里在my.cnf里面有个默认值是 1 、把默认值改掉、而不能新增
log-bin=mysql-bin #主从模式[必须]启用二进制日志，会在/var/lib/mysql下生成mysq-bin.000x文件

#需要同步的数据库
binlog-do-db    = 5ink    #需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
replicate-do-db = 5ink    #需要进行同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个replicate-do-db选项
#同步参数:
log-slave-updates
sync_binlog=1
slave-skip-errors=all #过滤掉一些没啥大问题的错误

#mysqld_safe是启动mysqld服务器的方法，增加了一些安全性，例如在发生错误时重新启动服务器，并将运行时信息记录到错误日志
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

2.从配置

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0


#主从配置(从)
auto_increment_offset = 2
auto_increment_increment = 2

server-id=12 
log-bin=mysql-bin #主从模式[必须]启用二进制日志

#需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
binlog-do-db    = 5ink    
#需要进行同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个replicate-do-db选项
replicate-do-db = 5ink    
#当互为主从，如果不加此参数，二者的bin-log中都只记录了作用在自身上的数据更新操作
log-slave-updates
sync_binlog=1
slave-skip-errors=all #过滤掉一些没啥大问题的错误


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid


!includedir /etc/my.cnf.d

配置文件写好后，重新按照上述master、slave配置反过来配置一遍。

五.误操作备份

当删除（更新）表中数据

1.备份数据库（别没找回来，再雪上加霜），关闭mysql服务
2.备份binlog日志(一般是最新的mysql-bin.00000x，位于/var/lib/mysql下），不然持续的写入操作可能会让你很难到原来误操作语句在binlog日志中的位置。)
3.备份完之后可启动mysql服务

确定/etc/my.cnf(么有就找/my.ini)中是否开启了以下四种

[mysqld]
#开启了binlog日志服务
log-bin=mysql-bin  
#ROW格式影响的是表中的行，对表中数据的操作都会记录到binlog中(且会有start-position和stop-position)，但如果是删库或者删表了，
binlog-format=ROW
#无论是否是主从，server-id参数都是必须的
server-id=1
#记录到binlog下的数据库(这步尤为重要)
binlog-do-db= 5ink

或者进入mysql服务

#查看是否开启binlog，value值为ON即为开启
1. show variables like '%log_bin%';  
#查看是否为row格式
2. show variables like '%binlog_format%';

安装binlog2sql

1.git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
2.pip3 install -r requirements.txt

执行语句，查看误操作语句（记住自己的误操作大概时间）

python binlog2sql.py -h localhost -u 用户名 -p 密码 -d 数据库名 -t 表名 --start-file='binsql.000011'  --start-datetime='2020-6-27 16:20:00' --stop-datetime='2020-6-27 16:40:00'

会出现下图所示，如果语句后跟 “#start .. end ..”，都是可能反解析出来的

1616311925091

反解析出sql语句(误操作的delete/update变为insert into/update)，加上-B就是反解析

python binlog2sql.py -h127.0.0.1 -P3306 -uroot -padmin -d数据库名 -t表名 --start-file='binlog.000011' --start-position=2213  --stop-position=2612  -B >  /back.sql

最后进入mysql，source /back.sql即可

posted @ 2023-02-26 12:07 MISF 阅读(269) 评论(0) 编辑收藏举报

刷新页面返回顶部

MISF