天天看点
返回

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:

如果change错了不要急；

MySQL彻底清除slave信息

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

mysql> stop slave;
mysql> reset slave all;

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

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

上一篇: 强烈推荐的一个工具ReSharper
下一篇: 如何发送表单