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。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22996654/viewspace-2673925/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- DataGaurd環境主庫崩潰後將備庫切為主庫
- oracle 之dataguard主庫系統崩潰之物理備庫切主庫Oracle
- MySQL 主備庫切換記錄MySql
- Data Guard主備庫切換
- MySQL 5.7主從新增新從庫MySql
- 分享:MySQL資料庫崩潰解決過程MySql資料庫
- DataGuard主備庫切換步驟
- Oracle Data Guard主庫備庫角色切換(Switchovers)Oracle
- DataGuard切換(主庫為Rac+備庫為Rac)
- 【DG】Data Guard主備庫Switchover切換
- 執行主備庫切換以解決主庫儲存不足
- 主備庫切換以解決主庫儲存空間不足
- Oracle10G Dataguard 多個備庫 - 主庫和物理備庫的切換Oracle
- Mysql 資料庫主庫,備庫實時同步配置MySql資料庫
- 【DG】Data Guard主備庫Failove切換AI
- 備庫的切換狀態為SWITCHOVER PENDING時進行dataguard主備庫角色切換
- MySQL 資料庫崩潰(crash)的常見原因和解決辦法MySql資料庫
- mysql主從複製+主備切換MySql
- failover切換後恢復原來主庫為新備庫AI
- 這個mysql資料庫是不是崩潰了啊?請高手指點。MySql資料庫
- 【Mysql】mysql主鍵的缺少導致備庫hangMySql
- MySQL從庫切換成主庫後的relay-bin問題MySql
- mysql 5.7主主同步MySql
- 一次資料庫崩潰處理事件資料庫事件
- 資料庫異常崩潰的元凶--OOM killer資料庫OOM
- WkWebView 令人崩潰的崩潰WebView
- MySQL 8.0.11 無故崩潰MySql
- mysql主鍵的缺少導致備庫hangMySql
- Mysql跨庫主從熱備失效問題MySql
- 遷移MySQL 5.7資料庫MySql資料庫
- mysql 5.7 sys資料庫初探MySql資料庫
- mysql5.7資料庫改名MySql資料庫
- 分享一個開發中捕獲崩潰的庫
- 資料庫崩潰恢復表結構的方法資料庫
- 記一次客戶oracle資料庫崩潰Oracle資料庫
- Mysql 5.6 Master和Slave 主備切換MySqlAST
- Mysql5.7在CentOs環境下定時備份資料庫MySqlCentOS資料庫
- SQLServer2012映象主庫掛掉如何切換到映象備庫SQLServer