MySQL Operations: Backup, Replication, and High Availability

[mysqld]
# Basic settings
server-id = 1
port = 3306
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock

# InnoDB settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# Connection settings
max_connections = 200
wait_timeout = 600
interactive_timeout = 600

# Query cache (MySQL 8.0 removed this)
# query_cache_type = 1

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# Binary logging
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

-- Create user with authentication
CREATE USER 'appuser'@'localhost' 
    IDENTIFIED WITH caching_sha2_password BY 'secure_password';

CREATE USER 'appuser'@'%' 
    IDENTIFIED WITH caching_sha2_password BY 'secure_password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'localhost';

-- Grant all privileges
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';

-- Grant with grant option
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost' 
    WITH GRANT OPTION;

-- Role (MySQL 8.0+)
CREATE ROLE 'app_readonly';
GRANT SELECT ON mydb.* TO 'app_readonly';

CREATE ROLE 'app_readwrite';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_readwrite';

-- Assign role to user
GRANT 'app_readwrite' TO 'appuser'@'localhost';
SET DEFAULT ROLE 'app_readwrite' FOR 'appuser'@'localhost';

-- Revoke privileges
REVOKE INSERT, UPDATE ON mydb.* FROM 'appuser'@'localhost';

-- View user privileges
SHOW GRANTS FOR 'appuser'@'localhost';

# Dump single database
mysqldump -u root -p mydb > mydb.sql

# Dump with data only
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

# Dump schema only
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# Dump all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Dump specific tables
mysqldump -u root -p mydb users orders > tables.sql

# Dump with triggers and routines
mysqldump -u root -p --routines --triggers mydb > mydb_full.sql

# Compressed backup
mysqldump -u root -p mydb | gzip > mydb.sql.gz

# Restore
mysql -u root -p mydb < mydb.sql

# Restore to different database
mysql -u root -p newdb < mydb.sql

# Install Percona XtraBackup
# apt install percona-xtrabackup

# Full backup
xtrabackup --backup --target-dir=/backup/full --user=root --password=secret

# Prepare backup (for restore)
xtrabackup --prepare --target-dir=/backup/full

# Restore
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql

# Incremental backup
xtrabackup --backup --target-dir=/backup/inc1 
    --incremental-basedir=/backup/full --user=root --password=secret

# 1. Take base backup
mysqldump --single-transaction --master-data=2 -u root -p mydb > backup.sql

# 2. Note binlog position from backup
# -- MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345

# 3. Restore base backup
mysql -u root -p mydb < backup.sql

# 4. Apply binary logs to point in time
mysqlbinlog --stop-datetime="2026-03-05 10:00:00" \
    mysql-bin.000001 mysql-bin.000002 | mysql -u root -p mydb

# Master my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_do_db = mydb
binlog_ignore_db = mysql

# Slave my.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
read_only = 1

-- On Master: Create replication user
CREATE USER 'repl'@'%' IDENTIFIED WITH caching_sha2_password BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- Show master status
SHOW MASTER STATUS;

-- On Slave: Start replication
CHANGE MASTER TO
    MASTER_HOST='master.example.com',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=12345;

START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

-- Install plugin on master and slave
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- Enable on master
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Enable on slave
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Configure timeout
SET GLOBAL rpl_semi_sync_master_timeout = 10000;  -- 10 seconds

# my.cnf for Group Replication
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = 1

# Group Replication
plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "node1:33061"
group_replication_group_seeds = "node1:33061,node2:33061,node3:33061"
group_replication_bootstrap_group = OFF

-- Start Group Replication
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- Check group members
SELECT * FROM performance_schema.replication_group_members;

-- Install MySQL Shell
# mysqlsh

-- Bootstrap cluster
mysqlsh --uri root@localhost:3306
# dba.createCluster('myCluster')

-- Add instance to cluster
cluster.addInstance('root@node2:3306');
cluster.addInstance('root@node3:3306');

-- Check cluster status
cluster.status();

# ProxySQL configuration
[databases]
mydb = 
    host=127.0.0.1 
    port=3306 
    user=proxuser 
    pass=proxpass 
    db=mydb

[mysql]
default_hostgroup = 0

# Query rules
query_rules = 
    rule_id=1
    destination_hostgroup=0
    match_pattern=mydb
    active=1

# Connect to ProxySQL
mysql -h 127.0.0.1 -P 6032 -u admin -padmin

-- Add MySQL backend
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (0, '127.0.0.1', 3306);

-- Add users
INSERT INTO mysql_users(username, password, default_hostgroup) 
VALUES ('appuser', 'apppassword', 0);

-- Load to runtime
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;

-- Enable performance schema (enabled by default in 8.0)
-- View current connections
SELECT * FROM performance_schema.processlist;

-- View statement statistics
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- View table io statistics
SELECT * FROM performance_schema.table_io_waits_summary_by_table
ORDER BY sum_timer_wait DESC LIMIT 10;

-- View index statistics
SELECT * FROM performance_schema.index_statistics;

-- View user statistics
SELECT * FROM performance_schema.user_summary_by_statements;

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;

-- View slow queries
SELECT start_time, query_time, rows_sent, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC LIMIT 20;

-- Check connections
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Check query cache (MySQL 5.7)
SHOW STATUS LIKE 'Qcache%';

-- Check InnoDB status
SHOW ENGINE INNODB STATUS;

-- Check table statistics
SHOW TABLE STATUS FROM mydb;

-- Check index statistics
SHOW INDEX FROM users;

# MySQL Enterprise Monitor
# Or use open-source alternatives:
# - PMM (Percona Monitoring and Management)
# - Prometheus + Grafana

-- Update table statistics
ANALYZE TABLE users;
ANALYZE TABLE orders;

-- Check table integrity
CHECK TABLE users;

-- Optimize table (defragment)
OPTIMIZE TABLE users;

-- Repair MyISAM tables
REPAIR TABLE myisam_table;

[mysqld]
# Buffer settings
innodb_buffer_pool_size = 2G        # 70-80% of RAM
innodb_buffer_pool_instances = 8    # For large buffer pools

# Log settings
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1  # 1 = safe, 2 = faster

# Query settings
join_buffer_size = 256M
sort_buffer_size = 256M
read_buffer_size = 128M

# Thread settings
thread_cache_size = 50
table_open_cache = 4000

# Temp tables
tmp_table_size = 256M
max_heap_table_size = 256M

-- EXPLAIN analysis
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Force index
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 1;

-- Check execution plan
SHOW CREATE TABLE users;

-- Use strong authentication
ALTER USER 'root'@'localhost' 
    IDENTIFIED WITH caching_sha2_password BY 'very_strong_password';

-- Limit network access
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'password';

-- Use SSL/TLS
SHOW VARIABLES LIKE '%ssl%';

-- Enable audit logging
INSTALL PLUGIN audit_log SONAME 'audit_log.so';