当前位置：Gxlcms > 数据库问题 > mysql5.7.26做主从复制配置

mysql5.7.26做主从复制配置

时间：2021-07-01 10:21:17 帮助过：4人阅读

repl‘@‘%‘ identified by ‘Zaq1xsw@‘; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql><span style="color: #000000;"> use mysql;
Reading table information </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> completion of table and column names
You can turn off this feature to get a quicker startup with </span>-<span style="color: #000000;">A
Database changed
mysql</span>> <span style="color: #0000ff;">select</span><span style="color: #000000;"> user,host from user;
</span>+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+
<span style="color: #800080;">4</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

三、Master、Slave上分别设置不同的Server_id，主库上开启二进制日志

mysql> show variables like <span style="color: #800000;">‘</span><span style="color: #800000;">%server_id%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
</span>+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | <span style="color: #800080;">0</span>     |
| server_id_bits | <span style="color: #800080;">32</span>    |
+----------------+-------+

如果配置文件没有设置server_id参数，则默认都是0

编辑/etc/my.cnf

添加service_id,它的值可以跟服务器的IP最后一位数字一样，这样就能保证内网中的服务器ID不重复。master上

server_id=<span style="color: #800080;">103</span><span style="color: #000000;">
log</span>-bin=<span style="color: #000000;">master
binlog_format</span>=row

slave上

server_id=<span style="color: #800080;">69</span>

四、将主库做一次全量备份，并恢复到从库上

在主库上操作

mysql><span style="color: #000000;"> show databases;
</span>+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

主库有一个test数据库

mysql><span style="color: #000000;"> show tables;
</span>+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

有一个test表

mysql> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test;
</span>+------+------+
| <span style="color: #0000ff;">id</span>   | name |
+------+------+
|    <span style="color: #800080;">1</span> | aaaa |
+------+------+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

表里有一条数据，开始备份主库

[root@node2 data]# mysqldump -uroot -p --all-databases > /home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/bak/bak.sql<br>[root@node2 data]# scp -P25601 /home/mysql-5.7.26/bak/bak.sql root@172.28.18.69:/home/mysql-5.7.26/bak/<br><br>在从库上操作，恢复主库数据

[root@localhost log]# mysql -uroot -p < /home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/bak/<span style="color: #000000;">bak.sql 
Enter password: 
[root@localhost log]# mysql </span>-uroot -<span style="color: #000000;">p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection </span><span style="color: #0000ff;">id</span> is <span style="color: #800080;">4</span><span style="color: #000000;">
Server version: </span><span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span><span style="color: #000000;"> MySQL Community Server (GPL)
Copyright (c) </span><span style="color: #800080;">2000</span>, <span style="color: #800080;">2019</span>, Oracle and/<span style="color: #000000;">or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and</span>/<span style="color: #000000;">or its
affiliates. Other names may be trademarks of their respective
owners.
Type </span><span style="color: #800000;">‘</span><span style="color: #800000;">help;</span><span style="color: #800000;">‘</span> or <span style="color: #800000;">‘</span><span style="color: #800000;">\h</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">for</span> help. Type <span style="color: #800000;">‘</span><span style="color: #800000;">\c</span><span style="color: #800000;">‘</span> to <span style="color: #0000ff;">clear</span><span style="color: #000000;"> the current input statement.
mysql</span>><span style="color: #000000;"> show databases;
</span>+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
mysql</span>>

此时，从库里test数据库有了，里面也有test数据表里记录

mysql><span style="color: #000000;"> show databases;
</span>+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
<span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
mysql</span>><span style="color: #000000;"> use test;
Reading table information </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> completion of table and column names
You can turn off this feature to get a quicker startup with </span>-<span style="color: #000000;">A
Database changed
mysql</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test
    </span>-><span style="color: #000000;"> ;
</span>+------+------+
| <span style="color: #0000ff;">id</span>   | name |
+------+------+
|    <span style="color: #800080;">1</span> | aaaa |
+------+------+
<span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.01</span> sec)

五、在slave上change msater 操作配置主从复制

首先获取主库日志文件名称和偏移量

mysql><span style="color: #000000;"> show master status \G;
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
             File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
         Position: </span><span style="color: #800080;">154</span><span style="color: #000000;">
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
</span><span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
ERROR: 
No query specified
mysql</span>>

在从库上执行

mysql><span style="color: #000000;"> change master to 
    </span>-> master_host=<span style="color: #800000;">‘</span><span style="color: #800000;">172.28.18.103</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
    </span>-> master_port=<span style="color: #800080;">3306</span><span style="color: #000000;">,
    </span>-> master_user=<span style="color: #800000;">‘</span><span style="color: #800000;">repl</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
    </span>-> master_password=<span style="color: #800000;">‘</span><span style="color: #800000;">Zaq1xsw@</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
    </span>-> master_log_file=<span style="color: #800000;">‘</span><span style="color: #800000;">master.000001</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
    </span>-> master_log_pos=<span style="color: #800080;">154</span><span style="color: #000000;">;
Query OK, </span><span style="color: #800080;">0</span> rows affected, <span style="color: #800080;">2</span> warnings (<span style="color: #800080;">0.21</span> sec)

启动从库

mysql><span style="color: #000000;"> start slave;
Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.01</span> sec)

查看从库状态

mysql><span style="color: #000000;"> show slave status\G;
</span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
               Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
                  Master_Host: </span><span style="color: #800080;">172.28</span>.<span style="color: #800080;">18.103</span><span style="color: #000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
                Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
              Master_Log_File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
          Read_Master_Log_Pos: </span><span style="color: #800080;">154</span><span style="color: #000000;">
               Relay_Log_File: localhost</span>-relay-bin.<span style="color: #800080;">000002</span><span style="color: #000000;">
                Relay_Log_Pos: </span><span style="color: #800080;">317</span><span style="color: #000000;">
        Relay_Master_Log_File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
             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: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                   Last_Error: 
                 Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
          Exec_Master_Log_Pos: </span><span style="color: #800080;">154</span><span style="color: #000000;">
              Relay_Log_Space: </span><span style="color: #800080;">528</span><span style="color: #000000;">
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: </span><span style="color: #800080;">0</span><span style="color: #000000;">
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
                Last_IO_Error: 
               Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: </span><span style="color: #800080;">103</span><span style="color: #000000;">
                  Master_UUID: ddbee8c3</span>-76da-11e9-<span style="color: #800080;">9174</span>-<span style="color: #000000;">90b11c15be09
             Master_Info_File: </span>/home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/data/master.<span style="color: #0000ff;">info</span>

此时:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主从复制配置已经生效

六、测试数据

在主库插入一条数据

mysql> insert test values(<span style="color: #800080;">2</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">bbbb</span><span style="color: #800000;">‘</span><span style="color: #000000;">);
Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.03</span> sec)

从库上查询

mysql> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test;
</span>+------+------+
| <span style="color: #0000ff;">id</span>   | name |
+------+------+
|    <span style="color: #800080;">1</span> | aaaa |
|    <span style="color: #800080;">2</span> | bbbb |
+------+------+
<span style="color: #800080;">2</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

数据已经复制成功了。

mysql5.7.26做主从复制配置

标签：variable aaa base 不同的查看 ref 也有 etc hang

mysql5.7.26做主从复制配置

三、Master、Slave上分别设置不同的Server_id，主库上开启二进制日志

四、将主库做一次全量备份，并恢复到从库上

五、在slave上change msater 操作配置主从复制

六、测试数据

人气教程排行