Tuesday, December 11, 2012

Simple setting of MySQL server for user's prefix

Few MySQL commands that I need:

  • connect to database
    mysql --user=root mysql -p
  • create db user 'paja' for connecting to localhost and also from outside
    CREATE USER 'paja'@'localhost' IDENTIFIED BY 'paja-password';
    CREATE USER 'paja'@'%' IDENTIFIED BY 'paja-password'; 
  • grant privileges to create databases with prefix 'paja_'
    GRANT ALL PRIVILEGES ON `paja\_%` . * TO 'paja'@'localhost';
    GRANT ALL PRIVILEGES ON `paja\_%` . * TO 'paja'@'%'
    ;
  • modify /etc/mysql/my.cnf by comment line
    bind-address           = 127.0.0.1
    that makes MySQL server listening only on localhost
  • test connection from localhost and from different host
    mysql --user=paja -h localhost -p
    mysql --user=paja -h myhost.address -p