为什么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为新的主库。

为什么MySQL 5.7主库崩溃切备库

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://cdiso.cn/article/pgohi.html

其他资讯