MySQL basic, tricks and techniques

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;

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

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

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.

Misc.

Enter into mysql prompt using (in this case, root is the user):

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

Useful commands (enter into $ mysql command prompt):

# List databases:
> SHOW DATABASES;

# List mysql users:
> SELECT User FROM mysql.user;

# Drop a user:
> 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. 

# 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;

# 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';

# Import SQL:
> USE dbname;
> SET autocommit=0; source dbname.sql; COMMIT;

# List tables in database:
> USE dbname;
> SHOW TABLES;

# Drop/delete a database:
> DROP DATABASE IF EXISTS family_talk;