MySQL / MariaDB System Admin

A few commands that might help to manage MySQL / MariaDB databases and tables

  1. User management
    1. Add a new user:
      mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
    2. Deleting an existing user:
      mysql> DROP USER 'finley'@'localhost';
    3. Updating the password of an existing user:
      mysql> SET PASSWORD FOR 'finley'@'localhost' = PASSWORD('auth_string');
  2. Dump
    1. A database:
      mysqldump -h db1 -ppass databaseName
    2. A table from a database:
      mysqldump -h db1 -ppass databaseName tableName
    3. Copy table by dumping between two hosts:
      mysqldump -h db1 -ppass1 dbname tableX | mysql -h db2 -ppass2 dbname
  3. Repair table:
    repair table tableX;
  4. Create a new table using the same structure as another table:
    create table users_backup like users;
  5. Show currently running queries:
    show processlist;