In your development work, there are sometimes you forgot the mysql root password so you cannot make any system-level change to the mysql server. This quick tutorial tells you how to reset mysql root password in CentOS. First, need to stop MySQL:[bash]root# service mysqld stop[/bash] Then, start MySQL in safe […]
mysql
14 posts
When working with replication on very big table, we may face problem when update table structure (such as adding an index, removing an index, adding a field, etc.). There are few cases (such as changing indexes) running a local sql is better than having it goes with replication, since the […]
There are some case we need to select random records in mysql. The normal way I did see many developers have used is ORDER BY RAND(). But they might not recognize that if the number of records increase, the query is extremely slow due to the fact that it must […]
This article describe an example of using MySQL as a NoSQL in which the application can exceed 750,000 qps on a commodity server. This is originated from http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html UPDATE: Oracle officially released memcached daemon plugin that talks with InnoDB. I’m glad to see that NoSQL+MySQL has become an official solution. It’s still preview release […]
When there are many errors when connecting to a mysql server, the following error might appear and the application cannot connect to the db server any more: Host ‘yourhost’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’ The above error might appear when the application server and […]
In a MySQL Replication based system, How to migrate a master database to another server/host? Scenario in short goes: dump out master databases, import data on the new master server and point slave to the new master server. If you follow this simple steps, master migration should proceed without problems. Export databases from […]
When you are using MySQL, you will (likely) have tables that can be fragmented. Fragmented tables in mysql needs to be optimized. You could simply OPTIMIZE every table in every database, but during an OPTIMIZE, the tables are locked, so writing is not possible. To minimize the time that MySQL […]
The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance: InnoDB is newer while MyISAM is older. InnoDB is more complex while MyISAM issimpler. InnoDB is more strict in data integrity while MyISAM is loose. InnoDB implements row-level lock for inserting and updating […]
This excellent article talks about database performance on very big system at Twitter and Paypal. The article is originated from iTnews.com.au Driving big performance using MySQL Engineers at Twitter, Paypal have joined Facebook in offering a look under the hood of the massive MySQL deployments that drive their web services. Database […]
This article mentions common problems when performing pagination on a big data set and solutions to overcome them. Scenario When querying with a large dataset, we often use SELCT …. ORDER BY a,b,c LIMIT M, N , but do we know how does this query perform? Let’s look at the […]