mysql主主备份

Justin_DZ

已于 2023-11-23 11:35:09 修改

阅读量524

点赞数 1

分类专栏：数据库系列文章标签： mysql 数据库 java

于 2022-07-06 13:15:29 首次发布

本文链接：https://blog.csdn.net/weixin_43804233/article/details/125183475

版权

数据库系列专栏收录该内容

10 篇文章 0 订阅

订阅专栏

引用：https://blog.csdn.net/weixin_43640848/article/details/115264786
主从宕机修复方案参考：https://blog.51cto.com/u_14402/7593630
https://blog.51cto.com/u_16213686/7623789

在这里插入图片描述

同步原理1
当Master有数据更新的时候，会按照binlog 的格式，将更新的事件类型写入master的binlog文件中，创建binlog dump thread通知slave说master库中有数据更新，此时slave接收到通知之后，会创建I/O thread来请求master，master会返回binlog文件的副本以及数据更新的位置，slave收到binlog副本文件后，将文件保存在relay log中（中继日志）中，中继日志也是记录日志更新的信息的，随后sql thread在slave中创建，将更新的内容同步到slave数据库中，这样就保证了主从的数据同步。

同步的原理2
MySQL服务的主从架构一般都是通过binlog日志文件来进行。即在主服务上打开binlog记录每一步的数据库操作，然后从服务上会有一个IO线程，负责跟主服务建立一个TCP连接，请求主服务将binlog传输过来。这时，主库上会有一个IO dump线程，负责通过这个TCP连接把binlog日志传输给从库的IO线程。接着从服务的IO线程会把读取到的binlog日志数据写入自己的relay日志文件中。然后从服务上另外一个线程（sql thread）会读取relay日志里的内容，进行操作重演，达到还原数据的目的

修改my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
init-connect='SET NAMES utf8'
basedir=/usr/local/mysql              #根据自己的安装目录填写 
datadir=/usr/local/mysql/data     #根据自己的mysql数据目录填写
socket=/tmp/mysql.sock
max_connections=200             # 允许最大连接数
character-set-server=utf8           # 服务端使用的字符集默认为8比特编码的latin1字符集
default-storage-engine=INNODB           # 创建新表时将使用的默认存储引擎
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-error=/usr/local/mysql/data/error.log  #输出告警日志
lower_case_table_names=1    #1表示不区分大小写 0表示默认区分


#MySQL主主配置

server-id=1   #服务器id (主主之间的id必须不一样)
log-bin=mysql-bin   #打开日志(主机需要打开)
binlog_format=mixed    # binlog日志格式，mysql默认采用statement，建议使用mixed
relay-log=relay-bin    #定义relay_log的名称
relay-log-index=slave-relay-bin.index    #定义relay_log的位置和名称

#在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。
auto-increment-offset=1     #表示自增长字段从那个数开始，他的取值范围是1 .. 65535
auto-increment-increment=2    #表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
expire_logs_days=7    #自动清理 7 天前的log文件,可根据需要修改
slave-skip-errors=all  #跳过所有错误


#mysql调优配置：

 
slow_query_log = on
long_query_time = 2 # 慢查询日志,慢查询设置为2秒，超过2秒的查询会被写入慢查询日志
slow-query-log-file = /home/mysql/data/slow.log
expire_logs_days=5
lower_case_table_names=1 #1表示不区分大小写 0表示默认区分
#开启mysql的查询缓存
#query_cache_type = 1
performance_schema_accounts_size = 100
performance_schema_hosts_size = 100
performance_schema_max_cond_instances = 6900
performance_schema_max_file_classes = 50
performance_schema_max_mutex_instances = 21000
performance_schema_max_rwlock_instances = 10800
performance_schema_max_table_handles = 4000
performance_schema_max_thread_instances = 2100
performance_schema_setup_actors_size = 100
performance_schema_setup_objects_size = 100
performance_schema_users_size = 100
max_seeks_for_key = 4294967295
max_write_lock_count = 4294967295
myisam_max_sort_file_size = 2146435072
key_buffer_size = 16k
max_allowed_packet = 1M
thread_stack = 512k
sort_buffer_size = 64k
net_buffer_length = 2k
performance_schema_max_table_instances = 12500
table_open_cache_instances = 1
eq_range_index_dive_limit = 10
sync_binlog = 0
innodb_buffer_pool_size = 8M
innodb_log_buffer_size = 1M
performance_schema = off








#以下是集群配置
#[mysqld]
#default-storage-engine=ndbcluster
#character_set_server=utf8
#lower_case_table_names=1
#basedir=/usr/local/mysql-cluster
#datadir=/usr/local/mysql-cluster/data
#ndbcluster
#ndb-connectstring = 192.168.6.129
#user=root
#[mysql_cluster]
#ndb-connectstring=192.168.6.126
#ndb-connectstring = 192.168.6.129

登录其中一个主1，创建一个账号

mysql> CREATE USER 'backup'@'%' IDENTIFIED BY 'backup'; #创建任意ip可登录数据库的root账号和密码
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' WITH GRANT OPTION;#授予权限
mysql> ALTER USER 'backup'@'%' IDENTIFIED WITH mysql_native_password BY 'backup'; #重置密码
mysql> flush privileges;刷新

紧接着，通过show master status查看状态信息，用于其他主或者从的mysql来监听操作

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      369 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)

登录另一个主2

先确保slave状态是停止的，先执行stop slave；

mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

紧接着关联到主1

mysql> change master to master_host='192.168.6.126',master_user='backup',master_password='backup',master_log_file='mysql-bin.000008',MASTER_LOG_POS=369;

启动slave
mysql> start slave;

查看状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.126
                  Master_User: dong
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 1776
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 1776
              Relay_Log_Space: 527
              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: c1180ba8-bb34-11ec-bdda-000c29f864f8
             Master_Info_File: mysql.slave_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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

主主是即当主又当从
192.168.6.126是127的主但又是127的从
192.168.6.127是126的主但又是126的从

报错经验

显示报错：
Last_IO_Error: error connecting to master 'repl@127.0.0.1:3316' - retry-time: 60 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决方案：
在从库上执行 show slave status\G
考虑到我的MySQL8 ，
查看主库：
SELECT plugin FROM `user` where user = 'repl';
原来是主库repl的plugin是caching_sha2_password 导致连接不上，修改为mysql_native_password即可解决。
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

Justin_DZ

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql主主备份

登录其中一个主1，创建一个账号紧接着，通过show master status查看状态信息，用于其他主或者从的mysql来监听操作登录另一个主2先确保slave状态是停止的，先执行stop slave；紧接着关联到主1启动slave主主是即当主又当从192.168.6.126是127的主但又是127的从192.168.6.127是126的主但又是126的从报错经验...
复制链接

扫一扫