編集履歴

質問編集履歴

質問内容の変更

2016/03/08 09:42

投稿

kumakumatan

スコア213

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -1,22 +1,18 @@
 Mysql Cominity Server 5.7.11にてmy.cnfの設定をしています。
-・validate_password=OFF
 ・max_connections = 500
 ・文字コード：UTF8
 ・DBファイルシステム領域について(1000M)自動作成をする
+・log_timestamps = 'system'
+・lower_case_table_names = 2
-事をしたいと思います。my.cnfを下記のように設定したのですが、サービスが開始になりません。
+事をしたいと思います。my.cnfを下記のように設定しました。
-InnoDBの設定を外したら、サービス開始になりますが、InnoDbを使いたく思います。
+ログ時刻は現在の時刻になっているみたいなのですが、最初の1行だけ時刻がずれているのは
+何故でしょうか？デーモンユーザで起動しているのでしょうか？
-また、ＯＳ側の時刻/var/log/mysqld.logに出力した、時刻に大幅なずれがあるのはどこか設定が悪いのでしょうか？
+lower_case_table_names = 2にしてもmysqlに反映されません。記述が間違っているのでしょうか？
+また、起動ログで注意、エラーが多い気がするのですが、問題ないでしょうか？
 必要のない記述があれば教えて下さい。
-========================my.cnf=================================
-```ここに言語を入力
+```my.cnf
 # For advice on how to change settings please see
 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 [mysqld]
 #
 # Remove leading # and set to the amount of RAM for the most important data
@@ -33,30 +29,23 @@
 # join_buffer_size = 128M
 # sort_buffer_size = 2M
 # read_rnd_buffer_size = 2M
+log_timestamps = 'system'
+lower_case_table_names = 2
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 # Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0
 character-set-server = utf8
 skip-character-set-client-handshake
 # --------------------------------------------------
 # Network
 # --------------------------------------------------
 max_connections = 500
 # --------------------------------------------------
 # Logging
 # --------------------------------------------------
 general_log=1
 general_log_file=/var/log/mysql_query.log
 # --------------------------------------------------
 # Cache & Memory
 # --------------------------------------------------
@@ -72,7 +61,6 @@
 read_buffer_size = 131072
 join_buffer_size = 131072
 read_rnd_buffer_size = 262144
 # --------------------------------------------------------------------
 # InnoDB behavior
 # --------------------------------------------------------------------
@@ -89,7 +77,6 @@
 innodb_autoinc_lock_mode = 1
 innodb_change_buffering = inserts
 innodb_old_blocks_time = 500
 # --------------------------------------------------------------------
 # InnoDB base
 # --------------------------------------------------------------------
@@ -104,7 +91,7 @@
 innodb_log_file_size = 64M
 innodb_log_files_in_group = 2
 innodb_log_buffer_size = 8M
-innodb_additional_mem_pool_size = 8M
+#innodb_additional_mem_pool_size = 8M
 innodb_thread_concurrency = 8
 innodb_flush_log_at_trx_commit = 1
 innodb_force_recovery = 0
@@ -117,34 +104,64 @@
 innodb_support_xa = FALSE
 innodb_lock_wait_timeout = 50
 innodb_table_locks = 1
 [client]
 default-character-set = utf8
 [mysqldump]
 default-character-set = utf8
 [mysql]
 default-character-set = utf8
 [mysqld_safe]
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid
 ```
-======================my.cnf==========================
---------------------/var/log/mysqld.log-------------------
+-----------------/var/log/mysqld.log-------------------
 ```ここに言語を入力
-2016-03-07T06:16:14.583794Z 0 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
-2016-03-07T06:16:14.584335Z 0 [Warning] InnoDB: Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed.
-2016-03-07T06:16:14.584356Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
-2016-03-07T06:16:17.142266Z 0 [Warning] InnoDB: New log files created, LSN=45790
-2016-03-07T06:16:17.353250Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
-2016-03-07T06:16:17.434115Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
+2016-03-08T08:54:41.930740Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
+2016-03-08T17:54:42.139845+09:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
+2016-03-08T17:54:42.140663+09:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.11-log) starting as process 21803 ...
+2016-03-08T17:54:42.146232+09:00 0 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
+2016-03-08T17:54:42.146718+09:00 0 [Warning] InnoDB: Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed.
+2016-03-08T17:54:42.146740+09:00 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
+2016-03-08T17:54:42.146833+09:00 0 [Note] InnoDB: PUNCH HOLE support available
+2016-03-08T17:54:42.146867+09:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
+2016-03-08T17:54:42.146880+09:00 0 [Note] InnoDB: Uses event mutexes
+2016-03-08T17:54:42.146890+09:00 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
+2016-03-08T17:54:42.146900+09:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
+2016-03-08T17:54:42.146912+09:00 0 [Note] InnoDB: Using Linux native AIO
+2016-03-08T17:54:42.147453+09:00 0 [Note] InnoDB: Number of pools: 1
+2016-03-08T17:54:42.147610+09:00 0 [Note] InnoDB: Not using CPU crc32 instructions
+2016-03-08T17:54:42.157075+09:00 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
+2016-03-08T17:54:42.180181+09:00 0 [Note] InnoDB: Completed initialization of buffer pool
+2016-03-08T17:54:42.184575+09:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
+2016-03-08T17:54:42.197089+09:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
+2016-03-08T17:54:42.223241+09:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
+2016-03-08T17:54:42.223353+09:00 0 [Note] InnoDB: Setting file '/var/lib/mysql/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
+2016-03-08T17:54:42.364921+09:00 0 [Note] InnoDB: File '/var/lib/mysql/ibtmp1' size is now 12 MB.
+2016-03-08T17:54:42.366681+09:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
+2016-03-08T17:54:42.366707+09:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
+2016-03-08T17:54:42.368574+09:00 0 [Note] InnoDB: Waiting for purge to start
+2016-03-08T17:54:42.418823+09:00 0 [Note] InnoDB: 5.7.11 started; log sequence number 1209999
+2016-03-08T17:54:42.420939+09:00 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
+2016-03-08T17:54:42.421367+09:00 0 [Note] Plugin 'FEDERATED' is disabled.
+2016-03-08T17:54:42.421368+09:00 0 [Note] InnoDB: Buffer pool(s) load completed at 160308 17:54:42
+2016-03-08T17:54:42.421969+09:00 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
+mysqld: Table 'mysql.plugin' doesn't exist
+2016-03-08T17:54:42.422038+09:00 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
+mysqld: File '/var/log/mysql_query.log' not found (Errcode: 13 - Permission denied)
+2016-03-08T17:54:42.422293+09:00 0 [ERROR] Could not use /var/log/mysql_query.log for logging (error 13 - Permission denied). Turning logging off for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL GENERAL_LOG=ON" or restart the MySQL server.
+2016-03-08T17:54:42.423054+09:00 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
+mysqld: Table 'mysql.gtid_executed' doesn't exist
+2016-03-08T17:54:42.423118+09:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
+2016-03-08T17:54:42.423163+09:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
+2016-03-08T17:54:42.423544+09:00 0 [Warning] CA certificate ca.pem is self signed.
+2016-03-08T17:54:42.426320+09:00 0 [Note] Server hostname (bind-address): '*'; port: 3306
+2016-03-08T17:54:42.427687+09:00 0 [Note] IPv6 is not available.
+2016-03-08T17:54:42.427748+09:00 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
+2016-03-08T17:54:42.427769+09:00 0 [Note] Server socket created on IP: '0.0.0.0'.
+2016-03-08T17:54:42.431671+09:00 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
+2016-03-08T17:54:42.431711+09:00 0 [Warning] Failed to open optimizer cost constant tables
+2016-03-08T17:54:42.432742+09:00 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
 ```
 -----------mysqld.log----

質問内容を具体的に修正

2016/03/08 09:42

投稿

kumakumatan

スコア213

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -6,40 +6,58 @@
 ・DBファイルシステム領域について(1000M)自動作成をする
 事をしたいと思います。my.cnfを下記のように設定したのですが、サービスが開始になりません。
-どこが悪いのかご教授いただければと思います。
+InnoDBの設定を外したら、サービス開始になりますが、InnoDbを使いたく思います。
-また、MysqlServerではコネクションプール（同時接続数は5まで）などの設定はできるのでしょうか？
+また、ＯＳ側の時刻/var/log/mysqld.logに出力した、時刻に大幅なずれがあるのはどこか設定が悪いのでしょうか？
-できるのであれば、どこに入れればいいのでしょうか？
-また、必要のない記述があれば教えて下さい。
+必要のない記述があれば教えて下さい。
 ========================my.cnf=================================
+```ここに言語を入力
 # For advice on how to change settings please see
 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
+[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
+#
+# 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
+datadir=/var/lib/mysql
+socket=/var/lib/mysql/mysql.sock
-[mysqld]
-# --------------------------------------------------
-# Base
-# --------------------------------------------------
-user = mysql
-port = 3306
-datadir = /var/lib/mysql
-socket  = /var/lib/mysql/mysql.sock
-pid-file = /var/run/mysqld/mysqld.pid
+# Disabling symbolic-links is recommended to prevent assorted security risks
-symbolic-links = 0
+symbolic-links=0
-sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
-default-storage-engine = InnoDB
-transaction-isolation = REPEATABLE-READ
 character-set-server = utf8
-collation-server = utf8_general_ci
 skip-character-set-client-handshake
+# --------------------------------------------------
+# Network
+# --------------------------------------------------
 max_connections = 500
-validate_password=OFF
 # --------------------------------------------------
+# Logging
+# --------------------------------------------------
+general_log=1
+general_log_file=/var/log/mysql_query.log
+# --------------------------------------------------
 # Cache & Memory
 # --------------------------------------------------
 # Global
@@ -54,8 +72,7 @@
 read_buffer_size = 131072
 join_buffer_size = 131072
 read_rnd_buffer_size = 262144
 # --------------------------------------------------------------------
 # InnoDB behavior
 # --------------------------------------------------------------------
@@ -72,7 +89,7 @@
 innodb_autoinc_lock_mode = 1
 innodb_change_buffering = inserts
 innodb_old_blocks_time = 500
 # --------------------------------------------------------------------
 # InnoDB base
 # --------------------------------------------------------------------
@@ -100,24 +117,35 @@
 innodb_support_xa = FALSE
 innodb_lock_wait_timeout = 50
 innodb_table_locks = 1
+[client]
+default-character-set = utf8
 [mysqldump]
 default-character-set = utf8
-quick
-max_allowed_packet = 16M
 [mysql]
 default-character-set = utf8
-no-auto-rehash
-show-warnings
-[client]
-default-character-set = utf8
-port   = 3306
-socket = /var/lib/mysql/mysql.sock
 [mysqld_safe]
-log-error = /var/log/mysql/error.log
-==================================================================================
+log-error=/var/log/mysqld.log
+pid-file=/var/run/mysqld/mysqld.pid
+```
+======================my.cnf==========================
+--------------------/var/log/mysqld.log-------------------
+```ここに言語を入力
+2016-03-07T06:16:14.583794Z 0 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
+2016-03-07T06:16:14.584335Z 0 [Warning] InnoDB: Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed.
+2016-03-07T06:16:14.584356Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
+2016-03-07T06:16:17.142266Z 0 [Warning] InnoDB: New log files created, LSN=45790
+2016-03-07T06:16:17.353250Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
+2016-03-07T06:16:17.434115Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
+```
+-----------mysqld.log----
 以上、ご教授をお願い致します。

必要のない記述を教えて下さい。

2016/03/07 07:23

投稿

kumakumatan

スコア213

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -11,6 +11,8 @@
 また、MysqlServerではコネクションプール（同時接続数は5まで）などの設定はできるのでしょうか？
 できるのであれば、どこに入れればいいのでしょうか？
+また、必要のない記述があれば教えて下さい。
 ========================my.cnf=================================
 # For advice on how to change settings please see
 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html