[TOC]

MySQL主从复制

主机(window)修改my.ini配置文件 (当window作为主机)

# The TCP/IP Port the MySQL Server will listen on
port=3306
# 主服务器唯一ID
server-id=1
# 启用二进制日志
log-bin="E:\mysql\log\mysql-bin"
# Error Logging.
log-error="E:\mysql\error\mysql-err"
# 主机可读可写
read-only=0
# 设置不需要复制的数据库
binlog-ignore-db=mysql
# 设置需要复制的数据库
binlog-do-db=xxx

主机(centos)修改my.cnf配置文件(当centos作为主机)

vim /etc/my.cnf
[mysqld]
# 开启二进制日志
log-bin=master-bin
# 设置server-id
server-id=1

从机(centos)修改my.cnf配置文件

1	vim /etc/my.cnf

1
2
3

# Linux默认开启二进制日志文件 /var/lib/mysql/binlog
Binary logging captures changes between backups and is enabled by
default. It's default setting is log_bin=binlog

[mysqld]
# 启用从服务器唯一ID
server-id=2
# 开启中继日志
relay-log=mysql-relay
# 开启二进制日志
log-bin=slave-bi

基于docker搭建从机

docker run --name MYSQL   
    -v $PWD/conf:/etc/mysql/my.cnf   # $PWD代表当前路径
    -v $PWD/logs:/logs 			     # -v 挂载
    -v $PWD/data:/var/lib/mysql 
    -e MYSQL_ROOT_PASSWORD=123456    # 初始化msql密码
    -d -i -p 3307:3306 mysql         # 后台启动 linux下3307端口映射docker容器中mysql默认的3306端口

# 进入mysql容器中
docker exec -it mysql容器ID或名称 /bin/bash   
# 在docker容器内部安装vim
apt-get update
apt-get install vim
# 或者可以直接在容器外部挂载文件中修改

# 容器内部修改配置文件
vim /etc/mysql/my.cof
[mysqld]
# 开启二进制日志
log-bin=slave-bin
# 设置server-id
server-id=2

1 2	# 重启docker容器 docker restart 容器ID

主机和从机重启后台mysql服务

1 2	net stop mysql80; net start mysql80;

1	systemctl restart mysqld;

主机和从机都关闭防火墙

1	systemctl stop firewalld;

在Window主机上建立账户并授权slave

# mysql8之前的写法
GRANT REPLICATION SLAVE ON *.*  TO '从库主机用户名'@'从库主机IP' IDENTIFIED BY '从库主机密码';
# mysql8之后的写法(必须先创建用户再s)
# 创建用户(在master上创建只允许slave主机连接的账户)
CREATE USER 'slave'@'slave的ip地址' IDENTIFIED BY 'slave的密码';
# 分配权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'slave的ip地址';

1 2	# 刷新权限 flush privileges;

1 2	# 查看主库状态 show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

在Linux从机上配置需要复制的主机

# mysql命令行
CHANGE MASTER TO MASTER_HOST = '主库主机IP',
MASTER_USER = 'slave',
# master上slave用户的密码
MASTER_PASSWORD = 'slave的密码',
# File 加引号
MASTER_LOG_FILE = 'mysql-bin.000001',
# Position 不加引号
MASTER_LOG_POS = 155;
CHANGE MASTER TO MASTER_HOST = '120.27.195.153', MASTER_USER = 'slave',MASTER_PASSWORD = '990712', MASTER_LOG_FILE = 'master-bin.000010', MASTER_LOG_POS = 1067;

1 2	# 启动从服务器复制功能 start slave;

1 2	# 查看从库状态 show slave status \G;

 # 主从配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 若出现以下错误: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
ALTER USER 'slave'@'salve的ip' IDENTIFIED WITH mysql_native_password BY 'slave的密码';
(新用户会默认使用新的 caching_sha2_password 加密方式, 客户端不支持新的加密方式, 修改用户的密码和加密方式)

1 2	# 查看中继日志是否开启 show global variables like '%log%';

1 2	# 查看节点的server id show global variables like '%server%';