MySQL之间数据复制的基础是二进制日志文件(binary log file)。一台MySQL数据库一旦启用二进制日志后,其作为master,它的数据库中所有操作都会以“事件”的方式记录在二进制日志中,其他数据库作为slave通过一个I/O线程与主服务器保持通信,并监控master的二进制日志文件的变化,如果发现master二进制日志文件发生变化,则会把变化复制到自己的中继日志中,然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中,以此实现从数据库和主数据库的一致性,也就实现了主从复制。
实现MySQL主从复制需要进行的配置:
主服务器:
- 开启二进制日志
- 配置唯一的server-id
- 获得master二进制日志文件名及位置
- 创建一个用于slave和master通信的用户账号
从服务器:
- 配置唯一的server-id
- 使用master分配的用户账号读取master二进制日志
- 启用slave服务
一、Docker镜像
1、拉取mysql镜像
docker pull mysql:5.7
2、创建MySQL配置文件
[mysqld]
bind-address=0.0.0.0
skip_name_resolve
#server-id
#log-bin
character_set_server=utf8
character_set_filesystem=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
init_connect='SET collation_connection = utf8_general_ci'
skip-character-set-client-handshake
3、我这里采用的是docker-compose一次性创建三个mysql容器:
version: "3.6"
services: #服务
mysql-master:
image: mysql:5.7
container_name: mysql-master
networks:
redis-network: #网络名称
ipv4_address: 192.168.1.13 #设置ip地址
environment:
MYSQL_ROOT_PASSWORD: "123456"
stdin_open: true
tty: true
privileged: true
ports:
- "3307:3306"
volumes:
- /docker/cache/docker/mysql/master/conf/my.cnf:/etc/my.cnf
- /docker/cache/docker/mysql/master/data:/var/lib/mysql
mysql-slave-1:
image: mysql:5.7
container_name: mysql-slave-1
networks:
redis-network: #网络名称
ipv4_address: 192.168.1.14 #设置ip地址
environment:
MYSQL_ROOT_PASSWORD: "123456"
stdin_open: true
tty: true
privileged: true
ports:
- "3308:3306"
volumes:
- /docker/cache/docker/mysql/slave1/conf/my.cnf:/etc/my.cnf
- /docker/cache/docker/mysql/slave1/data:/var/lib/mysql
mysql-slave-2:
image: mysql:5.7
container_name: mysql-slave-2
networks:
redis-network: #网络名称
ipv4_address: 192.168.1.15 #设置ip地址
environment:
MYSQL_ROOT_PASSWORD: "123456"
stdin_open: true
tty: true
privileged: true
ports:
- "3309:3306"
volumes:
- /docker/cache/docker/mysql/slave2/conf/my.cnf:/etc/my.cnf
- /docker/cache/docker/mysql/slave2/data:/var/lib/mysql
#引用外部预先定义好的网段
networks:
redis-network:
external:
name: redis-network
准备服务器(docke容器):
角色 | ip | 操作系统 | MySQL版本 | 端口 | 复制账户 | 复制密码 |
主master | 192.168.1.13 | centos7 | mysql5.7 | 3307 | slave | slave |
从slave-1 | 192.168.1.14 | centos7 | mysql5.7 | 3308 | ... | .... |
从slave-2 | 192.168.1.15 | centos7 | mysql5.7 | 3309 | .... | .... |
操作步骤如下:
4、主服务器mysql配置文件中添加下面配置
[mysqld]
log-bin=mysqlbin
server-id=1
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
binlog-do-db=test
#log-bin #开启binlog日志
#这里的server-id用于标识唯一的数据库,在从库必须设置为不同的值。可以根据自己的ip来设置
#binlog-ignore-db:表示同步的时候忽略的数据库
#binlog-do-db:指定需要同步的数据库
5、在主数据库中创建一个用于主从复制的用户并授权
docker exec -it mysql-master bash #进入容器
mysql -uroot -p #登陆mysql
use mysql; #切换到mysql库
select user,host,plugin from user;
#创建用户并制定加密方式
create user 'slave'@'192.168.1.%' identified with mysql_native_password by 'slave';
#刷新权限
flush privileges;
#MySQL8.0之前版本:
GRANT ALL ON *.* TO `admin`@`127.0.0.1` IDENTIFIED BY 'binleen' WITH GRANT OPTION;
#MySQL8.0版本:
#创建账号密码
CREATE USER `root`@`127.0.0.1` IDENTIFIED BY 'passowrd';
#授予权限
GRANT ALL ON *.* TO `root`@`127.0.0.1` WITH GRANT OPTION;
#删除权限
REVOKE all privileges ON databasename.tablename FROM 'username'@'host';
#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
#修改密码的加密方式
#mysql8.0之后默认的密码加加密方式会是caching_sha2_password(且不兼容) ,8.0之前的加密方式是mysql_native_password,需要统一加密方式
alter user 'admin'@'%' identified with mysql_native_password by 'root';
6、查看是否开启binlog日志命令:
show variables like '%log_bin%';
或者使用以下命令进行配置:
docker exec mysql-master bash -c "echo 'log-bin=/var/lib/mysql/mysql-bin' >> /etc/mysql/mysql.conf.d/mysqld.cnf";
docker exec mysql-master bash -c "echo 'server-id=1' >> /etc/mysql/mysql.conf.d/mysqld.cnf";
7、赋予从库权限账号,允许用户在主库上读取日志,赋予192.168.1.13也就是Slave机器有File权限,只赋予Slave机器有File权限还不行,还要给它REPLICATION SLAVE的权限才可以。
grant FILE on *.* to 'slave'@'192.168.1.13' identified by 'slave';
grant replication slave on *.* to 'slave'@'192.168.1.13' identified by 'slave';
flush privileges;
这里的用户是同步的时候从库使用的用户。
8、重启mysql,登录mysql,查看主库信息
show master status;
如果该命令没数据,说明上面配置有误。
File是同步会使用到的binlog文件,Position是同步的时候也要用到的。
注意:用户密码安全策略
要全局建立自动密码到期策略,请使用default_password_lifetime系统变量。其默认值为0,禁用自动密码过期。如果值default_password_lifetime正整数N,则表示 允许的密码生存期,以便密码必须每天更改N。可以加在配置文件中:
1)要建立全局策略,密码的使用期限大约为六个月,请在服务器my.cnf文件中使用以下行启动服务器:
[mysqld]
default_password_lifetime=180
2)要建立全局策略,以便密码永不过期,请将其设置default_password_lifetime为0:
[mysqld]
default_password_lifetime=0
这个参数是可以动态设置并保存的:
SET PERSIST default_password_lifetime = 180;
SET PERSIST default_password_lifetime = 0;
创建和修改带有密码过期的用户,帐户特定的到期时间设置示例:
要求每90天更换密码:
CREATE USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
禁用密码过期:
CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE NEVER;
遵循全局到期政策:
CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT;
- MySQL用户密码重用策略设置
MySQL允许限制重复使用以前的密码。可以根据密码更改次数、已用时间或两者来建立重用限制。帐户的密码历史由过去分配的密码组成。MySQL可以限制从 此历史记录中选择新密码:
1). 如果根据密码更改次数限制帐户,则无法从指定数量的最新密码中选择新密码。例如,如果密码更改的最小数量设置为3,则新密码不能与任何最近的3个密 码相同。
2). 如果帐户因时间的限制而被限制,则无法从历史记录中的新密码中选择新密码,该新密码不会超过指定的天数。例如,如果密码重用间隔设置为60,则新密 码不得在最近60天内选择的密码之间。
注意:空密码不记录在密码历史记录中,并随时可以重复使用。 要全局建立密码重用策略,请使用password_history和password_reuse_interval系统变量。要在服务器启动时指定变量值,请在服务器my.cnf文件中定义它们。
示例: 要禁止重复使用最近6个密码或密码超过365天的任何密码,请将这些行放入您的服务器 my.cnf文件中:
[mysqld]
password_history=6password_reuse_interval=365
要动态设置和保存配置,请使用如下所示的语句:
SET PERSIST password_history = 6;
SET PERSIST password_reuse_interval = 365;
- MySQL8.0的角色管理
MySQL角色是指定的权限集合。像用户帐户一样,角色可以拥有授予和撤消的权限。可以授予用户帐户角色,授予该帐户与每个角色相关的权限。用户被授予角 色权限,则该用户拥有该角色的权限。
以下列表总结了MySQL提供的角色管理功能:
- CREATE ROLE并 DROP ROLE角色创建和删除;
- GRANT并 REVOKE为用户和角色分配和撤销权限;
- SHOW GRANTS 显示用户和角色的权限和角色分配;
- SET DEFAULT ROLE 指定哪些帐户角色默认处于活动状态;
- SET ROLE 更改当前会话中的活动角色。
- CURRENT_ROLE()功能显示当前会话中的活动角色。
6、从服务器配置
6.1从服务器配置文件文件开启binlog日志
[mysqld]
log-bin = mysql-bin
server-id = 2 #此处不能重复
或者使用以下命令输入进行配置:
docker exec mysql-slave-1 bash -c "echo 'log-bin=/var/lib/mysql/mysql-bin' >> /etc/mysql/mysql.conf.d/mysqld.cnf";
docker exec mysql-slave-1 bash -c "echo 'server-id=2' >> /etc/mysql/mysql.conf.d/mysqld.cnf";
6.2从服务器根据主服务器上的信息进行配置如下:
change master to master_host='192.168.1.13',master_port=3306,master_user='slave',master_password='slave',master_log_file='mysqlbin.000001',master_log_pos=154;
start salve;
#master_host:主服务器ip
#master_port: 主服务器端口
#master_user:主服务器用户名
#master_password:主服务器密码
#master_log_file:指定主服务器的binlog日志
#master_log_pos:目前主服务器日志的节点
如果从服务器上配置出错
mysql主从赋值,从机验证报错:This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
原因:
mysql从机上已经进行过绑定了,如果继续绑定需要先进行重置。
解决办法
1)、停止已经启动的绑定
stop slave;
2)、重置绑定(主服务器上操作)
reset master
3)、执行复制主机命令(从服务器上操作)
change master to master_host='192.168.1.13',master_user='slave',master_password ='slave',master_port=3306,master_log_file='mysqlbin.000001',master_log_pos=154;
4)、发现此时已经不报错
5)、启动复制
start slave;
6.3 执行show slave status;查看从库同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.13
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqlbin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 777
Relay_Master_Log_File: mysqlbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 610
Relay_Log_Space: 978
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: af4e07c4-8ddd-11ea-bf95-0242c0a8010d
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
对于我们来说其中的信息主要是关注
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
reset slave all 清楚slave信息 测试的方法就是在主服务器中,添加一些数据测试观察从服务其中的数据变化情况。