MySQL Admin Notes

View MySQL running variables;

SHOW VARIABLES;

SHOW VARIABLES LIKE 'max_conn%';

SET GLOBAL wait_timeout=30;

SET GLOBAL general_log=1;
SET GLOBAL general_log_file='mariadb.log';

 

MySQL dumb tables, database, and grab user details;

mysqldump -u root -p my_db > my_db.sql # Dump entire DB with it's data
mysqldump -u root -p my_db --tables my_table --no-data --compact # Dump create statement for specific table
mysqldump --routines -u root -p -B my_db > my_db.sql # -B to have CREATE DATABASE statement
mysqldump -u root -p --no-create-db --no-create-info mydb > my_db.sql # Data only no create DB or table statements
mysqldump -u root -p --insert-ignore mydb > my_db.sql # --insert-ignore is needed if there will be clashing primary keys on import
mysqldump -d -B -u <username> -p <db_name> # Dump schema (CREATE DATABASE and CREATE TABLE command without row data)


# Export to PostgreSQL
mysqldump --compatible=postgresql --default-character-set=utf8 -r databasename.mysql -u root databasename

mysql -u root -p -B -s -e "show grants for 'dbuser'@'localhost';" > mysql_user.sql
# to add ; to the end of the statements in importing else where as valid statements
mysql -u root -p -B -s -e "show grants for 'dbuser'@'localhost';" | sed s/$/\;/ > mysql_user.sql

Notes on importing a MySQL dump on a new box;

create database db;
create user 'aaa'@'localhost' identified by 'bbb';
grant all privileges on db.* to 'aaa'@'localhost' with grant option;
# Opps too many privs, remove them
revoke all on db.* FROM 'aaa'@'localhost'; 
GRANT SELECT ON `db123`.* TO 'aaa'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `db`.* TO 'aaa'@'localhost';
GRANT LOCK TABLES ON `db`.* TO 'aaa'@'localhost'; # To allow mysqldump with this user
FLUSH PRIVILEGES;
show grants for 'aaa'@'localhost';

mysql -u aaa -pbbb db < dump.sql
mysql -u aaa -pbbb -D db -e 'show tables;'

To extract a specific table from a MySQL DB dump; either sed for table name into a separate file:

sed -n -e '/CREATE TABLE.*mytable/,/CREATE TABLE/p' mysql.dump > mytable.dump

or sed for line numbers;
# grep -n 'Table structure' dump.sql
19:-- Table structure for table `t1`
40:-- Table structure for table `t2`
61:-- Table structure for table `t3`

sed -n '40,61 p' dump.sql > t2.sql

Import CSV;
Make the table with the matching CSV column headings in the same order first:

mysql> load data local infile 'two-sql-fixed.csv' into table `fttx` fields terminated by ',' lines terminated by '\n';
Query OK, 683563 rows affected (32.02 sec)
Records: 683563  Deleted: 0  Skipped: 0  Warnings: 0

Check Users;

mysql> select Host, User, Password from mysql.user;
+-----------+------------------+-------------------------------+
| Host      | User             | Password                      |
+-----------+------------------+-------------------------------+
| localhost | root             | *1234567890ABCDEFGHI          |
| localhost | theusers         | *1234567890ABCDEFGHI          |
| localhost | adbuser          | *1234567890ABCDEFGHI          |
+-----------+------------------+-------------------------------+
3 rows in set (0.00 sec)


UPDATE mysql.user SET Password=PASSWORD('123abc') WHERE User='root';
FLUSH PRIVILEGES;


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

use mysql;
delete from user where `user`='username-here';
flush privileges;

Runn the 'mysql_secure_installation' script if possible (should be in $PATH) to remove some of the default less secure install options.

Checking tables;

CHECK TABLE tblXYZ;
REPAIR TABLE tblXYZ;
OPTIMIZE TABLE tblXYZ;
mysqlcheck -o --all-databases # -o for Optimize, all databases
mysqlcheck -u aaa -p --all-databases # Default is -c for CHECK, all databases
mysqlcheck -u aaa -p --auto-repair --optimize --all-databases

Move tables into their own InnoDB table space;

mysql -NBe "select concat('alter table ', table_schema, '.', table_name, ' engine=InnoDB;') from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine='InnoDB'; " > alter.sql

Table/DB Sizes;

--All DB sizes

SELECT table_schema "DB Name", sum( data_length + index_length ) / 1024 / 1024 "DB Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
+--------------------+----------------------+
| DB Name            | DB Size in MB        |
+--------------------+----------------------+
| lalala             |           0.04887009 | 
| information_schema |           0.00390625 | 
| mysql              |           0.50588703 | 
| a_db_name          |          20.42187500 | 
| test_db            |          14.89980698 | 
+--------------------+----------------------+
5 rows in set (0.01 sec)

--Specific DB Size

SELECT SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size IN MB" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "test_db";
+------------+
| Size IN MB |
+------------+
|      14.90 | 
+------------+
1 row in set (0.00 sec)

--All table sizes in DB

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024 ),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "test_db";
+------------+------------+-------------+--------------+------------+
| TABLE_NAME | table_rows | data_length | index_length | Size in MB |
+------------+------------+-------------+--------------+------------+
| cache      |        136 |      160976 |         9216 |       0.16 | 
| contacts   |        139 |        9052 |         7168 |       0.02 | 
| identities |         69 |        5428 |         3072 |       0.01 | 
| messages   |      12470 |    12815624 |       843776 |      13.03 | 
| session    |       5194 |     1459868 |       294912 |       1.67 | 
| users      |         68 |       12440 |         2048 |       0.01 | 
+------------+------------+-------------+--------------+------------+
6 rows in set (0.00 sec)

--All table counts in DB

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test_db';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| cache      |        136 | 
| contacts   |        139 | 
| identities |         69 | 
| messages   |      12470 | 
| session    |       5194 | 
| users      |         68 | 
+------------+------------+
6 rows in set (0.01 sec)

--Specific table size

SELECT COUNT(*) FROM messages;
+----------+
| COUNT(*) |
+----------+
|    12470 | 
+----------+
1 row in set (0.00 sec)

Altering existing tables

ALTER TABLE `poller_output` CHANGE COLUMN `output` `output` VARCHAR(800);

---