Mengadministrasi MySQL Server di Ubuntu

Beberapa perintah dasar untuk mengadministrasi MySQL Server. Kenali
perintah mysqladmin, mysql, mysqldump untuk keperluan mengadministrasi
database mysql Anda.

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)

Membackup Database


mysqldump -u root -pinipassword namadatabase > file_namadatabase.sql
Atau gunakan skrip berikut, untuk membackup semua database di mysql Anda.



#!/bin/bash

BACKUP_DIR="/data/backups/mysql"
DATE=`date +%F_%H-%M-%S`

echo "Backing up MySQL databases..."
mysql --defaults-file=/etc/mysql/debian.cnf --batch --skip-column-names -e "show databases" |
while read DB ; do
echo Dumping "${DB}" ...
mysqldump --defaults-file=/etc/mysql/debian.cnf --add-drop-table "${DB}" | gzip -c > "${BACKUP_DIR}/${DB}.${DATE}.sql.gz"
done