Menginstal MySQL Server Di Ubuntu

[+] Post Title :

Menginstal MySQL Server Di Ubuntu

[+] Date : Jumat, 17 Februari 2012
[+] Author : Alma'ruff audi
[+] Link : https://almaevolution.blogspot.com/2012/02/menginstal-mysql-server-di-ubuntu.html
[+] Type : ubuntu

Menginstal MySQL Server

Di Ubuntu, Anda tinggal menjalankan perintah berikut:

sudo apt-get install mysql-server

Mengganti Password Root

Pada saat Anda menginstal mysql-server, Anda akan diminta memasukkan password. Jika suatu saat Anda ingin menggantinya, jalankan perintah berikut:

mysqladmin -u root -pinipassswordlama password 'inipasswordbaru'

Membuat Database

Menggunakan mysqladmin,

mysqladmin -u root -pinipassword create database namadatabase

Kita cek menggunakan mysql client. Contoh keluaran,

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62974
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| namadatabase       |
| openx              |
| wiki               |
+--------------------+
5 rows in set (0.00 sec)
 
mysql>

Menghapus Database

Menggunakan mysqladmin,

mysqladmin -u root -pinipassword drop database namadatabase

Hati-hati dalam menjalankan perintah di atas, perintah ini akan menghapus database dan seluruh isinya.

Memberi Hak Akses

Jalankan mysql client,

mysql -u root -p

Lalu di prompt mysql client, ketikkan perintah berikut. Ingat sesuaikan dengan nama database yang mau di set.

CREATE USER 'namauser'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON namadatabase.* TO 'namauser'@'localhost';
FLUSH PRIVILEGES;

Untuk contoh lain, lihat di bagian selanjutnya. Cara memberi hak akses untuk remote user.

Mengakses Database dari Remote

Pertama, sunting berkas /etc/mysql/my.cnf. Cari bari berikut:

bind-address            = 127.0.0.1

Ganti menjadi seperti di bawah ini. Asumsi IP server = 192.168.1.5

bind-address            = 192.168.1.5

Setelah itu, restart service mysql,

sudo /etc/init.d/mysql restart

Selanjutnya Anda harus membuat user yang bisa digunakan dari remote client. Misal, IP yang akan mengakses ke server adalah 192.168.1.100 dan satu network 192.168.2.0/24.
Maka yang perlu Anda lakukan adalah, jalankan mysql client.

mysql -u root -p 

Di prompt mysql, ketik seperti contoh di bawah ini.

GRANT ALL ON namadatabase.* to 'namauser'@'192.168.1.100' IDENTIFIED BY 'inipassword';
GRANT ALL ON namadatabase.* to 'namauser'@'192.168.2.%' IDENTIFIED BY 'inipassword';
FLUSH PRIVILEGES;

Mengetahui Versi MySQL

mysqladmin -u root -pinipassword version

Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
 
Server version          5.0.51a-3ubuntu5.4
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 128 days 7 hours 39 min 7 sec
 
Threads: 1  Questions: 1754620  Slow queries: 0  Opens: 25709  Flush tables: 1  
Open tables: 64  Queries per second avg: 0.158

Melakukan Ping

mysqladmin -u root -pinipassword ping

Contoh keluaran,

mysqld is alive

Melihat Status MySQL

mysqladmin -u root -pinipassword status

Contoh keluaran,

Uptime: 11085987  Threads: 1  Questions: 1754537  Slow queries: 0  Opens: 25707  
Flush tables: 1  Open tables: 64  Queries per second avg: 0.158

Melihat Status MySQL dengan Lebih Detail

mysqladmin -u root -pinipassword extended-status

Contoh keluaran,

+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 0          |
| Aborted_connects                  | 2          |
| Binlog_cache_disk_use             | 0          |
| Binlog_cache_use                  | 0          |
| Bytes_received                    | 459162673  |
| Bytes_sent                        | 1128082649 |
| Com_admin_commands                | 129        |
| Com_alter_db                      | 0          |
| Com_alter_table                   | 78         |
| Com_analyze                       | 0          |
| Com_backup_table                  | 0          |
| Com_begin                         | 817        |
| Com_call_procedure                | 0          |
...
dst (dipotong)

Melihat Nilai Variable Sistem

mysqladmin  -u root -pinipassword variables

Contoh keluaran,

+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| auto_increment_increment        | 1                           |
| auto_increment_offset           | 1                           |
| automatic_sp_privileges         | ON                          |
| back_log                        | 50                          |
| basedir                         | /usr/                       |
| binlog_cache_size               | 32768                       |
| bulk_insert_buffer_size         | 8388608                     |
| character_set_client            | latin1                      |
| character_set_connection        | latin1                      |
| character_set_database          | latin1                      |
| character_set_filesystem        | binary                      |
| character_set_results           | latin1                      |
| character_set_server            | latin1                      |
| character_set_system            | utf8                        |
| character_sets_dir              | /usr/share/mysql/charsets/  |
| collation_connection            | latin1_swedish_ci           |
| collation_database              | latin1_swedish_ci           |
| collation_server                | latin1_swedish_ci           |
...
dst (dipotong)