@llplmlyd
2023-05-25T16:17:44.000000Z
字数 6429
阅读 397
数据库
refer to https://www.thegeekdiary.com/what-is-the-purpose-of-system-user-in-mysql-replication/
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.