MYSQL/MariaDB administrasiýasy üçin 20 mysqladmin buýrugy
mysqladmin, MySQL/MariaDB serweri bilen gelýän buýruk setiri maglumat bazasy dolandyryş programmasy bolup, maglumatlar bazasynyň dolandyryjylary tarapyndan kök parolyny kesgitlemek, kök parolyny üýtgetmek, mysql amallaryna gözegçilik etmek, artykmaçlyklary döretmek, döretmek// ýaly käbir esasy MySQL meselelerini ýerine ýetirmek üçin ulanylýar. maglumat bazalaryny taşlamak, serweriň ýagdaýyny barlamak, ulanyş statistikasyny görkezmek, işleýän talaplary öldürmek we ş.m.
Mysqladmin we umumy sintaksis ulanmagyň buýrugy:
# mysqladmin [options] command [command-arg] [command [command-arg]] ...
MySQL/MariaDB serweriňiz ýok bolsa ýa-da MySQL serweriniň köne wersiýasyny ulanýan bolsaňyz, aşakdaky makalalary ulanyp MySQL wersiýasyny gurmagy ýa-da täzelemegi maslahat berýäris:
- MySQL-i RHEL esasly paýlamalara nädip gurmaly
- MariaDB-ni RHEL we Debian ulgamlarynda nädip gurmaly
Bu makalada ulgam/maglumat bazasynyň dolandyryjylary tarapyndan gündelik işlerinde ulanylýan örän peýdaly mysqladmin buýruklaryny taýýarladyk. Bu meseleleri ýerine ýetirmek üçin ulgamyňyzda MySQL/MariaDB serweri gurlan bolmaly.
1. MySQL kök parolyny nädip düzmeli
MySQL/MariaDB serwerini täze gurnan bolsaňyz, kök ulanyjy hökmünde birikdirmek üçin parol talap etmeýär. Kök ulanyjy üçin MySQL parolyny bellemek üçin aşakdaky buýrugy ulanyň.
# mysqladmin -u root password YOURNEWPASSWORD
Duýduryş: mysqladmin ulanyp, täze MYSQL parolyny düzmek gowşak hasaplanmalydyr. Käbir ulgamlarda parolyňyz ulgamdaky işjeň prosesleriň ýagdaýyny bilmek üçin beýleki ulanyjylar tarapyndan ýerine ýetirilip bilinjek ps buýrugy ýaly ulgam ýagdaý programmalaryna görünýär.
2. MySQL kök parolyny nädip üýtgetmeli
MySQL kök parolyny üýtgetmek ýa-da täzelemek isleseňiz, aşakdaky buýrugy ýazmaly. Mysal üçin, köne parolyňyzy 123456 diýiň we ony täze parol bilen üýtgetmek isleýärsiňiz xyz123.
# mysqladmin -u root -p123456 password 'xyz123'
3. MySQL serweriniň ýagdaýyny nädip barlamaly
MySQL serweriniň işleýändigini ýa-da işleýändigini bilmek üçin aşakdaky buýrugy ulanyň.
# mysqladmin -u root -p ping Enter password: mysqld is alive
4. Haýsy MySQL wersiýamyň işleýändigini nädip barlamaly
Aşakdaky buýruk, häzirki işleýän ýagdaýy bilen birlikde MySQL wersiýasyny görkezýär.
# mysqladmin -u root -p version Enter password: mysqladmin Ver 9.1 Distrib 10.3.32-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others. Server version 10.3.32-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 18 min 6 sec Threads: 6 Questions: 20 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.018
5. MySQL serweriniň häzirki ýagdaýyny nädip tapmaly
MySQL serweriniň häzirki ýagdaýyny bilmek üçin aşakdaky buýrugy ulanyň. Mysqladmin buýrugy, işleýän sapaklar we talaplar bilen iş wagtynyň ýagdaýyny görkezýär.
# mysqladmin -u root -p status Enter password: Uptime: 1185 Threads: 6 Questions: 21 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.017
6. MySQL ýagdaý üýtgeýjilerini we olaryň bahalaryny nädip barlamaly
MySQL serwer üýtgeýjileriniň we bahalarynyň işleýiş ýagdaýyny barlamak üçin aşakdaky buýrugy ýazyň. Çykyş aşakdaky ýaly bolar.
# mysqladmin -u root -p extended-status Enter password: +--------------------------------------------------------------+ | Variable_name | Value | +--------------------------------------------------------------+ | Aborted_clients | 0 | | Aborted_connects | 2 | | Access_denied_errors | 2 | | Acl_column_grants | 0 | | Acl_database_grants | 0 | | Acl_function_grants | 0 | | Acl_procedure_grants | 0 | | Acl_package_spec_grants | 0 | | Acl_package_body_grants | 0 | | Acl_proxy_users | 2 | | Acl_role_grants | 0 | | Acl_roles | 0 | | Acl_table_grants | 0 | | Acl_users | 4 | | Aria_pagecache_blocks_not_flushed | 0 | | Aria_pagecache_blocks_unused | 15706 | | Aria_pagecache_blocks_used | 0 | | Aria_pagecache_read_requests | 0 | | Aria_pagecache_reads | 0 | | Aria_pagecache_write_requests | 0 | ...
7. MySQL serweriniň üýtgeýänlerini we bahalaryny nädip görmeli?
MySQL serweriniň işleýän üýtgeýänlerini we bahalaryny görmek üçin buýrugy aşakdaky ýaly ulanyň.
# mysqladmin -u root -p variables Enter password: +--------------------------------------------+-----------------------------+ | Variable_name | Value | +--------------------------------------------+-----------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | +---------------------------------------------------+----------------------+ ...
8. MySQL serweriniň işjeň sapaklaryny nädip barlamaly
Aşakdaky buýruk MySQL maglumat bazasynyň talaplarynyň ähli işleýän amallaryny görkezer.
# mysqladmin -u root -p processlist Enter password: +----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+ | 2 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 | | 1 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 | | 20 | root | localhost | | Query | 0 | Init | show processlist | 0.000 | +----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
9. MySQL serwerinde maglumat bazasyny nädip döretmeli
MySQL serwerinde täze maglumat bazasyny döretmek üçin aşakda görkezilen buýrugy ulanyň.
# mysqladmin -u root -p create tecmint Enter password: # mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 22 Server version: 10.3.32-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tecmint | +--------------------+ 4 rows in set (0.001 sec)
10. MySQL serwerinde maglumat bazasyny nädip taşlamaly
MySQL serwerinde maglumat bazasyny goýmak üçin aşakdaky buýrugy ulanyň. Sizden y düwmesini tassyklamagyňyz soralar.
# mysqladmin -u root -p drop tecmint Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'tecmint' database [y/N] y Database "tecmint" dropped
11. MySQL artykmaçlyklaryny nädip ýüklemeli/täzelemeli?
Gaýtadan ýüklemek buýrugy serwere grant tablisalaryny täzeden ýüklemegi buýurýar we täzeleme buýrugy ähli tablisalary ýuwýar we gündelik faýllaryny açýar.
# mysqladmin -u root -p reload # mysqladmin -u root -p refresh
12. MySQL serwerini nädip ýapmaly
MySQL serwerini ygtybarly ýapmak üçin aşakdaky buýrugy ýazyň.
mysqladmin -u root -p shutdown Enter password:
Şeýle hem MySQL serwerini başlamak/duruzmak üçin aşakdaky buýruklary ulanyp bilersiňiz.
# systemctl stop mysqld # systemctl start mysqld Or # systemctl stop mariadb # systemctl start mariadb
13. Käbir peýdaly MySQL Flush buýruklary
Aşakda olaryň beýany bilen käbir peýdaly buýruklar bar.
- flush-hostlar: hosthli host maglumatlaryny host keşinden ýuwuň.
- tablisalar: tableshli tablisalary ýuwuň.
- ýumşak sapaklar: threadhli sapaklaryň keşini ýuwuň.
- çyzgylar: informationhli maglumat surnallaryny ýuwuň.
- flush-artykmaçlyklar: Grant tablisalaryny täzeden ýükläň (täzeden ýüklemek ýaly).
- flush-status: status üýtgeýjileri arassalamak.
Geliň, bu buýruklary gözden geçireliň.
# mysqladmin -u root -p flush-hosts # mysqladmin -u root -p flush-tables # mysqladmin -u root -p flush-threads # mysqladmin -u root -p flush-logs # mysqladmin -u root -p flush-privileges # mysqladmin -u root -p flush-status
14. Uklaýan MySQL müşderi amalyny nädip öldürmeli?
Uklaýan MySQL müşderi amalyny kesgitlemek üçin aşakdaky buýrugy ulanyň.
# mysqladmin -u root -p processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 5 | root | localhost | | Sleep | 14 | | | | 8 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+
Indi, aşakda görkezilişi ýaly öldürmek we işlemek ID bilen aşakdaky buýrugy işlediň.
# mysqladmin -u root -p kill 5 Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 12 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+
Birnäçe prosesi öldürmek isleseňiz, aşakda görkezilişi ýaly bölünen prosedura ID-lerini geçiriň.
# mysqladmin -u root -p kill 5,10
15. Birnäçe mysqladmin buýruklaryny bilelikde nädip işletmeli
Birnäçe “mysqladmin” buýrugyny bilelikde ýerine ýetirmek isleseňiz, buýruk şeýle bolar.
# mysqladmin -u root -p processlist status version Enter password: +----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+ | 1 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 2 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 | | 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 | | 9 | root | localhost | | Query | 0 | Init | show processlist | 0.000 | +----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+ Uptime: 173 Threads: 6 Questions: 4 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.023 mysqladmin Ver 9.1 Distrib 10.3.32-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others. Server version 10.3.32-MariaDB Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 2 min 53 sec Threads: 6 Questions: 4 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.023
16. Uzakdan Mysql serwerini nädip birikdirmeli
Uzakdaky MySQL serwerine birikmek üçin uzakdaky enjamyň IP adresi bilen -h (host) ulanyň.
# mysqladmin -h 172.16.25.126 -u root -p
17. Uzakdaky MySQL serwerinde buýrugy nädip ýerine ýetirmeli
Uzakdaky MySQL serweriniň ýagdaýyny görmek isleýärsiňiz diýeliň, buýruk bolar.
# mysqladmin -h 172.16.25.126 -u root -p status
18. Gul serwerinde MySQL köpeltmesini nädip başlamaly/duruzmaly
Gul serwerinde MySQL köpeltmesini başlamak/duruzmak üçin aşakdaky buýruklary ulanyň.
# mysqladmin -u root -p start-slave # mysqladmin -u root -p stop-slave
19. MySQL serwer düzediş maglumatlary surnallara nädip saklamaly
Serwere, ulanylýan gulplar, ulanylýan ýat we ulanyş talaplary barada MySQL gündelik faýlyna hadysanyň meýilnamasy barada maglumatlary ýazmagy tabşyrýar.
# mysqladmin -u root -p debug Enter password:
20. mysqladmin opsiýalaryny we ulanylyşyny nädip görmeli
Myslqadmin buýrugynyň has köp görnüşini we ulanylyşyny bilmek üçin aşakda görkezilişi ýaly kömek buýrugyny ulanyň. Elýeterli wariantlaryň sanawyny görkezer.
# mysqladmin --help
Mysqladmin buýruklarynyň hemmesini diýen ýaly şu makalada öz mysallary bilen goşmak üçin elimizden gelenini etdik, eger-de bolsa, hiç zady sypdyrmadyk bolsak, teswirler arkaly bize habar beriň we dostlaryňyz bilen paýlaşmagy ýatdan çykarmaň.