Log into MySQL:


List Users:

SELECT User FROM mysql.user;

Create & Modify Users:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges with this
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’
REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;
ALL PRIVILEGES -- this allows the user all access to a database (or, if no database is selected, across the system)
CREATE -- allows them to create new tables or databases
DROP -- allows them to them to delete tables or databases
DELETE -- allows them to delete rows from tables
INSERT -- allows them to insert rows into tables
SELECT -- allows them to use the Select command to read through databases
UPDATE -- allow them to update table rows
GRANT OPTION -- allows them to grant or remove other users' privileges

Delete User:

DROP USER ‘demo’@‘localhost’;

Create Database:

create database <dbname>;

Show Processlist:

mysqladmin processlist


Backup a Single Database:

mysqldump database_name > database_name.sql
mysqldump -u'user' -p'passwd' database_name | gzip -c > database_name.sql.gz (or mysqldump -u'user' -p'passwd' database_name | gzip -c >

Backup Multiple Databases:

mysqldump --databases database_one database_two > two_databases.sql

Backup All Databases:

mysqldump --all-databases > all_databases.sql


Restore a Database:

mysql database_name < database_name.sql

Restore a Single Database from All Databases Backup File:

mysql --one-database database_name < all_databases.sql
    • Related Articles

    • Reset mysql password

      First try to see if you can login to mysql as root. ssh to root user, or su root from AD login.       mysql If you get an error similar to: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Then the password is ...
    • MYSQL basic user permissions

       Grant Different User Permissions On a MYSQL database. Here is a short list of other common possible permissions that users can enjoy. ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no ...
    • How do I backup a MySQL database?

      Backing up your MySQL database can be done couple different ways. Method #1 Using the phpMyAdmin web interface included in the Hsphere CP Method #2 Using command line utility "mysqldump" Using the phpMyAdmin Method: To access phpMyAdmin, you will ...
    • How to repair a DB on MySQL

      REPAIR TABLE & Database On MySQL ============================== The REPAIR TABLE method is only applicable to MyISAM, ARCHIVE, and CSV tables. You can use REPAIR TABLE if the table checking operation indicates that there is a corruption or that an ...
    • Your PHP installation appears to be missing the MySQL extension

      The attached phpinfo.txt, it does not look like the MYSQLI extensions are enabled for CLI The hsphere feature is ran on the web server. Hsphere however is a clustered control panel, by this each service (web, database, and mail) are all ran on ...