【MySQL】MySQL主从同步失败Slave_IO_Running: No与 Slave_SQL_Running: No。MySQL主从同步修复

最近在学习MySQL集群，主从集群，多主多从，负载Haproxy，高可用Keepalived

主从集群，利用MySQLbin-log实现

 #主节点状态

//编辑配置文件

[root@localhost mysql]# vi /etc/my.cnf

#默认是1，不是的话则改为1

server-id=1

#线程并发数(CPU*2)

thread_concurrency = 2

#打开二进制日志功能

log-bin=mysql-bin

#时区不对时加上

default_time_zone = "+8:00"
 

#从节点状态，主要看Slave_IO_Running和Slave_SQL_Running状态

# 设置与master不一样就行,

server-id=11

#线程并发数(CPU*2)

thread_concurrency = 2

#打开中继日志

relay-log=mysql-log

#设置relay-log-index文件名

relay-log-index=mysql-log.index

  上面的问题主从配置文件没有配置server-id

密码策略问题，不同MySQL版本也有坑哈，下面MySQL8.0.24版本解决方案

进入主节点，修改登录用户密码

ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY '你的密码';

--MySQL登录后使用这个命令查看密码策略
show variables like 'validate%';

--可以使用以下命令进行修改
--密码验证策略低要求(0或LOW代表低级)
set global validate_password.policy=LOW;

--密码至少要包含的小写字母个数和大写字母个数 0代表0个
set global validate_password.mixed_case_count=0;

--密码至少要包含的数字个数 0代表0个。
set global validate_password.number_count=0; 

--密码至少要包含的特殊字符数 0代表0个
set global validate_password.special_char_count=0; 

-- 密码最小长度
set global validate_password.length=1;
--跳过用户验证 OFF关闭 ON开启
set global validate_password.check_user_name='OFF';

binlog查询方法

命令 

mysql> show binlog events in 'binlog.000004';

结果

从节点注册主节点

登录从节点MySQL

stop slave;
reset slave;

change master to master_host='master_ip',master_user='copy',master_port=3306,master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=157;

登入所有从节点的mysql上执行以下命令:
master_host : 主节点主机名
master_user : 第2步创建的主从同步账户
master_port : 主节点mysql服务的端口号，因为没有这里改过所以是 3306
master_password : 第2步创建的主从同步账户的密码
master_log_file : 第4步获取的二进制文件名字
master_log_pos : 第4步获取的Position值

MySQL 8.0版本配置文件

## my.cnf for 8.0版本
## 注意：个别建议可能需要根据实际情况作调整，请自行判断或联系我，本人不对这些建议结果负相应责任
## 本配置文件主要适用于MySQL 8.0版本
#
[client]
port = 3306
socket = /data/mysql/mysql.sock
[mysql]
#登录提示语
prompt = "\u@mysqldb \R:\m:\s [\d]> "
#不使用自动补全功能的 默认是不使用自动补全功能的
no_auto_rehash
[mysqld]
user = mysql
port = 3306
#主从复制或MGR集群中，server_id记得要不同
#另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同
#server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以
server_id = 3306
#MySQL安装文件路径
basedir = /usr/local/mysql
#MySQL数据文件路径
datadir = /data/mysql
#MySQL SOCKET 文件位置
socket = /data/mysql/mysql.sock
pid_file = mysqldb.pid
#服务器默认字符编码
character_set_server = UTF8MB4
#禁止域名解析（也包括主机名）
skip_name_resolve = 1
#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"
#performance setttings
#事务将等待超时的时间(单位 秒)
lock_wait_timeout = 3600
#mysql打开最大文件数
open_files_limit = 65535
#指定MySQL可能的连接数量。当MySQL主线程在很短的时间内得到非常多的连接请求，该参数就起作用，之后主线程花些时间（尽管很短）检查连接并且启动一个新线程。
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。
back_log = 1024
#MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections
max_connections = 512
#max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系。
max_connect_errors = 1000000
#表描述符缓存大小，可减少文件打开/关闭次数
table_open_cache = 1024
table_definition_cache = 1024
#线程缓存；主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。
thread_stack = 512K
#是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段
sort_buffer_size = 4M
#应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的
#读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，
#而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作，
#然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。
join_buffer_size = 4M
#是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。　　　　　　　　　　　　　　　　　　
#如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
read_buffer_size = 8M
#是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，
#提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
read_rnd_buffer_size = 4M
#默认8M，当对MyISAM非空表执行insert … select/ insert … values(…),(…)或者load data infile时，使用树状cache缓存数据，每个thread分配一个；
#注：当对MyISAM表load 大文件时，调大bulk_insert_buffer_size/myisam_sort_buffer_size/key_buffer_size会极大提升速度
bulk_insert_buffer_size = 64M
#thread_cahe_size线程池，线程缓存。用来缓存空闲的线程，以至于不被销毁，如果线程缓存在的空闲线程，需要重新建立新连接，
#则会优先调用线程池中的缓存，很快就能响应连接请求。每建立一个连接，都需要一个线程与之匹配。
thread_cache_size = 768
#interactive_time -- 指的是mysql在关闭一个交互的连接之前所要等待的秒数(交互连接如mysql gui tool中的连接
interactive_timeout = 600
#wait_timeout -- 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数
wait_timeout = 600
#它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）
tmp_table_size = 32M
#独立的内存表所允许的最大容量.# 此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源.
max_heap_table_size = 32M
#log settings
log_timestamps = SYSTEM
log_error = /data/mysql/error.log
log_error_verbosity = 3
#开启慢查询
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /data/mysql/slow.log
#超过的时间 单位秒；MySQL能够记录执行时间超过参数 long_query_time 设置值的SQL语句，默认是不记录的。
long_query_time = 0.1
#记录管理语句和没有使用index的查询记录
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = /data/mysql/mybinlog
#在复制方面的改进就是引进了新的复制技术：基于行的复制。简言之，这种新技术就是关注表中发生变化的记录，而非以前的照抄 binlog 模式。
#从 MySQL 5.1.12 开始，可以用以下三种模式来实现：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，
#混合模式复制(mixed-based replication, MBR)。相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。MBR 模式中，SBR 模式是默认的。
binlog_format = ROW
#当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
#MySQL 8.0.22前，想启用MGR的话，需要设置binlog_checksum=NONE才行
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE
#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M
#replication settings
relay_log_recovery = 1
slave_parallel_type = LOGICAL_CLOCK
#可以设置为逻辑CPU数量的2倍
slave_parallel_workers = 64
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
slave_checkpoint_period = 2
#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#MGR本地节点IP:PORT，请自行替换
loose-group_replication_local_address = "172.16.16.10:33061"
#MGR集群所有节点IP:PORT，请自行替换
loose-group_replication_group_seeds = "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON
#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 45875M
innodb_buffer_pool_instances = 4
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = 1
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864
#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
innodb_monitor_enable = "module_adaptive_hash"
#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
[mysqldump]
quick

主从同步实例

从库查询状态，正常状态

异常状态

可以看到

            Slave_IO_Running: Yes
            Slave_SQL_Running: No

修复从库同步状态

先观看错误日志，上面的error信息，具体原因具体分析。

如果是确定要重新同步了，需要保证主库（master）不再有数据变动哦！！！

第一步，暂定从库

mysql>stop slave;
mysql>reset slave;

第二步，更新从库同步信息

在master上查看当前日志信息

 再到slave从库执行

mysql > change master to master_host='192.168.17.200',master_user='root',master_port=3322,master_password='123456',master_log_file='binlog.000010',master_log_pos=156;

第三步，启动同步

mysql>start slave;