Percona
自動關閉 MySQL 是 RHEL7
當我執行
systemctl start mysql
它時,它會啟動 MySQL,但我沒有返回命令提示符,它就這樣保持不變。數據庫正常啟動。幾分鐘後數據庫自動關閉並且 systemctl 報告失敗。我也試過
systemctl start mysql --no-block
它立即返回命令提示符,但幾分鐘後數據庫再次關閉。數據庫沒有崩潰,這是一個乾淨的關閉,所以我懷疑 systemctl 在一段時間後發出關閉信號。
2014-10-30 20:03:37 7401 [Note] /usr/sbin/mysqld: Normal shutdown
已安裝的 RPM
Percona-XtraDB-Cluster-galera-3-debuginfo-3.7-1.3254.rhel7.x86_64 Percona-XtraDB-Cluster-56-debuginfo-5.6.20-25.7.888.el7.x86_64 Percona-XtraDB-Cluster-garbd-3-3.7-1.3254.rhel7.x86_64 Percona-XtraDB-Cluster-test-56-5.6.20-25.7.888.el7.x86_64 percona-release-0.1-3.noarch Percona-XtraDB-Cluster-galera-3-3.7-1.3254.rhel7.x86_64 Percona-XtraDB-Cluster-client-56-5.6.20-25.7.888.el7.x86_64 percona-xtrabackup-2.2.5-5027.el7.x86_64 Percona-XtraDB-Cluster-server-56-5.6.20-25.7.888.el7.x86_64 Percona-XtraDB-Cluster-full-56-5.6.20-25.7.888.el7.x86_64 Percona-XtraDB-Cluster-shared-56-5.6.20-25.7.888.el7.x86_64
你
CentOS Linux release 7.0.1406 (Core)
如何確保 MySQl 不會關閉?
更新 1
[root@n5 ~]# systemctl start mysql (Time 23:31) Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details. (Time 23:46) [root@n5 ~]# [root@n5 ~]# [root@n5 ~]# systemctl status mysql.service mysql.service - Percona XtraDB Cluster Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled) Active: failed (Result: exit-code) since Thu 2014-10-30 23:46:47 MDT; 24min ago Process: 22821 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS) Process: 22778 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=2) Process: 18202 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=1/FAILURE) Process: 18201 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS) Process: 18168 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS) Main PID: 18201 (code=exited, status=0/SUCCESS) Oct 30 23:31:27 n5 rsyncd[19257]: sent 39 bytes received 181 bytes total size 48 Oct 30 23:46:29 n5 mysql-systemd[18202]: ERROR! Oct 30 23:46:29 n5 systemd[1]: mysql.service: control process exited, code=exited status=1 Oct 30 23:46:29 n5 mysql-systemd[22778]: ERROR! mysql pid file /var/lib/mysql /var/lib/mysql/mysqld.pid empty or not readable Oct 30 23:46:29 n5 mysql-systemd[22778]: WARNING: mysql may be already dead Oct 30 23:46:29 n5 systemd[1]: mysql.service: control process exited, code=exited status=2 Oct 30 23:46:47 n5 mysqld_safe[18201]: 141030 23:46:47 mysqld_safe mysqld from pid file /var/lib/mysql/mysqld.pid ended Oct 30 23:46:47 n5 mysql-systemd[22821]: SUCCESS! Oct 30 23:46:47 n5 systemd[1]: Failed to start Percona XtraDB Cluster. Oct 30 23:46:47 n5 systemd[1]: Unit mysql.service entered failed state.
來自 MySQL 日誌
2014-10-30 23:31:49 19205 [Note] /usr/sbin/mysqld: ready for connections. 2014-10-30 23:46:29 19205 [Note] /usr/sbin/mysqld: Normal shutdown
來自消息日誌
Oct 30 23:31:25 n5 mysql-systemd: /usr/bin/mysql-systemd: line 31: [: /var/lib: binary operator expected Oct 30 23:31:25 n5 mysql-systemd: /usr/bin/mysql-systemd: line 39: [: /var/lib: binary operator expected Oct 30 23:31:25 n5 mysql-systemd: /usr/bin/mysql-systemd: line 45: [: /var/log/mysqld.log: binary operator expected Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Logging to '/var/log/mysqld.log'. Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Skipping wsrep-recover for 6da46c5b-9acc-11e3-9d28-9f9960e4e0f4:2978271210 pair Oct 30 23:31:26 n5 mysqld_safe: 141030 23:31:26 mysqld_safe Assigning 6da46c5b-9acc-11e3-9d28-9f9960e4e0f4:2978271210 to wsrep_start_position Oct 30 23:46:29 n5 mysql-systemd: ERROR! Oct 30 23:46:29 n5 systemd: mysql.service: control process exited, code=exited status=1 Oct 30 23:46:29 n5 mysql-systemd: ERROR! mysql pid file /var/lib/mysql /var/lib/mysql/mysqld.pid empty or not readable Oct 30 23:46:29 n5 mysql-systemd: WARNING: mysql may be already dead Oct 30 23:46:29 n5 systemd: mysql.service: control process exited, code=exited status=2 Oct 30 23:46:47 n5 mysqld_safe: 141030 23:46:47 mysqld_safe mysqld from pid file /var/lib/mysql/mysqld.pid ended Oct 30 23:46:47 n5 mysql-systemd: SUCCESS! Oct 30 23:46:47 n5 systemd: Unit mysql.service entered failed state.
更新 2 伺服器記憶體資訊 - 伺服器有 256GB RAM 和 4GB 交換空間
top - 01:15:27 up 14:24, 2 users, load average: 0.00, 0.01, 0.05 Tasks: 314 total, 1 running, 313 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.0 us, 0.1 sy, 0.0 ni, 99.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem: 26394486+total, 24065484 used, 23987937+free, 688 buffers KiB Swap: 4194300 total, 0 used, 4194300 free. 21135896 cached Mem
我的.cnf
[MYSQLD] user=mysql datadir=/var/lib/mysql log_error=/var/log/mysqld.log log_warnings=2 #log_output=FILE bind_address=192.168.1.15 ### INNODB OPTIONS innodb_buffer_pool_size=180G innodb_flush_log_at_trx_commit=2 innodb_file_per_table=1 innodb_data_file_path = ibdata1:100M:autoextend ## You may want to tune the below depending on number of cores and disk sub innodb_read_io_threads=4 innodb_write_io_threads=4 innodb_io_capacity=400 innodb_doublewrite=1 innodb_log_file_size=1024M innodb_log_buffer_size=96M innodb_buffer_pool_instances=8 innodb_log_files_in_group=2 innodb_thread_concurrency=0 #innodb_file_format=barracuda innodb_flush_method = O_DIRECT innodb_autoinc_lock_mode=2 ## avoid statistics update when doing e.g show tables innodb_stats_on_metadata=0 default_storage_engine=innodb innodb_buffer_pool_load_at_startup=1 innodb_buffer_pool_dump_at_shutdown=1 #Time wait_timeout = 300 connect_timeout=60 interactive_timeout=300 # CHARACTER SET collation_server = utf8_unicode_ci init_connect='SET NAMES utf8' character_set_server = utf8 # REPLICATION SPECIFIC binlog_format=ROW # OTHER THINGS, BUFFERS ETC key_buffer_size = 24M tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 512M #sort_buffer_size = 256K #read_buffer_size = 256K #read_rnd_buffer_size = 512K #myisam_sort_buffer_size = 8M skip_name_resolve max_connect_errors = 100000000 sql_mode = '' sysdate_is_now=1 max_connections=200 thread_cache_size=512 query_cache_type = 0 query_cache_size = 0 table_open_cache=1024 lower_case_table_names=0 # 5.6 backwards compatibility explicit_defaults_for_timestamp=1 ## ## WSREP options ## # Full path to wsrep provider library or 'none' wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_node_address=192.168.1.15 # Provider specific configuration options wsrep_provider_options="gcache.size=32768M;gcs.fc_limit = 256; gcs.fc_factor = 0.99" # Logical cluster name. Should be the same for all nodes. wsrep_cluster_name="mh1" # Group communication system handle wsrep_cluster_address="gcomm://192.168.1.11,192.168.1.13,192.168.1.15" # Human_readable node name (non-unique). Hostname by default. #wsrep_node_name= # Address for incoming client connections. Autodetect by default. #wsrep_node_incoming_address= # How many threads will process writesets from other nodes wsrep_slave_threads=48 # DBUG options for wsrep provider #wsrep_dbug_option # Generate fake primary keys for non-PK tables (required for multi-master # and parallel applying operation) wsrep_certify_nonPK=1 # Location of the directory with data files. Needed for non-mysqldump # state snapshot transfers. Defaults to mysql_real_data_home. #wsrep_data_home_dir= # Maximum number of rows in write set wsrep_max_ws_rows=131072 # Maximum size of write set wsrep_max_ws_size=1073741824 # to enable debug level logging, set this to 1 wsrep_debug=1 # convert locking sessions into transactions wsrep_convert_LOCK_to_trx=0 # how many times to retry deadlocked autocommits wsrep_retry_autocommit=1 # change auto_increment_increment and auto_increment_offset automatically wsrep_auto_increment_control=1 # replicate myisam wsrep_replicate_myisam=1 # retry autoinc insert, which failed for duplicate key error wsrep_drupal_282555_workaround=0 # enable "strictly synchronous" semantics for read operations wsrep_causal_reads=0 # Command to call when node status or cluster membership changes. # Will be passed all or some of the following options: # --status - new status of this node # --uuid - UUID of the cluster # --primary - whether the component is primary or not ("yes"/"no") # --members - comma-separated list of members # --index - index of this node in the list #wsrep_notify_cmd= ## ## WSREP State Transfer options ## # State Snapshot Transfer method wsrep_sst_method=rsync # Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!! # (SST method dependent. Defaults to the first IP of the first interface) #wsrep_sst_receive_address= # SST authentication string. This will be used to send SST to joining nodes. # Depends on SST method. For mysqldump method it is root:<root password> wsrep_sst_auth="xxxx:xxxx" # Desired SST donor name. #wsrep_sst_donor= # Protocol version to use # wsrep_protocol_version= [MYSQL] socket=/var/lib/mysql/mysql.sock default_character_set=utf8 [client] socket=/var/lib/mysql/mysql.sock default_character_set=utf8 [mysqldump] max_allowed_packet = 512M socket=/var/lib/mysql/mysql.sock default_character_set=utf8 [MYSQLD_SAFE] pid_file=mysqld.pid log_error=/var/log/mysqld.log datadir=/var/lib/mysql
如果你改變這部分怎麼辦
my.cnf
[MYSQLD_SAFE] pid_file=mysqld.pid
成為
[MYSQLD_SAFE] pid_file=/var/lib/mysql/mysqld.pid
我猜 systemd 確實殺死了 MySQL,因為它沒有在該位置找到包含程序號的正確 pid 文件。
在您的 my.cnf 文件中更改以下行
[MYSQLD_SAFE] pid_file=mysqld.pid
到你的實際路徑
在我的身上
pid_file=/var/lib/mysql/mysqld.pid
請評論它是否適合您