0. Thanks To
mysql （master/slave）复制原理及配置
Last_IO_Error: error connecting to master Last_IO_Errno: 2003解决方法
MysqlMaster/Slave备份
修改mysql默认字符集的方法
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
网友评论