Centos下MySQL主从同步配置-CSDN博客

说明：由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样，

因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，
Master的版本肯定不能高于Slave版本。（版本向下兼容）

一、环境

主机： master操作系统：centos 7 64位

IP：192.168.119.253

MySQL版本：5.5.50-MariaDB

从机： slave操作系统：centos 7 64位

IP：192.168.119.252

MySQL版本：5.5.50-MariaDB

二、创建数据库

分别登录master机和slave机的mysql：mysql –u root –p

创建数据库：create database repl;

三、master机和slave机的相关配置

1、修改master机器中mysql配置文件my.cnf，该文件在/etc目录下

在[mysqld]配置段添加如下字段

server-id=1
log-bin=mysql-bin
log-slave-updates=1
binlog-do-db=repl #需要同步的数据库,如果没有本行表示同步所有的数据库
binlog-ignore-db=mysql #被忽略的数据

在master机上为slave机添加一同步帐号

MariaDB[(none)]>grant replication slave on *.* to 'repl'@'192.168.119.252' identified by '123456';

MariaDB[(none)]>flush privileges;

重启master机的mysql服务：service mysqld restart

用show master status 命令看日志情况

MariaDB[(none)]>show master status;

通过该命令获得File和Position，在slave中有用。注：基准这里的“mysql-bin.000001”和“245”,在下面 “（3）设置Slave复制”的配置中会用到

2、修改slave机中mysql配置文件

（1）修改slave机器中mysql配置文件my.cnf，该文件在/etc目录下

同样在[mysqld]字段下添加如下内容

server-id=2
log-bin= mysql-bin
relay-log= mysql-relay-bin
read-only=1
log-slave-updates=1
replicate-do-db=repl #要同步的数据库,不写本行表示同步所有数据库

然后重启slave机的mysql：service mysqld restart

（2）在slave机上验证对主机连接

 
          mysql -h192. 
          168.119 
          . 
          253  
          -urepl -p123456

MariaDB[(none)]>show grants for repl@192.168.119.252;

（3）设置Slave复制

CHANGE MASTER TO
MASTER_HOST='192.168.119.253',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245,
MASTER_CONNECT_RETRY=10;

（4）启动Slave

运行SHOW SLAVE STATUS查看输出结果：

主要查看Slave_IO_Running和Slave_SQL_Running 两列是否都为YES

四、测试主从服务器是否能同步

在主服务器上面新建一个表，必须在repl数据下

mysql> use repl

Database changed

mysql> create table test(id int,name char(10));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'zaq');

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,'xsw');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+------+------+

| id | name |

+-------+------+

| 1 | zaq |

| 1 | xsw |

+-------+------+

2 rows in set (0.00 sec)

在从服务器查看是否同步过来

mysql> use repl;

Database changed

mysql> select * from test;

+------+------+

| id | name |

+------+------+

| 1 | zaq |

| 1 | xsw |

+------+------+

2 rows in set (0.00 sec)

说明已经配置成功。

六、扩展——MySQL主从复制几个重要的启动选项

(1) log-slave-updates
log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作

(2) master-connect-retry

master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒

(3) read-only

read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作

(4) slave-skip-errors

在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。

Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。

--slave-skip-errors=[err1,err2,…….|ALL]

但必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本

七、问题排查

1.重启发现mariaDB无法启动可以通过查看日志来确定问题

2.错误在主机中执行了

 
          CHANGE MASTER  
          TO 
         
          MASTER_HOST= 
          '192.168.119.253' 
          , 
         
          MASTER_USER= 
          'repl' 
          , 
         
          MASTER_PASSWORD= 
          '123456' 
          , 
         
          MASTER_PORT=3306, 
         
          MASTER_LOG_FILE= 
          'mysql-bin.000001' 
          , 
         
          MASTER_LOG_POS=714, 
         
          MASTER_CONNECT_RETRY=10;

要清除slave,那么主机mariaDB中执行 reset slave all; 既可以

8.SHOW SLAVE STATUS输出的各列的含义详解

 
     
          <strong>MASTER_LOG_FILE</strong>  
          and  
          <strong>MASTER_LOG_POS</strong> are the coordinates  
          at  
          which the slave I/O thread should  
          begin  
          reading  
          from  
          the master the  
          next  
          time  
          the thread starts. 
         
 
          <strong>RELAY_LOG_FILE</strong>  
          and  
          <strong>RELAY_LOG_POS</strong> are the coordinates  
          at  
          which the slave SQL thread should  
          begin  
          reading  
          from  
          the relay log the  
          next  
          time  
          the thread starts. 
         
 
          If you specify either  
          of  
          MASTER_LOG_FILE  
          or  
          MASTER_LOG_POS, you cannot specify RELAY_LOG_FILE  
          or  
          RELAY_LOG_POS. 
         
 
          If you specify either  
          of  
          MASTER_LOG_FILE  
          or  
          MASTER_LOG_POS, you also cannot specify MASTER_AUTO_POSITION = 1 (described later  
          in  
          this  
          section 
          ). 
         
 
          If neither  
          of  
          MASTER_LOG_FILE  
          or  
          MASTER_LOG_POS  
          is  
          specified, the slave uses the  
          last  
          coordinates  
          of  
          the slave SQL thread before CHANGE MASTER  
          TO  
          was issued. 
         
 
          This ensures that there  
          is  
          no  
          discontinuity  
          in  
          replication, even if the slave SQL thread was late compared  
          to  
          the slave I/O thread, 
         
 
          when  
          you merely want  
          to  
          change, say, the  
          password  
          to  
          use. 
         
 
          翻译： 
         
 
   

 
          <strong>MASTER_LOG_FILE</strong> 和 <strong>MASTER_LOG_POS</strong> 下次slave线程启动从主机中读取数据的位置

 
          <strong>RELAY_LOG_FILE</strong> 和 <strong>RELAY_LOG_POS </strong>下次SQL线程启动从relay log中读取数据的位置<br>如果指定了MASTER_LOG_FILE 和MASTER_LOG_POS,那么就不能在指定 <strong>RELAY_LOG_FILE</strong> 和 <strong>RELAY_LOG_POS ,</strong>同时也不能指定MASTER_AUTO_POSITION=1<br>如果MASTER_LOG_FILE 和MASTER_LOG_POS都不指定, 从机将采取执行 CHANGE MASTER  
          TO 
          之前的最后一次的位置

 
          <br><br>

 
     
          <strong>MASTER_BIND</strong>  
          is  
          for  
          use  
          on  
          replication slaves  
          having  
          multiple network interfaces,  
          and  
          determines which  
          of  
          the slave's network interfaces  
          is  
          chosen  
          for  
          connecting  
          to  
          the master. 
         
 
   

 
     
          翻译:<br>MASTER_BIN 针对有多个网络接口的通过它来决定使用那个网络接口<strong>MASTER_CONNECT_RETRY</strong> specifies how many seconds  
          to  
          wait  
          between  
          connect  
          retries. The  
          default  
          is  
          60. <em id= 
          "__mceDel" 
          >翻译：</em> 
         
 
   

 
     
          <em id= 
          "__mceDel" 
          ><em id= 
          "__mceDel" 
          >MASTER_CONNECT_RETRY 指定多久重连,默认是60秒<br><br><strong>MASTER_DELAY</strong> specifies how many seconds behind the master the slave must lag. An event received  
          from  
          the master  
          is  
          not  
          executed until  
          at  
          least interval seconds later than its execution <br> 
          on  
          the master. The  
          default  
          is  
          0. An error occurs if interval  
          is  
          not  
          a nonnegative  
          integer  
          in  
          the range  
          from  
          0  
          to  
          231−1<br>翻译：<br>MASTER_DELAY 指定相对于主机延迟多久 
         

             
         

             
         
 
          <strong>MASTER_USER</strong>  
          and  
          <strong>MASTER_PASSWORD</strong> are the  
          user  
          name  
          and  
          password  
          of  
          the account  
          to  
          use  
          for  
          connecting  
          to  
          the master<br>翻译：<br>MASTER_USER 和 MASTER_PASSWORD　链接主机的账号密码 
         

             
         
 
          <strong>MASTER_HOST</strong>  
          and  
          <strong>MASTER_PORT</strong> are the host  
          name  
          ( 
          or  
          IP address)  
          of  
          the master host  
          and  
          its TCP/IP port<br>　　 Note 
         
 
            
          　 Replication cannot use Unix socket files. You must be able  
          to  
          connect  
          to  
          the master MySQL server using TCP/IP.<br>翻译：<br>MASTER_HOST 和 MASTER_PORT 主机的计算机名或IP地址，以及端口号<br>　　注意：主从复制必须使用TCP/IP，不能使用socke<br> 
         

             
         
 
          <strong>MASTER_RETRY_COUNT</strong> limits the number  
          of  
          reconnection attempts  
          and  
          updates the value  
          of  
          the Master_Retry_Count  
          column  
          in  
          the  
          output  
          of  
          SHOW SLAVE STATUS. 
         
 
          翻译：<br>　　MASTER_RETRY_COUNT 限制重连的次数<br><br> 
         
 
          <strong>RELAY_LOG_FILE</strong> can use either an  
          absolute  
          or  
          relative  
          path,  
          and  
          uses the same base  
          name  
          as  
          MASTER_LOG_FILE. (Bug #12190)<br>翻译：<br>　　RELAY_LOG_FILE 可以使用相对或绝对的路径，或使用 MASTER_LOG_FILE相同的基础的文件名 
         

             
         
 
          <strong>IGNORE_SERVER_IDS</strong> takes a comma-separated list  
          of  
          0  
          or  
          more server IDs. <br>翻译：<br>　　IGNORE_SERVER_IDS 使用都好分割的server IDS<br></em></em> 
         
 
   