Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about why the MySQL 5.7main library crashed. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Scene:

There is no downtime in the main library of MySQL: it is determined that it is caused by an IO problem.

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

The original my.cnf of the main library:

# 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" 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. Ensure that all relay log are updated and execute stop slave io_thread; show processlist on each slave library

Until you see Has read all relay log, it means that all updates from the library have been executed.

two。 Log in to all slave libraries, check the master.info file, and select the largest pos as the new master library. Here we choose 192.168.112.10 as the new master library.

3. Log in to 192.168.112.10, execute stop slave; and enter the database directory, delete master.info and relay-log.info files, and configure my.cnf files

Enable log-bin, comment out if there are log-slaves-updates and read-only, and execute reset master.

My.cnf of the new main library:

[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; queries the status of the main database.

OK .

This is why the MySQL 5.7main library crashed. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.