设置mysql主从库数据同步的配置方法

发表于 | 分类于 | | 阅读数

主机（master）配置：

1.修改mysql配置文件my.ini
在[mysqld]标签下添加以下几行

1
2
3
4
5
6
7
8
9
log-bin=名字    这个是开启二进制日志 如果名字mysql-bin 生成的二进制日志名为:mysql-bin.000001

server-id=你设置的数值   这个是唯一的id标识 默认为1 不能和从服务器重复

binlog-do-db=数据库名    这个是你要记录的数据库名

binlog-ignore-db=数据库名     指定不对哪个数据库记录二进制日志 不是必需要设的

max_allowed_packet=消息缓冲区的大小   这个数值主从设置的要一致最好设置大点 如:1G 使用命令行查看方式 show variables like '%max_allowed_packet%';

2.为从服务器添加mysql账户并配置权限

使用cmd命令进入mysql 输入进行权限设置

1
2
3
GRANT FILE ON *.* TO ‘backup’@'192.168.0.3' IDENTIFIED BY ‘1234’; 

GRANT REPLICATION SLAVE ON *.* TO ‘backup’@'193.168.0.3' IDENTIFIED BY ‘1234’;

这个账号和密码需有相应的权限如：从服务器ip使用此账号和密码可以连接到主服务器的数据库

3.记录File 及Position 项的值
　　重启mysql，使用show master status;查看主服务器状态，记录File 及Position 项的值，以便之后对从服务器进行配置。

从库（slave）配置：

1.修改mysql配置文件my.ini
在[mysqld]标签下添加以下面一行：

max_allowed_packet=消息缓冲区的大小 这个数值主从设置的要一致最好设置大点 如:1G 使用命令行查看方式 show variables like ‘%max_allowed_packet%’;

server-id=你设置的数值 这个是唯一的id标识 默认为2 不能和主服务器重复

replicate-do-db=数据库名 这个是你要读取的数据库名

2.重启mysql数据库并设置相关参数

使用cmd 命令进入mysql 输入命令

change master to master_host=’主服务器ip地址’,master_port=主服务器端口,master_user=’设置的主服务器账号’,master_password=’主服务器密码’,master_log_file=’二进制日志名File’,master_log_pos=Position 项;

最后输入命令查看是否配置正确

SHOW SLAVE STATUS\G

如果看到Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 正说明配置正确

停止复制数据需在从服务器命令行下输入 stop slave;

开启复制数据 start slave;

—————————————-补充和出错的可能———————————————-
一、需要把正式环境的数据导出一份到测试数据库

这个有几种办法，可以使用master/slave同步，也可以导出成SQL，然后再倒入。我喜欢导入/导出的方式。假设 192.168.0.10是master，192.168.0.11-14是slave。

#mysql -h192.168.0.10 -P3306 -uroot -ppassword databasename > /home/xieping/data.sql

#mysql -h192.168.0.11 -P3306 -uroot -ppassword databasename < /home/xieping/data.sql

上面的命令要相对简单一些，也可以使用mysqldump命令，或者tar命令。

导出后再设置slave，参考下面的第二节。

二、需要添加一个slave

添加slave设置好my.conf就可以了。现在问题是在同步的过程中，IO部分完成了，但是SQL部分一直报错。

使用

#stop slave;

#set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

#start slave;

这种操作还是报错中断。

这种情况需要查看master的状态。

#mysql -h192.168.0.10 -uroot -p

mysql> show master status\G;

*** 1. row ***

        File: mysql-bin.000012

    Position: 108822735

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

ERROR:

No query specified

通过该命令获得File和Position，在slave中有用。

#mysql -h192.168.0.11 -uroot -p

mysql>stop slave;

mysql>change master to

　　 >Master_Log_File = ‘mysql-bin.000012’,

　　 >Master_Log_Pos = ‘108822735’;

mysql>show slave status\G;

*** 1. row ***

         Slave_IO_State: Waiting for master to send event

            Master_Host: 192.168.0.10

            Master_User: root

            Master_Port: 3306

          Connect_Retry: 60

        Master_Log_File: mysql-bin.000012

    Read_Master_Log_Pos: 108942588

         Relay_Log_File: localhost-relay-bin.000035

          Relay_Log_Pos: 22317781

  Relay_Master_Log_File: mysql-bin.000012

       Slave_IO_Running: Yes

      Slave_SQL_Running: Yes

        Replicate_Do_DB: databasename

    Replicate_Ignore_DB: 

     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: 108942317

      Relay_Log_Space: 22318211

      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: 5

Master_SSL_Verify_Server_Cert: No

 Last_IO_Errno: 0

 Last_IO_Error: 

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

ERROR:

No query specified

现在就正常了， Seconds_Behind_Master: 5 表明slave比master略有延迟。

三、更换master

更换master如果通过修改my.conf的话比较麻烦。不但要重启mysql进程，而且还不一定成功。可以通过

mysql> stop slave;

mysql> reset slave;

mysql>start slave;

不过这样一来就重置了更新进度，更新会从头开始。

而是要change master to命令则可以通过命令设置，但这个命令不会影响my.conf文件，重启mysql后，设置就无效了。