« Back to list view

MySQL / Mariadb cheatsheet

http://code.openark.org/blog/mysql/useful-database-analysis-queries-with-information_schema

Queries

WHERE Regex

Find where list of characters is contained:

  • WHERE myfield REGEXP '[A-Z -/]'

Find where list of characters is NOT contained:

  • WHERE myfield REGEXP '[^A-Z -/]'

 

Ubuntu / MySQL 5.7 connect as root from user account fix

  • sudo mysql -u root 
    • ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypasswd';

Misc

Execute query on command line

mysql -e 'SHOW DATABASES'

Show table status

  • SHOW TABLE STATUS FROM db_name;

Change admin password

mysqladmin -u root -p password 'newpassword'

Change user password

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

# Or for mysql 5.7:
SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass';

 

or more securely:

mysqladmin -u myuser -p -h mysql.example.com password

Fix Debian Sysmaintainer Password

  • Get it from /etc/mysql/debian.cnf
  • SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('/etc/mysql/debian.cnf password');

# Or for mysql 5.7:
SET PASSWORD FOR 'debian-sys-maint'@'localhost' = 'password from /etc/mysql/debian.cnf password';

 

Create table

  • CREATE TABLE stocks (
  `id` int NOT NULL AUTO_INCREMENT,
  `slug` varchar(255),
  `date` date,
  `stueck_absolut` int(11),
  `kurs` decimal(15,2),
  `last_modified` datetime,
  `comment` varchar(255),
  PRIMARY KEY (id)
)

Drop multiple default values at once

  • ALTER TABLE my_table
    ALTER COLUMN col1 DROP DEFAULT,
    ALTER COLUMN col2 DROP DEFAULT
;

Create user with MySQL 8

  • CREATE USER foo@localhost IDENTIFIED WITH mysql_native_password BY 'secret';
  • GRANT ALL ON foo.* TO foodb@localhost;
  • FLUSH PRIVILEGES;

Reset mysql root password

Tested on Ubuntu 18.04

  • service mysql stop

  • mkdir -p /var/run/mysqld

  • chown mysql:mysql /var/run/mysqld

  • /usr/sbin/mysqld --skip-grant-tables --skip-networking &

  • mysql -u root

    • FLUSH PRIVILEGES;

    • USE mysql;

    • UPDATE user SET authentication_string=NULL WHERE User='root';

    • UPDATE user SET plugin="mysql_native_password" WHERE User='root';

    • quit

  • pkill mysqld

  • service mysql start

  • systemctl status mysql.service

mysqldump

mysqldump remote

  • mysqldump --add-drop-table --no-tablespaces --host="mysql.example.com" --user="my_username" --password my_database > my_database.sql

mysqldump with limited privileges:

  • mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

  • mysqldump --no-tablespaces

    • # mysqldump needs „PROCESS“ privilege if the --no-tablespaces option is not used

bz2 dump

mysqldump myDatabase --add-drop-table | bzip2 > myDump.sql.bz2

bzcat < myDump.sql.bz2 | mysql -D myDatabase

Show list of views

  • SHOW FULL TABLES IN usrdb_stunvmuu WHERE TABLE_TYPE LIKE 'VIEW';

Show sizes of databases

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;

Show table sizes

SELECT 
    table_name AS "Table",  
    round(((data_length + index_length) / 1024 / 1024), 2) as size   
FROM information_schema.TABLES  
WHERE table_schema = "YOUR_DATABASE_NAME"  
ORDER BY size DESC;

Comparing dates without time

Get date without time:

SELECT DATE(NOW());

Use this to compare date colums, otherwise we would not get events for today:

SELECT * FROM EVENT WHERE date >= DATE(NOW());

Comparing between two databases with subquery

SELECT * FROM old_database.hardware o WHERE o.id NOT IN 
  (
    SELECT i.inventory_number from ullright.ull_ventory_item i) 
  )


Correlated subquery, reference between query and subquery

Example within one database:

UPDATE ull_flow_doc d
  SET d.updated_at =
  (
    SELECT max(m.created_at)
      FROM ull_flow_memory m
      WHERE d.id = m.ull_flow_doc_id
  )
;

Between two databases:

UPDATE ullright.ull_entity e 
  SET e.cost_center = 
  (
    SELECT u.kostenstelle 
      FROM old_database.user u 
      WHERE e.id = u.userid
  )
;

Group a date field per month

SELECT COUNT(*), SUBSTR(d.created_at, 1, 7) AS month FROM ull_flow_doc d GROUP BY month;

GROUP_CONCAT directly create JSON which can be easily parsed to PHP array:

CONCAT('[', GROUP_CONCAT( DISTINCT
    CONCAT('{"id":"' , comment.id , '","name":"' , comment.name , '"}')
    ORDER BY comment.name
    SEPARATOR ','
), ']') AS comments,

Search and Replace

UPDATE ull_cms_item_translation SET body=REPLACE(body, 'search', 'replace')  WHERE body LIKE '%search%';


Find all columns LIKE

SELECT
	*
FROM
	information_schema.columns
WHERE
	column_name LIKE '%updated%;

 

Server Trouble Shooting

  • vi /var/log/mysql/error.log

Startup fails, no log entries:

  • sudo -u mysql mysqld

Message "mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2)
161026 10:44:40 [ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files":

  • vi /etc/mysql/my.cnf 
    • [mysqld]

secure-file-priv = ""

 Message "key_buffer is deprecated"

  • vi /etc/mysql/my.cnf
    • Rename "key_buffer" to "key_buffer_size"

 

Compare two databases

https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldbcompare.html

  • mysqldbcompare --run-all-tests --format=vertical --server1=root:pwd@localhost  db1:db2 > diff.txt

 

Changing SQL Mode at Runtime

  • $res = mysql_query("SHOW VARIABLES LIKE 'sql_mode'");
    var_dump(mysql_fetch_assoc($res));

    mysql_query("SET SESSION sql_mode=''");

 

Copy MySQL data directory directly

Tested with Ubuntu 20.04

  • service mysql stop
  • Backup current mysql directory
    • mv /var/lib/mysql /var/lib/mysql.bak
  • Copy files from other installation
    • cp -a /source/var/lib/mysql /var/lib/
  • Fix user and group
    • chown mysql:mysql /var/lib/mysql -R
  • service mysql start
  • Fix debian-sys-maint user

    • Get password from /etc/mysql/debian.cnf

      •  

        cat /etc/mysql/debian.cnf

    • mysql -u root -p

      • SET PASSWORD FOR 'debian-sys-maint'@'localhost' = 'password from /etc/mysql/debian.cnf password';

Install MySQL Workbench