この投稿では、レプリケーション スレーブの SHOW PROCESSLIST および information_schema.PROCESSLIST ビューで「システム ユーザー」が表示される理由を理解することに焦点を当てています。
システム ユーザーは実際のユーザーではなく、タスクを実行しているシステムであることを示す目的でのみ使用されます。これは、レプリケーション スレーブ上の I/O および SQL スレッド (接続およびアプライヤー スレッド) によって使用されます。これらのスレッドは、ログイン ユーザーではなく、システムによって処理されます。
たとえば、システム ユーザーは SHOW PROCESSLIST の出力で確認できます。 または information_schema.PROCESSLIST ビュー:
mysql> SHOW PROCESSLIST; +----+-----------------+-----------------+--------------------+---------+-------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+--------------------+---------+-------+--------------------------------------------------------+------------------+ | 2 | system user | | NULL | Connect | 10103 | Waiting for master to send event | NULL | | 3 | event_scheduler | localhost | NULL | Daemon | 10102 | Waiting on empty queue | NULL | | 8 | root | localhost:33356 | performance_schema | Query | 0 | starting | SHOW PROCESSLIST | | 10 | system user | | NULL | Connect | 0 | Slave has read all relay log; waiting for more updates | NULL | | 11 | system user | | NULL | Connect | 0 | System lock | NULL | | 12 | system user | | NULL | Connect | 0 | System lock | NULL | | 13 | system user | | NULL | Connect | 0 | System lock | NULL | | 14 | system user | | NULL | Connect | 0 | System lock | NULL | | 15 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL | | 16 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL | | 17 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL | | 18 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL | +----+-----------------+-----------------+--------------------+---------+-------+--------------------------------------------------------+------------------+ 12 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.PROCESSLIST; +----+-----------------+-----------------+--------------------+---------+-------+---------------------------------------------+----------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+-----------------+-----------------+--------------------+---------+-------+---------------------------------------------+----------------------------------------------+ | 3 | event_scheduler | localhost | NULL | Daemon | 10173 | Waiting on empty queue | NULL | | 12 | system user | | NULL | Connect | 3 | System lock | NULL | | 14 | system user | | NULL | Connect | 3 | Waiting for an event from Coordinator | NULL | | 13 | system user | | NULL | Connect | 3 | System lock | NULL | | 2 | system user | | NULL | Connect | 10174 | Waiting for master to send event | NULL | | 8 | root | localhost:33356 | performance_schema | Query | 0 | executing | SELECT * FROM information_schema.processlist | | 16 | system user | | NULL | Connect | 3 | Waiting for an event from Coordinator | NULL | | 15 | system user | | NULL | Connect | 3 | Waiting for an event from Coordinator | NULL | | 17 | system user | | NULL | Connect | 3 | Waiting for an event from Coordinator | NULL | | 18 | system user | | NULL | Connect | 3 | Waiting for an event from Coordinator | NULL | | 10 | system user | | NULL | Connect | 0 | Waiting for dependent transaction to commit | NULL | | 11 | system user | | NULL | Connect | 3 | Waiting for an event from Coordinator | NULL | +----+-----------------+-----------------+--------------------+---------+-------+---------------------------------------------+----------------------------------------------+ 12 rows in set (0.00 sec)
パフォーマンス スキーマは「システム ユーザー」ではなく、root@localhost を表示します。 これらの接続のために。 sys スキーマは代わりにスレッド名を表示します。たとえば、performance_schema.threads テーブルと sys.session ビューを使用する場合:
mysql> SELECT THREAD_ID, NAME, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_TIME, PROCESSLIST_STATE FROM performance_schema.threads WHERE TYPE = 'foreground'; +-----------+--------------------------------------+----------------+------------------+------------------+------------------+--------------------------------------------------------+ | THREAD_ID | NAME | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_TIME | PROCESSLIST_STATE | +-----------+--------------------------------------+----------------+------------------+------------------+------------------+--------------------------------------------------------+ | 77 | thread/sql/slave_io | 2 | NULL | NULL | 10111 | Waiting for master to send event | | 78 | thread/sql/event_scheduler | 3 | NULL | NULL | NULL | Waiting on empty queue | | 81 | thread/sql/compress_gtid_table | 4 | NULL | NULL | 10110 | Suspending | | 83 | thread/thread_pool/tp_one_connection | 8 | root | localhost | 0 | Sending data | | 85 | thread/sql/slave_sql | 10 | root | localhost | 0 | Slave has read all relay log; waiting for more updates | | 86 | thread/sql/slave_worker | 11 | root | localhost | 0 | System lock | | 87 | thread/sql/slave_worker | 12 | root | localhost | 0 | System lock | | 88 | thread/sql/slave_worker | 13 | root | localhost | 0 | System lock | | 89 | thread/sql/slave_worker | 14 | root | localhost | 0 | Waiting for an event from Coordinator | | 90 | thread/sql/slave_worker | 15 | root | localhost | 0 | Waiting for an event from Coordinator | | 91 | thread/sql/slave_worker | 16 | root | localhost | 0 | Waiting for an event from Coordinator | | 92 | thread/sql/slave_worker | 17 | root | localhost | 0 | Waiting for an event from Coordinator | | 93 | thread/sql/slave_worker | 18 | root | localhost | 0 | Waiting for an event from Coordinator | +-----------+--------------------------------------+----------------+------------------+------------------+------------------+--------------------------------------------------------+ 13 rows in set (0.00 sec)
mysql> SELECT thd_id, conn_id, user, state, time FROM sys.session; +--------+---------+---------------------+---------------------------------------------+-------+ | thd_id | conn_id | user | state | time | +--------+---------+---------------------+---------------------------------------------+-------+ | 77 | 2 | sql/slave_io | Waiting for master to send event | 10378 | | 83 | 8 | root@localhost | Sending data | 0 | | 86 | 11 | sql/slave_worker | System lock | 0 | | 87 | 12 | sql/slave_worker | System lock | 0 | | 88 | 13 | sql/slave_worker | Waiting for an event from Coordinator | 0 | | 89 | 14 | sql/slave_worker | Waiting for an event from Coordinator | 0 | | 90 | 15 | sql/slave_worker | System lock | 0 | | 91 | 16 | sql/slave_worker | Waiting for an event from Coordinator | 0 | | 92 | 17 | sql/slave_worker | Waiting for an event from Coordinator | 0 | | 93 | 18 | sql/slave_worker | Waiting for an event from Coordinator | 0 | | 85 | 10 | sql/slave_sql | Waiting for dependent transaction to commit | 0 | | 78 | 3 | sql/event_scheduler | Waiting on empty queue | NULL | +--------+---------+---------------------+---------------------------------------------+-------+ 12 rows in set (0.42 sec)注意 :「システム ユーザー」としてログインすることはできません。厳密に内部専用です。MySQL の予約済みユーザー アカウントとは
「mysql.sys@localhost」ユーザーの目的は何ですか