Updated: 10 May 2025
Check the status of the mysql service
sudo systemctl status mysql
Restart the mysql service
sudo systemctl stop mysql.service
sudo systemctl start mysql.service
Freelance software engineer United Kingdom
Updated: 10 May 2025
Check the status of the mysql service
sudo systemctl status mysql
Restart the mysql service
sudo systemctl stop mysql.service
sudo systemctl start mysql.service
Updated: 06 July 2024
Dynamically create a MySQL table which accommodates a CSV file, then import that CSV file.
#!/bin/bash
# create-table.sql
# CREATE TABLE `csv` (
# `id` int(11) NOT NULL AUTO_INCREMENT,
# PRIMARY KEY (`id`)
# );
mysql -u user -ppassword < create-table.sql
# Read column headers into an array.
IFS=',' read -ra COLS <<< $(head -n1 file.csv)
# Loop through the array and create a
# new table column for each csv column.
for COL in "${COLS[@]}"; do
mysql -u user -ppassword -e "ALTER TABLE csv ADD \`$COL\` TEXT DEFAULT NULL;"
done
# load-csv-into-table.sql
# LOAD DATA LOCAL INFILE 'file.csv'
# INTO TABLE `csv`
# FIELDS TERMINATED BY ',' ENCLOSED BY '"'
# LINES TERMINATED BY '\n'
# IGNORE 1 ROWS;
mysql -u user -ppassword < load-csv-into-table.sql
Updated: 11 April 2024
Insert into table the result of a select query
INSERT INTO orders (col1, col2, col3)
SELECT foo, bar, baz
FROM our_table
WHERE condition;
Updated: 11 April 2024
The ANY keyword means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.”
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query
SELECT * FROM t1
WHERE column1 = ANY (
SELECT column1 FROM t2
WHERE t2.column2 = t1.column2
);
Updated: 22 February 2024
Run mysql inside docker
docker run -p 3306:3306 --name our-mysql -e MYSQL_ROOT_PASSWORD=hopeless mysql:latest
Updated: 11 April 2024
Unexpected results can occur if mysql mode is not set. On a fresh cloud9 workspace (for example) mysql server has no mode settings
mysql> SELECT @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
| |
+------------+
Consequently truncation and attempts to insert the wrong types can pass without error
mysql> CREATE TABLE TEST (boolfield TINYINT(1), varcharfield VARCHAR(3));
mysql> INSERT INTO TEST VALUES ('A', 'wxyz');
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> SELECT * FROM TEST;
+-----------+--------------+
| boolfield | varcharfield |
+-----------+--------------+
| 0 | wxy |
+-----------+--------------+
Two warnings are issued but the query does run ok and results in an unintended boolfield value and a truncation.
Now set (one of many possible) sql modes
mysql> SET @@SQL_MODE = 'STRICT_TRANS_TABLES';
mysql> INSERT INTO TEST (boolfield) VALUES ('A');
ERROR 1366 (HY000): Incorrect integer value: 'A' for column 'boolfield' at row 1
and
mysql> INSERT INTO TEST (varcharfield) VALUES ('wxyz');
ERROR 1406 (22001): Data too long for column 'varcharfield' at row 1
Updated: 14 October 2024
This is how to enable the log table and query it directly with SQL
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
SELECT * FROM mysql.general_log ORDER BY event_time DESC;
Check the value of log_output
SELECT @@global.log_output;
Enable logging temporarily and write the log entries to a file
SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file='/var/log/mysql/mysql-all.log';
SET GLOBAL general_log = 1;
Updated: 26 January 2025
Install mysql client tools only (Ubuntu)
sudo apt-get install mysql-client
Run script on database, redirect output to log
mysql -u root -p db_name < sql_statement_file.sql >> query.log 2>&1
Run script on database, display results in a table
mysql --table -u the_user -pthe_user_pass -P 3307 -h 127.0.0.1 db_name < the-query.sql
Check if remote mysqld requires secure connections
mysql --ssl-mode=DISABLED -h example.com -u root -p
Execute a statement directly
mysql -u root -p db_name -e "SELECT * FROM tbl_foo LIMIT 5"
Run statements from inside a zipped SQL file
zcat /path/to/dump.sql.gz | mysql -u root -p the_database
Updated: 19 April 2023
Equivalent statements
LEFT JOIN LEFT OUTER JOIN
RIGHT JOIN RIGHT OUTER JOIN
FULL JOIN FULL OUTER JOIN
INNER JOIN JOIN
Updated: 13 November 2024
Create a remote user
CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password|caching_sha2_plugin BY 'password';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Change host for a user
UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;
Show the authentication plugin for each user
select user, plugin from mysql.user;