概念
- 主从复制(MySQL REPLICATION)指数据可以从主库复制到一个或多个从库;
- 默认采用异步复制方式;
- 根据配置,可以复制数据库中所有数据库,选定数据库或选定表。
原理
- MySQL复制是基于Binlog日志
- 从库I/O线程请求主节点的Binog,主库生成一个Log dump线程,用于给从节点I/O线程传Binlog,并将其写入Relay log(中继日志)文件中;
- 从库的SQL线程会读取Relay log文件中的日志,并解析成具体操作,从来保证数据一致性。
用处
- 容灾恢复,故障切换
- 读写分离
- 数据备份,避免影响业务
条件
- 主库开启binlog日志(设置log-bin参数);
- 主从的server-id值不同;
- 从库可以通过主从帐号连接上主库;
准备工作
- MySQL版本:MySQL 5.7.25
- 安装MySQL(在/root下执行,可以一键安装,很方便一键安装地址:https://oneinstack.com/)
wget -c http://mirrors.linuxeye.com/oneinstack-full.tar.gz && tar xzf oneinstack-full.tar.gz && ./oneinstack/install.sh --db_option 2 --dbinstallmethod 1 --dbrootpwd 123456 --pureftpd --reboot
- 主库:192.168.0.157
- 从库:192.168.0.158
- 主从库关闭防火墙
-
systemctl stop firewalld.service
-
-
主从库关闭iptables(如果安装了)
-
systemctl stop iptables;
-
主库配置
-
配置主库使用基于二进制日志文件位置的复制,必须启用二进制日志记录并建立唯一的服务器ID,否则则无法进行主从复制
vim /etc/my.cnf [mysqld] log-bin=mysql-bin server-id=1
- 重启MySQL服务
systemctl restart mysqld
- 创建用户,每个从库通过帐号密码连接到主库,可以共用一个,或则每个每个从库对应一个账号密码,只要该账户被授予REPLICATION SLAVE权限
mysql -uroot -p # 登录MySQL CREATE USER 'repli'@'192.168.0.158' IDENTIFIED BY '123456'; # 创建帐号(repli)设置密码(123456) GRANT REPLICATION SLAVE ON *.* TO 'repli'@'192.168.0.158'; # 授予权限
- 查看主库状态(File和Position参数后面从库需要)
MySQL [(none)]> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000006 | 1897 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
-
打包主库数据库数据,并复制到从库/tmp目录
mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p > /tmp/all.sql #打包数据到/tmp目录中 scp /tmp/all.sql root@192.168.0.158:/tmp # 复制到从库/tmp目录
从库配置
- 修改my.cnf文件
vim /etc/my.cnf [mysqld] server-id=2
-
重启MySQL服务
systemctl restart mysqld
-
将主库打包数据导出从库数据库
mysql -uroot -p < /tmp/all.sql
-
配置主从库通信
CHANGE MASTER TO MASTER_HOST='192.168.0.157', # 主库地址 MASTER_USER='repli', # 主从通信账户 MASTER_PASSWORD='123456', # 账户密码 MASTER_LOG_FILE='mysql-bin.000006', # 主库记录日志文件名 MASTER_LOG_POS=1897; # 主库日志位置
-
启动slave服务
start slave;
-
查看slave状态
MySQL [(none)]> show slave status;MySQL [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.157 Master_User: repli Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 1897 Relay_Log_File: eight-relay-bin.000006 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000006 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: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1897 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: 1 Master_UUID: 6a687b40-5902-11e9-9ee7-000c293d4ccc Master_Info_File: /data/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)
Slave_IO_State #从库当前状态
Slave_IO_Running: Yes #读取主程序二进制日志的I/O线程是否正在运行,必须为Yes
Slave_SQL_Running: Yes #执行读取主库中二进制日志事件的SQL线程是否正在运行。与I/O线程一样 ,必须为Yes
Seconds_Behind_Master #主从复制延迟时间
-
测试主从
- 主库创建数据库和数据
create database test; use test; create table member(id int, name varchar(30), primary key(id)); insert into member values(1,'Alice'),(2,'Bob');
-
查看从库
MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.08 sec) MySQL [(none)]> use test; Database changed MySQL [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | member | +----------------+ 1 row in set (0.02 sec) MySQL [test]> select * from member; +----+-------+ | id | name | +----+-------+ | 1 | Alice | | 2 | Bob | +----+-------+ 2 rows in set (0.02 sec)
感谢:https://segmentfault.com/a/1190000010867488#articleHeader12