多机MySQL一主双从详细安装主从复制
多机MySQL一主双从详细安装
一、复制的工作原理
要想实现AB复制,那么前提是master上必须要开启二进制日志
1.首先master将数据更新记录到二进制日志文件
2.从slave start开始,slave通过I/O线程向master请求二进制日志文件指定位置之后的内容
3.master接收到slave的io请求之后,就会从相应的位置点开始,给slave传日志
4.slave接收到日志后,会写入本地的中继日志中
5.slave通过sql线程读取中继日志的内容,在数据库中执行相应的操作,到此为止,master和slave上的数据一致,之后slave服务器进入等待状态,等待master的后续更新
二、主从复制配置
172.20.28.36 |
MySQL-master |
yum install mysql mysql-server -y |
172.20.28.37 |
MySQL-slave1 |
yum install mysql mysql-server -y |
172.20.28.38 |
MySQL-slave2 |
yum install mysql mysql-server -y |
小结:mysql服务是yum安装的,配置文件:/etc/my.cnf 数据存放目录:/var/lib/mysql |
2.1 修改主库和从库的配置文件
master |
Slave1 |
Slave2 |
[root@i-t27hedd8 ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=1 log-bin=/var/lib/mysql/mysql-bin [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=3 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=5 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
小结: 1、主库开启binlog日志 2、主从server-id不同 3、从库服务器能连通主库 |
2.2 在master端查看
mysql> show variables like "log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec)
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 341 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
2.3在主库创建复制用户
mysql> grant replication slave on *.* to 'oldboy123'@'172.20.28.%' identified by 'oldboy123'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) |
mysql> select user,host,password from mysql.user; +-----------+-------------+-------------------------------------------+ | user | host | password | +-----------+-------------+-------------------------------------------+ | root | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 | | root | i-t27hedd8 | | | root | 127.0.0.1 | | | | localhost | | | | i-t27hedd8 | | | oldboy123 | 172.20.28.* | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 | +-----------+-------------+-------------------------------------------+ |
2.4 分别在两台从库上操作
mysql> change master to mysql> flush privileges; |
2.5 分别开启两台从库
mysql> start slave;
2.6 分别查看两台从库的信息
show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.20.28.36 Master_User: oldboy123 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1130 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 667 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1130 Relay_Log_Space: 823 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: |
至此,MySQL一主双从就配置完成了。
最新文章
- js---OOP浅谈
- CI框架源码阅读笔记4 引导文件CodeIgniter.php
- Xcode - 修改变量名、类名及字符串的替换操作
- 学习angularjs时遇到 XX is not a function
- WCF使用net.tcp寄宿到IIS中(转)
- ZOJ3741 状压DP Eternal Reality
- Automotive Security的一些资料和心得(3):Vehicular Security技术
- phpquery笔记
- 2013杭州网络赛D题HDU 4741(计算几何 解三元一次方程组)
- Python中打印列表的序号和内容
- sort用法
- Asp.net core Razor 页面
- mysql无法启动的结果问题解决
- Java的selenium代码随笔(2)
- Kafka技术内幕 读书笔记之(二) 生产者——新生产者客户端
- JS自学笔记03
- int ,Intege,String 三者之间的转换
- sorted新用法
- Java 编程下使用 Class.forName() 加载类【转】
- H3C路由器映射端口到外网
热门文章
- 位运算 - 左移右移运算符 >;>;, <;<;, >;>;>;
- strict
- nginx 解决问题
- Charles抓包(http/https请求)
- PAT甲级——A1098 Insertion or Heap Sort
- java基础之Date类
- 5行代码怎么实现Hadoop的WordCount?
- List循环添加对象时遇到问题的解决
- windows API 第 18篇 FindFirstVolume FindNextVolume
- PHP CURL 异步测试