MySQL

Clear your MySQL password

Most people need their MySQL database protected with at least a decent password. I agree, but in development this often causes conflicts - and I prefer to work with my MySQL datbase without all the password-hassle. On Ubuntu I recently installed MySQL and set a password. Here’s how to remove that password so you can skip all the password stuff during development. ~ First, connect to MySQL and check what permissions are currently set:

Once and for all: Rails migrations integer :limit option

I literally always have to look up the meaning of :limit in migrations when it comes to integer values. Here’s an overview. Now let’s memorise it (oh, this works for MySQL, other databases may work differently):

Export CSV directly from MySQL

How ofter were you asked by a client for a CSV (or excel) file with data from their app? I get asked that question quite often, so I wanted make the process as easy as possible. And guess what? You can create CSV files directly from MySQL with just one query! Let’s say you want to export the id, name and email fields from your users table to a CSV file.

How to install MySQL on Ubuntu/Debian

It may seem easy for some, but for others, installing MySQL on Ubuntu or Debian Linux is not an easy task. This article explains to you how to install the MySQL Server and Client packages on a Ubuntu/Debian system. First of all, make sure your package management tools are up-to-date. Also make sure you install all the latest software available. sudo apt-get update sudo apt-get dist-upgrade After a few moments (or minutes, depending on the state of your system), you’re ready to install MySQL.

MySQL: (Re)set the auto-increment value of a table

Sometimes it’s necessary to set the starting point of a MySQL auto-increment value. Normally, MySQL starts auto-incrementing at 1. But let’s say you want to start at 10.000, because you want at least a five figure number. You can use the following query to set the MySQL auto-index: ALTER TABLE some_table AUTO_INCREMENT=10000 If you want to delete all records from your table and restart auto-index at 1, you might be tempted to run a DELETE query, followed by the above example, setting the auto increment value to 1.

Find and Replace with a MySQL Query

There are times when you have a lot of data in a database (let’s say wp_posts for a Wordpress blog like Ariejan.net). When you need to find and replace certain strings, this can be a very tedious task. Find all posts containing the “needle” string and manually replace all these occurrences with “chocolate”. With about 200 posts, you can imagine how long this would take to do manually. But, as I always say: “You’re a programmer!