Yuav tswj hwm MySQL database li cas? SSH hais kom tswj hwm MySQL server

yuav tswj li casMySQL database? Kev tswj hwm SSHMySQLneeg rau zaub mov

MySQL Tswj

Pib thiab nres MySQL server

Ua ntej, peb yuav tsum xyuas seb MySQL server puas tau los ntawm kev khiav cov lus txib hauv qab no:

ps -ef | grep mysqld

Yog tias MySql tau pib lawm, cov lus txib saum toj no yuav tso tawm cov npe ntawm cov txheej txheem mysql, yog tias mysql tsis pib, koj tuaj yeem siv cov lus txib hauv qab no los pib mysql server:

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

Yog tias koj xav kaw qhov tam sim no khiav MySQL server, koj tuaj yeem ua raws li cov lus txib hauv qab no:

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

MySQL User Settings

Yog tias koj xav tau ntxiv tus neeg siv MySQL, koj tsuas yog xav tau ntxiv tus neeg siv tshiab rau cov neeg siv cov lus hauv mysql database.

Cov hauv qab no yog ib qho piv txwv ntawm kev ntxiv tus neeg siv, tus neeg siv lub npe yog qhua, tus password yog qhua 123, thiab tus neeg siv tau tso cai ua SELECT, INSERT thiab UPDATE cov haujlwm:

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)

Thaum ntxiv tus neeg siv, thov nco ntsoov tias tus password yog encrypted siv PASSWORD() muaj nuj nqi muab los ntawm MySQL.Koj tuaj yeem pom hauv qhov piv txwv saum toj no tias tus neeg siv tus password encrypted yog: 6f8c114b58f2ce9e.

Nco tseg:Hauv MySQL 5.7, tus password ntawm tus neeg siv lub rooj tau raug hloov los ntawmauthentication_string.

Nco tseg:Paub txog qhov yuav tsum tau ua FLUSH PRIVILEGES nqe lus.Tom qab cov lus txib no raug ua tiav, lub rooj pub nyiaj yuav rov qab ua dua.

Yog tias koj tsis siv cov lus txib no, koj yuav tsis tuaj yeem siv tus neeg siv tsim tshiab los txuas rau mysql server tshwj tsis yog koj rov pib lub mysql server.

Thaum tsim ib tus neeg siv, koj tuaj yeem teev cov kev tso cai rau tus neeg siv. Hauv kab ntawv tso cai sib raug, muab tso rau 'Y' hauv cov lus ntxig rau cov npe ntawm cov neeg siv kev tso cai yog raws li hauv qab no:

• Select_priv
• Insert_priv
• Update_priv
• Delete_priv
• Create_priv
• drop_priv
• Reload_priv
• shutdown_priv
• Process_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv

Lwm txoj hauv kev ntxiv rau cov neeg siv yog los ntawm GRANT cov lus txib ntawm SQL, cov lus txib tom ntej no yuav ntxiv tus neeg siv zara rau cov ntaub ntawv teev tseg TUTORIALS, thiab tus password yog 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';

Cov lus txib saum toj no yuav tsim cov ntaub ntawv siv cov ntaub ntawv hauv cov neeg siv cov lus hauv mysql database.

Daim ntawv ceeb toom: MySQL SQL nqe lus raug txiav nrog ib lub cim (;).

/etc/my.cnf file configuration

Nyob rau hauv ib txwm muaj xwm txheej, koj tsis tas yuav hloov kho cov ntaub ntawv teeb tsa, lub neej ntawd configuration ntawm cov ntaub ntawv yog raws li nram no:

[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

Nyob rau hauv cov ntaub ntawv configuration, koj tuaj yeem teev cov npe ntawm cov ntaub ntawv sib txawv ntawm cov ntaub ntawv yuam kev tau muab khaws cia. Feem ntau, koj tsis tas yuav hloov cov kev teeb tsa no.

Cov lus txib los tswj MySQL

Cov hauv qab no teev cov lus txib feem ntau siv hauv cov txheej txheem ntawm kev siv Mysql database:

• SIV cov ntaub ntawv lub npe :
  Tom qab siv cov lus txib no, tag nrho Mysql cov lus txib tsuas yog rau cov ntaub ntawv no xwb.

  mysql> use chenweiliang;
  Database changed

• QHIA DATABASES: 
  Sau cov npe database ntawm MySQL database tswj system.

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

• SHOW TABLE:
  Ua ntej siv cov lus txib no, koj yuav tsum siv cov lus txib kom xaiv cov ntaub ntawv los ua haujlwm.

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

• Qhia COLUMNS los ntawm daim ntawv qhia:
  Muab cov ntaub ntawv teev tus cwj pwm, hom cwj pwm, cov ntaub ntawv tseem ceeb, txawm tias nws yog NULL, tus nqi qub thiab lwm yam ntaub ntawv.

  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)

• Qhia INDEX los ntawm daim ntawv qhia:
  Muab cov ntaub ntawv qhia ntxaws ntxaws ntawm cov ntaub ntawv, suav nrog PRIMARY KEY (tus yuam sij tseem ceeb).

  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)

• SHOW TABLE STATUS LIKE [los ntawm db_name] [LIKE 'pattern'] \G:
  Cov lus txib no yuav tso tawm qhov kev ua tau zoo thiab kev txheeb cais ntawm Mysql database tswj system.

  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，查询结果按列打印