为什么MySQL5.7主库崩溃切备库
这期内容当中小编将会给大家带来有关为什么MySQL 5.7主库崩溃切备库,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
站在用户的角度思考问题,与客户深入沟通,找到丰宁网站设计与丰宁网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:成都网站设计、成都网站建设、企业官网、英文网站、手机端网站、网站推广、域名注册、网络空间、企业邮箱。业务覆盖丰宁地区。
场景:
MySQL主库无故宕机:判断是IO问题导致。
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
200118 02:11:39 mysqld_safe Number of processes running now: 0
200118 02:11:39 mysqld_safe mysqld restarted
原来主库的my.cnf:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
basedir=/usr/local/mysql
datadir=/data/bak1
socket=/tmp/mysql.sock
server-id =11224722
default_password_lifetime=0
log-bin=/data/bak1/mysql-bin2
expire_logs_days = 1
max_binlog_size =600M
binlog_format = MIXED
#max_allowed_packet =1*1024*1024*1024
max_allowed_packet =1024M
event_scheduler = 1
transaction-isolation = READ-COMMITTED
max_connections=5000
innodb_buffer_pool_size = 80G
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
1.确保所有的relay log全部更新完毕,在每个从库上执行stop slave io_thread; show processlist;
直到看到Has read all relay log,则表示从库更新都执行完毕了。
2.登陆所有从库,查看master.info文件,对比选择pos最大的作为新的主库,这里我们选择192.168.112.10为新的主库。
3.登陆192.168.112.10,执行stop slave; 并进入数据库目录,删除master.info和relay-log.info文件, 配置my.cnf文件,
开启log-bin,如果有log-slaves-updates和read-only则要注释掉,执行reset master。
新主库的my.cnf:
[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/data/db
socket=/tmp/mysql.sock
server-id =111210
log-bin = /data/db/mysql-bin.log
# binlog_format = MIXED
#relay-log = rep_relay_log1
#relay-log-index = rep_relay_log1
#log-error=/data/bak/mysqld1.err
#skip-slave-start = 1
default_password_lifetime=0
# binlog-ignore-db=mysql
#expire_logs_days =7
max_connections = 2000
# user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
max_allowed_packet =1024M
#master_info_repository=table
#relay_log_info_repository=table
#relay_log_recovery=1
#transaction-isolation = READ-COMMITTED
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
4.SHOW MASTER STATUS; 查询主库状态。
OK。
上述就是小编为大家分享的为什么MySQL 5.7主库崩溃切备库了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注创新互联行业资讯频道。
分享名称:为什么MySQL5.7主库崩溃切备库
文章网址:http://pwwzsj.com/article/jcgshg.html