Jinsi ya kusimamia hifadhidata ya MySQL? Amri za SSH za Kusimamia Seva za MySQL

jinsi ya kusimamiaHifadhidata ya MySQL? Usimamizi wa amri ya SSHMySQL服务器

MySQL 管理

Anza na usimamishe seva ya MySQL

Kwanza, tunahitaji kuangalia ikiwa seva ya MySQL iko kwa kutekeleza amri ifuatayo:

ps -ef | grep mysqld

Ikiwa MySql tayari imeanzishwa, amri hapo juu itatoa orodha ya michakato ya mysql, ikiwa mysql haijaanzishwa, unaweza kutumia amri ifuatayo kuanza seva ya mysql:

root@host# cd /usr/bin
./mysqld_safe &

Ikiwa unataka kuzima seva ya MySQL inayoendesha sasa, unaweza kutekeleza amri ifuatayo:

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

Mipangilio ya Mtumiaji wa MySQL

Ikiwa unahitaji kuongeza mtumiaji wa MySQL, unahitaji tu kuongeza mtumiaji mpya kwenye jedwali la mtumiaji kwenye hifadhidata ya mysql.

Ufuatao ni mfano wa kuongeza mtumiaji, jina la mtumiaji ni mgeni, nenosiri ni mgeni123, na mtumiaji ameidhinishwa kufanya SELECT, INSERT na UPDATE:

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

Unapoongeza watumiaji, tafadhali kumbuka kuwa nenosiri limesimbwa kwa njia fiche kwa kutumia kipengele cha PASSWORD() kilichotolewa na MySQL.Unaweza kuona katika mfano hapo juu kwamba nenosiri la mtumiaji lililosimbwa ni: 6f8c114b58f2ce9e.

Kumbuka:Katika MySQL 5.7, nenosiri la jedwali la mtumiaji limebadilishwa namfuatano_wa_uthibitishaji.

Kumbuka:Jihadharini na haja ya kutekeleza FLUSH PRIVILEGES kauli.Baada ya amri hii kutekelezwa, jedwali la ruzuku litapakiwa tena.

Ikiwa hutumii amri hii, hutaweza kutumia mtumiaji mpya kuunganisha kwenye seva ya mysql isipokuwa uanzishe upya seva ya mysql.

Unapounda mtumiaji, unaweza kubainisha ruhusa kwa mtumiaji. Katika safu wima ya ruhusa inayolingana, iweke kuwa 'Y' katika taarifa ya kuingiza. Orodha ya ruhusa za mtumiaji ni kama ifuatavyo:

• Chagua_priv
• Insert_priv
• Sasisha_priv
• Futa_faragha
• Create_priv
• drop_priv
• Pakia upya_priv
• shutdown_priv
• Mchakato_faragha
• Faili_priv
• Grant_priv
• Marejeleo_priv
• Index_priv
• Alter_priv

Njia nyingine ya kuongeza watumiaji ni kupitia amri ya GRANT ya SQL. Amri inayofuata itaongeza mtumiaji zara kwenye hifadhidata maalum ya MAFUNZO, na nenosiri ni zara123.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

Amri iliyo hapo juu itaunda rekodi ya habari ya mtumiaji kwenye jedwali la mtumiaji kwenye hifadhidata ya mysql.

Notisi: Taarifa za SQL za MySQL zimekatishwa na semicolon (;).

/etc/my.cnf usanidi wa faili

Katika hali ya kawaida, hauitaji kurekebisha faili ya usanidi, usanidi chaguo-msingi wa faili ni kama ifuatavyo.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Katika faili ya usanidi, unaweza kutaja saraka ambapo faili tofauti za kumbukumbu za hitilafu zimehifadhiwa.Kwa ujumla, huna haja ya kubadilisha usanidi huu.

Amri za kudhibiti MySQL

Ifuatayo inaorodhesha amri zinazotumiwa sana katika mchakato wa kutumia hifadhidata ya Mysql:

• KUTUMIA jina la hifadhi ya data :
  Chagua hifadhidata ya Mysql itakayoendeshwa.Baada ya kutumia amri hii, amri zote za Mysql ni za hifadhidata hii pekee.

  mysql> use chenweiliang;
  Database changed

• ONYESHA HABARI: 
  Inaorodhesha orodha ya hifadhidata ya mfumo wa usimamizi wa hifadhidata ya MySQL.

  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | chenweiliang             |
  | cdcol              |
  | mysql              |
  | onethink           |
  | performance_schema |
  | phpmyadmin         |
  | test               |
  | wecenter           |
  | wordpress          |
  +--------------------+
  10 rows in set (0.02 sec)

• ONYESHA MAJEDWALI:
  Huonyesha majedwali yote ya hifadhidata iliyobainishwa.Kabla ya kutumia amri hii, unahitaji kutumia amri ya matumizi ili kuchagua hifadhidata kuendeshwa.

  mysql> use chenweiliang;
  Database changed
  mysql> SHOW TABLES;
  +------------------+
  | Tables_in_chenweiliang |
  +------------------+
  | employee_tbl     |
  | chenweiliang_tbl       |
  | tcount_tbl       |
  +------------------+
  3 rows in set (0.00 sec)

• ONYESHA SAFU KUTOKA karatasi ya data:
  Onyesha sifa za jedwali la data, aina za sifa, maelezo ya msingi, iwe ni NULL, maadili chaguo-msingi na taarifa nyingine.

  mysql> SHOW COLUMNS FROM chenweiliang_tbl;
  +-----------------+--------------+------+-----+---------+-------+
  | Field           | Type         | Null | Key | Default | Extra |
  +-----------------+--------------+------+-----+---------+-------+
  | chenweiliang_id       | int(11)      | NO   | PRI | NULL    |       |
  | chenweiliang_title    | varchar(255) | YES  |     | NULL    |       |
  | chenweiliang_author   | varchar(255) | YES  |     | NULL    |       |
  | submission_date | date         | YES  |     | NULL    |       |
  +-----------------+--------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)

• ONYESHA INDEX KUTOKA karatasi ya data:
  Onyesha maelezo ya kina ya faharasa ya jedwali la data, ikijumuisha UFUNGUO WA MSINGI (ufunguo msingi).

  mysql> SHOW INDEX FROM chenweiliang_tbl;
  +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | chenweiliang_tbl |          0 | PRIMARY  |            1 | chenweiliang_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
  +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  1 row in set (0.00 sec)

• ONYESHA HALI YA JEDWALI KAMA [KUTOKA db_name] [KAMA 'muundo'] \G:
  Amri hii itatoa utendaji na takwimu za mfumo wa usimamizi wa hifadhidata wa Mysql.

  mysql> SHOW TABLE STATUS  FROM chenweiliang;   # 显示数据库 chenweiliang 中所有表的信息
  
  mysql> SHOW TABLE STATUS from chenweiliang LIKE 'chenweiliang%';     # 表名以chenweiliang开头的表的信息
  mysql> SHOW TABLE STATUS from chenweiliang LIKE 'chenweiliang%'\G;   # 加上 \G，查询结果按列打印