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:
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,
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
|
Menginstal MySQL Server
