mysql 主从复制

由于最近老板闲来无事,觉得我们的项目读写效率不行,还要求提高效率的同时做到数据备份,为此小编特意跟大神请教学习了一下,最终决定使用数据库的主从复制来实现。

话不多说,讲一讲自己踩得那些坑吧。

 

首先呢,我们先来了解了解有关主从复制

 

1、mysql主从同步(复制)概念

 
       一句话表示就是,主数据库做什么,从数据库就跟着做什么。
  1. 将Mysql某一台主机数据复制到其它主机(slaves)上,并重新执行一遍来实现的。

  2. 复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。

  3. 主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。

  4. 当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。

  5. 从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。

·binlog:**是二进制日志文件,用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据)

2、Mysql支持哪些复制

 

 1. 基于语句的复制: 在主服务器执行SQL语句,在从服务器执行同样语句。

    注:MySQL默认采用基于语句的复制,效率较高。一旦发现没法精确复制时, 会自动选基于行的复制。

 2. 基于行的复制: 把改变的内容复制过去,而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

 3. 混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

   和上面相对应的日志格式也有三种:STATEMENT,ROW,MIXED。

 

3、Mysql主从复制原理

  1. master服务器将数据的改变都记录到二进制binlog日志中,只要master上的数据发生改变,则将其改变写入二进制日志;

  1. salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/O Thread请求master二进制事件

  2. 同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中

  3. 从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致

  4. 最后I/O Thread和SQL Thread将进入睡眠状态,等待下一次被唤醒.

    需要理解:

    1)从库会生成两个线程,一个I/O线程,一个SQL线程;

    2)I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;*

    3)主库会生成一个log dump线程,用来给从库I/O线程传binlog;

    4)SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

4、准备工作

  小编是新建了两台虚拟机

  1.主从数据库版本最好一致
     2.主从数据库内数据保持一致

    主数据库:192.168.20.129
    从数据库:192.168.20.131

5、具体配置

  1、主服务器

    

 vim /etc/my.cnf 

#
mysql master1 config [mysqld] server-id = 1 # 节点ID,确保唯一 log-bin = mysql-bin #开启mysql的binlog日志功能 sync_binlog = 1 #控制数据库的binlog刷到磁盘上去 , 0 不控制,性能最好,1每次事物提交都会刷到日志文件中,性能最差,最安全 binlog_format = mixed #binlog日志格式,mysql默认采用statement,建议使用mixed expire_logs_days = 7 #binlog过期清理时间 max_binlog_size = 100m #binlog每个日志文件大小 binlog_cache_size = 4m #binlog缓存大小 max_binlog_cache_size= 512m #最大binlog缓存大 binlog-ignore-db=mysql #不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,或者 复制这句话,写多行 auto-increment-offset = 1 # 自增值的偏移量 auto-increment-increment = 1 # 自增值的自增量 slave-skip-errors = all #跳过从库错误

#################################
以下可自由选择添加配置

  #不同步哪些数据库
  binlog-ignore-db = mysql
  binlog-ignore-db = test
  binlog-ignore-db = information_schema

 #只同步哪些数据库,除此之外,其他不同步
 binlog-do-db = game


配置完保存退出,进入数据库
systemctl restart mysqld
mysql -uroot -p
1.进入master的数据库,为master创建复制用户
  CREATE USER repl_user IDENTIFIED BY 'repl_passwd';
2.赋予该用户复制的权利
  grant replication slave on *.* to 'repl_user'@'192.168.20.131' identified by 'repl_passwd';
  FLUSH PRIVILEGES;
3.查看master的状态
  show master status;
  

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 1857 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

 

  2、从服务器

     

vim /etc/my.cnf 

[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin                        # 文件名
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%


############################
replicate-do-table  设定需要复制的表
replicate-ignore-table 设定需要忽略的复制表 

  增加通配符的两个配置

  replicate-wild-do-table=db_name.% 只复制哪个库的哪个表, 同 replication-do-table 功能一样

  replicate-wild-ignore-table=mysql.% 忽略哪个库的哪个表  同 replication-ignore-table 功能一样



配置完保存退出,进入数据库
systemctl restart mysqld
mysql
-uroot -p

1.进入从服务器的数据库 

   CHANGE MASTER TO MASTER_HOST='192.168.20.129', MASTER_USER='repl_user',MASTER_PASSWORD='repl_passwd',MASTER_PORT=3306,

   MASTER_LOG_FILE='mysql-bin.000004’,MASTER_LOG_POS=857,MASTER_RETRY_COUNT=60,MASTER_HEARTBEAT_PERIOD=10000;

 2.启动slave进程

   start slave;

 

 3.查看

  show slave status\G;

  

  

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.129
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1857
Relay_Log_File: mysql-relay-bin.000075
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
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: mysql.%,test.%,information_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1857
Relay_Log_Space: 693
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: 87c45585-a58a-11ea-9207-000c29b048ee
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: 60
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)

  

 

如果change错了不要急;

MySQL彻底清除slave信息

在我们的MySQL,Master和Slave进行主从切换的时候,Slave成功升级为主库,那么这个时候就需要彻底清理从库的信息,不然监控系统会认为这台服务器是Slave,而且会报主从同步失败。
其实非常的简单,只需要以下两步:

mysql> stop slave;
mysql> reset slave all;

reset slave all;是清除从库的同步复制信息、包括连接信息和二进制文件名、位置。
从库上执行这个命令后,使用show slave status将不会有输出。

 


 

 

 

 

 

最后去自己主服务器做一下操作吧,看看是不是生效了

 

posted @ 2020-06-11 16:15  许光宗  阅读(57)  评论(0编辑  收藏  举报