Bii o ṣe le ṣakoso data MySQL? Awọn aṣẹ SSH lati Ṣakoso awọn olupin MySQL

bi o lati ṣakoso awọnMySQL database? SSH pipaṣẹ isakosoMySQLolupin

MySQL Isakoso

Bẹrẹ ati da olupin MySQL duro

Ni akọkọ, a nilo lati ṣayẹwo boya olupin MySQL ba wa ni oke nipa ṣiṣe pipaṣẹ atẹle:

ps -ef | grep mysqld

Ti MySql ba ti bẹrẹ tẹlẹ, aṣẹ ti o wa loke yoo gbejade atokọ ti awọn ilana mysql, ti mysql ko ba bẹrẹ, o le lo aṣẹ atẹle lati bẹrẹ olupin mysql:

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

Ti o ba fẹ pa olupin MySQL ti nṣiṣẹ lọwọlọwọ, o le ṣe aṣẹ wọnyi:

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

Awọn Eto Olumulo MySQL

Ti o ba nilo lati ṣafikun olumulo MySQL kan, o kan nilo lati ṣafikun olumulo tuntun si tabili olumulo ni aaye data mysql.

Atẹle yii jẹ apẹẹrẹ ti fifi olumulo kun, orukọ olumulo jẹ alejo, ọrọ igbaniwọle jẹ guest123, olumulo naa si ni aṣẹ lati ṣe yiyan, FI sii ati awọn iṣẹ imudojuiwọn:

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)

Nigbati o ba n ṣafikun olumulo kan, jọwọ ṣe akiyesi pe ọrọ igbaniwọle ti wa ni fifi ẹnọ kọ nkan nipa lilo iṣẹ PASSWORD() ti a pese nipasẹ MySQL.O le rii ninu apẹẹrẹ loke pe ọrọ igbaniwọle olumulo ti paroko ni: 6f8c114b58f2ce9e.

Akiyesi:Ni MySQL 5.7, ọrọ igbaniwọle ti tabili olumulo ti rọpo nipasẹìfàṣẹsí_okun,

Akiyesi:Ṣe akiyesi iwulo lati ṣe AWỌN NIPA TI AWỌN NIPA gbólóhùn.Lẹhin ti aṣẹ yii ti ṣiṣẹ, tabili ẹbun yoo tun gbejade.

Ti o ko ba lo aṣẹ yii, iwọ kii yoo ni anfani lati lo olumulo tuntun ti a ṣẹda lati sopọ si olupin mysql ayafi ti o ba tun olupin mysql bẹrẹ.

Nigbati o ba ṣẹda olumulo, o le pato awọn igbanilaaye fun olumulo. Ninu iwe igbanilaaye ti o baamu, ṣeto si 'Y' ninu alaye ifibọ. Akojọ awọn igbanilaaye olumulo jẹ bi atẹle:

• Yan_priv
• Fi sii_priv
• Imudojuiwọn_priv
• Paarẹ_priv
• Ṣẹda_priv
• silẹ_priv
• Atunse_priv
• shutdown_priv
• Ilana_priv
• Faili_priv
• Grant_priv
• Awọn itọkasi_priv
• Atọka_priv
• Alter_priv

Ọnà miiran lati ṣafikun awọn olumulo ni nipasẹ aṣẹ GRANT ti SQL. Aṣẹ atẹle yoo ṣafikun zara olumulo si awọn TUTORIALS data ti a sọ pato, ati ọrọ igbaniwọle jẹ 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';

Aṣẹ ti o wa loke yoo ṣẹda igbasilẹ alaye olumulo ninu tabili olumulo ni ibi ipamọ data mysql.

Akiyesi: Awọn alaye MySQL SQL ti pari pẹlu semicolon (;).

/etc/my.cnf faili iṣeto ni

Labẹ awọn ipo deede, iwọ ko nilo lati yipada faili atunto, iṣeto aiyipada ti faili jẹ bi atẹle:

[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

Ninu faili iṣeto, o le pato itọsọna nibiti o ti fipamọ awọn faili log aṣiṣe oriṣiriṣi, ni gbogbogbo, iwọ ko nilo lati yi awọn atunto wọnyi pada.

Awọn aṣẹ lati ṣakoso MySQL

Atẹle yii ṣe atokọ awọn aṣẹ ti a lo nigbagbogbo ninu ilana ti lilo data data Mysql:

• lILO orukọ ipamọ data :
  Yan aaye data Mysql lati ṣiṣẹ lẹhin lilo aṣẹ yii, gbogbo awọn aṣẹ Mysql wa fun ibi data yii nikan.

  mysql> use chenweiliang;
  Database changed

• ṢAfihan awọn data data: 
  Ṣe atokọ atokọ data data ti eto iṣakoso data MySQL.

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

• ṢAfihan awọn tabili:
  Ṣe afihan gbogbo awọn tabili ti aaye data ti a ti sọ tẹlẹ Ṣaaju lilo aṣẹ yii, o nilo lati lo aṣẹ lilo lati yan ibi ipamọ data lati ṣiṣẹ.

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

• Ṣe afihan awọn ọwọn LATI iwe data:
  Ṣe afihan awọn abuda tabili data, awọn oriṣi ikaṣe, alaye bọtini akọkọ, boya o jẹ NULL, iye aiyipada ati alaye miiran.

  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)

• Afihan Atọka LATI iwe data:
  Ṣe afihan alaye itọka alaye ti tabili data, pẹlu KEY PATAKI (bọtini akọkọ).

  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)

• ṢAfihan ipo tabili bii [LATI db_name] [bii 'apẹẹrẹ'] \G:
  Aṣẹ yii yoo gbejade iṣẹ ati awọn iṣiro ti eto iṣakoso data data 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，查询结果按列打印