Tento příspěvek se zaměřuje na pochopení toho, proč je „uživatel systému“ vidět v zobrazení SHOW PROCESSLIST a v zobrazení information_schema.PROCESSLIST na replikačních slave.
Uživatel systému není skutečný uživatel a používá se pouze pro účely zobrazení k označení, že jde o systém provádějící úlohu. Používají ho I/O a SQL vlákna (připojovací a aplikační vlákna) na replikačním slave zařízení. Tato vlákna zpracovává systém a nikoli přihlášený uživatel.
Uživatel systému je například vidět ve výstupu ZOBRAZIT PROCESSLIST nebo information_schema.PROCESSLIST zobrazit:
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)
Schéma výkonu nezobrazí „uživatel systému“, ale spíše root@localhost pro tato spojení. Systémové schéma místo toho zobrazí název vlákna. Například pomocí tabulky performance_schema.threads a zobrazení 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)Poznámka :Není možné se přihlásit jako „systémový uživatel“ – je to pouze čistě interní. Co jsou vyhrazené uživatelské účty v MySQL
Jaký je účel uživatele „mysql.sys@localhost“