Mysql 主从服务复制部署

一、mysql 同步复制有关概述一般数据库都是读取压力大于写数据压力主从复制即为了实现数据库的负载均衡和读写分离。通过将Mysql的某一台主机的数据复制到其它主机slaves上主服务器只负责写而从服务器只负责读。如生产环境中使用redis 数据库作为缓存数据库用户访问业务数据时先从缓存数据库查询如果缓存数据库没有再从业务数据库读取。架构图如下1.1 mysql支持的复制方式基于语句的复制--StatementStatement-Based Replication,SBR 在主服务器上执行的sql语句在从服务器上执行同样的语句。mysql默认采用基于语句的复制效率比较高。一旦发现没法精确复制时会自动选择基于行的复制每一条会修改数据的sql都会记录在 binlog 中。基于行的复制--RowRow-Based Replication,RBR把改变的内容复制过去仅保存那条记录被修改。而不是把命令在从服务器上执行一遍 从mysql5.0开始支持。混合类型的复制--MixedMixed-Based Replication,MBR: 默认采用基于语句的复制一旦发现基于语句的无法精确的复制时就会采用基于行的复制Statement 和 Row 的混合体。通过alter user root identified by ‘新密码’;1.2 mysql支持的复制类型1.2.1异步复制mysql数据库默认的复制方式异步复制指主库以异步的方式同步数据到一个从库或多个从库中。主节点不会主动推送数据到从节点主库在执行完客户端提交的事务后会立即将结果返给给客户端并不关心从库是否已经接收并处理。主节点如果掉了此时主节点上已经提交的事务可能并没有传到从节点上如果此时强行将从节点提升为主节点可能导致新主节点上的数据不完整1.2.2 同步复制同步复制是mysql主节点特有的复制方式当主库执行完一个事务然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端1.2.3 半同步复制介于异步复制和全同步复制之间主库在执行完客户端提交的事务后不是立刻返回给客户端而是等待至少一个从库接收到并写到relay log中才返回成功信息给客户端只能保证主库的Binlog至少传输到了一个从节点上否则需要等待直到超时时间然后切换成异步模式再提交1.2.4 异步复制 / 同步复制 / 半同步复制图1.3 mysql复制解决的问题1.4 mysql复制是如何工作的复制整体来说有3个步骤二、mysql 同步复制搭建Mysql服务器IPMaster主节点192.168.88.1Slave1(从节点)192.168.88.22.1 主节点配置#创建二进制日志存储路径 mkdir /var/log/mysql/mysql.binlog/ #授权如果/var/log/mysql 请先给这个目录授权 chown -R mysql:mysql /var/log/mysql/mysql.binlog2.2 添加my.cnf参数必须添加内容如下[mysqld] #二进制日志存储路径 log-bin/var/log/mysql/mysql.binlog #配置唯一的服务器ID一般使用IP最后一位 server-id1 #设置binlog格式 binlog-formatMIXED可选参数# 0表示读写 (主机)1表示只读(从机) read-only0 #设置日志文件保留的时长单位是秒 binlog_expire_logs_seconds6000 #控制单个二进制日志大小。此参数的最大和默认值是1GB max_binlog_size20 #设置不要复制的数据库 binlog-ignore-dbtest #设置需要复制的数据库,不写参数则默认全部记录可以填写多个 binlog-do-db需要复制的主数据库名字 例如: binlog-do-dbdbtest01 binlog-do-dbdbtest02 #设置binlog格式 binlog_formatSTATEMENT下面是常用的配置可以参考一下[mysqld] basedir/usr/local/mysql datadir/usr/local/mysql/data socket/usr/local/mysql/data/mysql.sock pid_file/usr/local/mysql/data/mysql.pid port3306 collation-server utf8mb4_general_ci character_set_serverutf8mb4 usermysql #主从配置 log-bin/var/log/mysql/mysql.binlog server-id1 binlog-formatMIXED #日志 log_error/usr/local/mysql/data/mysql_errol.log general1 general_log_file/usr/local/mysql/data/mysql_genaral.log slow_query_log1 slow_query_log_file/usr/local/mysql/data/mysql_slow_query.log #InnoDB innodb_buffer_pool_size 128M innodb_log_file_size 128M innodb_file_per_table1 innodb_flush_method O_DIRECT #其他配置 #临时表最大值 tmp_table_size 32M #内存堆表最大值 max_heap_table_size 32M #最大连接数理想数为占比服务器上限的10% max_connections 1700 #线程缓存大小 thread_cache_size 384 #表缓存大小可减少文件打开/关闭次数,一般max_connections*2。 table_open_cache 1024 #打开文件上线 open_files_limit 65535 [client] socket /usr/local/mysql/data/mysql.sock port 33062.3 检查是否生效#看看配置的id是否生效 mysql show variables like server_id; ---------------------- | Variable_name | Value | ---------------------- | server_id | 1 | ---------------------- 1 row in set (0.00 sec) #是否开启二进制日志 mysql show variables like log_bin; ---------------------- | Variable_name | Value | ---------------------- | log_bin | ON | ---------------------- 1 row in set (0.00 sec) #默认是OFF关闭状态启用后主从将无法通信 mysql show variables like %skip_networking%; ------------------------ | Variable_name | Value | ------------------------ | skip_networking | OFF | ------------------------ 1 row in set (0.00 sec)2.4 主节点创建主从复制账号#创建slave1用户 mysql create user slave1% identified by lmx123; Query OK, 0 rows affected (0.01 sec) #给slave1授权 mysql grant replication slave on *.* to slave1%; Query OK, 0 rows affected (0.00 sec) #使用mysql_native_password插件认证 mysql alter user slave1% identified with mysql_native_password by lmx123; Query OK, 0 rows affected (0.00 sec) #刷新 mysql flush privileges; Query OK, 0 rows affected (0.00 sec)2.5 查看主节点的二进制日志的名称其中File和Position两个参数需要在从库配置中使用mysql show master status; --------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | --------------------------------------------------------------------------- | mysql.000011 | 2388 | | | | --------------------------------------------------------------------------- 1 row in set (0.00 sec)2.6 从节点配置添加my.cnf参数[mysqld] #配置唯一的服务器ID一般使用IP最后一位 server-id2 #开启中继日志从主服务器上同步日志文件记录到本地 relay-logrelay-log-bin可选参数#定义中继日志文件的位置和名称 relay-log-indexslave-relay-bin.index2.7 在从节点配置需要复制的主机参数master_log_file 和 master_log_pos的值在步骤2.5中可以看到mysql change master to master_host192.168.88.1,master_userslave1, - master_passwordlmx123,master_log_filemysql.000011,master_log_pos2388; Query OK, 0 rows affected, 8 warnings (0.01 sec) #语法 change master to master_host主节点IP, master_user主节点用户名, master_password用户密码, master_log_filemysql-bin.具体数字, master_log_pos具体值;2.8 在从节点开启slave同步查看同步状态mysql start slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.88.1 Master_User: slave1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql.000011 Read_Master_Log_Pos: 2388 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes主要看这两行就行如果报错Slave failed to initialize relay_log info structure from the repository需要清理之前的relay_log,重新启用新的relay_log即可reset slave;三、测试主从同步mysql create database a; Query OK, 1 row affected (0.00 sec) #添加a1表 mysql create table a1(id int,name varchar(10),class varchar(10)); Query OK, 0 rows affected (0.01 sec) #插入数据 mysql insert into a1 values(1,喜羊羊,1班),(2,美羊羊,2班),(3,喜懒羊,3班); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0mysql show databases; -------------------- | Database | -------------------- | a | | information_schema | | mysql | | performance_schema | | sys | -------------------- 5 rows in set (0.00 sec) mysql use a; mysql select * from a1; ------------------------ | id | name | class | ------------------------ | 1 | 喜羊羊 | 1班 | | 2 | 美羊羊 | 2班 | | 3 | 喜懒羊 | 3班 | ------------------------ 3 rows in set (0.00 sec) #删除数据 mysql delete from a1 where id1; Query OK, 1 row affected (0.00 sec) #查看从库 mysql select * from a1; ------------------------ | id | name | class | ------------------------ | 2 | 美羊羊 | 2班 | | 3 | 喜懒羊 | 3班 | ------------------------ 2 rows in set (0.01 sec)四、主从同步关闭和开启#开启slave同步 mysql start slave; #关闭slave同步 mysql stop slave; #重启slave同步 mysql reset slave;五、部署主从常见的问题权限问题报错原因错误代码1130表示连接被拒绝通常是因为用户权限或网络配置问题。在你的情况下错误信息指出从库尝试连接到主库时被拒绝了原因是主机192.168.88.2没有被允许连接到主库的MySQL服务器。#请将replication_userslave_host更换为实际的用户名 SHOW GRANTS FOR replication_userslave_host; #如果权限有问题请在主库更改为以下权限 GRANT REPLICATION SLAVE ON *.* TO slave1%插件认证问题**报错原因**表示从库在尝试连接到主库时遇到了认证插件问题。具体来说它使用了caching_sha2_password认证插件但该插件要求使用安全连接即加密连接#请将slave1%更换为实际用户名 mysql alter user slave1% identified with mysql_native_password by lmx123;IO线程问题报错原因无法在运行的从库 I/O 线程上执行此操作#停止特定的将channel_name更换成实际的名称 STOP SLAVE IO_THREAD FOR CHANNEL channel_name; #如果只有一个默认的复制通道可以省略 FOR CHANNEL 部分 STOP SLAVE IO_THREAD;数据分布 (Data distribution )—提高数据操作自然并行度以达到最优的执行效率的目的负载平衡(load balancing)—主服务器只负责写而从服务器只负责读备份(Backups)—灾难恢复对损坏的数据进行恢复和还原高可用性和容错行( High availability and failover)—确保 mysql 数据库在故障和异常情况下仍然能够提供可靠的服务master将改变记录到二进制日志(binary log)中这些记录叫做二进制日志事件binary log eventsslave将master的binary log events拷贝到它的中继日志(relay log)slave重做中继日志中的事件将改变反映它自己的数据Slave_IO_RunningIO线程负责与主机的io通信Slave_SQL_RunningSQL线程责自己的slave MySQL进程主节点添加a库从库查看同步数据检查用户权限使用插件认证停止线程