SQL stands for Structured Query Language and is a data query language to Create/ Read / Update or Delete (CRUD) data, structured around tables that are composed of rows sub-divided into columns of various typed data, almost exactly analogous to a spreadsheet.
SQL databases are often used as back-end data stores for many services, such as web-services, so an administrator often needs to be able to deal with the basics of administrating an SQL server, some of the most common of which are MySQL / MariaDB.
MySQL or MariaDB (which is a fork derived from the original MySQL) are common free database servers that an administrator might have to deal with. Learning to manage the basics with MySQL/MariaDB often translates well to other database servers such as PostgreSQL, or other SQL based database servers.
Installing the DB: # mysql_install_db --user=mysql
# mysql_install_db --user=mysql
# chmod 755 /etc/rc.d/rc.mysqld
Start the database, then run mysql_secure_installation to complete the installation. Be sure to put a password on the "root" account and disable guest accounts for a secure installation. It is the case that the systems super- user no longer needs a password to access the MariaDB root account regardless of whether there is one or not, though normal users will be required to enter it (probably to prevent an administrator from being locked out of the database from forgetting / losing the password.)
mysql_secure_installation
[client] user=<username> password=<password> database=<database>
mysql
# chmod 600 ~/.my.cnf
> mysql [-u user] [-h host] [-p] [database]
> mysql
-u
-h
-p
Option -u user Selects the user to connect to the database as. -h host Selects host (localhost by default) to connect to. -p Ask for password. database Database to use upon connecting.
create database databasename;
create database
;
use database;
use
drop database database; drop user user@localhost;
drop database
drop user
@localhost;
create user username@hostname [identified by 'password'];
create user
@
identified by '
'
'%'
'localhost'
set password [for user] = password('password')
set password
for
= password('
')
Grants are how permissions are applied to databases for specific users. A user is identified by their username + hostname combination. The hostname is the host they connect from. Grants can be applied at the global, database, table, column and routine level.
Syntax:
grant [all privileges | SQL commands ] on database.table to username@hostname [identified by 'password'] [with grant option]
grant
all privileges
on
.
with grant option
Grant specifier Privilege: *.* Global (all databases) database.* All tables in a specific database database.table A specific table in a specific database
*.*
.*
grant all privileges on database.* to username@localhost identified by 'password' with grant option;
grant all privileges on
.* to
@localhost identified by '
' with grant option;
grant create routine on database.* to user@localhost; grant alter routine on database.* to user@localhost; grant execute on database.* to user@localhost;
grant create routine on
grant alter routine on
grant execute on
grant select,lock on database.* to username@localhost;
grant select,lock on
show grants for user@localhost;
show grants for
flush privileges;
@localhost
@'%'
use mysql; update user set host='%' where user='user' and host='localhost';
use mysql;
update user set host='%' where user='
' and host='localhost';
# mysqldump --all-databases | gzip > /some/place/all-dbs.gz
> mysqldump db_name > backup.sql
> mysqldump
> backup.sql
> mysql db_name < backup.sql
< backup.sql
# mysqlcheck [--all-databases | db_name]
# mysqlcheck
--all-databases
mysqlshow | tail +4 | tr -d '|' | sed 's/ //g' | grep -v '+---*'
echo "show databases;' | mysql --batch mysql -B -e 'show databases;' mysql -r -s -e 'show databases;'
echo "show databases;' | mysql --batch
mysql -B -e 'show databases;'
mysql -r -s -e 'show databases;'
#!/bin/bash db_dir="/some/path"; for db in $(mysql -B -e 'show databases;'); do echo "mysqldump $db | gzip > $db_dir/$db.sql"; done