MySQL主从复制
文章目录
1.概述
主从复制是指将主数据库的DDL和 DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状复制
MySQL 复制的有点主要包含以下三个方面
1.主库出现问题,可以快速切换到从库提供服务。
2.实现读写分离,降低主库的访问压力。
3.可以在从库中执行备份,以避免备份期间影响主库服务
2.原理
1.Master 主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中
2.从库读取主库的二进制日志文件 Binlog ,写入到从库的中继日志 Relay Log。
3.slave重做中继日志中的事件,将改变反映它自己的数据
3.MySql主从搭建
ubuntu安装MySQL主从集群
一、配置依赖环境
1、1创建相关目录并安装MySQL
# 安装MySQL
apt update
apt install mysql-server -y
# 创建数据目录
mkdir /data/mysql/ -p
# 创建日志目录
mkdir -p /data/logs/mysql/
二、安装数据库
2、1 配置文件规范化
修改数据目录、日志目录,新增默认数据引擎,字符集编码、缓存池内存等设置
修改 server-id 从库需与主库不同,保持id唯一性。建议:取ip最后一位
vim /etc/mysql/mysql.conf.d/mysqld.cnf
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 13306
datadir = /data/mysql
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
mysqlx = 0 (MySQL5.7.12 之后新增了X plugin,添加mysqlx=0关闭X plugin)
#mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
#key_buffer_size = 16M
# max_allowed_packet = 64M
# thread_stack = 256K
# thread_cache_size = -1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam-recover-options = BACKUP
max_connections = 1000 (max_connections通常设为1000,如果并发量大访问量高可设为3000)
skip_name_resolve = 1 (禁用DNS主机名查找,启用以后用内网地址向mysqlslap请求响应快了一半)
# table_open_cache = 4000
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file = /var/log/mysql/query.log
# general_log = 1
#
# Error log - should be very few entries.
#
log_error = /data/logs/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log = 1 (是否开启慢查询日志收集)
slow_query_log_file = /data/logs/mysql/mysql-slow.log (慢查询日志位置)
long_query_time = 2 (设置记录慢查询超时时间)
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 6002
log_bin = /data/logs/mysql/mysql-bin.log
binlog_expire_logs_seconds = 691200
max_binlog_size = 500M
(必须为1到232–1之间的一个正整数值。并且,从服务器的ID必须与主服务器的ID不相同
log_bin:binlog的保存位置,不能指定确定的文件名如mysql-bin.log,只能指定位置和前缀,会生成以前缀为开头的一系列文件
mysql8 默认设置的binlog过期时间是30天;如下设置过期日期为7天:
bin log日志每达到设定大小后,会使用新的bin log日志。如mysql-bin.000002达到500M后,创建并使用mysql-bin.000003文件作为日志记录)
# binlog_do_db = include_database_name
# binlog_ignore_db = include_database_name
default-storage-engine=InnoDB
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
# read_only=1
#mysql服务ID,保证整个集群环境中唯一,取值范围: 1 - 232-1,默认为1server-id=1
#是否只读,1 代表只读,0 代表读写
read-only=0
#忽略的数据,指不需要同步的数据库
#binlog-ianore-db=mysal
#指定同步的数据库
#binlog-do-db=db01
innodb_buffer_pool_size=10G
缓存innodb表的索引,数据,插入数据时的缓冲,专用mysql服务器设置的大小: 操作系统内存的70%-80%最佳
innodb_buffer_pool_instances=8
可以开启多个内存缓冲池,把需要缓冲的数据hash到不同的缓冲池中,这样可以并行的内存读写
innodb_log_buffer_size=64M
事务在内存中的缓冲。 分配原 则:控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次
innodb_page_cleaners=8
为了提升扩展性和刷脏效率,在5.7.4版本里引入了多个page cleaner线程。从而达到并行刷脏的效果
在该版本中,Page cleaner并未和buffer pool绑定,其模型为一个协调线程 + 多个工作线程,协调线程本身也是工作线程。因此如果innodb_page_cleaners设置为8,那么就是一个协调线程,加7个工作线程
log_timestamps=SYSTEM
在MySQL 5.7.2 新增了 log_timestamps 这个参数,该参数主要是控制 error log、genera log,等等记录日志的显示时间参数。 在 5.7.2 之后改参数为默认 UTC 这样会导致日志中记录的时间比中国这边的慢,导致查看日志不方便。修改为 SYSTEM 就能解决问题
sort_buffer_size=1M
是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存
2、2修改数据目录
停掉数据库以后操作
systemctl stop mysql.service
cd /var/lib/mysql/
cp -rf * /data/mysql/
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /data/logs/mysql
systemctl start mysql.service
2、3 apparmor服务权限设置
# 修改安全保护文件
root@bugaoxing:~# vi /etc/apparmor.d/usr.sbin.mysqld
# 原文件内容 大约再60行
# 数据目录
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
# 日志目录
/var/lib/logs/mysql/mysql.err rw,
/var/lib/logs/mysql/mysql.log rw,
/var/lib/logs/mysql/ r,
/var/lib/logs/mysql/** rw,
# 修改为以下内容
# 数据目录
/data/mysql/ r,
/data/mysql/** rwk,
# 日志目录
/data/logs/mysql/mysql.err rw,
/data/logs/mysql/mysql.log rw,
/data/logs/mysql/ r,
/data/logs/mysql/** rw,
# 修改访问控制权限
root@bugaoxing:~# vim /etc/apparmor.d/abstractions/mysql
/var/lib/mysql{,d}/mysql{,d}.sock rw
# 修改为
/data/mysql{,d}/mysql{,d}.sock rw
# 重启服务生效
systemctl restart apparmor
三、配置主从:
3、1 创建管理员账号并授权
关于 mysql 密码策略相关参数;1)、validate_password_length 固定密码的总长度;2)、validate_password_dictionary_file 指定密码验证的文件路径;3)、validate_password_mixed_case_count 整个密码中至少要包含大/小写字母的总个数;4)、validate_password_number_count 整个密码中至少要包含阿拉伯数字的个数;5)、validate_password_policy 指定密码的强度验证等级,默认为 MEDIUM;关于 validate_password_policy 的取值:0/LOW:只验证长度;1/MEDIUM:验证长度、数字、大小写、特殊字符;2/STRONG:验证长度、数字、大小写、特殊字符、字典文件;
root@bugaoxing:~# mysql -u root -p
mysql> show variables like 'validate_password%'
# 修改root密码
alter user 'root'@'localhost' identified with mysql_native_password by 'your passwd';
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create user ‘test’@‘%’ identified by ‘12345’;
mysql> grant all privileges on testdb.* to ‘test’@‘%’ with grant option;
mysql> flush privileges;
# 配置远程用户访问
mysql> update user set host=‘%’ where user=‘test’;//给test用户设置可以访问地址为所有主机’%’
mysql> flush privileges; //到这就可以了
mysql> alter user ‘test’@‘%’ identified with mysql_native_password by ‘Flyscale_sw123’;//如果要修改用户密码,保证安全性
3、2 配置主从
master配置
mysql>
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1416 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
slave配置
mysql> CHANGE MASTER TO
MASTER_HOST='10.50.20.1',
MASTER_PORT=33061,
MASTER_USER='slave',
MASTER_PASSWORD='123451',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1034,
GET_MASTER_PUBLIC_KEY=1;
change master to master_host= '10.50.20.0', master_user='root', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=1034,GET_MASTER_PUBLIC_KEY=1;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.254.1.146
Master_User: slave
Master_Port: 33061
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1416
Relay_Log_File: ops-yunwei-mysql-254-1-147-relay-bin.000002
Relay_Log_Pos: 1632
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
指定master ip、port、user、user_password、bin-log、及GET_MASTER_PUBLIC_KEY=1;
如果复制用户的身份验证插件是:caching_sha2_password,则需要指定GET_MASTER_PUBLIC_KEY=1;
master_host 主服务器地址
master_port 主服务器端口号
master_user 主服务器用于复制的用户
master_password 主服务器用于复制的用户的密码
master_log_file 就是主服务器show master status; 中的 File
master_log_pos 就是主服务器show master status; 中的 Postion
master_connect_retry:如果连接失败,重试的时间间隔,单位是秒,默认是60秒
状态中Slave_IO_Running和Slave_SQL_Running都为"Yes"才表明配置成功。若有失败,查看mysql日志定位。
IO为Connecting :需要查看用户创建和授权是否正确
sql线程为no:需要查看file和pos是否和主库一致。
四、遇到的问题
问题1:安装完数据库初始化是必须将 /var/lib/mysql/的数据复制到自己规定的数据目录下,否则会出现用户无法登录情况
cp -rf /var/lib/mysql/* /data/mysql
chown -R mysql:mysql /data/mysql
问题2:Slave_IO_Running:Connecting 线程非ok状态
问题原因:
(1)网络不通
(2)防火墙端口未开放
(3)mysql账户密码错误
(4)mysql主从机配置文件写错
(5)配置从机连接语法错误
(6)主机未开放账户连接权限
mysql账户密码错误:**用"mysql -u用户名 -p密码 -h主机ip"尝试登录主机,笔者就是这个问题,提示因为连接失败次数太多,导致远程连接被拒
root@bugaoxing:~# mysqladmin -uroot -p123456 flush-hosts
再次从库服务器尝试远程登录,成功登录后可查看线程状态
####验证:
主从配置成功后,状态均为ok。
主库创建测试库测试表,在从库查看有没有新建的测试库及测试表,如果有 则ok,如果无,则需要根据日志具体排查。
binlog:
MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。