MySQL

Fact Finding

If you have just been given access to a server with some form of MySQL on it and you want to find out about it...

ps -ef  | grep mysql

Will prove it's actually running and show who it's running as. Note that whichever user mysql is actually running as, you will normally need 'root' access to be able to manage MySQL.

cat /etc/my.cnf

Will show you the config, or link you to the config, and may also give some hints as to what flavour of MySQL you are dealing with.e.g. Percona will link to these locations from /etc/my.cnf...!includedir /etc/my.cnf.d/
!includedir /etc/percona-server.conf.d/

mysql -V

You might get lucky and be able to run the mysql client as your login user. Even better if you know some database users and their passwords.

MySQL Status

sudo systemctl status mysqld

sudo service mysql status

● mysql.service - MySQL Community Server   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en   Active: active (running) since Thu 2019-12-12 13:46:47 GMT; 8min ago     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 29088 (mysqld)   Status: "Server is operational"    Tasks: 39 (limit: 4915)   CGroup: /system.slice/mysql.service           └─29088 /usr/sbin/mysqld
Dec 12 13:46:36 nightingale systemd[1]: Stopped MySQL Community Server.Dec 12 13:46:36 nightingale systemd[1]: Starting MySQL Community Server...Dec 12 13:46:47 nightingale systemd[1]: Started MySQL Community Server.

mysql -uusername -p -estatus

mysql  Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id:   11Current database: Current user:  root@localhostSSL:   Not in useCurrent pager:  stdoutUsing outfile:  ''Using delimiter: ;Server version:  8.0.18 MySQL Community Server - GPLProtocol version: 10Connection:  Localhost via UNIX socketServer characterset: utf8mb4Db     characterset: utf8mb4Client characterset: utf8mb4Conn.  characterset: utf8mb4UNIX socket:  /var/run/mysqld/mysqld.sockUptime:   16 min 26 sec
Threads: 2  Questions: 23  Slow queries: 0  Opens: 538  Flush tables: 4  Open tables: 35  Queries per second avg: 0.023--------------

sudo /etc/init.d/mysql status

This only works on some systems

MySQL Start

Linux

sudo systemctl start mysqld

sudo service mysql start

Windows

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld"

or (assuming a MySQL Windows Service has been configured)

sc start mysqld_service_name

or

NET START mysqld_service_name 

MySQL Stop

Linux

sudo service mysql stop

Windows

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin" -u root shutdown  -p

or (assuming a MySQL Windows Service has been configured)

sc stop mysqld_service_name

or

NET STOP mysqld_service_name 

MySQL Version

mysql -V

mysql --version

mysqld --version

If there are no firewalls blocking your route, you can also get basic version information using telnet or nc...

telnet myserver 3306

nc myserver 3306

Example output from nc...

U8.0.23-commercial(h(Mj3_*n▒▒▒▒▒5ek-*(1LNcaching_sha2_password^C

SELECT VERSION();

SELECT @@VERSION;

SHOW VARIABLES LIKE "%version%";

MySQL Uptime

SHOW GLOBAL STATUS LIKE 'Uptime';


SELECT TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') AS Uptime

  FROM information_schema.GLOBAL_STATUS

 WHERE VARIABLE_NAME='Uptime';

MySQL Run Script

source /path/script.sql

MySQL Architecture

Bibliography