mysql主从

carl

2024-09-14

学习

mysql主从

M-S 主-从主有读写从只有读从是把主的binlog拿过来重新跑了一遍

主从复制的功能

对master数据的备份
实现数据库集群功能，升级高可用性
实现读写分离，实现降低主库的读取压力

实操

一主两从

配置master


vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=1

binlog-ignore-db=mysql

systemctl restart mariadb


mysql

create user 'rep'@'%' identified by 'zhaoxi';

grant replication slave on . to 'rep'@'%';

flush privileges;

flush tables with read lock; ## 锁库

show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      1200 |              | mysql            |

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

## 要记住file名字和pos的值

2.配置slave


vi /etc/my.cnf

[mysqld]

server-id=2   ## slave2机器设置3

relay-log=relay-bin

systemctl restart mariadb


mysql

change master to 

master_host='192.168.32.106',

master_user='rep',

master_password='zhaoxi',

master_log_file='mysql-bin.000001',

master_log_pos=1200;

start slave;

show slave status \G

## 检查slave_io_running和slave_sql_running是否都为yes

3.完成配置


##master

unlock tables;

4.验证复制


##master:

create database test_ms;

create table test_table(id int auto_increment primary key,name varchar(50),status varchar(50));

insert into test_table (name,status) values ('zhaoxi','action'),('guoyihan','down');

##slave

select * from test_ms.test_table;

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

| id | name     | status |

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

|  1 | zhaoxi   | action |

|  2 | guoyihan | down   |

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