Kepiye carane ngatur database MySQL? Perintah SSH kanggo Ngatur Server MySQL

carane ngaturdatabase MySQL? Manajemen printah SSHMySQL服务器

MySQL Manajemen

Miwiti lan mungkasi server MySQL

Pisanan, kita kudu mriksa yen server MySQL wis aktif kanthi nglakokake printah ing ngisor iki:

ps -ef | grep mysqld

Yen MySql wis diwiwiti, printah ing ndhuwur bakal nampilake dhaptar pangolahan mysql, yen mysql ora diwiwiti, sampeyan bisa nggunakake perintah ing ngisor iki kanggo miwiti server mysql:

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

Yen sampeyan pengin mateni server MySQL sing lagi mlaku, sampeyan bisa nglakokake printah ing ngisor iki:

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

Setelan Pangguna MySQL

Yen sampeyan kudu nambah pangguna MySQL, sampeyan mung kudu nambah pangguna anyar menyang tabel pangguna ing database mysql.

Ing ngisor iki minangka conto nambahake pangguna, jeneng pangguna minangka tamu, sandhi yaiku guest123, lan pangguna duwe wewenang kanggo nindakake operasi SELECT, INSERT lan 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)

Nalika nambahake pangguna, elinga yen tembung sandhi dienkripsi nggunakake fungsi PASSWORD () sing diwenehake dening MySQL.Sampeyan bisa ndeleng ing conto ing ndhuwur yen tembung sandhi pangguna sing dienkripsi yaiku: 6f8c114b58f2ce9e.

Cathetan:Ing MySQL 5.7, sandhi tabel pangguna wis diganti deningstring_authentication.

Cathetan:Dadi weruh saka perlu kanggo ngleksanakake FLUSH PRIVILEGES pratelan.Sawise printah iki dieksekusi, tabel hibah bakal dimuat maneh.

Yen sampeyan ora nggunakake printah iki, sampeyan ora bakal bisa nggunakake pangguna sing mentas digawe kanggo nyambung menyang server mysql kajaba sampeyan miwiti maneh server mysql.

Nalika nggawe pangguna, sampeyan bisa nemtokake ijin kanggo pangguna. Ing kolom ijin sing cocog, setel dadi 'Y' ing statement sisipan. Dhaptar ijin pangguna kaya ing ngisor iki:

• Pilih_priv
• Insert_priv
• Update_priv
• Busak_priv
• Nggawe_priv
• drop_priv
• Reload_priv
• shutdown_priv
• Proses_priv
• File_priv
• Grant_priv
• Referensi_priv
• Index_priv
• Alter_priv

Cara liya kanggo nambah pangguna liwat printah GRANT SQL. Printah sabanjuré bakal nambah pangguna zara kanggo TUTORIALS database kasebut, lan sandi 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';

Printah ing ndhuwur bakal nggawe rekaman informasi pangguna ing tabel pangguna ing database mysql.

cathetan: Pernyataan MySQL SQL dipungkasi nganggo titik koma (;).

/etc/my.cnf konfigurasi berkas

Ing kahanan normal, sampeyan ora perlu ngowahi file konfigurasi, konfigurasi standar file kaya ing ngisor iki:

[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

Ing file konfigurasi, sampeyan bisa nemtokake direktori ing ngendi file log kesalahan sing beda disimpen. Umume, sampeyan ora perlu ngganti konfigurasi kasebut.

Prentah kanggo ngatur MySQL

Ing ngisor iki dhaptar perintah sing umum digunakake ing proses nggunakake database Mysql:

• Panganggone jeneng panyimpenan data :
  Pilih database Mysql sing arep dioperasikake. Sawise nggunakake printah iki, kabeh printah Mysql mung kanggo database iki.

  mysql> use chenweiliang;
  Database changed

• TUNJUKKAN DATABASE: 
  Dhaptar dhaptar database sistem manajemen database MySQL.

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

• TABEL TABEL:
  Tampilake kabeh tabel ing basis data sing ditemtokake. Sadurunge nggunakake printah iki, sampeyan kudu nggunakake printah use kanggo milih database sing bakal dioperasikake.

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

• tuduhake kolom saka lembar data:
  Tampilake atribut tabel data, jinis atribut, informasi kunci utama, apa NULL, nilai standar lan informasi liyane.

  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)

• SHOW INDEX Saka lembar data:
  Tampilake informasi indeks rinci tabel data, kalebu KUNCI PRIMARY (kunci utama).

  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)

• TUNJUKKAN STATUS TABEL SEPERTI [FROM db_name] [LIKE 'pola'] \G:
  Printah iki bakal ngasilake kinerja lan statistik sistem manajemen database 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，查询结果按列打印