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 master_host='172.20.28.36', master_port=3306, master_user='oldboy123', master_password='oldboy123', master_log_file='mysql-bin.000001', master_log_pos=714;
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一主双从就配置完成了。
