MySQL 多种部署方式

# 查看 MySQL 版本信息
SELECT VERSION();

# 查看当前连接的用户和主机信息
SELECT USER(), CURRENT_USER(), @@hostname;

# 查看 MySQL 服务器启动时间
SHOW GLOBAL STATUS LIKE 'Uptime';

# 查看 MySQL 服务器当前运行的线程数
SHOW GLOBAL STATUS LIKE 'Threads_running';

# 查看 MySQL 数据库中当前可用的存储引擎
SHOW ENGINES;

# 查看 MySQL 数据库中当前正在运行的进程
SHOW PROCESSLIST;

# # 查看帐号列表
SELECT User, Host FROM mysql.user;

#### 方法一: 本地可访问
# 创建一个帐号和密码
CREATE USER 'deployer'@'localhost' IDENTIFIED BY 'new_password';

# 将数据库 Test 权限赋予给 deployer 帐号
GRANT ALL PRIVILEGES ON test.* TO 'deployer'@'localhost';

#### 方法一:任何连接可访问
CREATE USER 'deployer'@'%' IDENTIFIED BY 'new_password'; 
GRANT ALL PRIVILEGES ON test.* TO 'deployer'@'%'; 

# 刷新权限
FLUSH PRIVILEGES;

# 创建一个数据库
CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;

use mydb;

# 创建表
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id)
);
show tables;

# 插入数据
INSERT INTO users (name, age) VALUES
('Alice', 30),
('Bob', 25),
('Charlie', 35),
('Daisy', 28),
('Edward', 40);

select * from users;

# 不推荐空密码
docker run --name mysql -e ALLOW_EMPTY_PASSWORD=yes bitnami/mysql:8.0.36

# 推荐使用强密码
docker run -d --name mysql -e MYSQL_ROOT_PASSWORD="强密码" bitnami/mysql:8.0.36
docker run -d --name mysql -e MYSQL_ROOT_PASSWORD="BbtpmPOYQN52lp72" bitnami/mysql:8.0.36

mkdir -p /tmp/mysql-persistence

# 授权
chown -R 1001:root /tmp/mysql-persistence

# 查看权限
ls -ld /tmp/mysql-persistence

# 创建网络名称
docker network create app-tier --driver bridge

# 查看网络
docker network ls app-tier

# 运行容器
docker run -d \
    --name mysql-storage \
    --network app-tier \
    -e MYSQL_ROOT_PASSWORD="BbtpmPOYQN52lp72" \
    -v /tmp/mysql-persistence:/bitnami/mysql/data \
    bitnami/mysql:8.0.36

# 查看
docker logs mysql-storage

# 创建一个客户端连接
docker run -it --rm \
    --network app-tier \
    bitnami/mysql:8.0.36 mysql -h mysql-storage -u root -p

# 删除
rm -rf  /tmp/mysql-persistence/*
docker rm -f mysql-storage

version: '2'

networks:
  app-tier:
    driver: bridge

services:
  mysql:
    image: 'bitnami/mysql:8.0.36'
    environment:
      - MYSQL_ROOT_PASSWORD="BbtpmPOYQN52lp72"
    networks:
      - app-tier
  myapp:
    image: 'YOUR_APPLICATION_IMAGE'
    networks:
      - app-tier

docker run -d --name mysql-master \
  -e MYSQL_ROOT_PASSWORD=sxFCQH63JfF18xnw \
  -e MYSQL_REPLICATION_MODE=master \
  -e MYSQL_REPLICATION_USER=my_repl_user \
  -e MYSQL_REPLICATION_PASSWORD=lMklMMV448U8Y365 \
  -e MYSQL_USER=my_user \
  -e MYSQL_PASSWORD=RRSRhe32uucc00RV \
  -e MYSQL_DATABASE=my_poor \
  bitnami/mysql:8.0.36

docker run -d --name mysql-slave --link mysql-master:master \
  -e MYSQL_REPLICATION_MODE=slave \
  -e MYSQL_REPLICATION_USER=my_repl_user \
  -e MYSQL_REPLICATION_PASSWORD=lMklMMV448U8Y365 \
  -e MYSQL_MASTER_HOST=mysql-master \
  -e MYSQL_MASTER_ROOT_PASSWORD=sxFCQH63JfF18xnw \
  bitnami/mysql:8.0.36

cat > mysql-master-slave.yaml <<EOF
version: '3'

services:
  mysql-master:
    image: 'bitnami/mysql:5.7.43-debian-11-r73'
    ports:
      - '3306'
    volumes:
      - /path/to/mysql-persistence:/bitnami/mysql/data
    environment:
      - MYSQL_REPLICATION_MODE=master
      - MYSQL_REPLICATION_USER=repl_user
      - MYSQL_REPLICATION_PASSWORD=repl_password
      - MYSQL_ROOT_PASSWORD=master_root_password
      - MYSQL_USER=my_user
      - MYSQL_PASSWORD=my_password
      - MYSQL_DATABASE=my_database
  mysql-slave:
    image: 'bitnami/mysql:5.7.43-debian-11-r73'
    ports:
      - '3306'
    depends_on:
      - mysql-master
    environment:
      - MYSQL_REPLICATION_MODE=slave
      - MYSQL_REPLICATION_USER=repl_user
      - MYSQL_REPLICATION_PASSWORD=repl_password
      - MYSQL_MASTER_HOST=mysql-master
      - MYSQL_MASTER_PORT_NUMBER=3306
      - MYSQL_MASTER_ROOT_PASSWORD=master_root_password
EOF

# 启动服务：
docker-compose -f mysql-master-slave.yaml  up --detach --scale mysql-master=1 --scale mysql-slave=3

# 停止 
docker-compose -f mysql-master-slave.yaml down -v

# 查看主库的状态，获取当前binlog日志的文件名称和位置点
show master status;

-- 开始进行主从复制
start slave;

-- 查看主从复制的状态
show slave status \G;

mkdir data

chown -R 1000:1000 data

ls -lnd data

drwxr-xr-x 2 1000 1000 4096 Aug 27 15:54 data

docker run -v "$PWD/data":/var/lib/mysql --user 1000:1000 --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:5.7