MySQL主从复制

baby@*

已于 2023-05-29 16:48:41 修改

阅读量297

点赞数 1

文章标签： mysql 数据库服务器

于 2023-05-29 16:34:43 首次发布

本文链接：https://blog.csdn.net/qq_64010732/article/details/130930819

版权

MySQL主从复制

文章目录

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的二进制日志是事务安全型的。

baby@*

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL主从复制

主从复制是指将主数据库的DDL和 DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行(也叫重做），从而使得从库和主库的数据保持同步。MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制MySQL 复制的有点主要包含以下三个方面1.主库出现问题，可以快速切换到从库提供服务。2.实现读写分离，降低主库的访问压力。3.可以在从库中执行备份，以避免备份期间影响主库服务。
复制链接

扫一扫