Posting ini berfokus pada pemahaman mengapa "pengguna sistem" terlihat di SHOW PROCESSLIST dan tampilan information_schema.PROCESSLIST pada slave replikasi.
Pengguna sistem bukan pengguna sebenarnya dan digunakan untuk tujuan tampilan hanya untuk menunjukkan bahwa sistem yang melakukan tugas. Ini digunakan oleh utas I/O dan SQL (utas koneksi dan applier) pada budak replikasi. Utas ini ditangani oleh sistem dan bukan oleh pengguna yang masuk.
Pengguna sistem misalnya dapat dilihat pada output SHOW PROCESSLIST atau information_schema.PROCESSLIST lihat:
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)
Skema Kinerja tidak akan menampilkan “pengguna sistem” melainkan root@localhost untuk koneksi ini. Skema sys akan menampilkan nama utas sebagai gantinya. Misalnya menggunakan tabel performance_schema.threads dan tampilan 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)Catatan :Tidak mungkin untuk login sebagai “pengguna sistem” – ini hanya untuk internal. Apa yang dimaksud dengan Akun Pengguna Cadangan di MySQL
Apa tujuan dari pengguna “mysql.sys@localhost”