Mysql5.6主从同步 不停机重建

最近公司的windows-server 突然断电，启动以后发现MySQL从节点报错异常，下面是报错信息

slave节点报错 Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.004131' at 83999749, the last event read from 'F:\mysql\mysql3306\binlog\mysql-bin.004131' at 4, the last byte read from 'F:\mysql\mysql3306\binlog\mysql-bin.004131' at 4.'

原因是日志文件损坏，为了保持数据的一致性所以决定重建主从

主节点全量备份

1
2
3
4
5
6
7
8
9
10
11
12
13
`mysql在Windows下使用mysqldump命令手动备份数据库和自动备份数据库`
输入盘符 F:
cd F:\mysql\mysql3306\bin
mysqldump --help
# 数据备份到 G盘  在从库操作
mysqldump -uroot -pZ132465z --single-transaction --master-data=2 --no-autocommit -A > "G:\back\all_repl.sql"
# mysqldump -uroot -p --single-transaction --master-data=2 --no-autocommit -A -B > all.sql
# 解释：
# --single-transaction 设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表
# --master-data=2  该选项将二进制日志的位置和文件名写入到输出中 下图中的标红部分
# --no-autocommit 采用批量提交方式(提高还原性能)
# -A 同参数--all-databases 备份所有库，并在备份文件中生成创建库的语句及use语句。这样方便恢复时不用创建指定数据库
# -B 此参数用于指定多个数据库，同-A参数，生成创建库的语句及use语句。

slave 节点导入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
`首先  MySQL在导入数据的时候由于数据量大于10G的时候就需要对其优化，否则导入速度会非常慢`
=============================================解决MySQL导入数据量大速度慢问题 ======================================================
解决MySQL导入数据量大速度慢问题  # 导入大数据（10G以上）最好做一下参数调整，不然会导入数据过慢
这两个参数可以在线修改，若想快速导入，可以按照下面步骤来操作：
# 1.进入MySQL命令行 临时修改这两个参数
set global innodb_flush_log_at_trx_commit = 2;
set global sync_binlog = 2000;
 
# 2.执行SQL脚本导入
mysql -uroot -pZ132465z --default-character-set=utf8 < G:\databak\all_repl.sql
mysql -uroot -pxxxxxx testdb < testdb.sql
 
# 3.导入完成 再把参数改回来
set global innodb_flush_log_at_trx_commit = 1;
set global sync_binlog = 1;
 ====================================================== ======================================================
 - mysql导入数据可能会报错ERROR 1146 (42S02):  doesn't exist
mysql导入的时候一直有报错，在SOURCE xxxx.sql后，取到报错信息  `doesn't exist`
在检查mysql配置文件my.cnf之后，mysql大小写敏感未设置
在将 lower_case_table_names=1设置后，数据成功导入
---------------------
lower_case_table_names： 此参数不可以动态修改，必须重启数据库
lower_case_table_names=1  表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=0  表名存储为给定的大小和比较是区分大小写的 
lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的
---------------------
mysql内查看:
show variables like 'lower%';
============================================================================================================
 
 
`直接导入数据，导入完成以后在恢复原来配置`
F:\mysql\mysql3306\bin\mysql -hlocalhost -uroot -pZ132465z --default-character-set=utf8 < G:\databak\all_repl.sql

指定主节点恢复主从

1
2
3
4
5
6
7
8
9
10
11
12
# 这里使用的是windows主从同步，最终决定停止从节点，重新主从同步，需要注意一下几点
1、master和slave的server-id一定不一样
2、首先停掉matser的slave节点，以及停掉slave
- stop slave;
- reset slave all;
- show master status \G;
- show slave status \G;
3、如果master的postition 一直变化无法锁定，那就master数据全备份，然后重新导入从节点，然后查看sql文件前60行，找到“MASTER_LOG_FILE”，以及“MASTER_LOG_POS”
windows 查看文件前几行，需要打开powershell，cd进入当前目录，然后执行一下命令，就可以找到文件
-  type .\all_repl.sql|select -First 60
4、找到 logbin 复制然后指定，成功以后查看主从状态
CHANGE MASTER TO MASTER_HOST='192.168.13.8', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='Z132465z@', MASTER_LOG_FILE='mysql-bin.004179', MASTER_LOG_POS=37722197;