mysql主从配置示例

0、基本信息：

win7系统

mysql版本：5.1.47

主mysql数据库ip：192.168.1.229

从mysql数据库ip：192.168.1.119

---

常用到的语句：

flush tables with read lock;
unlock tables;
show master status;

change master to master_host='192.168.1.229',master_user='sasuke',master_password='sasukeuser',master_port=3306,master_connect_retry=60,master_log_file='mysql-bin.000001',master_log_pos=106;

show slave status;
stop slave;
set global sql_slave_skip_counter=1;
start slave;

mysqlbinlog --start-position="161477356" WIN-CES5DI7KTSN-relay-bin.000005 -d mydatabase>   testsql.sql

---

1、主mysql数据库设置

（1）、停止mysql服务器，给my.ini文件最后添加如下内容：

注意：my.ini文件一般在mysql的安装目录下（如：C:\Program Files\MySQL\MySQL Server 5.1\ 目录下）

# add by jery in 20160902
server-id = 1                               #主从id需要不一同
log-bin = mysql-bin                  #开始mysql的日志记录功能
binlog-do-db = labg1601        #表示只记录labg1601的日志（多个可以写多个，不写则记录所有数据库的日志）
binlog-ignore-db = mysql     #表示不记录mysql的日志（多个可以写多个，不写则以binlog-do-db配置的为准）

log-slave-updates = 1             #这个参数一定要加上，否则不会给更新的记录些到二进制文件里

slave-skip-errors = 1               #表示是跳过错误，继续执行复制操作

binlog_format = MIXED      #binlog日志格式，mysql默认采用statement，建议使用mixed

【注意：mysql到5.5版本时，默认binlog_format格式是statement，所以主从配置时，mysql主从my.ini都需要配置该选项binlog_format=MIXED，这里binlog_format=MIXED、binlog_format='MIXED'都可以配置成功，命令配置有误引号也可配置成功，如 SET GLOBAL binlog_format=ROW;SET GLOBAL binlog_format=‘ROW’;】

【该配置详情见mysql主从复制模式及遇到的问题_XiaoXiao_RenHe的博客-CSDN博客】

（2）、给从服务器开一个可以复制主服务器binlog的用户

在主服务器上，使用root用户登录mysql后，创建用户如：sasuke，密码 sasukeuser

mysql> grant replication slave on  *.* to sasuke@192.168.1.119 identified by 'sasukeuser';

新建一个用户（用户名/密码：sasuke/sasukeuser），赋予该用户复制功能权限（replication slave），且只允许该用户从192.168.1.119上来复制主服务器的信息

具体操作如下图所示：

（3）、备份该主服务上的labg1601数据库为labg1601_20160902.sql

2、从mysql数据库设置

（1）、停止mysql服务器，给my.ini文件最后添加如下内容：

# and by jery in 20160902
server-id = 2                                              #主从数据库id必须不同
log-bin = mysql-bin                                  #开始mysql的日志记录功能
master-host = 192.168.1.229                #表示主数据库的ip
master-user = sasuke                            #表示需要访问主数据库的用户名
master-password = sasukeuser          #表示需要访问主数据库的密码
master-port = 3306                                  #表示主数据库的端口号
master-connect-retry = 60                      #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db = labg1601                   #表示需要复制主服务器上的数据库是labg1601
replicate-ignore-db = mysql                  #表示不从主服务器上复制的数据库mysql（多个可以写多个，不写则以replicate-do-db配置的为准）

log-slave-updates = 1                            # #这个参数一定要加上，否则不会给更新的记录些到二进制文件里
slave-skip-errors = 1                              # 表示是跳过错误，继续执行复制操作

（2）、从服务器上验证sasuke是否可以连接主数据库，并查看权限信息：

黑窗口中输入> mysql -h 192.168.1.229 -u sasuke -p

                        >*********（这里输入sasukeuser，及sasuke的密码）

连接成功后mysql>show grants for sasuke@192.168.1.119;（查看该用户在主服务器上的权限）

具体操作如下图所示：

（3）、从服务器使用root用户登录，设置从mysql：

    1）、停止从mysql的复制进程（slave进程）（以防万一）

mysql> slave stop;

    2）、设置从mysql复制进程slave的复制文件及复制开始位置

mysql> change master to master_log_file='mysql-bin.000001',master_log_pos=1281;

    3）、启动从mysql的复制进程（slave进程）

mysql> slave start;

    4）、查看从mysql的复制进程（slave进程）

mysql> show slave status\G;

    5）、解锁住库表

mysql > unlock tables;

具体操作见下图：

（4）、给从mysql还原之前在主mysql上备份的数据库labg1601_20160902.sql

3、主从数据变化验证

（1）、主mysql的主mysql的labg1601中v_sys_log表记录删除前：

     1）、主mysql的labg1601中v_sys_log表中记录总数为1030条。

   2）、从mysql的labg1601中v_sys_log表中记录总数为1020条

（2）、主mysql的主mysql的labg1601中v_sys_log表删除三条记录（这三条记录不是从mysql的labg1601中v_sys_log中缺少的那10条记录）：

    1）、主mysql的labg1601中v_sys_log表中记录总数为1027条。

 

    2）、主mysql的labg1601中v_sys_log表中记录总数为1017条。

4、已经安装的且配有log-bin的mysql，重启可能报1067错误，解决办法：

将D:\ProgramData\MySQL\MySQL Server 5.1\data（数据存放目录）该目录下mysql-bin.index里面的内容删除后重新启动。

该目录下，ib_logfile0 \ ib_logfile1 \ master.info  均可以删除后，重新启动mysql

另外可能引起问题原因是mysql版本，mysql5.5版本不支持my.ini配置文件设置master-host，master-port，master-user，

master-password参数，且参数log-slave-updates直接被禁用了，mysql5.5可如下设置主库信息

（注意：这里的master_host应调整为192.168.1.229）：

change master to master_host='192.168.1.229',master_user='sasuke',master_password='sasukeuser',master_port=3308,master_connect_retry=60,master_log_file='mysql-bin.000002',master_log_pos=107;

change master to master_host='192.168.1.229',master_user='sasuke',master_password='sasukeuser',master_port=3308,master_connect_retry=60,master_log_file='mysql-bin.000002',master_log_pos=107;

mysql5.7版本sql_mode=only_full_group_by默认设置，会导致一些查询报500错误

注意：如果是新安装的mysql，这里删除没有问题，如果mysql已经使用了好长时间，那么千万千万不要删除 ibdata1【这里存放数据库的各种数据、data目录下存放数据库的各种表结构】，这样就可以用该种方式备份数据库。

6、主从同步期间遇到的错误

从数据库查看状态时，出现如下错误，如mysql报1062、1452错误

mysql>show slave status\G;

...

io thread    YES

sql thread  NO

...

mysql 1062错误----------》主从同步第二次，即从库已经存在该条数据，但主从还在继续同步报错【可能原因直接在从库进行了增删改操作】

mysql 1452错误----------》主从同步时，同步的数据有外键约束，但是从库的外键对应的主表无数据

7、IO thread--------》从库从主库拿binlog日志的进程

SQL thread-------》从库执行从主库拿到的binlog日志进行

8、几个文件作用：

master.info---------》记录主库访问信息【同步文件、同步位置、ip/port/用户名/密码等】

mysql-bin.index /server2-relay-bin.index-----------》记录当前数据库的binlog文件汇总 /记录从主库拿过来的binlog日志文件汇总

relay-log.info-------》中继日志，记录从库执行到当前的binlog日志及位置，主库的binlog日志及位置

ib_logfile0 / ib_logfile1------》记录事务commit之前的数据（redo、undo、回滚），记录回滚、日志重做

mysql-bin.000088---------》记录事务commit之后的数据，数据库已经操作过的更新数据记录

server2.err--------》当前mysql错误日志文件

---

做主从配置时，若出现不同步的现象，可以将从库的 master.info/ server2-relay-bin系列文件/ ib_logfile系列文件/ server2.err删除后，重新做同步

可能用到的其他命令：

slave stop;

set global sql_slave_skip_counter=1;  (跳过一个错误）

slave start;

reset slave all;

主库加解锁：

flush tables with read lock;

unlock tables;

9、mysql主从停止时，如下截图，坏掉的位置是 68619653

可以在从服务器使用如下语句导出sql语句，查看主从到哪块坏掉了【从导出语句的最开始往下看，很快能看到报错的语句，可以顺次执行该条语句及之前的语句，启动slave服务后若报错，发现新报错是刚才执行的几条语句之一，可以跳过】

mysqlbinlog --start-position=68619653  WIN-CES5DI7KTSN-relay-bin.000194 -d mydatabase > mydatabase.sql