mysql

recent posts related to the mysql database

How to find all MySQL database tables that have specific column names

MySQL FAQ: How can I find all MySQL database tables that have specific column names?

I found the solution on this SO page. Here’s my take on it.

First, assuming that you want to copy and paste those column names after you get them, I recommend starting the MySQL command line client like this:

How to list MySQL database table column names without the table formatting

If you want to list all of the MySQL database table column names (field names) as a simple list of names, with each column name listed on a separate line, just follow these steps.

First, start MySQL with the -sN options, like this:

$ mysql -sN -u root -p

Then execute a query like this:

How to search for a string in all fields of every table in a MySQL database

This is pretty cool, if you want to search for a text string in all fields of all tables of a MySQL database, you can use phpMyAdmin to do this very easily. Here are the steps.

1) Select the desired database

The first step is to select the database you want to search. Don’t select a table — just select the database you want to search. (If you select a table you’ll get a different search form in Step 2.)

How to use a my.cnf configuration file with MAMP/MySQL

I don’t know why, but I was unable to get MySQL under MAMP 3.5 to read a my.cnf configuration file. I put that file in all of the usual places, but it wasn’t read. Then I tried to get it to read my configuration file by modifying this MAMP/MySQL startup script:

/Applications/MAMP/bin/startMysql.sh

but all I got then was an error message that these two options don’t work:

--defaults-file=FILE
--defaults-extra-file=FILE

I got those errors even though those options are shown in the mysqld_safe help output:

mysqldump error: 1044: Access denied for user ... when using LOCK TABLES

A quick note on a MySQL error: I got the following error when using mysqldump this past weekend:

mysqldump: Got error: 1044: 
Access denied for user 'USER'@'localhost' to database 'DBNAME' when using LOCK TABLES

The solution to this problem/error was to use one of these mysqldump commands:

MySQL - Default a date (timestamp) field to now

MySQL DATE syntax FAQ: How do I set a MySQL DATE field to default to "now", i.e., the current time?

Setting the date to "now"

Unfortunately you can't default a MySQL DATE field to "now", but you can get the "now" behavior with a MySQL TIMESTAMP field. The syntax to create a MySQL TIMESTAMP "now" field is:

A MySQL ‘create table’ syntax example

I used to use MySQL every day for years, but over the last two years I haven't used it much. Today I needed to create a MySQL database table, and had to wonder for a few moments what the MySQL CREATE TABLE syntax was. Fortunately I have plenty of examples out here.

Here's a quick example of a MySQL "users" table:

MySQL create database example - How to create a MySQL database and user account for Drupal

MySQL “create database” FAQ: Can you share an example of how to create a MySQL database, including (a) creating the MySQL database and (b) a new MySQL database user, with all appropriate MySQL grant permissions for that user?

Sure. I've written about the process of How to add/create a MySQL user before, but I just ran across the Drupal installation documentation, which also has a nice description for how to create a MySQL database (for use with Drupal), so I thought I'd include some of their documentation here, along with some notes from my own recent Drupal and MySQL installation.

1) Create a new MySQL database

The first part of the Drupal installation document refers to creating a MySQL database: