mysql一主最多几从,mysql 多主一从

搜索热词

一、主服务器准备

1.1、环境准备

两台主机器ip分别为

100.100.100.105 (主1)

100.100.100.106(主2)

[root@centos ~]# yum install MysqL-server MysqL-client -y

[root@centos ~]# service MysqLd start

[root@centos ~]# /usr/bin/MysqLadmin -u root password '123456'

100.100.100.105

[root@centos ~]# vim /etc/my.cnf

log-bin=MysqL-bin

server-id=105

100.100.100.106

[root@centos ~]# vim /etc/my.cnf

log-bin=MysqL-bin

server-id=106

MysqL> grant replication slave on *.* to slave@'100.100.100.103' identified by '123456';

1.4、查看主服务器信息

MysqL> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| MysqL-bin.000006 | 261 | | |

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

MysqL> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| MysqL-bin.000003 | 261 | | |

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

二、从服务器准备

2.1、环境准备

从机器ip分别为

100.100.100.103

[root@centos ~]# yum install MysqL-server MysqL-client -y

#这里我们不直接启动MysqL，而是使用多进程的方法进行写分离。

2.2、编辑配置文件

[root@centos ~]# vim /etc/my.cnf

[MysqLd_multi]

MysqLd=/usr/bin/MysqLd_safe

MysqLadmin=/usr/bin/MysqLadmin

log=/tmp/multi.log

[MysqLd05]

port=3306

datadir=/var/lib/MysqLa/

pid-file=/var/lib/MysqLa/MysqLd.pid

socket=/var/lib/MysqLa/MysqL.sock

user=MysqL

server-id=20

#replicate-do-db=test

binlog-ignore-db=MysqL

binlog-ignore-db=infogmation_schema

[MysqLd06]

port=3307

datadir=/var/lib/MysqLb/

pid-file=/var/lib/MysqLb/MysqLd.pid

socket=/var/lib/MysqLb/MysqL.sock

user=MysqL

server-id=20

#replicate-do-db=test

binlog-ignore-db=MysqL

binlog-ignore-db=infogmation_schema

2.3、以不同的用户目录进行初始化

[root@centos ~]# MysqL_install_db --datadir=/var/lib/MysqLa --user=MysqL

[root@centos ~]# MysqL_install_db --datadir=/var/lib/MysqLb --user=MysqL

[root@centos ~]# chown -R MysqL /var/lib/MysqLa/

[root@centos ~]# chown -R MysqL /var/lib/MysqLb/

2.4、启动进程

[root@centos ~]# MysqLd_multi --defaults-file=/etc/my.cnf start 05

[root@centos ~]# MysqLd_multi --defaults-file=/etc/my.cnf start 06

[root@centos ~]# netstat -tunlp|grep MysqLd

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1864/MysqLd

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 3563/MysqLd

2.5、登录子进程，进行配置

[root@centos ~]# MysqL -uroot -P3306 -S /var/lib/MysqLa/MysqL.sock

MysqL> change master to

master_user='slave',master_password='123456',master_host='100.100.100.105',master_log_file='MysqL-bin.000006',master_log_pos=261;

MysqL> start slave;

[root@centos ~]# MysqL -uroot -P3307 -S /var/lib/MysqLb/MysqL.sock

MysqL> change master to

master_user='slave',master_host='100.100.100.106',master_log_file='MysqL-bin.000003',master_log_pos=261;

MysqL> start slave;

2.5、出现如下，则成功

MysqL> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 100.100.100.105

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: MysqL-bin.000006

Read_Master_Log_Pos: 261

Relay_Log_File: MysqLd-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: MysqL-bin.000006

Slave_IO_Running: Yes

Slave_sql_Running: Yes

MysqL> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 100.100.100.106

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: MysqL-bin.000003

Read_Master_Log_Pos: 261

Relay_Log_File: MysqLd-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: MysqL-bin.000003

Slave_IO_Running: Yes

Slave_sql_Running: Yes

总结

以上是编程之家为你收集整理的mysql 多主一从全部内容，希望文章能够帮你解决mysql 多主一从所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。