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