MySQL basic, tricks and techniques - mhulse/mhulse.github.io GitHub Wiki

Difference between a MySQL procedure and a function

PROCEDURES may or may not return a value but FUNCTION must return a value.
PROCEDURES can have input/output parameter but FUNCTION only has input parameter.
We can call FUNCTION from PROCEDURES but cannot call PROCEDURES from a function.
We cannot use PROCEDURES in SQL statement like SELECT, INSERT, UPDATE, DELETE, MERGE etc. but we can use them with FUNCTION.
We can use try-catch exception handling in PROCEDURES but we cannot do that in FUNCTION.
We can use transaction in PROCEDURES but it is not possible in FUNCTION.

Enter into mysql prompt

In this case, root is the user:

$ mysql -u root -p
# Probably no password if using root, so just hit return.

Show which database is currently in use

Use SELECT DATABASE();. The default database when logging in to the MySQL shell is NULL.

Example:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use mysql

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

Clone and backup a database

# Use the `--no-data` flag if you just want the table structure.
$ mysqldump -u root -p my_database -r /home/mhulse/my_database.sql

Open MySQL shell mysql -u root -p and:

> CREATE DATABASE my_database_copy;
> USE my_database_copy;
> SOURCE /home/mhulse/my_database.20190716.sql;

Give an existing user access to the database:

> GRANT ALL PRIVILEGES ON my_database_copy.* TO 'my_user'@'localhost';
> FLUSH PRIVILEGES;

… or, create a new user:

> CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'some_password';
> GRANT ALL PRIVILEGES ON my_database_copy.* TO 'new_my_user'@'localhost';
> FLUSH PRIVILEGES;

Temp backup table

Using a temporary store of the old data in case your attempted update/insert business fails/introduces a bug:

# Creates a backup table with the same schema:
> create table session_content_backup LIKE session_content
# Copy the existing data over to the new backup table:
> insert into session_content_backup SELECT * FROM session_content
# Deletes the existing data, while preserving data in the backup:
> truncate session_content
#
# DO YOUR UPDATE/INSERT here!
#
# Clean-up time:
> drop table session_content_backup

Track all the connections to a DB

Here’s an example of the query I was using to track all the connections to a DB:
SELECT id, User, Host, Command 
FROM information_schema.processlist 
WHERE user='dbuser';
You could always do:
SELECT *
FROM information_schema.processlist;
or:
SHOW PROCESSLIST;
… if you just want to see everything.

– h/t @PatrickRBailey

Dumping and importing from/to MySQL in an UTF-8 safe way

In a nutshell: to avoid your shell character set from messing with imports, use -r to export and SOURCE when importing.

Explain

In MySQL you can prepend a query with explain and it shows you how the optimizer will handle the query; so if you’re doing a lot of joins, it will tell you if it’s using an index, or how many rows it needs to deal with.

Data display

# Display column:value on single row:
> select * from table_name\G
# Shows detailed information regarding table structure (field + value types):
> desc table_name
# Shows the sql needed to create a given table:
> show create table table_name

Copy SQL from one server to other

$ scp ./output/2018-05-29-foo.sql <user>@111.11.111.11:/var/www/project/2018-05-29-foo.sql

Table to CSV

$ 'mysql -h 111.11.111.11 -u <user> -p<password> -D <database_name>' -BAq < foo.sql > ./output/2018-05-29-foo.sql

Dump and load sql

# Dump:
$ mysqldump -u root db_name > dump.sql
# Load:
$ mysql -u root < dump.sql

Note: -p = prompt for password (default, so this option may be omitted). For no prompt, you can include the password like using -p<password> (no space between the -p and <!) This also works: --password=pass_val.

Import SQL

USE dbname;
SET autocommit=0;
source dbname.sql;
COMMIT;

List all databases

SHOW DATABASES;

Create a database

CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# If database already exists, and you forgot to set the character set and collation:
ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

List mysql users

SELECT User FROM mysql.user;

Drop user(s)

DROP USER IF EXISTS user;
# Drop multiple users:
DROP USER IF EXISTS user1, user2, user3;

Create a user

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
# Note: user@% would allow access from all locations.
# user@localhost would only allow access from localhost.

User permissions

# User permission for specific database:
GRANT SELECT, INSERT, DELETE ON dbname.* TO 'user'@'localhost';

# Another example:
GRANT
CREATE TEMPORARY TABLES,
DELETE,
EXECUTE,
GRANT OPTION,
INSERT,
LOCK TABLES,
SELECT,
SHOW VIEW,
UPDATE
ON foo_assessment_tool.* TO 'foo_user' @'localhost';

# Another:
GRANT ALL PRIVILEGES ON foo_assessment_tool.* TO 'foo_user'@'%';

# When finished with permission changes:
FLUSH PRIVILEGES;

# View grants:
SHOW GRANTS FOR 'user'@'localhost';

List tables in database

USE my_database;
SHOW TABLES;

Drop/delete a database

DROP DATABASE IF EXISTS my_database;