Linux
Docker
Docker 运行 Mysql

Docker 运行 Mysql

使用 docker 运行 mysql 

1
2
3

docker run --name mariadb -p 3306:3306 \
-v /data/docker/data/mysql1:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root -itd mariadb

如果一定要使用 mysql 

1
2
3

docker run --name mysql1 -p 3306:3306 \
-v /data/docker/data/mysql1:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root -itd mysql

mysql 数据导出 

mysqldump -h 127.0.0.1 mysql --single-transaction -uroot -p > /root/mysql.sql

如果有 lock 权限，你可以删除这里的 –single-transaction

mysql 创建用户并授权 

grant all privileges on goods.* to 'goods'@'%' identified by 'goods';

mysql 查看磁盘空间占用

查看每个库的大小:

1
2
3
4
5
6
7
8

SELECT
  TABLE_SCHEMA                                             AS DatabaseName,
  concat (ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2), 'MB')  AS DataSize,
  concat (ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2), 'MB') AS IndexSize
FROM
  `information_schema`.`TABLES`
GROUP BY
  TABLE_SCHEMA

查看指定库的大小:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  TABLE_NAME                                               AS `TableName`,
  concat (ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2), 'MB')  AS DataSize,
  concat (ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2), 'MB') AS IndexSize
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = 'loong'
GROUP BY
  TABLE_NAME

将这里的 loong 改为你需要的数据库名称即可;

mysql 备份脚本 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

#!/bin/bash
time=$(date "+%Y-%m-%d")
fullPath="/opt/backup/full_"$time
hostFullPath="/opt/data/backup/mariadb-backup/full_"$time
if [ ! -d "$hostFullPath" ]; then
  echo 'full path not exists make full backup'
  docker exec mariadb /usr/bin/mariadb-backup --backup --target-dir=$fullPath --user=root --password=root
else
  echo 'full path exists make incremental backup'
  time2=$(date "+%Y-%m-%d_%H_%M_%S")
  incrementalPath=/opt/backup/incremental_$time2
  hostIncrementalPath=/opt/data/backup/mariadb-backup/incremental_$time2
  if [ ! -d "$hostIncrementalPath" ]; then
    docker exec mariadb /usr/bin/mariadb-backup --backup --target-dir=$incrementalPath --incremental-basedir=$fullPath --user=root --password=root
  else
    echo 'backup exists ignore.' 
  fi
fi

删除指定日期前创建的文件 

1
2
3

#!/bin/bash
cd /opt/data/backup/mariadb-backup
find ./ -type d -ctime +10 | xargs rm -rf
最后更新于
Docker 运行 Redis