Dave Horner's Website - Yet another perspective on things...
Home Tech Talk Random Tech MySQL
130 guests
Rough Hits : 2925756
moon and stars
how did u find my site?

our entire universe; minuscule spec in gigantic multiverse which is mostly lethal.

"Consistency is the last refuge of the unimaginative." -- Oscar Wilde
\begin{bmatrix} 1 & 0 & \ldots & 0 \\ 0 & 1 & 0 & \vdots \\ \vdots & 0 & \ddots & 0\\ 0 & \ldots & 0 & 1_{n} \end{bmatrix}


Tuesday, 04 September 2007 15:55
Planet MySQL - Your blogs, news and opinions relating to mysql;
MySQL :: Download Connector/Net - go here to get the latest connector for .net mysql access.
MySQL :: MySQL 5.0 Reference Manual :: 2.18.5 Copying MySQL Databases to Another Machine
Real Programmers: How To: Set Up An Ssh Tunnel With Putty (mysql)
SSIS Stuff : Connecting to MySQL from SSIS
maatkit - Project Hosting on Google Code - Maatkit (formerly MySQL Toolkit) contains essential command-line tools for MySQL, such as table checksums, a query profiler, and a visual EXPLAIN tool. It provides missing features such as checking whether slaves have the same data as the master.
MySQL :: Download Connector/ODBC
Download details: SSMA 2008 for MySQL v1.0 CTP1
HeidiSQL - MySQL made easy - a nice gui for mysql.
innotop - The best top clone for MySQL, with special attention paid to InnoDB - Google Project Hosting
mycheckpoint | openark forge - an open source monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.
MyTAP: Unit Testing for MySQL - MyTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in mysql scripts. The TAP output is suitable for harvesting, analysis, and reporting by a TAP harness, such as those used in Perl applications.

Blog by Shlomi Noach | code.openark.org
Tagging and Folksonomy Schema Design for Scalability and Performance -Folksonomy adds the “user dimension”

Writing MySQL Scripts with Python DB-API

Doing INTERSECT and MINUS in MySQL | Carsten’s Random Ramblings

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

Getting mysql to work on ubuntu with mysql_config

apt-get install gcc libc6-dev python-dev libmysqlclient15-dev

got a packet bigger than 'max_allowed_packet'

Edit your /etc/my.conf and inside [mysqld] set max_allowed_packet=100M. Restart mysqld. Then to additionally be sure, set the cmd line params on the client ie. 'mysql --max_allowed_packet=100M' Might not want to leave it so high, but it does get past this error.

remember to change that password

To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h a.host.com password 'new-password'

Reset your mysql password

mysqld_safe --skip-grant-tables &
Recover MySQL root password [hack] | Ranjith Siji | Programming the Web

Comparing and diffing mysql

An algorithm to find and resolve data differences between MySQL tables at Xaprb
mysqldiff -- a utility for comparing MySQL database structures
Synchronizing Your MySQL Databases Using a Free MySQL Admin Tool - SQLyog — DatabaseJournal.com

Replace data within your table

Sometimes you'd like to replace certain text within your tables...for example here's a query I used to replace all my bad br's...
UPDATE `jos_content` SET introtext = replace(introtext,"<br>","<br/>") WHERE introtext like '%<br>%';

MySQL performance

Optimizing MySQL performance « Sachin’s Weblog
Sample datasets for benchmarking and testing - MySQL Performance Blog
MySQL I/O - blog of Mr. Juntgen.
MySQLTuner-perl by major MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. major/MySQLTuner-perl

MySQL replication

MySQL :: MySQL 5.6 Reference Manual :: 16.1.1 How to Set Up Replication
Live Backups of MySQL Using Replication - O'Reilly Media
rubyrep: Home - ruby database replication.
Paul Chilton - talks a bit about his use of rubyrep with PostgreSQL.
Running MySQL Replication Over SSL | Replication and other database things
Checking Slaves are consistent | Replication and other database things

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

When connecting to MySQL from .net connector, you may find you get an error like: "Unable to convert MySQL date/time value to System.DateTime". To handle this, you can fix the offending dates or use a connection string that converts zero (invalid) DateTime to the minimum DateTime value (DateTime.MinValue). To do this, add ";Convert Zero Datetime=True" to the end of your connection string.
How to fix “Unable to convert MySQL date/time value to System.DateTime” exception when using MySQL .NET Connector « George Birbilis @zoomicon
Implement MySQL Replication in 3 simple steps | Maikel Alderhout's Blog

message queue for MySQL

5 subtle ways you're using MySQL as a queue, and why it'll bite you - Engine Yard Developer Blog | Engine Yard Developer Blog
Q4M - a Message Queue for MySQL
Queue::Q4M - search.cpan.org

tumblr/jetpants · GitHub - Ruby, Jetpants is an automation toolkit for handling monstrously large MySQL database topologies. It is geared towards common operational tasks like cloning slaves, rebalancing shards, and performing master promotions.

Graphs and hierarchies in mysql

GRAPH Computation Engine - Documentation | Australian peace of mind for your MySQL infrastructure. - OQGRAPH Docs allows you to handle hierarchies (tree structures) but also complex graphs (nodes having many connections in several directions).
Mike Hillyer's Personal Webspace: Managing Hierarchical Data in MySQL
Nested set model - Wikipedia, the free encyclopedia
Using nested sets - Joomla! Documentation

importing mysql 3.3 to mysql 5.5

mysqldump - Moving from mysql 3.3 to mysql 5.5 - Stack Overflow - question I answered. sed -i '/^--/d' inputfile; sed -i 's/ENGINE=MyISAM/ENGINE=MyISAM/g' inputfile; sed -i 's/timestamp/timestamp/g' inputfile;
echo "drop database davejoomla; create database davejoomla;use davejoomla;"| cat - davejoomla > davejoomla.sql
export - How can I get rid of these comments in a MySQL dump? - Stack Overflow - grep -v '^\/\*![0-9]\{5\}.*\/;$' or sed -e 's/^\/\*![0-9]\{5\}.*\/;$//g'
His Deeds Are Dust » My Perl and MySQL UTF-8 crib

every derived table must have its own alias

select x.*,x.something-x.somethingelse FROM ( YOUR CRAZY COMPLEX QUERY)x;
sql - MYSQL ERROR 1248 (42000): Every derived table must have its own alias - Stack Overflow


How to replace MySQL with MariaDB in 11.36 | kieranbarnes

Parameterized queries in MySQL using .net mysql connector

@Henning » Parameterized queries in MySQL
MySqlConnection connection = new MySqlConnection(_connectionString);
MySqlCommand command = connection.CreateCommand();
MySqlParameter forumNameParameter = new MySqlParameter("?forumName", forumName);
command.CommandText = "SELECT * FROM Forum where name = ?forumName";

Altering and changing tables in mysql

MySQL :: MySQL 5.7 Reference Manual :: 13.1.6 ALTER TABLE Syntax
mysql - Is there any harm in resetting the auto-increment? - Stack Overflow - ALTER TABLE tbl AUTO_INCREMENT = 1; , or just switch from unsigned int=max 4,294,967,295, and instead use BIGINT 2^64 over 18 quintillion 18,446,744,073,709,551,615.

recover deleted space mysql || get diskspace back from mysql

if you perform a bunch of deletes or alters, you won't see a change in your disk space until you run a OPTIMIZE TABLE your_big_table; which of course is table locking...

MySQL Character Sets and Collations

MySQL Character Sets and Collations - Conversion & Setting Defaults

mysqlcheck your database.

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

Useful MySQL Tricks - www.bentasker.co.uk

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

mmm, sorta an ubuntu and php issue; but try removing php5-mysql, which will intelligently install php5-mysqlnd. I think phpmyadmin might have been what put me in this situation, since it removed that when removing php5-mysql. mileage may vary.

select connected clients from mysql

Rational Relational: How to Easily See Who's Connected to Your MySQL Server
SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
FROM information_schema.processlist
ok, now kill the sleeping clients.
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

How to get the sizes of the tables of a mysql database? - Stack Overflow
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

Get last month

Getting last months data with SQL | Replication and other database things
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

Mysql Workbench >6.0 includes a "Database-Search Table Data..." menu item, phpMyAdmin has a search feature, HeidiSQL has a find text on server in table tools, SQLyog too...
Search-find through all databases, tables, columns in MySQL | ..::CHANGE is INEVITABLE::.. - written with mysql procedure
anywhereindb - Comprehensive Search on MYSQL database - Google Project Hosting - mysql with php

whats the difference between MyISAM and INNODB MySQL storage Engines

MyISAM is for speed and InnoDB for data integrity.
Choosing between MyISAM and INNODB – MySQL Storage Engines | ..::CHANGE is INEVITABLE::..
mysql - What's the difference between MyISAM and InnoDB? - Stack Overflow

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 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.
MyISAM for large constant tables or logging tables, relatively infrequent updates or Fast selects – these will not lock the table for the long time and thus it will not reduce performance. Choose MyISAM if you really don’t need InnoDB. Choose InnoDB storage engine when following is required: Intensively updated tables – which can have many long selects running at the same time. Multi-statement transactions Advanced isolation levels and row-level locking Foreign key constraints.
This seems to be a very popular interview question.

DimitriK's (dim) Weblog : MySQL

< Prev  Next >
Last Updated on Saturday, 06 September 2014 14:09