Catatan Perjalanan

Kumpulan Perintah MYSQL

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

Kumpulan Perintah MYSQL Rating: 4.5 Diposkan Oleh: ferry mulyanto

0 comments:

Post a Comment