Have you ever encountered that after a successful logins to your mysql then the other day you are not able to login anymore using the root account? To fix it, follow below steps for linux users.

1. Stop Mysql
shell> /etc/init.d/mysql stop
2. Start Mysql Safe
shell> mysqld_safe -skip-grant-tables &

3. Login as root without password
shell> mysql -u root -p

4. Update root password using "UPDATE" statement
shell> UPDATE mysql.user SET password = PASSWORD('yourpassword') WHERE user='root';

5. Stop or kill the mysqld_safe instance

shell> pidof mysqld
shell> kill -TERM TheProcessID 
3. Start Mysql normally
shell> /etc/init.d/mysql start OR
shell> service mysql …

To get the current database size just by querying into your query browser or CLI from the INFORMATION_SCHEMA database in table TABLES.

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
Get the database free space

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;
Get the database last update ordered by update time then by create time.

SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA
FROM `TABLES`
GROUP BY TABLE_SCHEMA
ORDER BY 1, 2;

I’m in the process of getting the tests passing for the 0.03 release of NIST::NVD::Store::SQLite3 wherein our hero imports the CWE data and cross-indexes it with CVEs and CPEs.

Follow along and suggest some patches. I’m developing on Debian Wheezy, but I would very much like input from devs on other platforms.

http://git.colliertech.org/?p=NIST-NVD-Store-SQLite3.git;a=summary

cjac@foxtrot:/tmp$ time git clone http://git.colliertech.org/git/NIST-NVD-Store-SQLite3.git
Cloning into 'NIST-NVD-Store-SQLite3'...

real    0m32.757s
user    0m0.200s
sys     0m0.088s
cjac@foxtrot:/tmp$ ls NIST-NVD-Store-SQLite3/t/data/
cwec_v2.1.xml  nvdcve-2.0-test.xml

Publish your patches and I’ll fetch them, or you can submit them in udiff format and I’ll review/apply. Thanks for playing along!

[edit 20120216T1456 -0800] …

In a recent rant on Why I think SQL_MODE is useless…, I wanted to counteract this statement with why we MUST all use SQL_MODE, even with the inherit flaws.

The fundamental principle of a database is to restore and retrieve data. When I can insert data into the database and then I select this data it is different, this is fundamentally wrong. This is a loss of essential data integrity, something a database should NEVER do.

SQL_MODE solves the problem of “silent truncation” in most instances, and produces an all important error. As pointed out, the SQL_MODE has several limitations, however the benefits do out way the risks. Quality control on source code can reduce the limitations, but no amount of coding can stop the CRUD that comes out of the database without some SQL_MODE settings.

I would ask two more …

While doing some routine fiddling regarding some topic I've now forgotten, I discovered that ndb_restore was doing something quite surprising. It's been common wisdom for some time that one can use ndb_restore -m to restore metadata into a new cluster and automatically have your data re-partitioned across the data nodes in the destination cluster. In fact, this was the recommended procedure for adding nodes to a cluster before online add node came along. Since MySQL Cluster 7.0, though, ndb_restore hasn't behaved that way, though that change in behavior doesn't seem to be documented and most don't know that the change ever took place.

I'll go through some of the methods you can use to find information about the partitioning strategy for an NDB table, talk a bit about why ndb_restore stopped working the way most everyone expected (and still expect) it to, and discuss some possible …

I ran across the "Training on Demand" option for a MySQL for Database Administrators class. I thought I would share in case you missed it.

https://blogs.oracle.com/MySQL/entry/mysql_for_dbas_training_on

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getCourseDesc?dc=D61762GC20

They also are offering a  new course :  MySQL Performance Tuning
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getCourseDesc?dc=D61820GC20

More information can be found on the MySQL Database Administration and Development - Learning …

In addition to speaking at RMOUG event in Denver, I also spoke today in Broomfield on “Improving MySQL Performance with Better Indexes”.

This presentation included details on :

• Effective examples of capture SQL via application logging and TCP/IP analysis necessary for identifying the best candidates. Slow is not always the best query to start with.
• All the commands necessary to identify why you may need an index, how to create varying types of indexes, and how to confirm there true effectiveness.
• How the number of table rows and different storage engines can greatly effect the optimization choice and query performance
• The presentation shows how to determine/create and verify covering indexes for a single table example, a master/child example and a production 13 table …

Many of you may monitor their databases for different purposes. Beside alerting it is often good to also make some graphs from MySQL performance counters to see what is actually happening on your database.

The following graphs where made with our FromDual Performance Monitor for MySQL as a Service (MaaS) set-up. If you do not have the time to install a performance monitoring yourself please feel free to contact us for our MaaS solution.

Overview

First of all it is a good idea to have an overview of all the settings in you different databases and if they are compliant to your standards.

Here it looks like …

Many of you may monitor their databases for different purposes. Beside alerting it is often good to also make some graphs from MySQL performance counters to see what is actually happening on your database.

The following graphs where made with our FromDual Performance Monitor for MySQL as a Service (MaaS) set-up. If you do not have the time to install a performance monitoring yourself please feel free to contact us for our MaaS solution.

Overview

First of all it is a good idea to have an overview of all the settings in you different databases and if they are compliant to your standards.

Here it looks like …

I've written a few times about database consistency before, mainly in conjunction with NoSQL and the concept of Eventual consistency. Now, I'm about to do an update on the subject, as I have come to realize a few things.

From an oldtimer like myself, having been an SQL guy for 25 years, I remember Punk-rock and even The Beatles and I having hair growing out of my ears, what can be contributed? Well, let me beging with stating what I mean when I say Database consistency. What I mean is Consistency as the C in ACID (no, we aren't talking drugs here, we are talking databases). Let's see what the online authorative reference work on just about anything on this planet, from the size of J-Lo's feet to the number of Atoms in the universe (those two numbers are quite far apart by the way), Wikipedia: "The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database …