Momwe mungasamalire database ya MySQL? Lamulo la SSH Kuwongolera Ma seva a MySQL

momwe mungayendetsereMySQL database? SSH command managementMySQL服务器

MySQL Kuwongolera

Yambani ndikuyimitsa seva ya MySQL

Choyamba, tiyenera kuyang'ana ngati seva ya MySQL yakwera poyendetsa lamulo ili:

ps -ef | grep mysqld

Ngati MySql yayamba kale, lamulo ili pamwambapa litulutsa mndandanda wa mysql, ngati mysql sinayambike, mungagwiritse ntchito lamulo ili kuti muyambe seva ya mysql:

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

Ngati mukufuna kutseka seva ya MySQL yomwe ikuyenda pano, mutha kuchita izi:

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

Zokonda Zogwiritsa Ntchito MySQL

Ngati mukufuna kuwonjezera wogwiritsa ntchito MySQL, muyenera kungowonjezera wogwiritsa ntchito patebulo la osuta mu mysql database.

Zotsatirazi ndi chitsanzo chowonjezera wosuta, dzina lake ndi mlendo, mawu achinsinsi ndi alendo123, ndipo wogwiritsa ntchito ali ndi chilolezo chochita SELECT, INSERT ndi 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)

Mukawonjezera wosuta, chonde dziwani kuti mawu achinsinsi amasungidwa pogwiritsa ntchito PASSWORD () ntchito yoperekedwa ndi MySQL.Mutha kuwona pachitsanzo pamwambapa kuti mawu achinsinsi osungidwa ndi: 6f8c114b58f2ce9e.

Chidziwitso:Mu MySQL 5.7, mawu achinsinsi a tebulo la ogwiritsa ntchito asinthidwaauthentication_string.

Chidziwitso:Dziwani kufunika kokhazikitsa ZOCHITIKA ZA FLUSH mawu.Lamuloli litaperekedwa, tebulo lothandizira lidzatsegulidwanso.

Ngati simugwiritsa ntchito lamulo ili, simungathe kugwiritsa ntchito wogwiritsa ntchito watsopano kuti agwirizane ndi seva ya mysql pokhapokha mutayambitsanso seva ya mysql.

Mukapanga wosuta, mutha kutchula zilolezo za wogwiritsa ntchito. Mugawo lolingana la chilolezo, ikani kuti 'Y' m'mawu oyikapo. Mndandanda wa zilolezo za ogwiritsa ntchito uli motere:

• Select_priv
• Insert_priv
• Kusintha_priv
• Chotsani_priv
• Create_priv
• drop_priv
• Reload_priv
• shutdown_priv
• Process_priv
• Fayilo_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv

Njira inanso yowonjezerera ogwiritsa ntchito ndi kudzera mu lamulo la GRANT la SQL.Lamulo lotsatira lidzawonjezera wogwiritsa zara ku database yomwe yatchulidwa TUTORIALS, ndipo mawu achinsinsi ndi 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';

Lamulo lomwe lili pamwambapa lipanga mbiri ya ogwiritsa ntchito patebulo la ogwiritsa ntchito mu database ya mysql.

Zindikirani: Mawu a MySQL SQL amathetsedwa ndi semicolon (;).

/etc/my.cnf file kasinthidwe

Nthawi zonse, simuyenera kusintha fayilo yosinthira, mawonekedwe osasintha a fayilo ali motere:

[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

Mufayilo yosinthira, mutha kufotokoza chikwatu komwe mafayilo amtundu wa zolakwika amasungidwa. Nthawi zambiri, simuyenera kusintha masinthidwe awa.

Lamulo loyang'anira MySQL

Zotsatirazi zikutchula malamulo omwe amagwiritsidwa ntchito kwambiri pogwiritsira ntchito Mysql database:

• Gwiritsani ntchito dzina losungira deta :
  Sankhani database ya Mysql kuti mugwiritse ntchito.Mutatha kugwiritsa ntchito lamuloli, malamulo onse a Mysql ndi a database iyi yokha.

  mysql> use chenweiliang;
  Database changed

• ONANI ZINTHU ZONSE: 
  Imalemba mndandanda wa database wa MySQL database management system.

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

• ONANI MATABWA:
  Imawonetsa matebulo onse a database yomwe mwatchulidwa Musanagwiritse ntchito lamuloli, muyenera kugwiritsa ntchito lamulo loti musankhe nkhokwe yoti mugwiritse ntchito.

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

• ONETSANI ZINTHU ZINTHU ZA tsamba lazambiri:
  Onetsani mawonekedwe a tebulo la data, mitundu yamakhalidwe, zidziwitso zoyambira, kaya ndi NULL, makonda osasinthika ndi zina zambiri.

  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)

• ONETSANI INDEX KUCHOKERA tsamba lazambiri:
  Onetsani zambiri zatsatanetsatane za tebulo la data, kuphatikiza PRIMARY KEY (kiyi yoyambira).

  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)

• SONYEZANI ZOCHITIKA ZA TEBULO NGATI [KUCHOKERA ku db_name] [KUMWANGA 'pattern'] \G:
  Lamuloli litulutsa magwiridwe antchito ndi ziwerengero za Mysql database management 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，查询结果按列打印