Posts in the “mysql” category

MySQL FAQ: How do I show/list MySQL users, i.e., the user accounts in a MySQL or MariaDB database?

Solution

To show the users in a MySQL database, first log into your MySQL server as an administrative user using the mysql command line client, then run this MySQL query:

[toc]

MySQL “users” FAQ: How do I add a new user to a MySQL or MariaDB database (i.e., how do I create a MySQL user account, and then grant database permissions to that new user account)?

MySQL database backup FAQ: Can you share a Linux shell script that I can use to make a MySQL backup (i.e., a shell script that wraps the mysqldump command)?

I currently have a collection of websites on several different servers (including GoDaddy and A2 Hosting web servers), so I was just spending some time trying to automate my MySQL database backups. To that end, I just created a MySQL shell script that I use on each Linux server to make my database backups, and I thought I'd share that script here.

MySQL database FAQ: Can you provide a large, complete, example MySQL database design?

I recently worked on a project where I wrote a web-based user interface that would let "everyday users" maintain their own Nagios configuration data. After logging in to the web interface, users could modify the Nagios configuration as desired, for instance, when they added new equipment to their network.

MySQL/MariaDB FAQ: What is the MySQL create table syntax?

Here’s a quick example of the MySQL create table syntax on a database table named users:

drop table if exists users;

create table users (
    id int auto_increment not null,
    username varchar(32) not null,
    password varchar(16) not null,
    primary key (id),
    constraint unique index idx_users_unique (username)
) ENGINE = InnoDB;

If you need more examples of the MySQL create table syntax, or examples of the MySQL unique constraint syntax, check out my MySQL Nagios database design. It includes many database tables, and as a result, it covers examples of many MySQL/MariaDB features.

MySQL FAQ: How do I show/list the tables in a MySQL (or MariaDB) database using the mysql command line client?

Solution: How to list the tables

To list/show the tables in a MySQL database:

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:

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.

Update: On September 21, 2021, using MySQL 8 and Ubuntu 20.04, the solution to this problem was to edit this file:

/etc/mysql/mysql.conf.d/mysqld.cnf

Then add this configuration line to the end of that file:

sql_mode = ""

Once I did that, the problem described below went away.

Note that you can use this command to show your MySQL version on Ubuntu Linux:

$ mysql -V
mysql  Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Background and older solution

NOTE: Everything below this point describes the background to this problem, and solutions I used with older versions of MySQL and Ubuntu Linux.

As a brief note to self, I ran into this MySQL error when running a SQL SELECT and GROUP BY query:

02:14:33.911 [main] ERROR scalikejdbc.StatementExecutor$$anon$1 - SQL execution failed
(Reason: Expression #3 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'aad8final.u.alias' which is not functionally dependent on columns
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by):

select tn.nid, count(tn.nid) as countOfTnNid, u.alias, n.title 
from taxonomy_index tn, url_alias u, node_field_data n 
where tn.tid in (select tid from taxonomy_index where nid = 9423) 
and tn.nid = u.nid 
and tn.nid = n.nid 
and n.status = 1 
and n.nid != 9423 
group by tn.nid 
order by countOfTnNid 
desc limit 5

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

I found the solution on this SO page, and 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:

$ mysql -sN -u root -p

The -sN options get rid of all of the header and formatting information that MySQL typically puts around query results.

Next, this is the MySQL/MariaDB query you want to use:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('column1', 'column2')
AND TABLE_SCHEMA='your_database_name';

Example results and output

For example, I was just looking for a column named langcode in all Drupal 8 database tables, so I used this query:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('langcode')
AND TABLE_SCHEMA='drupal8';

This returned a very long list of tables. I’ll show only the first five tables, which are listed like this by the MySQL client:

block_content
block_content__body
block_content_field_data
block_content_field_revision
block_content_revision

As mentioned, the -sN options get rid of all the usual MySQL output formatting, so those are the entire results. If you don’t use those options, your output will look like this instead:

+-----------------------------------+
| TABLE_NAME                        |
+-----------------------------------+
| block_content                     |
| block_content__body               |
| block_content_field_data          |
| block_content_field_revision      |
| block_content_revision            |

Summary

In summary, if you want to know how to find one or more column names that are contained in all of your MySQL database tables, I hope you find this helpful.

For some reason when I use SQL I can never remember how to search for database table fields that are either NULL or NOT NULL. I always try to use the = operator or something else.

So, as a note to myself, here’s an example of how to perform a SQL query and find all records where a field in a database table is NULL:

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.

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.)

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

MySQL FAQ: How do I show the schema of a MySQL database table?

Answer: Use the desc command from the MySQL command line client.

For instance, in my current application I have a database table named orders, and when I need to see the schema for that table I show it with the desc command like this:

desc orders

The MySQL output looks like this:

MySQL vacuum FAQ: How do I vacuum a MySQL database table, or an entire MySQL database?

This may be an unusual situation, but as I'm creating a new application that uses a MySQL database, I've found that I want a "MySQL vacuum" operation; I want to reset all my MySQL auto_increment counters back to 1 without having to completely drop and then re-create my MySQL database.

Specifically what I'm doing is this:

MySQL FAQ: How can I drop a series of database tables that have foreign key relationships between them?

If you ever have a problem where you need to drop a series of MySQL database tables that have foreign key relationships between them, the key to doing this is setting the FOREIGN_KEY_CHECKS variable before and after your MySQL DROP TABLE queries.

For example, something like this should work:

MySQL “DROP TABLE” FAQ: Help, my MySQL database tables have a lot of foreign keys, and as a result it's a pain to use the MySQL DROP TABLE command in my scripts; they keep failing because of all the foreign keys. Is there something I can do to work around this DROP TABLE foreign keys problem?

MySQL FAQ: How do I create a MySQL database table index?

Here's an example of how to create an index on a MySQL database table. In this particular example my table uses the MySQL InnoDB database engine, but that doesn't matter too much -- it just happens to be a MySQL database engine that supports foreign key relationships.

To get started, here's the SQL definition of a MySQL table I've named ftp_files:

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: