mysql 多主一从_mysql 多主一从

一、主服务器准备

1.1、环境准备

两台主机器ip分别为

100.100.100.105 (主1)

100.100.100.106(主2)

安装 mysql

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

[root@centos ~]# service mysqld start

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

1.2、修改配置文件

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

1.3、添加mysql授权用户

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

安装 mysql

[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_password='123456',

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

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:1024 设计师:我叫白小胖 返回首页
评论

打赏作者

硬核社

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值