Centos下MySQL主从同步配置

说明：由于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输出的各列的含义详解


MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the slave I/O thread should begin reading from the master the next time the thread starts. 
RELAY_LOG_FILE and RELAY_LOG_POS 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.
翻译：

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

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

MASTER_BIND 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.

翻译:
MASTER_BIN 针对有多个网络接口的通过它来决定使用那个网络接口MASTER_CONNECT_RETRY specifies how many seconds to wait between connect retries. The default is 60. 翻译：

MASTER_CONNECT_RETRY 指定多久重连,默认是60秒

MASTER_DELAY 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 
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
翻译：
MASTER_DELAY 指定相对于主机延迟多久


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

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


MASTER_RETRY_COUNT limits the number of reconnection attempts and updates the value of the Master_Retry_Count column in the output of SHOW SLAVE STATUS.
翻译：
　　MASTER_RETRY_COUNT 限制重连的次数


RELAY_LOG_FILE can use either an absolute or relative path, and uses the same base name as MASTER_LOG_FILE. (Bug #12190)
翻译：
　　RELAY_LOG_FILE 可以使用相对或绝对的路径，或使用 MASTER_LOG_FILE相同的基础的文件名

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

　　

参考：http://www.cnblogs.com/meetrice/p/5311839.html

How to Setup MariaDB (Master-Slave) Replication in CentOS/RHEL 7 and Debian 8