mysql的master slave_Mysql-Master-Slave主从配置（1）

0. Thanks To

1. 概述

概念：

主从数据备份，是指，当主服务器上数据库中的数据变化的时候，从服务器的数据库跟着变化，从而达到实时备份的目的。

原理：

其大概的原理是：当主服务器上的数据发生变化的时候，主服务器会把这些变化记录到一个日志文件中，然后通知从服务器，从服务器会拉取这个日志文件中的变化信息，然后做出相应的数据变化。

深究其原理的话，主从服务器上对于这个日志文件都有着一个Position位置指针，都指向当前最新的位置，当发现其不同时，变会更新。

同步数据的类型

也就是mysql支持的复制类型：

(１)：基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。

(２)：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

(３)：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

2. 配置过程

1)创建一个用户，用于从服务器登陆主服务器

从服务器需要跟主服务器建立连接，而这个连接是基于账号密码的。

创建账户语法示例：

MariaDB [test]> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*

-> TO kidBackup@'xxx.xxx.xxx.xxx'

-> IDENTIFIED BY 'ppp';

示例中，创建了一个只能由：xxx.xxx.xxx.xxx 登陆的密码为：ppp，名为：kidBackup的用户。

2)关闭主从的Mysql，先手动同步数据

在开启前，先保证主从的数据是一致的。

3)配置Master主服务器

找到Mysql的配置文件：my.cnf

位置在：

如果是lampp的Linux：/opt/lampp/etc/my.cnf

如果是直接安装的mysql的Linux：/user/local/mysql/my.cnf

找到后，打开配置文件，加入如下之：

server-id=1

log-bin=mysql-bin

binlog-do-db = test #要同步的库名

binlog-ignore-db=mysql,test #不记录日志的库,即不需要同步的库

server-id：为主服务器A的ID值

log-bin：二进制变更日值

重启master主服务器的Mysql：

1.启动：

/opt/lampp/lampp start

2.停止：

/opt/lampp/lampp stop

或者：

service mysql start

service mysql stop

运行SHOW MASTER STATUS，输出如下

1.png

可以看到，File为二进制日志文件，Position为主服务器当前指针位置。

4)配置Slave从服务器

同样的，找到Mysql的配置文件：my.cnf，位置参考上面，打开加入以下语句：

log_bin = mysql-bin

server_id = 2

relay_log = mysql-relay-bin

log_slave_updates = 1

read_only = 1

server_id是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。

relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。

有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

5)启动Slave

接下来就是让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：

mysql> CHANGE MASTER TO MASTER_HOST='这里应该是主服务器的IP',

-> MASTER_USER='刚刚生成的账号名称',

-> MASTER_PASSWORD='密码',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=0;

MASTER_LOG_POS的值为0，因为它是日志的开始位置。

你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: server1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

...omitted...

Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No,表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。实际上，MySQL知道的第一个事件的位置是4。

开启复制：

mysql> START SLAVE;

运行SHOW SLAVE STATUS查看输出结果：mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: server1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 164

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 164

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...omitted...

Seconds_Behind_Master: 0

在这里主要是看:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

4. 命令总汇

mysql> START SLAVE; //开启从服务器同步

mysql> STOP SLAVE; //关闭从服务器同步

mysql> SHOW SLAVE STATUS\G //查看从服务器的同步状态

mysql> SHOW SLAVE STATUS //查看主服务器主从配置

mysql> SHOW MASTER STATUS //查看主服务器主从配置

mysql> CHANGE MASTER TO MASTER_HOST='这里应该是主服务器的IP', //在SLAVE上主动设置Master

-> MASTER_USER='刚刚生成的账号名称',

-> MASTER_PASSWORD='密码',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=0;

5. Master语法说明

log-bin = mysql-bin #因为MYSQL是基于二进制的日志来做同步的,每个日志文件大小为 1G

server-id = 1 #主标服务标识号,必需唯一

binlog-do-db = db_user #要同步的库名

binlog-ignore-db=mysql,test #不记录日志的库,即不需要同步的库

6. SLAVE语法说明

server-id = 2

log-bin = mysql-bin #如果不做双向同步则slave机不需要写二进制日志的

log-slave-updates

master-host = 192.168.1.1

master-user = slave #Slave主机访问Master的用户名

master-password = 123456 ＃Slave主机访问Master的密码

master-port = 3306

replicate-ignore-db = mysql #不同步的数据库

replicate-do-db = db_user #要同步的数据库

7. 一些坑

1)因为mysql的版本不同问题，导致账号的密码算法不同，以至于从服务器登陆不上主服务器

使用以下语句进行设置：(在主服务器下)

set password for 'backup'@'10.100.0.200'=old_password('1234'))

2)配置完从服务器后，使用：mysql> SHOW SLAVE STATUS\G，发现有ERROR：2003

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'coolcloud@XXXX:XX' - retry-time: 60 retries: 86400

排查：

(1) 在配置文件中排查了与bindaddress和skip-networking与相关参数后，均没有配置；

(2) 于是排查服务器的防火墙，发现防火墙处于开启状态

(3) 关闭防火墙后，重新执行start slave;发现主从配置成功。

3)配置完发现同步的中文乱码

原因：主从服务器的字符集不同。

使用命令：show variables like '%char%';去查看主从服务器的字符集：

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

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

从输出我们比对一下主从服务器的字符集。若是不同则可确定问题为字符集的不停而导致了乱码。

解决方法，设置字符集。

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

以上是临时修改，要永久修改则需要修改my.cnf:

default-character-set = utf8

character_set_server = utf8