MySQL主从复制

MySQL主从原理

从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库的binlog，并将得到的binlog日志写到relay log(中继日志) 文件中；

主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

MySQL主从复制步骤简要

1.修改my.cnf 添加二进制日志和服务id   每个服务器id必须不同
2.启动mysql
3.进入查看主服务器状态和偏移量   show master status
4.创建一个用于slave和master通信的用户账号
#MySQL8.0 分成两步实现
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';

grant replication slave on *.* 'name'@'ip.%'   identified by 'passwd'
5.重新查看偏移量 是否有变化    有变化的话重置偏移量  reset master
6.进入从数据库设置从节点   change master to master_host="" master_port="" master_user="" master_password="" master_log_file=""  master_log_pos=""
7.启动从节点  start slave
8.进入数据库查看状态  show slave status \G;   io线程和sql线程都是yes

MySQL主从复制

主10.0.0.19 从10.0.0.41

主节点

[root@mmaster~]#cat /etc/my.cnf
[mysqld]

server-id=19
log_bin     #写了路径重启报错 不知道原因   ，改成mysql或者client没有问题


[root@mmaster~]#systemctl restart mysqld.service
[root@mmaster~]#mysql

mysql> show master logs;    #查看-------------------------------------
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| mmaster-bin.000001 |       179 | No        |
| mmaster-bin.000002 |       156 | No        |
+--------------------+-----------+-----------+
2 rows in set (0.00 sec)
 
mysql> flush logs;    #刷新日志-------------------------------------
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> show master logs;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| mmaster-bin.000001 |       179 | No        |
| mmaster-bin.000002 |       205 | No        |
| mmaster-bin.000003 |       156 | No        |
+--------------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> reset master;    #清除所有二进制日志，从头开始-------------------------------------
Query OK, 0 rows affected (0.03 sec)

mysql> show master status;   #查看详细的二进制日志
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mmaster-bin.000001 |      156 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#完全备份
[root@mmaster~]#mysqldump -A -F --master-data=1 --single-transaction > /data/all.sql
#创建复制用户并授权
mysql> create user repluser@'10.0.0.%' identified by "123456";
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to repluser@"10.0.0.%";
Query OK, 0 rows affected (0.01 sec)

#将备份复制到从节点
[root@mmaster~]#scp /data/all.sql  10.0.0.41:/data

从节点

#配置从节点
[root@mslave~]#cat /etc/my.cnf

[mysqld]
server-id=41
read-only
[root@mslave~]#systemctl restart mysqld
[root@mslave~]#vim /data/all.sql
CHANGE MASTER TO
  MASTER_HOST='10.0.0.19',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156;

#从节点还原备份
mysql> set sql_log_bin=0;
mysql> source /data/all.sql;
mysql>set sql_log_bin=1;
#从节点开始复制
mysql> start slave;
mysql> show slave status\G  
.....
.....
Slave_IO_Running: Yes   
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0     #复制的延迟时间