mysql

recent posts related to the mysql database

How to search for a string in all fields of every table in a MySQL database alvin May 30, 2017 - 11:04am

Here’s a cool tip: 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 to search every MySQL/MariaDB database table.

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 find all MySQL database tables that have specific column names alvin May 8, 2016 - 9:56pm

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 alvin May 7, 2016 - 7:45pm

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 use a my.cnf configuration file with MAMP/MySQL alvin May 2, 2016 - 8:10pm

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 alvin September 9, 2013 - 1:00pm

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 show status: How to show open database connections

MySQL "show status" FAQ: Can you demonstrate how to use the MySQL show status command to show MySQL variables and status information, such as the number of open MySQL connections?

I don't have a whole lot of time today to give this a detailed discussion, but here is a quick look at some MySQL work that I did recently to show MySQL open database connections.

How to default a MySQL 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 backup - How to backup a MySQL database alvin January 9, 2012 - 9:40am

MySQL database FAQ: How do I backup (back up) a MySQL database?