Maintenance

Maintenance

Check Table Integrity

myisamchk -s /var/lib/mysql/*/*.MYI

Repair Table

myisamchk -r /var/lib/mysql/dbname/table_name.MYI

If it fails

systemctl stop mariadb 
myisamchk -r --update-state /var/lib/mysql/dbname/table_name.MYI
systemctl start mariadb

Basic SQL Commands

DB Management

Show Databases

SHOW DATABASES;

Create Database

CREATE DATABASE database-name;

Example:

CREATE DATABASE cmsdatabase;

Select a Database

USE database-name;

Example:

USE cmsdatabase;

Drop/Delete Database

Warning: This will delete the database and there is no way to restore it.

DROP database-name;

Table Management

Show Tables

SHOW TABLES;

Create Table

CREATE TABLE table-name ( column datatype, column datatype );

Show Table Fields

DESCRIBE table-name;

Drop/Delete Table

Warning: This will delete the table and there is no way to restore it.

DROP table-name;

User Management

Show Users

Show users with the access host and password:

SELECT user,host,password FROM mysql.user;

Add User

GRANT ALL PRIVILEGES ON database-name.table-name TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';

Example: (This will allow the user to change ALL tables on the cmsdatabase database.)

GRANT ALL PRIVILEGES ON cmsdatabase.* TO 'cmsuser'@'localhost' IDENTIFIED BY 'lyx*I@y2#\Z$hC@CDhXXF';
Please note that if you need your user to be able to give permissions to other DB users you can add WITH GRANT OPTION in the end of the command before the ; . This can be a security issue.

Drop/Delete a User

DROP USER 'username'@'host';

Example:

DROP USER 'cmsuser'@'localhost';

Change Users Password

Change a users password:

SET PASSWORD FOR 'USERNAME'@'IP' = PASSWORD('PASSWORD');

Update Privileges

After changing privileges for a database or global user do NOT restart the database service instead you will need to do this:

FLUSH PRIVILEGES;

Import/Export

Import Database

Import a dump of an SQL:

mysql -u username -p -h localhost DATABASENAME < filename.sql 

Export Database

Create a dump (backup) of a Database:

mysqldump -u username -p databasename > filename.sql

/home/www/sites/esgr.in/wiki/data/pages/systems/sql_commands.txt · Last modified: 2020/04/29 15:23 by eServices Greece