Wednesday, October 4, 2017

MySQL/MariaDB getting started.

Getting initial root permissions

/!\ Note! I assume that all actions are conducted as a superuser or via sudo command.
  1. Find out your DB version:
    #mysql --version
  2. Stop DB server
    #service mysql stop (or mariadb)
  3. Start DB instance in safe mode (yeah, there goes security...)
    #mysqld_safe --skip-grant-tables --skip-networking &
  4. Login as root
    IMPORTANT NOTE: In newer versions of mysql you can login as root into database if you are under superuser system account. Which means: you have to be root in system, to get mysql root shell.
    #mysql -u root
  5. Flush priviliges and alter permissions
    mysql> FLUSH PRIVILIGES;
    MySQL 5.7.6+ and MariaDB 10.1.20+:
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
    Prior MySQL and MariaDB versions:
    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); 
  6. Due to restricted usage of root in newer versions, I adwise to create admin user:
    CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
    FLUSH PRIVILEGES;
  7. Kill mysqld_safe process
    Find PID in /var/run/mysql/mysql.pid or via ps aufx
    Ensure no mysql survives :)))
  8. Restart mysql (of course you can simply use "star", but I prefer that way)
    #service mysql restart
Everybody happy.

Another way, that just might help.

  1. Open /etc/mysql/my.cnf (or any other effective config)
  2. under
    [mysqld]
    add
    skip-grant-tables
  3. #service restart mysql
  4. #mysql -u root
  5. mysql>UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPass'WHERE User = 'root' AND Host = 'localhost';
  6. /!\ Remove skip-grant-tables from my.cnf
  7. #service restart mysql

VIM cheat sheet

Basic basics :) i - start editing, current symbol a - start editing, next symbol Esc - stop editing :w - write to disk :w <filename> -...