Mysql 一主多备安装部署文档

最新推荐文章于 2022-10-16 22:26:08 发布

大师兄你家猴跑啦

最新推荐文章于 2022-10-16 22:26:08 发布

阅读量279

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/jeffiny/article/details/114526910

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文档详细介绍了如何进行MySQL的一主多备安装部署，包括主节点和从节点的配置步骤，如my.cnf修改、同步账号创建与授权、数据同步测试等，并针对错误情况`Slave_IO_Running:Connecting`进行了原因分析和解决方案，确保了主备数据的实时同步。

摘要由CSDN通过智能技术生成

Mysql 一主多备安装部署文档

1 主节点配置

1.1 my.cnf 配置

server-id=1
log-bin=mysql-bin(主节点有，备份不用设置)
lower_case_table_names=1

1.2 配置同步账号

CREATE USER 'eversec'@'%' IDENTIFIED BY 'EverSec99813!@#';

mysql> CREATE USER 'eversec'@'%' IDENTIFIED BY 'EverSec99813!@#';
Query OK, 0 rows affected (0.00 sec)
mysql>

1.3 授权同步账号

grant replication slave on *.* to 'eversec'@'%' identified by 'EverSec99813!@#';

+-------------------------------------------------+
| Grants for eversec@%                            |
+-------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'eversec'@'%' |
+-------------------------------------------------+

1.4 授权远程登录

grant replication slave on *.* to 'eversec'@'%' identified by 'EverSec99813!@#';

1.5 刷新

flush privileges;

1.6 查看Master状态

show master status \G

结果如下：

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql>

2 Slave节点配置

2.1 my.cnf 配置

server-id=2 （注意：每个节点的id需要唯一！）
lower_case_table_names=1

2.2 设置Master

CHANGE MASTER TO MASTER_HOST='192.168.91.11',MASTER_USER='eversec',MASTER_PASSWORD='EverSec99813!@#',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

说明：

MASTER_HOST='192.168.91.11',  <----主节点IP；
MASTER_USER='eversec',          <----同步数据用户；
MASTER_PASSWORD='EverSec99813!@#', <----同步数据用户密码；
MASTER_LOG_FILE='mysql-bin.000001',     <----同步数据binglog文件，master状态里的 File: mysql-bin.000001
MASTER_LOG_POS=154; <----同步数据binglog位置，master状态里边的Position: 154

执行结果如下：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.91.11',MASTER_USER='eversec',MASTER_PASSWORD='EverSec99813!@#',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.22 sec)

2.3 查看slave状态

 show slave status \G

执行结果如下：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.91.11
                  Master_User: eversec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 890
               Relay_Log_File: dmp10-relay-bin.000002
                Relay_Log_Pos: 1056
        Relay_Master_Log_File: mysql-bin.000001
             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: 890
              Relay_Log_Space: 1263
              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: eba44650-7fb6-11eb-87e4-0cc47ae22d70
             Master_Info_File: /data1/mysql_bb_data/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)

备注：
如下指标如果都yes则算添加Slave成功！！
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3 数据同步测试

3.1 测试数据


CREATE TABLE test_tb (id int ,name VARCHAR(100) NOT NULL);

insert into test_tb(id,name) values(360 ,"hello kitty");

3.2 Master数据

主节点查看结果：

mysql> select * from  test_tb;
+------+-------------+
| id   | name        |
+------+-------------+
|  360 | hello kitty |
+------+-------------+
1 row in set (0.00 sec)

3.2 Slave1同步数据

备份节点1查看结果：

mysql> select * from test_tb;
+------+-------------+
| id   | name        |
+------+-------------+
|  360 | hello kitty |
+------+-------------+
1 row in set (0.00 sec)

3.3 Slave2 同步数据

备份节点2同步结果：

mysql> select * from test.test_tb;
+------+-------------+
| id   | name        |
+------+-------------+
|  360 | hello kitty |
+------+-------------+
1 row in set (0.00 sec)

3.4 结论

集群符合主备备的设计，能够正常的同步主节点数据；

4 错误集锦

4.1 Slave_IO_Running: Connecting

1、错误日志

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting to reconnect after a failed registration on master
                  Master_Host: 192.168.91.11
                  Master_User: eversec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: dmp10-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            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: 154
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1597
                Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'eversec'@'%' (using password: YES) (Errno: 1045)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: eba44650-7fb6-11eb-87e4-0cc47ae22d70
             Master_Info_File: /data1/mysql_bb_data/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: 210308 10:47:07
     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.01 sec)

2、从日志中可以明显的感觉到权限不够！

Master command COM_REGISTER_SLAVE failed: Access denied for user 'eversec'@'%' (using password: YES) (Errno: 1045)

3、测试授权的问题:

mysql> show grants for 'eversec'@'%';
+-------------------------------------+
| Grants for eversec@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'eversec'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)

4、正确的授权

mysql> show grants for 'eversec'@'%';
+-------------------------------------------------+
| Grants for eversec@%                            |
+-------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'eversec'@'%' |
+-------------------------------------------------+
1 row in set (0.00 sec)

5、解决措施

grant replication slave on *.* to 'eversec'@'%' identified by 'EverSec99813!@#';

注意密码不能错！

大师兄你家猴跑啦

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

Mysql 一主多备安装部署文档

Mysql 一主多备安装部署文档

文章目录

1 主节点配置

1.1 my.cnf 配置

1.2 配置同步账号

1.3 授权同步账号

1.4 授权远程登录

1.5 刷新

1.6 查看Master状态

2 Slave节点配置

2.1 my.cnf 配置

2.2 设置Master

2.3 查看slave状态

3 数据同步测试

3.1 测试数据

3.2 Master数据

3.2 Slave1同步数据

3.3 Slave2 同步数据

3.4 结论

4 错误集锦

4.1 Slave_IO_Running: Connecting