This post focusses on understanding why the “system user” is seen in SHOW PROCESSLIST and the information_schema.PROCESSLIST view on replication slaves.
The system user is not a real user and is used for display purposes only to indicate it is the system performing the task. It is used by the I/O and SQL threads (connection and applier threads) on a replication slave. These threads are handled by the system and not by a login user.
The system user can for example be seen in the output of SHOW PROCESSLIST or the information_schema.PROCESSLIST view:
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)
The Performance Schema will not display “system user” but rather root@localhost for these connections. The sys schema will display the thread name instead. For example using the performance_schema.threads table and sys.session view:
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)
Note: It is impossible to login as the “system user” – it is strictly internal only.