mysql 的主从复制

时间:2022-09-15 11:09:50

主服务器  (mysql-master)  47.74.132.151 已安装mysql (内无数据)

从服务器 (mysql-slave)  47.92.107.42  已安装mysql (内无数据)

一 开始配置 主服务器 47.74.132.151

1.配置之前先查看/etc/my.cnf

[mysqld]

log-bin=mysql-bin 启动二进制文件

server-id=1 服务器ID

重启mysql  (service mysqld start)

2.登录mysql,在mysql中添加一个backup的账号,并授权给从服务器。

mysql -uroot -p

mysql>grant replication slave on *.* to 'backup'@'192.168.48.130' identified by 'backup'; 创建backup用户,并授权给192.168.48.130使用。
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      261 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

二 开始配置从服务器 47.92.107.42

确保/etc/my.cnf中有log-bin=mysql-bin和server-id=1参数,并把server-id=1修改为server-id=10。修改之后如下所示:

[mysqld]

log-bin=mysql-bin 启动二进制文件

server-id=10 服务器ID

重启mysql  (service mysqld start)

mysql -uroot -p

change master to master_host='47.74.132.151',master_user='backup',master_password='backup',master_log_file='mysql-bin.000001',master_log_pos=261;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.74.132.151
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 261
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        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: 261
              Relay_Log_Space: 407
              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:
1 row in set (0.00 sec)

检查主从同步,如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常

三 开始验证

在主数据库上进行插入数据

mysql> create database mysqltest;

mysql> use mysqltest;

mysql> create table user(id int(5),name char(10));

mysql> insert into user values (00001,'zhangsan');

登录从数据库查看此数据是否存在

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysqltest          |
+--------------------+
3 rows in set (0.00 sec)

use mysqltest
mysql> select * from user;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |

复制成功

 

注释下自己配置的时候遇到的问题 Slave_IO_Running 一直为NO 自己的原因是通信不行 服务器安全组配置没有配置好导致的通信失败