eSGR Documentation

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Maintenance ======

==== Check Table Integrity ====
<sxh bash>myisamchk -s /var/lib/mysql/*/*.MYI</sxh>

==== Repair Table ====
<sxh bash>myisamchk -r /var/lib/mysql/dbname/table_name.MYI</sxh>

=== If it fails ===
<sxh bash>systemctl stop mariadb 
myisamchk -r --update-state /var/lib/mysql/dbname/table_name.MYI
systemctl start mariadb</sxh>

====== Basic SQL Commands ======

==== DB Management ====

=== Show Databases ===
<sxh bash>SHOW DATABASES;</sxh>

=== Create Database ===
<sxh bash>CREATE DATABASE database-name;</sxh>

Example:
<sxh bash>CREATE DATABASE cmsdatabase;</sxh>

=== Select a Database ===
<sxh bash>USE database-name;</sxh>

Example:
<sxh bash>USE cmsdatabase;</sxh>

=== Drop/Delete Database ===
<wrap em>Warning: This will delete the database and there is no way to restore it.</wrap>
<sxh bash>DROP database-name;</sxh>


==== Table Management ====

=== Show Tables ===
<sxh bash>SHOW TABLES;</sxh>

=== Create Table ===
<sxh bash>CREATE TABLE table-name ( column datatype, column datatype );</sxh>

=== Show Table Fields ===
<sxh bash>DESCRIBE table-name;</sxh>

=== Drop/Delete Table ===
<wrap em>Warning: This will delete the table and there is no way to restore it.</wrap>
<sxh bash>DROP table-name;</sxh>

==== User Management ====

=== Show Users ===
Show users with the access host and password:

<sxh bash>SELECT user,host,password FROM mysql.user;</sxh>

=== Add User ===

<sxh bash>GRANT ALL PRIVILEGES ON database-name.table-name TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';</sxh>

Example: (This will allow the user to change ALL tables on the cmsdatabase database.)
<sxh bash>GRANT ALL PRIVILEGES ON cmsdatabase.* TO 'cmsuser'@'localhost' IDENTIFIED BY 'lyx*I@y2#\Z$hC@CDhXXF';</sxh>
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 ===

<sxh bash>DROP USER 'username'@'host';</sxh>

Example:
<sxh bash>DROP USER 'cmsuser'@'localhost';</sxh>

=== Change Users Password ====
Change a users password:

<sxh bash>SET PASSWORD FOR 'USERNAME'@'IP' = PASSWORD('PASSWORD');</sxh>

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

<sxh bash>FLUSH PRIVILEGES;</sxh>

==== Import/Export ====

=== Import Database ===
Import a dump of an SQL:

<sxh bash>mysql -u username -p -h localhost DATABASENAME < filename.sql </sxh>

=== Export Database ===
Create a dump (backup) of a Database:

<sxh bash>mysqldump -u username -p databasename > filename.sql</sxh>