MySQL DBA Mastery: Administration & Performance Guide

1. Create a Database and MySQL User

CREATE DATABASE appdb;

CREATE USER 'appdbuser'@'localhost' IDENTIFIED BY 'password@strong';
CREATE USER 'appdbuser'@'%' IDENTIFIED BY 'password@strong';

GRANT ALL PRIVILEGES ON appdb.* TO 'appdbuser'@'localhost';
GRANT ALL PRIVILEGES ON appdb.* TO 'appdbuser'@'%';

FLUSH PRIVILEGES;

Note: appdb.* means all tables inside the appdb database.

More Secure Remote Access

CREATE USER 'appdbuser'@'192.168.1.%' IDENTIFIED BY 'password@strong';

GRANT ALL PRIVILEGES ON appdb.* TO 'appdbuser'@'192.168.1.%';

FLUSH PRIVILEGES;

See Allowed Host list for the specific User

SELECT user,host from mysql.user where user ='appdbuser';

2. Create a Read-Only MySQL User

CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'NewStrongPassword!';

GRANT SELECT, SHOW VIEW ON appdb.* TO 'readonly_user'@'%';

FLUSH PRIVILEGES;

3. Create Local Read-Only User

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'NewStrongPassword!';

GRANT SELECT, SHOW VIEW ON appdb.* TO 'readonly_user'@'localhost';

FLUSH PRIVILEGES;

Important: ON .* is invalid syntax.

4. Change MySQL User Password

SELECT user, host
FROM mysql.user
WHERE user = 'appdbuser';

ALTER USER 'appdbuser'@'localhost'
IDENTIFIED BY 'NewStrongPassword!';

ALTER USER 'appdbuser'@'192.168.1.1'
IDENTIFIED BY 'NewStrongPassword!';

FLUSH PRIVILEGES;

5. Avoid Unsafe Root Access

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Avoid this in production environments.

6. MySQL SSL Configuration

ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

require_secure_transport = ON

7. Require or Remove SSL for a User

ALTER USER 'appdbuser'@'192.168.1.1'
REQUIRE SSL;

FLUSH PRIVILEGES;

ALTER USER 'appdbuser'@'192.168.1.1'
REQUIRE NONE;

FLUSH PRIVILEGES;

8. Dump Database

mysqldump --single-transaction --quick --lock-tables=false \
  --ignore-table=appdb.temp_table \
  appdb > appdb.sql

mysqldump --single-transaction --quick --lock-tables=false \
  appdb > appdb.sql

9. Import Database

CREATE DATABASE newdb;

mysql -u root -p -A newdb < appdb.sql

10. Transfer Backup to Another Server

scp appdb.sql root@192.168.1.3:/root/dbback/

11. Check Total Database Size

SELECT 
  table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Size (GB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

12. Check Largest Tables

SELECT 
  table_schema AS 'DB Name',
  table_name AS 'Table Name',
  ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Table Size (GB)'
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN (
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
  )
ORDER BY data_length + index_length DESC
LIMIT 10;

13. Check MySQL User Grants

SHOW GRANTS FOR 'appdbuser'@'%';

SHOW GRANTS FOR 'appdbuser'@'localhost';

GRANT PROCESS ON *.* TO 'appdbuser'@'localhost';

FLUSH PRIVILEGES;

14. Query Debugging and Optimization

EXPLAIN
SELECT *
FROM users
WHERE user_id = '1000'
  AND ststud = 1
  AND deleted_at IS NULL
ORDER BY id DESC
LIMIT 1;

SHOW CREATE TABLE users\G

15. Useful MySQL Administration Commands

SELECT user, host FROM mysql.user;

SHOW PROCESSLIST;

KILL 12345;

SELECT VERSION();

SELECT DATABASE();

16. Security Recommendations

• Use strong passwords.

• Avoid remote root access.

• Restrict database access by IP whenever possible.

• Enable SSL for production environments.

• Keep regular backups.

• Monitor slow queries regularly.