MySQL开启binlog, 配置主从小记

发表于   |   分类于
本文阅读大概需要 4 分钟

开启binlog

在 /etc/my.conf 中打开 log-bin=/your/dir, 重启Mysql服务

根据不同的安装, MySQL的配置文件的位置可能不同, 视具体情况而定.

配置哪些数据库需要记录binlog:

1
2
3
4
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-do-db=kit
  • binlog-ignore-db配置哪些数据不需要记录binlog
  • binlog-do-db 配置哪些数据需要记录binlog

查看binlog状态

查看binlog的名称

1
2
3
4
5
6
7
mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       616 |
+---------------+-----------+
1 row in set (0.00 sec)

查看二进制日志的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show binlog events;
+---------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                |
+---------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
| binlog.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.30-log, Binlog ver: 4                                                               |
| binlog.000001 | 120 | Query       |         1 |         197 | BEGIN                                                                                               |
| binlog.000001 | 197 | Query       |         1 |         317 | use `kit`; UPDATE `user` SET `username`='xxxx' WHERE `id`=5                                         |
| binlog.000001 | 317 | Xid         |         1 |         348 | COMMIT /* xid=72 */                                                                                 |
| binlog.000001 | 348 | Query       |         1 |         425 | BEGIN                                                                                               |
| binlog.000001 | 425 | Query       |         1 |         585 | use `kit`; INSERT INTO `user_profile` (`user_id`, `sex`, `last_login_ip`) VALUES (5, 1, 3232238081) |
| binlog.000001 | 585 | Xid         |         1 |         616 | COMMIT /* xid=77 */                                                                                 |
+---------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
7 rows in set (0.01 sec)

导出二进制文件

1
$ mysqlbinlog --start-position=4 --stop-position=585 binlog.000001 > /home/out.txt

或者全部导出:

1
$ mysqlbinlog binlog.000001 > /home/out.txt

导出的文件名可以通过上面的show binary logs; 来查看需要导出的binlog文件

position 可以根据 show binlog events中的Pos字段来确定.

配置主从

查看主库状态

1
2
3
4
5
6
7
mysql> show master status;
+---------------+----------+--------------+---------------------------------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+---------------+----------+--------------+---------------------------------------------+-------------------+
| binlog.000003 |      120 | kit          | mysql,information_schema,performance_schema |                   |
+---------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

主从配置

配置主库

修改Master服务器MySQL配置文件:

1
2
log-bin=binlog //必须开启binlog
server-id=1 // 必须, 服务器唯一ID, 默认是1,一般取IP最后一段

在Master服务器上建立帐户并授权slave:

1
mysql > grant replication slave on *.* to slave01@192.168.10.12 identified by '123qwe';

重启MySQL.然后查看主库状态.

配置从库

修改Slave服务器MySQL配置文件：

1
2
log-bin=binlog //不是必须
server-id=2 //必须, 需要唯一,一般取IP最后一段

重启MySQL.

配置Slave:

1
2
mysql> change master to master_host='192.168.10.11',master_user='slave01',master_password='123qwe';
mysql> start slave;
  • master_host 配置为Master服务器的IP地址
  • master_user 和 master_password 分别为在Master服务器上进行授权的账号和密码

查看从库状态：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql>show slave status;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.11
                  Master_User: slave01
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 1155
               Relay_Log_File: mysqld-relay-bin.000009
                Relay_Log_Pos: 1315
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
							...

之前需要将主库的数据导到从库.

遇到的问题

在从库执行 show slave staus 后发现：

1
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;  these UUIDs must be different for replication to work.

在网上找到的原因和解决方案是：

这个错误是由于主从使用了相同的UUID「在主从的server_id确保是各不相同的情况下」.

首先分别在主从机器上查看：

1
2
3
[root@master ~]# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=3636a3ee-21b8-11e6-aebc-080027e27e5e
1
2
3
[root@slave ~]# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=3636a3ee-21b8-11e6-aebc-080027e27e5e

发现是相同的，可以通过以下方法解决：

在从库机器上：

1
2
[root@slave ~]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.back
[root@slave ~]# service mysqld restart

通过重启MySQL来重新生成uuid. 最后问题解决.

另外，我们需要在从库上配置授权访问，保证其他服务器可以访问：

1
mysql> grant all on *.* to vagrant@'%' identified by 'vagrant' with grant option;

%标示任何IP地址都可以进行访问, 当然也可以指定服务器IP, 保证自己的服务器能够访问, 而其他IP不能访问。