MySQL优化之――复制 - 数据库编程

ble: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 107 Relay_Log_Space: 436 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) ERROR: No query specified

注意：如果在主库上面（192.168.1.102）的复制用户repl没有允许远程主机从库的访问，那么在执行show slave status的时候就会报错

 Last_IO_Errno: 1130
 Last_IO_Error: error connecting to master 'repl@192.168.1.102:3360  retries: 8640006' - retry-time: 60  retries: 86400

这时候，只需要在主库（192.168.1.102）上面执行下面语句即可

use mysql;
select * from user where user='repl';
update user set host = '%' where user ='repl';
flush privileges;

7、在主库和从库上面是否成功设置复制功能，首先在主库（192.168.1.102）上查看test库中的表

use test;
show tables;

查询从库中（192.168.1.100）test库里表的情况

use test;
show tables;

跟主库一样

8、在主库（192.168.1.102）中增加表rep_t ，并插入数据

create table rep_t(data int);

insert into rep_t values(1);

9、在从库（192.168.1.100）上查询表是否已经创建并复制数据到从库中

USE test;

show variables like '%server%';

show tables;

SELECT * FROM rep_t;

至此，主从库成功切换

如果主机和从机server-id一样如何解决

通常情况下，master和slave的server-id是不会一样的，如果一样的话会出现报错

出现这种情况，用户可以使用如下命令来查看服务器的server-id，然后手动进行修改，如下所示

show variables like '%server_id%';

Variable_name Value 
------------- ----- 
server_id     2     

(1 row(s) affected)

SET global server_id=1

修改完成后，执行slave start命令，查询slave主机的状态，查看问题可否解决

从机状态显示Last_IO_Error错误代码为2013的原因

有时候会遇到这样的情况，在执行show slave status \G 命令中 Slave_IO_Running和Slave_SQL_Running的值都是YES

但是Last_IO_Error发生2013错误

发生这种问题主要原因是网络问题，首先要检查下master主机创建的用户是否授予远程连接的权限

GRANT replication slave ON *.*TO repl@'%' IDENTIFIED BY '123';

这里%表示任何的repl用户都可以访问master主机，另外需要查看是否有防火墙设置和网络的其他故障

MYSQL复制不同步的原因

mysql replication（复制）采用binlog进行网络传输，所以网络延时是产生mysql主从不同步的主要原因，这会给我们进行读写分离带来

一定困难为了避免这种情况，在配置服务器的时候推荐使用INNODB存储引擎的表，在主机上可以设置sync_binlog

下面内容摘抄自《MYSQL行调优和架构设计》

“sync_binlog”：这个参数是对于 MySQL 系统来说是至关重要的，他不仅影响到 Binlog 对 MySQL 所

带来的性能损耗，而且还影响到 MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如

下：

● sync_binlog=0，当事务提交之后，MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中

的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache 满了之后才同步到磁

盘。

● sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来

将 binlog_cache 中的数据强制写入磁盘。

在 MySQL 中系统默认的设置是 sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性

能是最好的，但是风险也是最大的。因为一旦系统 Crash，在 binlog_cache 中的所有 binlog 信息都会被

丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统

Crash，也最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验

和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为 0 和设置为 1 的系统写入性能差

距可能高达 5 倍甚至更多。

如果master主机上的max_allowed_packet比较大，但是从机上没有配置该值的话，该参数还是使用默认值1MB

此时很有可能导致同步失败，建议主从两台机器都设为5MB比较合适

MySQL优化之――复制(六)