MySQL实战：MySQL二进制包安装及启动问题排查

tiyan

2.5中没有修改数据库相关的目录的属主　　
[root@ZST1 mysql]# service mysql start
　　
Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/mysql3306/data/mysql.pid).
　　

　　
查看错误日志
　　
[root@ZST1 mysql]# tail -n 10 /data/mysql/mysql3306/data/error.log2017-08-31T09:30:07.528203Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.2017-08-31T09:30:07.528381Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2017-08-31T09:30:07.544406Z 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.2017-08-31T09:30:07.544477Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.19-log) starting as process 28477 ...
　　
mysqld: File '/data/mysql/mysql3306/logs/mysql-bin.index' not found (Errcode: 13 - Permission denied)2017-08-31T09:30:07.548527Z 0 [ERROR] Aborting2017-08-31T09:30:07.548544Z 0 [Note] Binlog end2017-08-31T09:30:07.548838Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
　　
logs目录的权限问题(Permission denied)
　　

　　
[root@ZST1 mysql]# ll /data/mysql/mysql3306
　　
total 12drwxr-xr-x. 2 mysql mysql 4096 Aug 31 17:30 data
　　
drwxr-xr-x. 2 root  root  4096 Aug 31 17:25 logs
　　
drwxr-xr-x. 2 root  root  4096 Aug 31 17:25 tmp
　　
修改目录属主信息
　　
[root@ZST1 mysql]# cd /data/mysql/mysql3306
　　
[root@ZST1 mysql3306]# chown -R mysql:mysql logs
　　
[root@ZST1 mysql3306]# chown -R mysql:mysql tmp
　　

　　
再次启动失败
　　
[root@ZST1 mysql]# /etc/init.d/mysql start
　　
Starting MySQL...... ERROR! The server quit without updating PID file (/data/mysql/mysql3306/data/mysql.pid).
　　

　　
查看错误日志
　　
[root@ZST1 data]# tail -n 100 /data/mysql/mysql3306/data/error.log2017-08-31T09:52:57.010606Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!2017-08-31T09:52:57.011019Z 0 [Note] InnoDB: Setting file './ibdata1' size to 100 MB. Physically writing the file full; Please wait ...2017-08-31T09:52:57.011542Z 0 [Note] InnoDB: Progress in MB: 1002017-08-31T09:52:57.115478Z 0 [Note] InnoDB: File './ibdata1' size is now 100 MB.2017-08-31T09:52:57.116269Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100 MB2017-08-31T09:52:57.116414Z 0 [Note] InnoDB: Progress in MB: 1002017-08-31T09:52:57.600744Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 100 MB2017-08-31T09:52:57.600967Z 0 [Note] InnoDB: Progress in MB: 1002017-08-31T09:52:58.127698Z 0 [Note] InnoDB: Setting log file ./ib_logfile2 size to 100 MB2017-08-31T09:52:58.127915Z 0 [Note] InnoDB: Progress in MB: 1002017-08-31T09:52:58.666114Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile02017-08-31T09:52:58.666227Z 0 [Warning] InnoDB: New log files created, LSN=457902017-08-31T09:52:58.666254Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables2017-08-31T09:52:58.666444Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...2017-08-31T09:52:58.681658Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.2017-08-31T09:52:58.681940Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new2017-08-31T09:52:58.851112Z 0 [Note] InnoDB: Doublewrite buffer created2017-08-31T09:52:58.902414Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.2017-08-31T09:52:58.902485Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.2017-08-31T09:52:58.911638Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2017-08-31T09:52:58.956602Z 0 [Note] InnoDB: Foreign key constraint system tables created2017-08-31T09:52:58.956727Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.2017-08-31T09:52:58.957381Z 0 [Note] InnoDB: Tablespace and datafile system tables created.2017-08-31T09:52:58.957432Z 0 [Note] InnoDB: Creating sys_virtual system tables.2017-08-31T09:52:58.957842Z 0 [Note] InnoDB: sys_virtual table created2017-08-31T09:52:59.023019Z 0 [Note] InnoDB: Waiting for purge to start2017-08-31T09:52:59.073741Z 0 [Note] InnoDB: 5.7.19 started; log sequence number 02017-08-31T09:52:59.116756Z 0 [Note] Plugin 'FEDERATED' is disabled.
　　
mysqld: Table 'mysql.plugin' doesn't exist2017-08-31T09:52:59.117295Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.2017-08-31T09:52:59.173209Z 0 [Note] Salting uuid generator variables, current_pid: 30669, server_start_time: 1504173176, bytes_sent: 0,
　　
2017-08-31T09:52:59.183122Z 0 [Note] Generated uuid: '2bc37fda-8e32-11e7-8369-000c29c1025c', server_start_time: 8632556062243312709, bytes_sent: 568343522017-08-31T09:52:59.183198Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2bc37fda-8e32-11e7-8369-000c29c1025c.2017-08-31T09:52:59.185831Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2017-08-31T09:52:59.188080Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key2017-08-31T09:52:59.188112Z 0 [Note] Server hostname (bind-address): '*'; port: 33062017-08-31T09:52:59.198187Z 0 [Note] IPv6 is available.2017-08-31T09:52:59.198309Z 0 [Note]   - '::' resolves to '::';2017-08-31T09:52:59.198368Z 0 [Note] Server socket created on IP: '::'.2017-08-31T09:52:59.232943Z 0 [Warning] Failed to open optimizer cost constant tables2017-08-31T09:52:59.233342Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist2017-08-31T09:52:59.233452Z 0 [ERROR] Aborting2017-08-31T09:52:59.233515Z 0 [Note] Binlog end2017-08-31T09:52:59.235765Z 0 [Note] Shutting down plugin 'ngram'2017-08-31T09:52:59.235798Z 0 [Note] Shutting down plugin 'BLACKHOLE'2017-08-31T09:52:59.235810Z 0 [Note] Shutting down plugin 'ARCHIVE'2017-08-31T09:52:59.235818Z 0 [Note] Shutting down plugin 'partition'2017-08-31T09:52:59.235826Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'2017-08-31T09:52:59.235834Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'2017-08-31T09:52:59.235841Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'2017-08-31T09:52:59.235848Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'2017-08-31T09:52:59.235855Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'2017-08-31T09:52:59.235863Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'2017-08-31T09:52:59.235870Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'2017-08-31T09:52:59.235877Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'2017-08-31T09:52:59.235884Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'2017-08-31T09:52:59.235891Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'2017-08-31T09:52:59.235898Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'2017-08-31T09:52:59.235905Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'2017-08-31T09:52:59.235913Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'2017-08-31T09:52:59.235919Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'2017-08-31T09:52:59.235927Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'2017-08-31T09:52:59.235934Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'2017-08-31T09:52:59.235941Z 0 [Note] Shutting down plugin 'INNODB_METRICS'2017-08-31T09:52:59.235948Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'2017-08-31T09:52:59.235955Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'2017-08-31T09:52:59.235962Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'2017-08-31T09:52:59.235969Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'2017-08-31T09:52:59.235985Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'2017-08-31T09:52:59.235994Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'2017-08-31T09:52:59.236001Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'2017-08-31T09:52:59.236008Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'2017-08-31T09:52:59.236016Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'2017-08-31T09:52:59.236023Z 0 [Note] Shutting down plugin 'INNODB_CMP'2017-08-31T09:52:59.236030Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'2017-08-31T09:52:59.236038Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'2017-08-31T09:52:59.236045Z 0 [Note] Shutting down plugin 'INNODB_TRX'2017-08-31T09:52:59.236052Z 0 [Note] Shutting down plugin 'InnoDB'2017-08-31T09:52:59.236349Z 0 [Note] InnoDB: FTS optimize thread exiting.2017-08-31T09:52:59.236499Z 0 [Note] InnoDB: Starting shutdown...2017-08-31T09:52:59.337034Z 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql3306/data/ib_buffer_pool2017-08-31T09:52:59.337528Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170831 17:52:592017-08-31T09:53:00.862954Z 0 [Note] InnoDB: Shutdown completed; log sequence number 12099612017-08-31T09:53:00.863526Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"2017-08-31T09:53:00.863560Z 0 [Note] Shutting down plugin 'MEMORY'2017-08-31T09:53:00.863576Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'2017-08-31T09:53:00.863762Z 0 [Note] Shutting down plugin 'MRG_MYISAM'2017-08-31T09:53:00.863777Z 0 [Note] Shutting down plugin 'MyISAM'2017-08-31T09:53:00.865027Z 0 [Note] Shutting down plugin 'CSV'2017-08-31T09:53:00.865062Z 0 [Note] Shutting down plugin 'sha256_password'2017-08-31T09:53:00.865075Z 0 [Note] Shutting down plugin 'mysql_native_password'2017-08-31T09:53:00.865415Z 0 [Note] Shutting down plugin 'binlog'2017-08-31T09:53:00.868573Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
　　

　　
重新初始化
　　
[root@ZST1 mysql]# pwd/usr/local/mysql
　　
[root@ZST1 mysql]# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize2017-08-31T09:54:45.865304Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.2017-08-31T09:54:45.865492Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2017-08-31T09:54:45.865561Z 0 [Note] Ignoring --secure-file-priv value as server is running with --initialize(-insecure) or --bootstrap.2017-08-31T09:54:45.865593Z 0 [Note] ./bin/mysqld (mysqld 5.7.19-log) starting as process 30720 ...2017-08-31T09:54:45.868650Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.2017-08-31T09:54:45.868696Z 0 [ERROR] Aborting2017-08-31T09:54:45.868723Z 0 [Note] Binlog end2017-08-31T09:54:45.868950Z 0 [Note] ./bin/mysqld: Shutdown complete
　　
数据目录有文件存在(data directory has files in it)，2.6也提到初始化时--datadir目录下不能有数据文件
　　

　　
删除/data/mysql/mysql3306/{data,logs,tmp}目录下的所有文件(夹)，再重新初始化