• Edit
  • Delete

MySQL cheatsheet

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

Change admin password

mysqladmin -u root -p password 'newpassword'

Change user password

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('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');

bz2 dump

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

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

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;

 

Server Trouble Shooting

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=''");