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:

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

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:

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

How to search for a string in all fields of every table in a MySQL database alvin May 5, 2016 - 4:55pm

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:

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

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:

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

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.

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

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:

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

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:

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

MySQL backup - How to backup a MySQL database

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

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

MySQL restore: How to restore a MySQL database from a backup

MySQL database FAQ: How do I restore a MySQL backup? (Also written as, "How do I restore a mysqldump file?")

Assuming that you’ve created a MySQL backup file as shown in my “MySQL backup - How to Backup a MySQL database” tutorial, you can use the commands shown here to restore your MySQL database.

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.