Yadda ake sarrafa MySQL database? Dokokin SSH don Sarrafa Sabar MySQL

yadda ake sarrafaMySQL database? Gudanar da umarnin SSHMySQL服务器

MySQL Gudanarwa

Fara kuma dakatar da uwar garken MySQL

Da farko, muna buƙatar bincika idan uwar garken MySQL ya tashi ta hanyar aiwatar da umarni mai zuwa:

ps -ef | grep mysqld

Idan an riga an fara MySql, umarnin da ke sama zai fitar da jerin ayyukan mysql, idan ba a fara MySQL ba, zaku iya amfani da wannan umarni don fara sabar mysql:

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

Idan kuna son rufe uwar garken MySQL mai gudana a halin yanzu, zaku iya aiwatar da umarni mai zuwa:

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

MySQL Saitunan Mai amfani

Idan kuna buƙatar ƙara mai amfani MySQL, kawai kuna buƙatar ƙara sabon mai amfani zuwa teburin mai amfani a cikin bayanan mysql.

Wannan misali ne na ƙara mai amfani, sunan mai amfani baƙo ne, kalmar sirri baƙo123 ne, kuma an ba mai amfani izinin yin SELECT, INSERT da 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)

Lokacin ƙara mai amfani, lura cewa an rufaffen kalmar sirri ta amfani da aikin PASSWORD() da MySQL ya bayar.Kuna iya gani a cikin misalin da ke sama cewa rufaffen kalmar sirrin mai amfani ita ce: 6f8c114b58f2ce9e.

Lura:A cikin MySQL 5.7, an maye gurbin kalmar sirrin tebur mai amfani databbaci_string.

Lura:Yi hankali da buƙatar aiwatarwa GASKIYAR FARUWA sanarwa.Bayan an aiwatar da wannan umarni, za a sake loda teburin tallafin.

Idan ba ku yi amfani da wannan umarni ba, ba za ku iya amfani da sabon mai amfani don haɗawa da uwar garken mysql ba sai dai idan kun sake kunna uwar garken mysql.

Lokacin ƙirƙirar mai amfani, zaku iya ƙirƙira izini ga mai amfani.A cikin ginshiƙin izini daidai, saita shi zuwa 'Y' a cikin bayanan sakawa. Jerin izinin mai amfani shine kamar haka:

• Zaɓi_priv
• Saka_priv
• Sabuntawa_priv
• Share_priv
• Ƙirƙiri_priv
• sauke_priv
• Sake saukewa_priv
• shutdown_priv
• Tsari_priv
• Fayil_priv
• Grant_priv
• Nassoshi_priv
• Index_priv
• Alter_priv

Wata hanyar da za a ƙara masu amfani ita ce ta hanyar GRANT Command na SQL. Umurni na gaba zai ƙara mai amfani zara zuwa takamaiman bayanai TUTORIALS, kuma kalmar sirri shine 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';

Umurnin da ke sama zai haifar da rikodin bayanan mai amfani a cikin tebur mai amfani a cikin bayanan mysql.

Sanarwa: An ƙare bayanan MySQL SQL tare da ƙaramin yanki (;).

/etc/my.cnf fayil sanyi

A ƙarƙashin yanayi na al'ada, ba kwa buƙatar gyara fayil ɗin daidaitawa, tsarin tsoho na fayil ɗin shine kamar haka:

[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

A cikin fayil ɗin daidaitawa, zaku iya saka adireshin inda ake adana fayilolin log ɗin kuskure daban-daban. Gabaɗaya, ba kwa buƙatar canza waɗannan saitunan.

Umarni don sarrafa MySQL

Masu zuwa suna lissafin umarnin da aka saba amfani da su wajen yin amfani da bayanan Mysql:

• AMFANI sunan ajiyar bayanai :
  Zaži Mysql database da za a sarrafa.Bayan amfani da wannan umarni, duk umarnin Mysql na wannan database ne kawai.

  mysql> use chenweiliang;
  Database changed

• NUNA DATABASES: 
  Ya lissafa jerin bayanai na tsarin sarrafa bayanai na MySQL.

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

• NUNA TEBLES:
  Nuna duk tebur na ƙayyadaddun bayanai kafin amfani da wannan umarni, kuna buƙatar amfani da umarnin amfani don zaɓar bayanan da za a sarrafa.

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

• NUNA SHAFIN DAGA takardar bayanai:
  Nuna halayen tebur na bayanai, nau'ikan sifa, bayanan farko na farko, ko NULL ne, ƙimar tsoho da sauran bayanai.

  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)

• NUNA INDEX DAGA takardar bayanai:
  Nuna cikakkun bayanan fihirisar bayanai, gami da KYAUTAR FARKO (maɓalli na farko).

  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)

• NUNA MATSAYI KAMAR [DAGA db_name] [KAMAR 'tsarin'] \ G:
  Wannan umarnin zai fitar da ayyuka da kididdiga na tsarin sarrafa bayanai na 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，查询结果按列打印