myisamchk -s /var/lib/mysql/*/*.MYI
myisamchk -r /var/lib/mysql/dbname/table_name.MYI
systemctl stop mariadb myisamchk -r --update-state /var/lib/mysql/dbname/table_name.MYI systemctl start mariadb
SHOW DATABASES;
CREATE DATABASE database-name;
Example:
CREATE DATABASE cmsdatabase;
USE database-name;
Example:
USE cmsdatabase;
Warning: This will delete the database and there is no way to restore it.
DROP database-name;
SHOW TABLES;
CREATE TABLE table-name ( column datatype, column datatype );
DESCRIBE table-name;
Warning: This will delete the table and there is no way to restore it.
DROP table-name;
Show users with the access host and password:
SELECT user,host,password FROM mysql.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 USER 'username'@'host';
Example:
DROP USER 'cmsuser'@'localhost';
Change a users password:
SET PASSWORD FOR 'USERNAME'@'IP' = PASSWORD('PASSWORD');
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 a dump of an SQL:
mysql -u username -p -h localhost DATABASENAME < filename.sql
Create a dump (backup) of a Database:
mysqldump -u username -p databasename > filename.sql