我的MYSQL学习心得（十七） 复制
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了我的MYSQL学习心得（十七） 复制，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16895字，纯文字阅读大概需要25分钟。
内容图文
[mysqld] log-bin="D:/MYSQLDataBase/binlog" expire_logs_days=10 max_binlog_size=100M server-id=1 binlog-do-db=test binlog-ignore-db=mysql
这些配置语句的含义
server-id：表示服务器表示id号，master和slave主机的server-id不能一样
binlog-do-db：表示需要复制的数据库，这里以test库为例
binlog-ignore-db：表示不需要复制的数据库
7、重启master主机上的mysql服务，然后输入show master status命令查询master主机的信息
8、将master主机的数据备份出来，然后导入到slave主机中去，具体执行语句如下
mysqldump -u root -p -h 127.0.0.1 test >D:\TEST.TXT
TEST库里面的表和数据
innodb_monitor表是没有数据的
dump出来的txt文件内容
View Code
将D:\TEST.TXT文件复制到slave机器上，然后执行如下操作
在命令行登录mysql，然后 USE TEST;
记得一定要USE TEST，切换数据库上下文，否则会报错：NO DATABASE SELECTED 的错误信息
然后执行source命令导入TEXT.txt文件的内容
可以看到，数据已经导入到slave上面了
9、配置slave机器（192.168.1.102）的my.ini配置文件
具体配置信息如下
[mysql] default-character-set=utf8 log_bin="C:/MYSQLLOG/binlog" expire_logs_days=10 max_binlog_size=100M [mysqld] server-id=2
提示：配置slave主机my.ini文件的时候，需要将server-id=2写到[mysqld]后面
另外如果配置文件中还有log_bin的配置，可以将他注释掉，如下所示
#Binary Logging #log-bin #log_bin="xxx"
10、重启slave主机（192.168.1.102）的mysql服务，在slave主机（192.168.1.102）的mysql中执行如下命令
关闭slave服务
stop slave;
11、设置slave从机实现复制相关的信息，命令如下
change master to master_host=‘192.168.1.100‘, master_user=‘repl‘, master_password=‘123‘, master_log_file=‘binlog。000004‘, master_log_pos=107; Command(s) completed successfully.
各个参数所代表的具体含义如下：
master_host：表示实现复制的主机ip地址
master_user：表示实现复制的登录远程主机的用户
master_password：表示实现复制的登录远程主机的密码
master_log_file：表示实现复制的binlog日志文件
master_log_pos：表示实现复制的binlog日志文件的偏移量
12、继续在从机执行操作，显示slave从机的状况，如下所示
start slave; Command(s) completed successfully.
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog銆?00004
Read_Master_Log_Pos: 107
Relay_Log_File: Steven-PC-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog銆?00004
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading dat
a from binary log: ‘Could not find first log file name in binary log index file‘
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
在上述执行show slave status \G命令中很显然存在一些问题，问题如下
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading dat
a from binary log: ‘Could not find first log file name in binary log index file‘
下面的步骤可以解决问题，具体步骤如下
1、重启master（192.168.1.100）主机的mysql服务，执行show master status \G命令
记下File和Position的值，后面slave主机会用到，命令执行如下
SHOW MASTER STATUS;
2、在slave（192.168.1.102）主机上重新设置信息，命令执行如下
stop slave;
change master to master_log_file=‘binlog.000005‘, master_log_pos=107;
start slave;
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 107
Relay_Log_File: Steven-PC-relay-bin.000002
Relay_Log_Pos: 250
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 410
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
这次正常了，实际上刚才有两个地方是错误的
第一个：在从机的my.ini里面
[mysql] default-character-set=utf8 #log_bin="C:/MYSQLLOG/binlog" #expire_logs_days=10 #max_binlog_size=100M
在从机的my.ini里面的mysql配置节下面配置了binlog，实际上这样做是错误的，要配置binlog需要在[mysqld]配置节下
第二个：第一次配置从机的同步的时候本人写错了标点符号，.号写成。号
master_log_file=‘binlog。000004‘,
这时候，我们可以在从机上面执行show processlist来查询从服务器的进程状态
mysql> show processlist \G
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost:60968
db: information_schema
Command: Sleep
Time: 3613
State:
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 3613
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 2769
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 4. row ***************************
Id: 7
User: root
Host: localhost:61007
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
4 rows in set (0.04 sec)
结果表明slave已经连接上master，开始接收并执行日志
relay-log.info文件里面的内容
.\Steven-PC-relay-bin.000002 250 binlog.000005 107 7
relay-log.info文件里面记录了slave端的relaylog的当前文件名和位置，还有master端的binlog文件名和位置
Windows环境下主从复制测试
1、在master端的mysql环境下，执行下面命令
use test; create table rep_test(data integer); insert into rep_test values(2);
2、在slave端的mysql环境下，查看主机刚才添加的表和数据是否成功同步到从机上
use test; show tables; select * FROM REP_TEST;
测试表明，数据已经成功地同步到slave上，实验中只是用到了主从同步，在实际生产环境中MYSQL架构可能会用到一主多从的架构
MYSQL主要复制启动选项
（1）log-slave-updates
log-slave-updates这个参数主要用来配置从服务器的更新是否写入二进制日志，该选项默认是不打开的，如果这个
从服务器同时也作为其他服务器的主服务器，搭建一个链式的复制，那么就需要开启这个选项，这样从服务器才能获取他
的二进制日志进行同步操作
（2）master-connect-retry
master-connect-retry这个参数用来设置和主服务器连接丢失的时候进行重试的时间间隔，默认是60秒
（3）read-only
read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对
从数据库进行更新操作。如果主数据库创建了一个普通用户，在默认情况下，该用户是可以更新从数据库的数据的，如果
使用read-only选项启动从数据库以后，用户对从数据库进行更新时会提示错误
在Linux下启动mysql例子
[root@localhost~]#mysqld_safe -read-only
（4）slave-skip-errors
在复制过程中，从服务器可以会执行BINLOG中的错误SQL语句，此时如果不忽略错误，从服务器会停止复制进程，等待用户处理错误。
这种错误如果不能及时发现，将会对应用或者备份产生影响。slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动
跳过的错误号，设置该参数后，mysql会自动跳过所配置的一系列错误，直接执行后面的SQL语句，该参数可以定义多个错误号，如果
设置成all，则表示跳过所有的错误，在my.ini或者my.cnf里配置如下
slave-skip-errors=1007,1051,1062
如果从数据库主要作为主库的备份，那么就不应该使用这个启动参数，因为一旦设置不当很可能造成主从库的数据不同步。
如果从库仅仅是为了分担主库的查询压力，并且对数据的完整性要求不高，那么这个选项可以减轻DBA维护从库的工作量
查看slave的复制进度
很多情况下，用户都想知道从服务器复制的进度，从而判断从服务器上复制数据的完整性，同时判断是否需要手工来做
主从同步工作。
事实上，用户可以通过show processlist列表中的Slave_SQL_Running线程的Time值得到，他记录了从服务器当前执行的SQL时间戳
和系统时间之间的差距，例如下面的例子
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 2769
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Time时间说明从服务器最后执行的更新操作大概是主服务器2769秒前的更新操作
日常管理和维护
复制配置完成后，DBA需要进行日常的监控和管理维护工作，以便能够及时发现问题和解决问题
以保证主从数据库能够正常工作。
1、了解服务器的状态
一般使用show slave status命令来检查从服务器
在查看服务器信息中，首先要查看下面的两个进程是否为YES。
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Slave_IO_Running表明此进程是否能够由从服务器到主服务器上正确地读取binlog日志，并写入到从服务器的中继日志中
Slave_SQL_Running表明此进程能否读取并执行中继日志中的binlog信息
2、服务器复制出错原因
问题一：出现“log event entry exceeded max_allowed_pack”错误
如果在应用中使用大的BLOB列或CLOB列或者长字符串，那么在从服务器上回复时，可能会出现
“log event entry exceeded max_allowed_pack”的错误，这是因为含有大文本的记录无法通过网络进行传输而导致的
解决方法是在主服务器和从服务器上添加max_allowed_packet参数，该参数默认设置为1MB
show variables LIKE ‘%max_all%‘
Variable_name Value ------------------ ------- max_allowed_packet 1048576 (1 row(s) affected)
set @@global.max_allowed_packet=16777216;
同时在my.ini或my.cnf文件里设置max_allowed_packet=16M，数据库重启之后该参数将有效
问题二：多主复制时的自增长变量冲突问题
大多数情况下使用一台主服务器对一台或者多台从服务器，但是在某些情况下可能会存在多个服务器配置为复制主服务器，
使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入时多个主服务器会试图使用相同的auto_increment值
服务器变量auto_increment_increment和auto_increment_offset可以协调多主服务器复制auto_increment列
在多主服务器复制到从服务器的过程中会发生主键冲突问题，可以将不同的主服务器的这两个参数重新进行设置，将A库
上设置auto_increment_increment=1，auto_increment_offset=1，此时B库上设置
auto_increment_increment=1，auto_increment_offset=0
提示：一般不建议使用双主或多主，因为这样会带来意想不到的冲突状况，就像SQLSERVER的对等复制，虽然有很多冲突检测措施
但是有时候冲突是不可预料的，出现冲突DBA要排查，维护成本较高，我们生产环境里是没有使用双主和多主，主要使用的是一主多从或一主一从
切换主从服务器
在实际生产环境，如果主机上的主库发生故障，需要将从机上的从库切换成主库，同时需要修改服务器C的配置文件，使程序连接到从机
下面介绍主从切换的步骤
1、首先要确保所有的从库都已经执行了relay log中的全部更新，看从库的状态是否是Has read all relay log，是否更新都已经执行完成
在从库上执行下面命令
STOP SLAVE IO_THREAD; Command(s) completed successfully.
mysql> show processlist \G
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost:60968
db: test
Command: Sleep
Time: 45
State:
Info: NULL
*************************** 2. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 3949
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 7
User: root
Host: localhost:61007
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
3 rows in set (0.00 sec)
2、在从库上停止slave服务，然后执行reset master重置成为主库
STOP SLAVE; Command(s) completed successfully. RESET MASTER; Command(s) completed successfully.
注意：如果从库上并未开binlog，那么在执行reset master的时候会报错：ERROR 1186(HY000):BINLOG CLOSED ,CANNOT RESET MASTER
在切换之后，在从库的数据目录会多出master.info文件
master.info文件里的内容
18 binlog.000005 393 192.168.1.100 repl 123 3306 60 0 0 1800.000 0 0
基本上记录了主库的复制用户、密码和binlog文件名和位置等
3、在从库B（192.168.1.102）上添加具有replication权限的用户repl，查询主库状态，命令如下
GRANT REPLICATION SLAVE ON *.*TO ‘repl‘@‘localhost‘ identified by ‘123‘; show master status;
4、修改主服务器的my.ini文件里的server-id为1，从服务器的server-id为2
5、在原来的主库（192.168.1.100）上配置复制参数
change master TO master_host=‘192.168.1.102‘, master_user=‘repl‘, master_password=‘123‘, master_port=3306, master_log_file=‘on.000004‘, master_log_pos=107;
6、在从库（192.168.1.100）上执行show slave status命令查看从库是否启动成功
START SLAVE;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.102
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: on.000004
Read_Master_Log_Pos: 107
Relay_Log_File: joe-relay-bin.000006
Relay_Log_Pos: 246
Relay_Master_Log_File: on.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
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的复制方面的内容，MYSQL复制是比较重要的技术
文本的主从切换使用手工的方式，当然在真实生产环境一般使用自动切换脚本和软件工具去做自动主从切换，这里不做介绍了
我的MYSQL学习心得（十七） 复制
标签：complete exe 文本 _id 命令行 处理 解决方法 replicate itblog
本文系统来源：http://www.cnblogs.com/mckstt/p/6768547.html
内容总结
以上是互联网集市为您收集整理的我的MYSQL学习心得（十七） 复制全部内容，希望文章能够帮你解决我的MYSQL学习心得（十七） 复制所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。