MySQL主从复制

?1、横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
??
?2、数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。 ?
??
?3、分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。 ? ?
??
?4、备份 - 可以使用从服务器数据进行备份，减轻主服务器的压力。

Replication的原理

?原理 主服务器上面的任何修改都会保存在二进制日志（ Bin-log日志） 里面。 ?
??
?1、从库上面启动一个I/O线程，（5.5以后多线程）连接到主服务器上面请求读取二进制（Bin-log）日志文件
??
?2、把读取到的二进制日志写到本地的Realy-log（中继日志）里面
??
?3、从服务器上面同时开启一个SQL线程，读取Realy-log（中继日志），如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
??
?注：前提是作为主服务器角色的数据库服务器必须开启二进制（binlog）日志

准备环境

准备两台服务器

关闭防火墙

?systemctl stop firewalld
?systemctl disable firewalld

关闭selinux

?setenforce 0 ?  临时关闭
?vim /etc/selinux/config ? 打开selinux的配置文件

修改主机名

?hostnamectl set-hostname master ?  主服务器
?hostnamectl set-hostname slave ? ? 从服务器

主机名解析

?vim /etc/hosts

固定IP

?vim /etc/sysconfig/network-scripts/ifcfg-ens33

清理环境

?清理数据库
?[root@master ~]# yum -y erase `rpm -qa | egrep "mysql|mariadb"`
?[root@master ~]# rm -rf /etc/my* && rm -rf /var/lib/mysql && rm -rf /usr/bin/mysql
?检查是否清理干净
?[[ ! -f /etc/mysql.cnf ]] && [[ ! -d /var/lib/mysql ]] && [[ ! -f /usr/bin/mysql ]] && echo '环境已清理干净' || echo '环境未清理干净'

主、从服务器安装MySQL

?yum安装mysql
?参照上一篇博客
?启动mysql
?systemctl start mysqld
?修改密码
?mysqladmin -uroot -p"`awk '/temporary password/{p=$NF}END{print p}' /var/log/mysqld.log`" password '新密码'

MySQL无数据

主库配置（master）

?#开启binlog
?[root@master ~]# mkdir -pv /data/ ? ? ? ?  #创建二进制日志文件的存储目录
??
?[root@master ~]# chown mysql.mysql /data ? #改属主属组为mysql
??
?[root@master ~]# vim /etc/my.cnf         ? #编辑mysql配置文件添加以下内容
?server-id=328 ? ? ? ? ?       #id号可自定义
?log-bin=/data/mysql-bin ? ? ? #日志文件名可自定义
??
?# 重启数据库
?[root@master ~]# systemctl restart msyqld
?# 创建并授权一个账号
?mysql> grant replication slave on *.* to '账号名'@'授权网段或ip' identified by '账号的密码';
?mysql> flush privileges;
?# 查看当前binlog日志文件以及pos位置点
?mysql> show master status\G
?*************************** 1. row ***************************
? ? ? ? ? ? ? File: mysql-bin.000001
? ? ? ? ? Position: 606
? ? ? Binlog_Do_DB: 
? Binlog_Ignore_DB: 
?Executed_Gtid_Set: 
?1 row in set (0.00 sec)

从库配置（slave）

?#编辑mysql配置文件添加server-id
?[root@slavr ~]# vim /etc/my.cnf
?server-id=329
?# 重启数据库
?[root@slavr ~]# systemctl restart msyqld
?mysql> ? change master to ?             #查找需要插入的配置主从信息模板
?模板如下 修改配置信息后并执行
?mysql> CHANGE MASTER TO
?  MASTER_HOST='master', ? ? ? ? ? ? ? ? ? ?
?  MASTER_USER='账号名', ? ? ? ? ? ? ? ? ? ? #注意用户名与密码是主库授权的
?  MASTER_PASSWORD='账号的密码',
?  MASTER_PORT=3306,
?  MASTER_LOG_FILE='mysql-bin.000001', ? ? 主库查到的File: mysql-bin.000001
?  MASTER_LOG_POS=606, ? ? ? ? ? ? ? ? ? ? 主库查到的Position: 606
?  MASTER_CONNECT_RETRY=10;
?# 启动slave
?mysql> start slave;
?# 查看主从状态
?mysql> show slave status\G

查看主从的状态

?mysql> show slave status\G 
?*************************** 1. row ***************************
? ? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ?  Master_Host: master
? ? ? ? ? ? ? ? ?  Master_User: relication
? ? ? ? ? ? ? ? ?  Master_Port: 3306
? ? ? ? ? ? ? ?  Connect_Retry: 10
? ? ? ? ? ? ?  Master_Log_File: mysql-bin.000001
? ? ? ? ?  Read_Master_Log_Pos: 154
? ? ? ? ? ? ? ? Relay_Log_File: slave-relay-bin.000002
? ? ? ? ? ? ? ?  Relay_Log_Pos: 320
? ? ? ?  Relay_Master_Log_File: mysql-bin.000001
? ? ? ? ? ? ? Slave_IO_Running: Yes ? ? ? #这里为Yes则配置成功
? ? ? ? ? ?  Slave_SQL_Running: Yes ? ? ? #这里为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: 527
? ? ? ? ? ? ?  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: 203
? ? ? ? ? ? ? ? ?  Master_UUID: 6f6d0551-a073-11ee-9f28-000c298a6e96
? ? ? ? ? ? ? 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)

如有问题要重新配置主从

?#首先关闭线程服务
?mysql>stop slave;
?#重新修改配置主从信息后执行
?mysql> reset slave;
?mysql>CHANGE MASTER TO
?  MASTER_HOST='master',
?  MASTER_USER='用户', ? ? ? 
?  MASTER_PASSWORD='密码',
?  MASTER_PORT=3306,
?  MASTER_LOG_FILE='mysql-bin.000001',
?  MASTER_LOG_POS=154,
?  MASTER_CONNECT_RETRY=10;
?# 启动slave后
?mysql>start slave;
?#继续查看主从状态 直到成功
?mysql> show slave status\G

MySQL有数据

主库配置（master）

?#配置主从
?mysql> grant replication slave on *.* to '账号名'@'授权网段或ip' identified by '账号的密码';
?mysql>flush privileges;
?# 创建必要目录
?[root@master ~]# mkdir /data
?[root@master ~]# chown mysql.mysql /data
?# 修改配置文件
?[root@master ~]# vim /etc/my.cnf
?server-id = 328
?log-bin = /data/mysql-bin
?# 重启数据库
?[root@master ~]# systemctl restart mysqld

进入数据库并且锁表备份（重点）

?锁表备份
?mysql>flush tables with read lock;
?#新开一个终端备份全库 
?[root@master ~]# mysqldump -uroot -p密码 -A | gzip > all.sql.gz
?#将备份好的全库文件压缩包远程拷贝至从库
?[root@master ~]# scp  all.sql.gz slave:需要存放的位置
?#返回登录mysql的终端查看二进制日志文件以及位置点信息
?mysql>show master status\G
?*************************** 1. row ***************************
? ? ? ? ? ? ? File: mysql-bin.000001
? ? ? ? ? Position: 1205
? ? ? Binlog_Do_DB: 
? Binlog_Ignore_DB: 
?Executed_Gtid_Set: 
?1 row in set (0.00 sec)

从库配置（slave）

?#导入主库备份的数据全库
?[root@slave ~]# mysql -uroot -p密码 < all.sql
?#配置主从
?[root@slave ~]# vim /etc/my.cnf
?server-id = 329
?# 重启数据库
?[root@slave ~]# systemctl restart mysqld
?修改主从配置信息并执行
?mysql>change master to
?master_host='master',
?master_user='replication',
?master_password='Aa@123456',
?master_port=3306,
?master_log_file='mysql-bin.000001',
?master_log_pos=1250;
?#启动slave
?mysql>start slave;
?# 查看主从状态
?mysql> show slave status\G
?*************************** 1. row ***************************
? ? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ?  Master_Host: master
? ? ? ? ? ? ? ? ?  Master_User: repl
? ? ? ? ? ? ? ? ?  Master_Port: 3306
? ? ? ? ? ? ? ?  Connect_Retry: 60
? ? ? ? ? ? ?  Master_Log_File: mysql-bin.000001
? ? ? ? ?  Read_Master_Log_Pos: 154
? ? ? ? ? ? ? ? Relay_Log_File: slave-relay-bin.000002
? ? ? ? ? ? ? ?  Relay_Log_Pos: 320
? ? ? ?  Relay_Master_Log_File: mysql-bin.000001
? ? ? ? ? ? ? Slave_IO_Running: Yes ? ? ? #这里为Yes则配置成功
? ? ? ? ? ?  Slave_SQL_Running: Yes ? ? ? #这里为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: 527
? ? ? ? ? ? ?  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: 203
? ? ? ? ? ? ? ? ?  Master_UUID: 5aad69d6-a09e-11ee-b909-000c298a6e96
? ? ? ? ? ? ? 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:

主库解锁

?mysql> unlock tables; 或者 直接退出mysql数据库自动解锁

跳过错误码

?vim /etc/my.cnf
?slave-skip-errors=1062 ?  （1062为错误原因的编号）

文章来源:https://blog.csdn.net/zbw0323/article/details/135231008
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！