mysql主从复制及主主复制

1.主从复制

=============================================================================

给每个mysql的配置文件中增加server-id vi /etc/my.cnf，增加如下配置

log-bin=mysql-bin #开启二进制日志

server-id=1 #设置server-id

重启mysql服务

grant replication slave on *.* to 'mysql3306'@'118.190.42.241' identified by 'cc@processor2020!';

show master status;查看master_log_file、master_log_pos的值

 

登录Slave从服务器mysql

change master to master_host='118.190.15.29',master_user='mysql3306',master_password='cc@processor2020!',master_log_file='mysql-bin.000001',master_log_pos=471;

启动从库

start slave;

查看主从同步状态

show slave status \G;

看到以下字段就证明主从同步成功了

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

=============================================================================

2.配置主主复制

=============================================================================

查看：show slave status;

停止：stop slave;

清除：reset slave; 把master.info和relay-log.info文件给删除，但里面的同步信息还在

彻底清除：reset slave all;

 

查看：show master status;

重置：reset master;

=============================================================================

查看master1 master2的状态 File和Position

在master2上授权master1访问的ip及用户名密码

grant replication slave on *.* to 'mysql3306'@'39.104.100.166' identified by 'cc@processor2020!';

在master1上执行以下代码 MASTER_HOST为master2的ip以及用户名密码、File和Position，同步master2

CHANGE MASTER TO MASTER_HOST='118.190.151.59',

MASTER_PORT=3306,

MASTER_USER='mysql3306',

MASTER_PASSWORD='cc@processor2020!',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=471;

 

start slave;

show slave status \G;

看到以下字段就证明主主同步成功了

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

=============================================================================

在master1上授权master1访问的ip及用户名密码

grant replication slave on *.* to 'mysql3306'@'118.190.151.59' identified by 'cc@processor2020!';

在master1上执行以下代码 MASTER_HOST为master2的ip以及用户名密码、File和Position，同步master2

CHANGE MASTER TO MASTER_HOST='39.104.100.166',

MASTER_PORT=3306,

MASTER_USER='mysql3306',

MASTER_PASSWORD='cc@processor2020!',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=120;

 

start slave;

show slave status \G;

看到以下字段就证明主主同步成功了

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

=============================================================================