07-31
0
mysql主从数据库 docker环境
1.配置主库
master.cnf
[mysqld]
# 同一局域网内注意要唯一
server-id=100
# 开启二进制日志功能，可以随便取
log-bin=mysql-bin查看二进制日志是否开启
SHOW GLOBAL VARIABLES LIKE '%log%';
查看主节点的server id
SHOW GLOBAL VARIABLES LIKE '%server%';
创建有复制权限的用户slave
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%' IDENTIFIED by '123456';
刷新配置
FLUSH PRIVILEGES;
查看File和Position字段的值
show master status;2.配置从库
slave.cnf
[mysqld]
server-id=101
#开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
#relay_log配置中继日志
relay_log=edu-mysql-relay-bin停止主从复制
stop slave;
设置主数据库信息
change master to master_host='172.17.0.5', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=856, master_connect_retry=30;
启动主从复制
start slave;
查看主从同步状态
show slave status;设置从库只读
# 开启只读
show global variables like "%read_only%";
# 对所有用户生效，包括super用户（不建议使用）
flush tables with read lock;
# 只对普通用户生效，如slave用户
set global read_only=1;
show global variables like "%read_only%";
# 关闭只读
unlock tables;
set global read_only=0;Docker配置
创建 /app/docker_files/mysql 文件夹
docker-compose.yml 配置文件 放在 /app/docker_files目录下
version: '3'
services:
db-master:
build: './mysql'
image: 'db-server:1.0'
container_name: 'db-master'
ports:
- "3366:3306"
volumes:
- "/app/docker_files/mysql/conf/master.cnf:/etc/mysql/conf.d/master.cnf"
- "/app/docker_files/mysql/data:/var/lib/mysql"
restart: always
environment:
MYSQL_ROOT_PASSWORD: qwe123!@#
command: ["mysqld", "--server-id=1", "--log-bin=mysql-bin"]
networks:
- lnmp
db-slave:
build: './mysql'
image: 'db-server:1.0'
container_name: 'db-slave'
ports:
- "3307:3306"
volumes:
- "/app/docker_files/mysql/conf/slave.cnf:/etc/mysql/conf.d/slave.cnf"
- "/app/docker_files/mysql/slave-data:/var/lib/mysql"
restart: always
environment:
MYSQL_ROOT_PASSWORD: qwe123!@#
command: ["mysqld", "--server-id=2"]
networks:
- lnmp
networks:
lnmp:
driver: bridge
03-20
0
mysql命令
常用代码
// 设置自增字段
alter table 数据表名 modify 字段名 int auto_increment;
alter table demo modify id int auto_increment;
// 修改自增 起始值
alter table 数据表名 auto_increment = 1001;
alter table demo auto_increment = 1000;linux终端
//登录mysql(有密码请输入密码)
mysql -u root -p
//打开数据库
show database;
//设置密码
set password = password('123456');
//退出
exit;
//使用 mysql 数据库
use mysql;
// 查询MySQL账户
select user,host from user;
//更改访问限制
update user set host=’%’where user=’用户名’@'本地账户';
//示例:
update user set host=’%’where user=’root’@'localhost';
//新增远程访问账号
grant all privileges on 数据库.数据表 to 用户名 @"%" identified by "密码";
//示例:
grant all privileges on *.* to remoteUser @"%" identified by "123456";
//更新配置
flush privileges;
//创建用户
CREATE USER '远程用户'@'%' IDENTIFIED BY '密码';
//示例:
CREATE USER 'remoteUser'@'%' IDENTIFIED BY '123456';
修改密码
ALTER user '用户名'@'本地账户'IDENTIFIED BY '密码';
//示例:
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
// 用户分配权限
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `数据库名称`.* TO '远程用户'@'%';
//示例:
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `mysql`.* TO 'remoteUser'@'%';
// 分配指定表
GRANT GRANT OPTION ON `数据库名称`.* TO '远程用户'@'%';
GRANT GRANT OPTION ON `mysql`.* TO 'remoteUser'@'%';