Mysql主从介绍
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的,在B机器上读数据,实现数据的读写分离,可以极大的提升性能,降低服务器负载, MySQL主从是基于binlog的,主上须开启binlog才能进行
主从过程大致有3个步骤
1)主将更改操作记录到binlog里
2)从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
3)从根据relaylog里面的sql语句按顺序执行
主上有一个log dump线程,用来和从的I/O线程传递binlog
从上有两个线程,其中I/O线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的sql语句落地
如下图:
配置主机器A(master)
编辑配置文件/etc/my.cnf,在[mysqld]中添加如下内容:
#定义server-id,不能与B机器(slave)的server-id重复,定义binlog名称
重启mysql服务后即可在数据目录下生成binlog文件:
[root@master ~]# service mysql restart
Shutting down MySQL.... [ OK ]
Starting MySQL. [ OK ]
[root@master ~]# ll /data/mysql/ |grep mslog
-rw-r----- 1 mysql mysql 154 Nov 4 21:34 mslog.000001
-rw-r----- 1 mysql mysql 15 Nov 4 21:34 mslog.index
创建数据同步用户:
grant replication slave on *.* to msuser@'122.51.40.214' identified by 'msuser';
#创建用户msuser,密码msuser,授权ip为B机器(slave)地址,授权权限:replication slave
锁表:
mysql> flush tables with read lock;
#停止数据写入,防止主从不同步,配置完主从后需要解锁:unlock tables;
导出测试的数据库:
[root@master ~]# mysqldump -uroot -p ms_db > /tmp/msdb.sql
记录file、position的值:
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mslog.000001 | 451 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
将msdb库导出的sql脚本发送到B机器:
[root@master ~]# scp /tmp/msdb.sql 122.51.49.214:/tmp
配置从机器B(slave)
编辑配置/etc/my.cnf 定义server-id:
#slave机器不需要配置binlog
重启服务:
[root@slave ~]# service mysql restart
恢复A机器导出的ms_db库:
mysql> create database ms_db;
Query OK, 1 row affected (0.00 sec)
mysql> use ms_db;
Database changed
mysql> source /tmp/msdb.sql
停止slave:
mysql> stop slave;
配置A机器(master)信息:
mysql> change master to master_host='139.159.162.126', master_user='msuser', master_password='msuser', master_log_file='mslog.000001', master_log_pos=451;
定义A机器IP地址,主从同步数据的用户和密码以及之前记录的file、position信息
启动slave:
mysql> start slave
查看主从状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 139.159.162.126
Master_User: msuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mslog.000001
Read_Master_Log_Pos: 451
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 316
Relay_Master_Log_File: mslog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#Slave_IO_Running、Slave_SQL_Running都显示Yes即表示主从配置成功,还需要注意Seconds_Behind_Master的值,表示主从延迟的时间以及Last_IO_Errno、Last_IO_Error、Last_SQL_Errno、Last_SQL_Error等信息
在A机器(master)解锁表:
mysql> unlock tables;
测试主从:
在A机器操作测试数据库ms_db的数据:
mysql> use ms_db;
Database changed
mysql> select * from test_tb;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 20 |
| 2 | b | 22 |
| 3 | c | 18 |
+----+------+------+
3 rows in set (0.00 sec)
mysql> delete from test_tb where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_tb;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 20 |
| 2 | b | 22 |
+----+------+------+
2 rows in set (0.00 sec)
在B机器查看数据:
mysql> use ms_db;
Database changed
mysql> select * from test_tb;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 20 |
| 2 | b | 22 |
+----+------+------+
2 rows in set (0.00 sec)
#在A机器删除的数据,B机器也同步删除
补充
配置参数:
主服务器上
binlog-do-db= //仅同步指定的库,可以写多个库名,用逗号分隔
binlog-ignore-db= //忽略指定库
从服务器上
replicate_wild_do_table= //如ms_db.%,支持通配符%,表示同步ms_db中所有表
replicate_wild_ignore_table=
如果通过克隆的方式配置主从,会导致mysql uuid相同,Slave_IO无法启动,报错信息:The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决方法:修改数据存放目录(/data/mysql)下的auto.cnf文件,使uuid不同即可