MySQL

What version of mysql am I running again?

select version()

Find duplicate rows

SELECT name FROM atable GROUP BY name HAVING COUNT(*) > 1;

Grant all priv to root mysql user.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost.localdomain' WITH GRANT OPTION;

add user,grant privileges, set password, flush

CREATE USER 'loki'@'localhost';
GRANT ALL PRIVILEGES ON db_name.* TO 'loki'@'localhost';
SET PASSWORD FOR 'loki'@'localhost' = PASSWORD('odin');
FLUSH PRIVILEGES;

Getting mysql to work on ubuntu with mysql_config

got a packet bigger than 'max_allowed_packet'

remember to change that password

Reset your mysql password

Comparing and diffing mysql

Replace data within your table

MySQL performance

MySQL replication

"Unable to convert MySQL date/time value to System.DateTime"

message queue for MySQL

Graphs and hierarchies in mysql

importing mysql 3.3 to mysql 5.5

every derived table must have its own alias

MariaDB

Parameterized queries in MySQL using .net mysql connector

MySqlConnection connection = new MySqlConnection(_connectionString);
MySqlCommand command = connection.CreateCommand();
MySqlParameter forumNameParameter = new MySqlParameter("?forumName", forumName);
command.Parameters.Add(forumNameParameter);
command.CommandText = "SELECT * FROM Forum where name = ?forumName";

Altering and changing tables in mysql

recover deleted space mysql || get diskspace back from mysql

MySQL Character Sets and Collations

mysqlcheck your database.

mysqlcheck -A --auto-repair -uroot -ppassword

Removing Tables based on their Prefix

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';') AS statement FROM 
information_schema.tables WHERE table_schema = 'Btaskercouk' AND table_name LIKE 'jos_%';

headers and client library minor version mismatch

select connected clients from mysql

SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
GROUP_CONCAT(DISTINCT USER) AS users,
COUNT(*)
FROM information_schema.processlist
GROUP BY host_short WITH ROLLUP

mysql -s -s -e "SELECT GROUP_CONCAT('kill ',id SEPARATOR '; ') AS kill_list FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command='Sleep';" | mysql

select size of tables from mysql

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

select number of rows from each table with rollup

SELECT table_name, sum(table_rows) as table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<schema>' GROUP BY table_name WITH ROLLUP;

Get last month

BETWEEN date_format(NOW() – INTERVAL 1 MONTH, ‘%Y-%m-01?) AND last_day(NOW() – INTERVAL 1 MONTH) 

Get row number with table results mysql

set @row :=0;
select *,@row:=@row+1 as ‘rownum’ from
( your select here)

select @cnt:=@cnt+1, t1.* FROM (your select here) t1, (SELECT @cnt:=0) t2;

mysql search all tables for string

whats the difference between MyISAM and INNODB MySQL storage Engines

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.
Tables are really fast for select-heavy loads Table level locks limit their scalability for write intensive multi-user environments. Smallest disk space consumption Fulltext index Merged and compressed tables. 

INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB
ACID transactions Row level locking Consistent reads – allows you to reach excellent read write concurrency. Primary key clustering – gives excellent performance in some cases. Foreign key support. Both index and data pages can be cached. Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still recover to the consistent state- No check / repair like MyISAM may require. All updates have to pass through transactional engine in InnoDB, which often decreases performance compared to non-transactional storage engines.