从属关系mysql_Mysql主从同步

主从同步概述

mysql复制

mysql内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将mysql的某一台主机的数据复制到其它主机(slave)上，并重新执行一遍来实现。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循坏，这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知的更新。需注意的是：在进行mysql复制时，所有对复制中的表的更新必须在主服务器上进行。否则必须要小心，以避免用户对主服器上的表进行更新与对从服务器上的表所进行更新之间的冲突。

mysql支持哪些复制

基于语句的复制：在主服务器上执行的sql语句，在从服务器上执行同样的语句。mysql默认采用基于语句的复制，效率边角高。一旦发现没法精确复制时，会自动选着基于行的复制。

基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从mysql 5.0开始支持

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

mysql复制解决的问题

数据分布

负载平衡

数据备份,保证数据安全

高可用性与容错性

实现读写分离，缓解数据库压力(主数据库用来做数据写入，从数据库用来做数据读取)

mysql主从复制原理

mysql主从复制原理图如下：

master服务器将数据的改变记录二进制binlog日志;当master上的数据发生改变时，则将其改变写入二进制日志中；

slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中

slave节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。

注意事项：

master将操作语句记录到binlog日志中，然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能，不过slave中的binlog功能非必须的)。

slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和 master数据保持一致了。

mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

mysql复制最好确保master和slave服务器上的mysql版本相同(如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本)

master和slave两节点间时间需同步

mysql复制的模式

主从复制：主库授权从库远程连接，读取binlog日志并更新到本地数据库的过程；主库写数据后，从库会自动同步过来(从库跟着主库变)；

主主复制：主从相互授权连接，读取对方binlog日志并更新到本地数据库的过程；只要对方数据改变，自己就跟着改变；

mysql主从复制优点

在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;(主库写，从库读，降压)

在从主服务器进行备份，避免备份期间影响主服务器服务;(确保数据安全)

当主服务器出现问题时，可以切换到从服务器。(提升性能)

mysql主从同步步骤

主从同步部署(主数据库无数据)

环境

操作系统

主机地址

数据库版本

角色

Centos 7.4

192.168.10.170

mysql 5.7

master

Centos 7.4

192.168.10.171

mysql 5.7

slave

部署

安装mysql

具体安装细节请参考文档：https://www.cnblogs.com/guge-94/p/10552374.html

修改配置文件

master配置文件

[client]default-character-set =utf8mb4

[mysqld]

server-id=1# 这一行是master端必须拥有的，设定服务ID，局域网络中不唯一即可，master端的尽量靠前

log-bin=/usr/local/mysql/data/bin/mysql-bin

# 设置binlog日志的位置，可不写，不写则按照默认安装路劲进行存储

sync_binlog=0# 每进行1次事务提交之后，MySQL将进行一次磁盘同步指令来将binlog_cache中的数据强制写入磁盘；非必须选项

expire_logs_days=7# 设定binlog日志保存的最大期限

binlog_cache_size=1M

# 设定binlog缓存的值

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

skip-ssl

[mysql]default-character-set = utf8mb4

slave配置文件

[client]default-character-set =utf8mb4

[mysqld]

server-id=2# 服务器唯一ID

read_only=1# 设置数据库为只读，防止从库数据修改后，主从数据不一致，但是有Super权限的账号还是有写的权限，所以要某个账号只读的话，可以去掉账号的Super权限

binlog_cache_size=1M

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

skip-ssl

[mysql]default-character-set = utf8mb4

重启mysql服务

注：无论是master还是slave都需要重启数据库，应该调整mysql主配置文件需要让其生效。

配置同步

master

mysql> create user 'repl'@'%' identified by '123..com';

# 创建从属数据库同步所使用的用户

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# 设定从属数据库所能同步的数据库权限

mysql> flush tables with readlock;

# 锁定数据库，防止数据库有内容写入

mysql>show master status;+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 595 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00sec)

# 查看master端的binlog与pos偏移量

注：若是主数据库中有内容需要记得做数据导入操作，从主导入至从

slave

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.170', MASTER_USER='repl', MASTER_PASSWORD='123..com',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=595;

Query OK,0 rows affected, 2 warnings (0.41sec)

# 设定同步账户及其他信息

mysql>start slave;

Query OK,0 rows affected (0.02sec)

# 启用slave状态，停止可使用stop slave;mysql>show slave status \G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:192.168.10.170Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos:595Relay_Log_File: node1-relay-bin.000002Relay_Log_Pos:320Relay_Master_Log_File: mysql-bin.000001Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:595Relay_Log_Space:527Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1Master_UUID: ce106b1a-e99a-11e9-9706-525400a34841

Master_Info_File:/usr/local/mysql/data/master.info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State: Slave hasread all relay log; waiting formore updates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:1 row in set (0.00 sec)

# 只要上述的信息中'Slave_IO_Running'与'Slave_SQL_Running'最终都变成了YES,则表明从数据库已经成功的连接了master，也就是说主从同步成功；

mysql> reset slave all;# 清理掉之前的配置，防止同步已经同步了的数据；然后重新连接主库，进行同步。

master

mysql>unlock tables

# 做完主从同步之后，最后记得解锁数据库，否则数据无法写入

总结

启用slave功能则在mysql解释环境中执行sart slave;

默认slave与master功能是随着mysql的启动而启动；slave如需设置禁止自动启动则需要在my.cnf文件内容组[mysqld]中增加 skip-slave-start 选项

彻底解除主从复制关系

stop slave;

reset slave;

# 或直接删除master.info和relay-log.info这两个文件；

# 最后修改配置文件

做了MySQL主从复制以后，使用mysqldump对数据备份时，一定要注意按照如下方式：'mysqldump --master-data --single-transaction --user=username --password=password dbname> dumpfilename'；这样就可以保留file 和 position 的信息，在新搭建一个slave的时候，还原完数据库，file 和 position 的信息也随之更新，接着再start slave 就可以很迅速的完成增量同步！

参考地址：https://www.cnblogs.com/wade-lt/p/9008058.html