Mysql Commands: Difference between revisions
Jump to navigation
Jump to search
document detailing some common mysql commands. these can be used on mysql database as well as mariadb database |
|||
| (21 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
=Installation= | =Installation= | ||
*Installation on ubuntu server through terminal | *Installation on ubuntu server through terminal | ||
< | : <code> sudo apt install mariadb-server </code> | ||
sudo apt install mariadb-server | : <code> sudo mysql_secure_installation </code> | ||
sudo mysql_secure_installation | : <code> sudo systemctl status mariadb </code> | ||
sudo systemctl status mariadb | |||
</ | |||
*Start or stop mariadb server services | *Start or stop mariadb server services | ||
< | : <code> sudo systemctl stop mariadb </code> | ||
sudo systemctl stop mariadb | : <code> sudo systemctl start mariadb </code> | ||
sudo systemctl start mariadb | |||
</ | |||
=Database Management= | =Database Management= | ||
*Create database: param | *Create database: param | ||
** Using defaults | **Using defaults | ||
< | :: <code> MariaDB [(none)]> CREATE DATABASE param; </code> | ||
:*Using specific parameters | |||
</ | :: <code> MariaDB [(none)]> CREATE DATABASE param DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; </code> | ||
< | |||
</ | |||
*Delete database: param | *Delete database: param | ||
< | : <code> MariaDB [(none)]> DROP DATABASE param;</code> | ||
*List the databases created in the database server | |||
</ | : <code> MariaDB [(none)]> SHOW DATABASES; </code> | ||
*Use a particular database and see all the tables in the database | |||
: <code> MariaDB [(none)]> use param; </code> | |||
: <code> MariaDB [param]> show tables; </code> | |||
=User Management= | =User Management= | ||
* Show users | |||
: <code> MariaDB [(none)]> mysqlselect host, user, password from mysql.user; </code> | |||
* Show grants to a user | |||
: <code> MariaDB [(none)]> SHOW GRANTS FOR 'dbusername'@'%'; </code> | |||
Here dbusername is the name of the user that is used to handle database access | |||
*Revoke all grants to a user | |||
: <code> MariaDB [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dbusername'@'%'; </code> | |||
*Delete a user | |||
: <code> MariaDB [(none)]> DROP USER 'dbusername'@'%'; </code> | |||
=Access Rights Management= | =Access Rights Management= | ||
=Table Management= | =Table Management= | ||
=Backup Management= | =Backup Management= | ||
Latest revision as of 08:41, 13 January 2020
Installation
- Installation on ubuntu server through terminal
sudo apt install mariadb-serversudo mysql_secure_installationsudo systemctl status mariadb
- Start or stop mariadb server services
sudo systemctl stop mariadbsudo systemctl start mariadb
Database Management
- Create database: param
- Using defaults
MariaDB [(none)]> CREATE DATABASE param;
- Using specific parameters
MariaDB [(none)]> CREATE DATABASE param DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- Delete database: param
MariaDB [(none)]> DROP DATABASE param;
- List the databases created in the database server
MariaDB [(none)]> SHOW DATABASES;
- Use a particular database and see all the tables in the database
MariaDB [(none)]> use param;MariaDB [param]> show tables;
User Management
- Show users
MariaDB [(none)]> mysqlselect host, user, password from mysql.user;
- Show grants to a user
MariaDB [(none)]> SHOW GRANTS FOR 'dbusername'@'%';
Here dbusername is the name of the user that is used to handle database access
- Revoke all grants to a user
MariaDB [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dbusername'@'%';
- Delete a user
MariaDB [(none)]> DROP USER 'dbusername'@'%';