Skip to main content

Commands


Config Path

mysql --help | grep -B 1 etc/my.cnf
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf

mysqldump

With DB Creation

User --databases

mysqldump -hlocalhost -uroot -P3306 -pcmajor --databases foo > db-2025-05-09-17-51.sql

Safe Backup

mysqldump -h <host> -u <user> -p'<password>' \
  --single-transaction \
  --master-data=2 \
  --quick \
  --databases foo > foo.sql

Docker

Pull Image

https://hub.docker.com/_/mysql/tags

docker pull mysql:8.0.36

Run Container

docker run -d \
  --name mysql-1 \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123123 \
  -v ~/data1:/var/lib/mysql \
  mysql:8.0.36
docker run -d \
  --name mysql-foo \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123123 \
  -v ~/mysql-foo/data:/var/lib/mysql \
  -v ~/mysql-foo/conf:/etc/mysql/conf.d \
  mysql:8.0.36

Use 127.0.0.1 instead of localhost.

mysql -h127.0.0.1 -P3306 -p123123

Create my.cnf in ~/mysql-foo/conf and mount it to /etc/mysql/conf.d. It will keep /etc/my.cnf default config and overwrite specific properties. For example:

~/mysql-foo/conf/my.cnf
[mysqld]
enforce_gtid_consistency = ON
gtid_mode = ON

Disk Stats

Database Size

Specify DB
SELECT 
    table_schema AS `Database`, 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size_MB`
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
GROUP BY table_schema;
All DB
SELECT 
    table_schema AS `Database`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size_MB`
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

Table Size

SELECT 
    table_name AS `Table`,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Size_MB`
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;