mysql 多主一从
2024-08-24 23:40:07
一、主服务器准备
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
最新文章
- windows 安装 mongodb
- 网络热恋之NSURLSession
- fzuoop期中练习
- 页面提交错误,页面间参数传递java.lang.NumberFormatException: null
- ceil与intval区别
- python函数注释,参数后面加冒号:,函数后面的箭头→是什么？
- Python第四章（北理国家精品课 嵩天等）
- 解决XCode7.1 上无法安装 Alcatraz PackageManager
- scheme 教程 #lang racket
- cordova app 监听物理返回键
- 使用js/jquery查找iframe中的
- Zabbix部署-LNMP环境
- Python中的一些函数
- php判断密码强度函数
- (转)pipe row的用法, Oracle split 函数写法.
- ahp层次分析法软件
- PHP:php遍历数组 foreach echo() list()总结
- C语言联合体
- .Net Mvc 返回Json，动态生成EasyUI Tree
- gdbt原理解析