my.cnfの設定で困っています。
MySQLにリモートアクセスしたく、my.cnfを設定すると、なぜかMySQL serverが再起動しなくなるという問題が発生して大変困っています。皆様にどこの箇所が問題かを指摘していただけると大変助かります。
現状を少し書きます。
環境は、
MySQL version: 5.7.20
OS: Ubuntu 16.04
を使っています。
新たにMySQL serverを立ち上げ、下記のようにuser accountを作成しました。
mysql -u root -p
create user user_test1@"%" identified by "user_test1";
また、このユーザーにすべての権限を与えたいので、以下のようにしました。
grant all on *.* to user_test1@"%" identified by "user_test1";
この状態で、いくつかdatabase.tableを作成しました。
仮に以下のようにしておきます。
create database TEST;
use TEST;
create table trgt_table (n int);
このtrgt_tableに外部からリモートでアクセスしたいと考えています。
そのため、my.cnf(/etc/mysql/my.cnf)を改編することにしました。
defaultでは、コメントアウトされている部分以外では、以下の2行がありました。
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
この下に、以下の2行を加えました。
[mysqld]
bind-address = xxx.xxx.xxx.xxx (xxxの部分は実際のIPaddressにしてあります。)
この状態で、
service mysql restart
で、mysqlが再度立ち上がってこないことがわかりました。(shutdownはしているようです。)
ただし、[bind-address]の部分をmy.cnfに記入しませんと、外部からのアクセスが可能にならないのではと思っています。
もし、この問題を解決し、外部からアクセス可能になるような方法をお知りでしたら、ご教授お願いします。よろしくお願いいたします。
追記:
上記の「bind-address = xxx.xxx.xxx.xxx」を加えた状態で、
/etc/init.d/mysql restart
したときのエラーログ(/var/log/mysql/error.log)を追記させていただきます。
また、「Do you already have another mysqld server running on port: 3306 ?」とエラーにあります。しかし、restartの時に完全にmysqlがとまっていることは[ps aux]で確かめております。
よろしくお願いいたします。
以下がエラーログです。
2017-11-07T09:58:24.343609Z 0 [Note] Giving 0 client threads a chance to die gracefully
2017-11-07T09:58:24.343646Z 0 [Note] Shutting down slave threads
2017-11-07T09:58:24.343653Z 0 [Note] Forcefully disconnecting 0 remaining clients
2017-11-07T09:58:24.343662Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2017-11-07T09:58:24.343712Z 0 [Note] Binlog end
2017-11-07T09:58:24.344302Z 0 [Note] Shutting down plugin 'ngram'
2017-11-07T09:58:24.344313Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2017-11-07T09:58:24.344319Z 0 [Note] Shutting down plugin 'partition'
2017-11-07T09:58:24.344324Z 0 [Note] Shutting down plugin 'ARCHIVE'
2017-11-07T09:58:24.344328Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2017-11-07T09:58:24.344333Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2017-11-07T09:58:24.344338Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-11-07T09:58:24.344343Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-11-07T09:58:24.344347Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-11-07T09:58:24.344352Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-11-07T09:58:24.344356Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-11-07T09:58:24.344361Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-11-07T09:58:24.344365Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-11-07T09:58:24.344370Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-11-07T09:58:24.344374Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-11-07T09:58:24.344378Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-11-07T09:58:24.344394Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-11-07T09:58:24.344399Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-11-07T09:58:24.344403Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-11-07T09:58:24.344406Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-11-07T09:58:24.344410Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-11-07T09:58:24.344414Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2017-11-07T09:58:24.344418Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2017-11-07T09:58:24.344422Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-11-07T09:58:24.344426Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-11-07T09:58:24.344430Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-11-07T09:58:24.344434Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-11-07T09:58:24.344438Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-11-07T09:58:24.344442Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-11-07T09:58:24.344445Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-11-07T09:58:24.344449Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-11-07T09:58:24.344453Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2017-11-07T09:58:24.344457Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-11-07T09:58:24.344461Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-11-07T09:58:24.344465Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2017-11-07T09:58:24.344469Z 0 [Note] Shutting down plugin 'InnoDB'
2017-11-07T09:58:24.344537Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-11-07T09:58:24.344648Z 0 [Note] InnoDB: Starting shutdown...
2017-11-07T09:58:24.444841Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2017-11-07T09:58:24.445099Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 171107 9:58:24
2017-11-07T09:58:25.604583Z 0 [Note] InnoDB: Shutdown completed; log sequence number 58560307
2017-11-07T09:58:25.608117Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-11-07T09:58:25.608134Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-11-07T09:58:25.608170Z 0 [Note] Shutting down plugin 'MEMORY'
2017-11-07T09:58:25.608176Z 0 [Note] Shutting down plugin 'CSV'
2017-11-07T09:58:25.608182Z 0 [Note] Shutting down plugin 'MyISAM'
2017-11-07T09:58:25.608190Z 0 [Note] Shutting down plugin 'sha256_password'
2017-11-07T09:58:25.608195Z 0 [Note] Shutting down plugin 'mysql_native_password'
2017-11-07T09:58:25.608503Z 0 [Note] Shutting down plugin 'binlog'
2017-11-07T09:58:25.609015Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2017-11-07T09:58:25.672518Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-11-07T09:58:25.672575Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-11-07T09:58:25.817077Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-07T09:58:25.818266Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20-0ubuntu0.16.04.1) starting as process 23264 ...
2017-11-07T09:58:25.821610Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-11-07T09:58:25.821622Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-11-07T09:58:25.821625Z 0 [Note] InnoDB: Uses event mutexes
2017-11-07T09:58:25.821627Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-11-07T09:58:25.821630Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-11-07T09:58:25.821633Z 0 [Note] InnoDB: Using Linux native AIO
2017-11-07T09:58:25.821857Z 0 [Note] InnoDB: Number of pools: 1
2017-11-07T09:58:25.821951Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-11-07T09:58:25.823119Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-11-07T09:58:25.828562Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-11-07T09:58:25.829973Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-11-07T09:58:25.841352Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-11-07T09:58:25.885929Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-11-07T09:58:25.886029Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-11-07T09:58:25.972723Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-11-07T09:58:25.973500Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-11-07T09:58:25.973509Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-11-07T09:58:25.973996Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 58560307
2017-11-07T09:58:25.974226Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-11-07T09:58:25.974277Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-11-07T09:58:25.975848Z 0 [Note] InnoDB: Buffer pool(s) load completed at 171107 9:58:25
2017-11-07T09:58:25.977039Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-11-07T09:58:25.977050Z 0 [Note] Server hostname (bind-address): '10.1.0.29'; port: 3306
2017-11-07T09:58:25.977057Z 0 [Note] - '10.1.0.29' resolves to '10.1.0.29';
2017-11-07T09:58:25.977078Z 0 [Note] Server socket created on IP: '10.1.0.29'.
2017-11-07T09:58:25.977087Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
2017-11-07T09:58:25.977089Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2017-11-07T09:58:25.977099Z 0 [ERROR] Aborting