Mysql主从配置

  • Mysql主从介绍
  • 准备工作
  • 配置主
  • 配置从
  • 测试主从同步

Mysql主从介绍

Mysql主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的。
Mysql主从是基于binlog的,主上须开启binlog才能进行主从。
主从过程大致有3个步骤

  1. 主将更改操作记录到binlog里
  2. 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
  3. 从根据relaylog里面的sql语句按顺序执行

主上有一个log dump线程,用来和从的i/o线程传递binglog
从上有两个线程,其中i/o线程用来同步主的binlog并生成relaylog,另外一个sql线程用来把relaylog里面的sql语句落地

Mysql主从配置

准备工作(两台机器上搭建mysql)
mysql安装

master:192.168.221.10 mysql 关闭防火墙和selinux

slave: 192.168.221.20 mysql 关闭防火墙和selinux

配置主master

vim /etc/my.cnf  //以下是配置文件内容
log_bin = apenglinux
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
server_id = 128
socket = /tmp/mysql.sock

查看由log_bin生成的相关的文件

ls -lt /data/mysql/|grep apenglinux  //以下是文件内容
-rw-rw----. 1 mysql mysql      260 2月  25 02:33 apenglinux.index
-rw-rw----. 1 mysql mysql      120 2月  25 02:33 apenglinux.000013
-rw-rw----. 1 mysql mysql      143 2月  25 01:34 apenglinux.000012
-rw-rw----. 1 mysql mysql      143 2月  25 01:32 apenglinux.000011
-rw-rw----. 1 mysql mysql      143 2月  25 01:13 apenglinux.000010
-rw-rw----. 1 mysql mysql      143 2月  24 22:27 apenglinux.000009
-rw-rw----. 1 mysql mysql      143 2月  24 22:23 apenglinux.000008
-rw-rw----. 1 mysql mysql      143 2月  24 22:16 apenglinux.000007
-rw-rw----. 1 mysql mysql      143 2月  24 22:14 apenglinux.000006
-rw-rw----. 1 mysql mysql      143 2月  24 22:12 apenglinux.000005
-rw-rw----. 1 mysql mysql      143 2月  24 22:11 apenglinux.000004
-rw-rw----. 1 mysql mysql      143 2月  24 22:08 apenglinux.000003
-rw-rw----. 1 mysql mysql      143 2月  24 22:08 apenglinux.000002
-rw-rw----. 1 mysql mysql      143 2月  24 22:07 apenglinux.000001

创建一个数据库apeng并将mysql库备份恢复到apeng库上。

mysql -uroot -p -e "create database apeng;"
mysqldump -uroot -p mysql > /tmp/mysql.sql
mysql -uroot -p apeng < /tmp/mysql.sql

配置用作数据同步的用户,锁表,查看master的状态

mysql -uroot -p
mysql> grant replication slave on *.* to 'repl'@'192.168.221.20' identified by '123';
mysql> flush tables with read lock;
mysql> show master status;  //以下状态信息
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| apenglinux.000013 |   656513 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

查看master上有哪些数据库

mysql -uroot -p -e "show databases;"  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apeng              |
| mysql              |
| performance_schema |
| test               |
+--------------------+

为了保证数据的一致性,还需要备份apeng数据库,然后将备份传到slave机器上。

mysqldump -uroot -p apeng > /tmp/apeng.sql

配置从slave

vim /etc/my.cnf  //增加如下
server_id=130
/etc/init.d/mysqld restart

将master上的数据库apeng的备份文件拷贝到slave上

scp 192.168.221.10:/tmp/apeng.sql /tmp/

创建数据库apeng,恢复数据库到apeng库

mysql -uroot -p -e "create database apeng;"
mysql -uroot -p apeng < /tmp/apeng.sql
mysql> stop slave;
mysql> change master to master_host='192.168.221.10',master_port=3306,master_user='repl',master_password='123',master_log_file='apenglinux.000013',master_log_pos=656513;
mysql> start slave;
mysql> show slave status\G

以下是查看到salve的信息,包含以下说明主从配置成功

      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
         Seconds_Behind_Master: 0
         Last_IO_Errno: 0
         Last_SQL_Errno: 0

在master作如下设置

mysql> unlock tables;

测试主从同步

在master上创建数据库along,在along上创建表students并插入数据,显示结果如下
mysql> create database along;
mysql> use along;
mysql> create table students( id int(3) not null auto_increment primary key, name varchar(50) not null );
mysql> insert into students(name)
    -> values ('zhangsan'),('lisi'),('wangwu'),('zhaoliu');

mysql> select * from students;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhaoliu  |
+----+----------+

在slave上查看

mysql> select * from along.students;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhaoliu  |
+----+----------+

/etc/my.cnf配置文件中可以这样定义

master
binglog-do-db=     //仅同步指定的库
binglog-ignore-db=   //忽略指定库
slave
replication_do_db=  
replication_ignore_db=
replication_do_table=
replication_ignore_table=
replication_wild_do_table=   //如apeng.%
replicatin_wild_ignore_table=  

在slave上删除了数据库,马上查从的状态,从显示ok。
在master删除了同样的数据库,再次查slave的状态会有如下提示

 Last_SQL_Error: Error 'Can't drop database 'along'; database doesn't exist' on query. Default database: 'along'. Query: 'drop database along'    //以下方法可以解决(数据还是一样的情况下)

在主上查看master的状态。

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| apenglinux.000013 |   657627 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

在slave上作如下配置

mysql> stop slave;
mysql> change master to master_host='192.168.221.10',master_port=3306,master_user='repl',master_password='123',master_log_file='apenglinux.000013',master_log_pos=657627;
mysql> start slave;
mysql> show slave status\G   //这时主从数据库又恢复正常

对于以下这种错误,可以这样解决。
dba:(none)> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

在保证主从数据一致时,可以
mysql> reset slave;
mysql> stop slave;
mysql> change master to master_host='',..... 这里就省略了
mysql> start slave;
mysql> show slave status\G
这样就可以保证同步了