Posts in the “mysql” category
A MySQL database backup (mysqldump) shell script
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 example database design: A Nagios MySQL database design
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.
A MySQL “create table” syntax example
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 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:
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.
MySQL error: Expression of SELECT list is not in GROUP BY clause, nonaggregated column
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
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, 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.
How to perform a SQL query for fields that are null (or not null)
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 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.
How to search for a string in all fields of every table in a 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 backups: How to backup a MySQL database
MySQL database FAQ: How do I backup (back up) a MySQL database?
MySQL FAQ: How do I show the fields or schema of a database table?
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:
A MySQL Vacuum database tables script (auto_increment reset)
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:
A MySQL foreign keys “drop table” and re-create table example
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 and foreign keys tip - the FOREIGN_KEY_CHECKS variable
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?
A MySQL database table index example
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
:
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: