Kumpulan Perintah MYSQL

30
Berikut ini adalah perintah-perintah mysql yang cukup sering digunakan, perintah dibawah ini untuk mysql yang terpasang pada sistem operasi CentOS 6.


  1. Install package
            #yum install mysql-server
  1. Secure Config, set root password
            #/usr/bin/mysql_secure_installation
  1. Set IP Tables
            #iptables -I INPUT -p tcp –dport 3306 -m state –state NEW,ESTABLISHED -j ACCEPT
            #iptables -I OUTPUT -p tcp –sport 3306 -m state –state ESTABLISHED -j ACCEPT
            #/sbin/service iptables save
  1. Jalankan Mysql
           #/sbin/service mysqld start
  1. Jalankan script setiap booting
           #chkconfig mysqld on
  1. Test Mysql
          #/usr/bin/mysql -u root -p
  1. Buat DB
          CREATE DATABASE demodb;
  1. Assign user to DB
          GRANT ALL PRIVILEGES ON namadb.* To ‘namauser’@’%’ IDENTIFIED BY ‘passwordanda’;
  1. Refresh
           #FLUSH PRIVILEGES;
  1. Grant Permisi
          GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
  1. Ijinkan akses root dari luar
          #update user set host=’localhost’ where user=’root’ and host=’%’;
          #GRANT ALL PRIVILEGES ON *.* TO  ‘root’@’%’  IDENTIFIED  BY  ‘SPTIK_PasswordRootDB’;
  1. List user
              #use mysql;
              #select host, user from user;
  1. Grant Root akses dri luar
              #GRANT ALL PRIVILEGES ON *.* TO  ‘root’@‘%’  IDENTIFIED  BY  ‘passwordanda’;
  1. Buat user lain selain root
              #CREATE USER ‘namauser’@’%’ IDENTIFIED BY ‘passwordanda’;
  1. COPY DB ke Server LAIN
              #mysqldump -u root -p ‘passwordanda’ namadb | ssh root@iptujuan mysql -u root -p    ‘passworddbanda’ namadb
  1. Lihat informasi database disimpan
               # show variables like ‘data%’;
  1. COPY DB pake RSYNC
              #rsync -auvH –progress mysql/ root@118.97.186.205:/var/lib/mysql
  1. Repair DB
             #mysqlcheck –repair –databases db_name
             #mysqlcheck –repair –all-databases
  1. Benchmark Mysql
              #SELECT BENCHMARK(1000000,1+1);
  1. Rebuild Single DB
              #mysqldump db_name > dump.sql
              #mysql db_name < dump.sql
  1. Rebuild All DB
              #mysqldump –all-databases > dump.sql
              #mysql < dump.sql
  1. DUPLICATE DB
             Sebelumnya tambah parameter dulu.
              #mysql -u root -ppasswordanda -e “SET GLOBAL net_write_timeout=100; SET GLOBAL      max_allowed_packet=1024*1024*1024;”
               #mysqldump -h localhost -u root -ppasswordanda namadb | mysql -h localhost -u root -ppasswordanda namadb
  1. Statistic Mysql
               #mysqladmin -u root -ppassworddbanda proc stat
  1. Backup DB

               #mysqldump -u root -ppassworddbanda namadb > namafile.sql

>