Questo post si concentra sulla comprensione del motivo per cui l'"utente di sistema" viene visualizzato in SHOW PROCESSLIST e nella vista information_schema.PROCESSLIST sugli slave di replica.
L'utente del sistema non è un utente reale e viene utilizzato solo a scopo di visualizzazione per indicare che è il sistema che esegue l'attività. Viene utilizzato dai thread I/O e SQL (thread di connessione e applicatore) su uno slave di replica. Questi thread sono gestiti dal sistema e non da un utente di accesso.
L'utente del sistema può essere visto ad esempio nell'output di SHOW PROCESSLIST o lo schema_informativo.PROCESSLIST visualizza:
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)
Lo schema delle prestazioni non visualizzerà "utente di sistema" ma piuttosto root@localhost per questi collegamenti. Lo schema sys visualizzerà invece il nome del thread. Ad esempio, utilizzando la tabella performance_schema.threads e la vista 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)Nota :È impossibile accedere come "utente di sistema" - è solo strettamente interno. Quali sono gli account utente riservati in MySQL
Qual è lo scopo dell'utente "mysql.sys@localhost"