部署环境:
name
Value
Mysql版本
mysql 5.5.32
OS版本
redhat 6.5
主库ip:port
192.168.218.43:3306
从库ip:port
192.168.218.43:3307
Mysql 主从同步
一、概述
本次blog是记录mysql最基础的主从搭建过程,以及涉及的相关基础理论知识,希望能起到参考作用。若blog中有错误的地位,请读者直接评论指出,笔者会及时更改。
1.1 知识点:
主要线程
主库一个线程:I/O线程
从库两个线程:I/O线程 SQL线程
I/O线程将从库将接收的日志写到relay-log 中继日志;I/O线程会将收到中继日志后,更新master.info
sql线程,不断的读relay-log写进磁盘中
在从库上配置连接信息(ip、用户、密码. 端口 change mast file 、 log pos)
change master是主库全备后的位置
保证在开始位置点以前主从库数据库是一致的(开始是需要进行全备)
从库创建同步用户
主库打开binglog开关
从库 start slave(其实就是让 I/O线程和SQL线程开始)
主服务器和每一个从服务器都必须配置一个唯一的ID号
相关日志的作用
relay-log.info —给SQL线程用的,SQL线程会读完中继日志(relay-log)然后写到relay-log.info
master.info – 从库io线程使用的,用于告诉从库起点
如果从库还想级联从库,需要打开log-bin和log-slave-updates
二、主从搭建配置
2.1 打开log-bin,检查server-id
log-bin = /data/3306/mysql-bin --打开binlog 放在 [mysqld] 下面
server-id = 1 --确认不一样的serverid
检查
###检查配置文件
[root@study ~]# egrep "log-bin|server-id" /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1
[root@study ~]# egrep "log-bin|server-id" /data/3307/my.cnf
#log-bin = /data/3307/mysql-bin
server-id = 2
###数据库中查询是否开启log-bin
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like 'log_bin';"
2.2 创建用于复制用户
在主库授权,允许从库登录
grant replication slave on *.* to 'rep'@'192.168.218.%' identified by '123456';
flush privileges;
2.3主库进行全备
1.主库加锁
flush table with read lock;
2.备份数据库
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events --master-data=2 > /opt/123.sql
–master-data=1
备份文件中会有log-bin文件起始点,并在执行恢复的时候执行;
这样在主从同步的时候就不需要配置MASTER_LOG_FILE, MASTER_LOG_POS参数。
–master-data=2 备份文件中也会记录log-bin文件起始点,但是不会再恢复的时候执行。
3.在主库上查看log-bin的信息
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000014 | 384 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 349 |
| mysql-bin.000002 | 571 |
| mysql-bin.000003 | 6203 |
| mysql-bin.000004 | 563 |
| mysql-bin.000005 | 126 |
| mysql-bin.000006 | 126 |
| mysql-bin.000007 | 126 |
| mysql-bin.000008 | 1103 |
| mysql-bin.000009 | 992 |
| mysql-bin.000010 | 126 |
| mysql-bin.000011 | 1164 |
| mysql-bin.000012 | 608 |
| mysql-bin.000013 | 354 |
| mysql-bin.000014 | 384 |
+------------------+-----------+
14 rows in set (0.01 sec)
4.在备库上恢复数据库
mysql -uroot -p123456 -S /data/3307/mysql.sock < /opt/123.sql
恢复完成后如果之前执行过锁表,记得要在主库解锁
unlock tables;
5.在备库上建立同步机制
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.218.43',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000014',
-> MASTER_LOG_POS=384;
Query OK, 0 rows affected (0.01 sec)
6.开启主从同步
mysql> start slave;
至此已经主备搭建完成
三、检查
3.1 检查主从同步状态
[root@study data]# mysql -uroot -p123456 -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.218.43
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 384
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 384
Relay_Log_Space: 826
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:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
主要是看这两个参数是yes说明是已经成功搭建
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.2 检查 master.info内容
[root@study data]# cat master.info
18
mysql-bin.000014
384
192.168.218.43
rep
123456
3306
60 --默认出问题后60s重试
0
0
1800.000
0
主库主进程接收备库的验证请求,然后再交给I/O线程
备库的SQL线程只会读,relay-log.info是记录备库读到哪里了,relay-bin.index存放索引信息