MariaDB/Mysql Cheat Sheet

User Management

Users are stored in the users table of the mysql database.

MySQL "show users" - how to show/list the users in a MySQL database

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO 'custom'@'localhost';
mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO 'custom'@'host47.example.com';
mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO 'custom'@'%.example.com';

Show the list of users:

select host, user, password, select_priv, delete_priv from user;

Logging in

daniel@mycomputer:~$ mysql -u daniel -p -h localhost test
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 63
Server version: 10.1.4-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4144

$ mysql --user=finley --password=password db_name
$ mysql -u finley -ppassword db_name

Setting root password

No password on root?

Apparently you can reset the root password on MariaDB to blank by logging in without access to tables. This confounds me becuase if you could reset it... wouldn't it be insecure? I think the idea is to only allow the system root user access to the mysql root password so that you have to be logged in as a system administrator to log into mysql as root.

Article1 about resetting root password Article2 about resetting root password

Running a SQL file

mysql -uroot -p banks_development < ~/Downloads/banks_development.sql

Viewing Threads/Connections

mysql -e 'SHOW STATUS WHERE variable_name LIKE "Threads_%" OR variable_name = "Connections"'

mysqladmin extended-status | grep Threads && mysqladmin processlist

$ mysqladmin extended-status | grep Threads
| Threads_cached                                               | 0                                      |
| Threads_connected                                            | 3                                      |
| Threads_created                                              | 1399                                   |
| Threads_running                                              | 1                                      |

$ mysqladmin processlist
+------+---------+-----------+---------------------+---------+--------+-------+------------------+----------+
| Id   | User    | Host      | db                  | Command | Time   | State | Info             | Progress |
+------+---------+-----------+---------------------+---------+--------+-------+------------------+----------+
| 1356 | root    | localhost | banks_development   | Sleep   | 265153 |       |                  | 0.000    |
| 1594 | root    | localhost | regdata_development | Sleep   | 589    |       |                  | 0.000    |
| 1630 | wkotzan | localhost |                     | Query   | 0      | init  | show processlist | 0.000    |
+------+---------+-----------+---------------------+---------+--------+-------+------------------+----------+

You can get the same thing as above using SQL and ActiveRecord:

r = ActiveRecord::Base.connection.execute "SHOW status WHERE variable_name LIKE 'conn%' OR variable_name like '%thread%';"
> r.entries
=> [["Connection_errors_accept", "0"],
 ["Connection_errors_internal", "0"],
 ["Connection_errors_max_connections", "0"],
 ["Connection_errors_peer_address", "0"],
 ["Connection_errors_select", "0"],
 ["Connection_errors_tcpwrap", "0"],
 ["Connections", "1646"],
 ["Delayed_insert_threads", "0"],
 ["Innodb_master_thread_active_loops", "8710"],
 ["Innodb_master_thread_idle_loops", "2353950"],
 ["Performance_schema_thread_classes_lost", "0"],
 ["Performance_schema_thread_instances_lost", "0"],
 ["Slow_launch_threads", "0"],
 ["Threadpool_idle_threads", "0"],
 ["Threadpool_threads", "0"],
 ["Threads_cached", "0"],
 ["Threads_connected", "4"],
 ["Threads_created", "1644"],
 ["Threads_running", "1"],
 ["wsrep_thread_count", "0"]]
 
> r = ActiveRecord::Base.connection.execute 'SHOW processlist';
   (36.6ms)  SHOW processlist
> r.entries
=> [[1356, "root", "localhost", "banks_development", "Sleep", 1917, "", nil, 0.0],
 [1594, "root", "localhost", "regdata_development", "Sleep", 1702, "", nil, 0.0],
 [1635, "root", "localhost", "banks_development", "Sleep", 1885, "", nil, 0.0],
 [1641, "root", "localhost", "common_crawl_extractor_development", "Query", 0, "init", "SHOW processlist", 0.0]]

Getting the Connection ID for Current Process

SELECT connection_id();

From Rails:

> r = ActiveRecord::Base.connection.execute "SELECT connection_id();"
   (22.9ms)  SELECT connection_id();
> r.entries
=> [[1641]]

To Kill a Bad Connection

KILL CONNECTION <connection_id>

Dump MySQL File

mysqldump -u root -p regdata_development line_item_translation_rules > line_item_translation_rules.sql

mysqldump -uroot -p --add-drop-table banks_production fi_app_metadata > /mnt/mysql/dump/fi_app_metadata.sql

mysqldump -uroot -p --where="processing_run_id=46" banks_production fi_app_metadata > /mnt/mysql/dump/fi_app_metadata.sql

Getting the database size

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema ; 

SELECT table_schema "Data Base Name", 
sum( data_length + index_length ) / 1024 / 
1024 "Data Base Size in MB", 
sum( data_free )/ 1024 / 1024 "Free Space in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema ; 

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema;

--http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "regdata_development"
    AND table_name IN ("ffiec_line_items", "fts_line_items", "fts_span_line_items");

Getting the database row counts

SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` 
WHERE `table_schema` = 'YOUR_DB_NAME';

See the data storage directory

MariaDB [regdata_development]> SHOW VARIABLES LIKE '%dir%'
    -> ;
+-----------------------------------------+---------------------------------------------------------+
| Variable_name                           | Value                                                   |
+-----------------------------------------+---------------------------------------------------------+
| aria_sync_log_dir                       | NEWFILE                                                 |
| basedir                                 | /usr/local/Cellar/mariadb/10.1.17                       |
| binlog_direct_non_transactional_updates | OFF                                                     |
| character_sets_dir                      | /usr/local/Cellar/mariadb/10.1.17/share/mysql/charsets/ |
| datadir                                 | /usr/local/var/mysql/                                   |
| ignore_db_dirs                          |                                                         |
| innodb_data_home_dir                    |                                                         |
| innodb_log_arch_dir                     | ./                                                      |
| innodb_log_group_home_dir               | ./                                                      |
| innodb_max_dirty_pages_pct              | 75.000000                                               |
| innodb_max_dirty_pages_pct_lwm          | 0.001000                                                |
| innodb_tmpdir                           |                                                         |
| innodb_undo_directory                   | .                                                       |
| lc_messages_dir                         |                                                         |
| plugin_dir                              | /usr/local/Cellar/mariadb/10.1.17/lib/plugin/           |
| slave_load_tmpdir                       | /var/folders/j0/16g0mm8506n123wd4pwt8nmc0000gn/T/       |
| tmpdir                                  | /var/folders/j0/16g0mm8506n123wd4pwt8nmc0000gn/T/       |
| wsrep_data_home_dir                     | /usr/local/var/mysql/                                   |
| wsrep_dirty_reads                       | OFF                                                     |
+-----------------------------------------+---------------------------------------------------------+
19 rows in set (0.00 sec)

wakproductions/mariadb_cheat_sheet.md