Learn › MySQL › Priya scales the database and locks it down

MySQL Ch 12 / 18 Advanced

🏛️

Priya scales the database and locks it down

Users and grants, read replicas, backups, my.cnf tuning — production-ready MySQL

⏱ 14 min 6 commands 5 takeaways

🏛️

In this chapter

Priya

DBA, scaling and securing the database

The story

Priya's company grew. The database had 5 million users and 20 million orders. The single MySQL server started struggling. She needed to set up a replica.

She also set up proper user accounts because the app was still connecting as root -- a serious security risk.

MYSQL USERS AND SECURITY

The app connecting as root is like giving a cashier the master key to the building. Create specific users with only the permissions they need.

-- Create a user for the application:
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongP@ssword123';
-- '%' means from any host. Use '10.0.0.%' to restrict to a subnet.

-- Grant only what the app needs:
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

-- Grant stored procedure execute permission:
GRANT EXECUTE ON myapp_db.* TO 'appuser'@'%';

-- Create a read-only user for reports and analytics:
CREATE USER 'reporter'@'%' IDENTIFIED BY 'ReportP@ss123';
GRANT SELECT ON myapp_db.* TO 'reporter'@'%';
FLUSH PRIVILEGES;

-- See what permissions a user has:
SHOW GRANTS FOR 'appuser'@'%';

-- Revoke a permission:
REVOKE DELETE ON myapp_db.* FROM 'appuser'@'%';

-- Remove a user:
DROP USER 'appuser'@'%';

MYSQL REPLICATION -- READ REPLICA

Replication keeps a second MySQL server (replica) in sync with the primary. Use cases:

- Read scaling: direct SELECT queries to the replica, writes to the primary

- High availability: if primary fails, promote the replica

- Backups: take backups from the replica so primary is not affected

On the PRIMARY server (my.cnf):

[mysqld]
server-id = 1
log_bin   = /var/log/mysql/mysql-bin.log
binlog_format = ROW

sudo systemctl restart mysql

-- Create replication user on primary:
CREATE USER 'replicator'@'%' IDENTIFIED BY 'ReplicaP@ss';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

-- Get the binary log position:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Note: File and Position values
UNLOCK TABLES;

On the REPLICA server (my.cnf):

[mysqld]
server-id = 2

-- Take a dump from primary and restore on replica first, then:
CHANGE MASTER TO
    MASTER_HOST = '10.0.0.1',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'ReplicaP@ss',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS  = 154;

START SLAVE;
SHOW SLAVE STATUS\G

-- Key fields to check:
Slave_IO_Running:  Yes   -- replica is connected to primary
Slave_SQL_Running: Yes   -- replica is applying changes
Seconds_Behind_Master: 0 -- how far behind (should be 0 or near 0)

BACKING UP MYSQL

-- Full backup with mysqldump:
mysqldump -u root -p myapp_db > backup_$(date +%Y%m%d).sql

-- Compressed:
mysqldump -u root -p myapp_db | gzip > backup_$(date +%Y%m%d).sql.gz

-- Backup all databases:
mysqldump -u root -p --all-databases > all_$(date +%Y%m%d).sql

-- Backup with stored procedures and triggers:
mysqldump -u root -p --routines --triggers myapp_db > full_backup.sql

-- Restore:
mysql -u root -p myapp_db < backup_20260316.sql

-- Automated nightly backup in cron:
0 2 * * * mysqldump -u root -pYOURPASSWORD myapp_db | gzip > /backups/myapp_$(date +\%Y\%m\%d).sql.gz

IMPORTANT MYSQL CONFIGURATION

-- Find the config file:
mysql --help | grep 'Default options'
-- Usually: /etc/mysql/mysql.conf.d/mysqld.cnf

Key settings in my.cnf [mysqld]:
max_connections       = 200    -- max simultaneous connections
innodb_buffer_pool_size = 4G   -- set to 70-80% of RAM for dedicated DB server
slow_query_log        = 1      -- enable slow query logging
long_query_time       = 1      -- log queries over 1 second
max_allowed_packet    = 64M    -- max size of a query or row

-- See current values:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';    -- how many are currently connected

Priya set up the replica in one afternoon. She moved all analytics queries to it. The primary became noticeably faster. She also locked down the app to use appuser instead of root -- the team now panics slightly less at 2am.

Key takeaways

Never let your app connect as root — create a dedicated appuser with only SELECT, INSERT, UPDATE, DELETE grants

Create a reporter user with SELECT-only access for analytics — reports should never modify data

MySQL replication: primary writes to binlog, replica reads and applies it — Seconds_Behind_Master should stay near 0

mysqldump --routines --triggers backs up stored procedures and triggers too — a dump without these is incomplete

innodb_buffer_pool_size should be 70-80% of total RAM on a dedicated database server — most important performance setting

Commands from this chapter

$ CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPass'; GRANT SELECT,INSERT,UPDATE,DELETE ON myapp_db.* TO 'appuser'@'%'; FLUSH PRIVILEGES;

Create app user with limited permissions

$ SHOW GRANTS FOR 'appuser'@'%';

See all grants for a user

$ SHOW SLAVE STATUS\G

Check replica connection, lag, and whether it is running

$ mysqldump -u root -p --routines --triggers myapp_db | gzip > backup_$(date +%Y%m%d).sql.gz

Full backup including procedures and triggers

$ SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Check current buffer pool size

$ SHOW STATUS LIKE 'Threads_connected';

How many connections are currently active