为什么MySQL 5.7主库崩溃切备库
发布时间：2022-01-13 14:03:57 所属栏目：MySql教程 来源：互联网
导读：这期内容当中小编将会给大家带来有关为什么MySQL 5.7主库崩溃切备库，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。 场景： MySQL主库无故宕机：判断是IO问题导致。 The manual page at http://dev.mysql.com/doc/mysq
|
这期内容当中小编将会给大家带来有关为什么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主库崩溃切备库了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。 （编辑：达州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
