15 October 2016

MySQL on Ubuntu

Install MySQL
  1. In stall MySQL:
    sudo apt-get update
    sudo apt-get install mysql-server
  2. Set the root password
  3. Secure it:
    sudo mysql_secure_installation
    Answer the questions including disallowing root remote login
  4. Test it:
    service mysql status
    mysqladmin -p -u root version

Create New MySQL User

  1. Start a mysql session:
    mysql -u root -p
  2. Show existing users:
    SELECT User FROM mysql.user;
  3. Create a new User:
    CREATE USER '<newuser>'@'localhost' IDENTIFIED BY '<password>';
NB: Change password:
Requirements: >8 chars; upper and lower case; special chars e.g. "!"
shell: mysql --host=localhost --user=<user> --password=<password> <datbase>
mysql> use mysql;
mysql> SET PASSWORD FOR '<username>'@'<hostname>' = PASSWORD('<password>');

Create New Database

e.g. for Zabbix monitoring tool (from official documentation):
  1. Start a mysql session:
    mysql -u root -p
  2. Show existing databases:
  3. Create the Database:
    create database zabbix character set utf8 collate utf8_bin;
  4. Grant Permissions to user 'zabbix':
    GRANT ALL PRIVILEGES ON zabbix.* to zabbix@localhost identified by '<password>';
  5. Flush the privileges:

No comments:

Post a Comment