mysql2主2从_MYSQL一主二从

1.主库操作

1.开启binlog

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

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port=3306

socket=/tmp/mysql.sock

server_id=1 #主库这里写 1

log_err=/usr/local/mysql/data/mysql.err

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

2.重启并连接数据库

[root@db01 ~]# systemctl restart mysqld

[root@db01 ~]# mysql

3.主库授权一个用户，给从库用来连接

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';

Query OK, 0 rows affected (0.18 sec)

4.主库查看binlog信息

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 120 | | | |

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

1 row in set (0.00 sec)

2.从库"1"操作

1.编辑配置文件

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

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port=3306

socket=/tmp/mysql.sock

server_id=2 #从库这里写 2

log_err=/usr/local/mysql/data/mysql.err

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

#从库需要知道的主库信息

#主库的IP

#连接主库用的用户和密码

#binlog信息

2.重启并连接数据库

[root@db01 ~]# systemctl restart mysqld

[root@db01 ~]# mysql

3.配置主从

mysql> change master to

-> master_host='172.16.1.51', #主库的ip

-> master_user='rep', #从库连接主库的用户 (主从复制专属用户)

-> master_password='123', #从库连接主库的用户的密码

-> master_log_file='mysql-bin.000001', #主库上获取到的"file"名称

-> master_log_pos=120, #主库上获取到的"pos"值

-> master_port=3306; #主库服务器的端口

4.开启IO线程和sql线程

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

3.从库"2"操作

1.编辑配置文件

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

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port=3306

socket=/tmp/mysql.sock

server_id=3 #从库这里写 3

log_err=/usr/local/mysql/data/mysql.err

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

#从库需要知道的主库信息

#主库的IP

#连接主库用的用户和密码

#binlog信息

2.重启并连接数据库

[root@db01 ~]# systemctl restart mysqld

[root@db01 ~]# mysql

3.配置主从

mysql> change master to

-> master_host='172.16.1.51', #主库的ip

-> master_user='rep', #从库连接主库的用户 (主从复制专属用户)

-> master_password='123', #从库连接主库的用户的密码

-> master_log_file='mysql-bin.000001', #主库上获取到的"file"名称

-> master_log_pos=120, #主库上获取到的"pos"值

-> master_port=3306; #主库服务器的端口

4.开启IO线程和sql线程

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

每个数据库的server_id 必须不一致。

5.查看主从状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.1.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 208

Relay_Log_File: db03-relay-bin.000002

Relay_Log_Pos: 371

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes #这里要是yes

Slave_SQL_Running: Yes #这里要是yes

``