Create a full admin

--Need to sudo so you'll have permissions: 

--sudo mysql -u root -p

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'EnterPasswordHERE';

GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin2'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Restore a DB from a mysqldump .sql file

--To restore:

/*

  --sudo mysql -u root -p

  Create Database

  USE it

  source the input file name

 

  ** Note that the mysql database has all the logins

*/

create database x;

use x;

source <file>.sql;


mySQL output to flat file on the file system


Must put the file into this location to avoid issues
Use SHOW VARIABLES LIKE "secure_file_priv"; to see the directory that has been configured.

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

Example:
select LinkLocations From JoeThumbs INTO OUTFILE '/var/lib/mysql-files/joethumbs.txt';

Natural Sorting in MySQL

LENGTH(a.Name), a.Name
https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly

Find Gaps in Sequential Numbering

--How can we find gaps in sequential numbering in MySQL?
--https://stackoverflow.com/questions/4340793/how-can-we-find-gaps-in-sequential-numbering-in-mysql/29736658#29736658

SELECT
  CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
  SELECT
    @rownum:=@rownum+1 AS expected,
    IF(@rownum=YourValue, 0, @rownum:=YourValue) AS got
  FROM
    (SELECT @rownum:=0) AS a
    JOIN YourTable
    ORDER BY YourValue
  ) AS z
WHERE z.got!=0;

Note that the order of columns expected and got is critical.

--If you know that YourCol doesn't start at 1 and that doesn't matter, you can replace

(SELECT @rownum:=0) AS a

with

(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a


 .