【MySQL】主从复制实战

这篇博客详细介绍了如何进行MySQL主从复制的实战操作,包括环境准备、主库和从库的配置,以及测试步骤。在环境准备阶段,设定了一个主库和两个从库。主库配置涉及server-id设置、bin_log开启、数据备份及上传。从库配置则需要关闭bin_log,还原备份数据,并设置主从同步。最后通过在主库创建表并插入数据,验证了从库是否成功同步。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

环境准备

两个机器:一主两从

  主库(MySQL Master):IP为192.168.74.140 port为3306
  从库(MySQL Slave ):IP为192.168.95.130 port为3307和3308(多实例安装MySQL)

 主库配置

1、设置server-id值并开启bin_log参数

[root@localhost ~]# vim /etc/my.cnf
[mysqld]                          //此标签下添加以下参数                     
log_bin = mysql-bin 
server_id = 120

2、重启数据库

[root@localhost ~]# systemctl restart mysqld

3、建立同步账号

mysql> grant replication slave on *.* to 'rep'@'192.168.74.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

4、锁表(设置为只读)

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

5、查看主数据库状态

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

6、备份数据库

   1)创建备份目录:

[root@localhost ~]# mkdir -p /server/backup/

   2)备份:

[root@localhost ~]# mysqldump -uroot -p -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
Enter password: 

   3)查看是否备份成功:

[root@localhost ~]# cd /server/backup/
[root@localhost backup]# ll
总用量 208
-rw-r--r--. 1 root root 210638 1月  25 17:29 mysql_bak.2021-01-25.sql.gz

7、解锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

8、主库备份数据上传到从库

[root@localhost backup]# scp /server/backup/mysql_bak.2021-01-25.sql.gz 192.168.74.144:/server/backup/
The authenticity of host '192.168.74.144 (192.168.74.144)' can't be established.
ECDSA key fingerprint is c6:5c:ff:b0:f6:52:31:61:1d:fe:28:30:71:01:44:fd.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.74.144' (ECDSA) to the list of known hosts.
root@192.168.74.144's password: 
mysql_bak.2021-01-25.sql.gz                                            100%  206KB 205.7KB/s   00:00    

 

从库上配置

1、设置server-id值并关闭bin_log参数

[root@mysql-bin ~]# vim /mysql/3307/my.cnf

[mysqld]                           //此标签下写入如下参数
server-id = 45

此处从库采用多实例安装MySQL,且配置文件分开创建,配置文件中未开启log_bin参数。

若只有一个从服务器,则关闭主配置文件bin_log参数(如#log_bin = /data/mysql/data/mysql-bin)

2、重启数据库

[root@mysql-bin ~]# systemctl restart mysqld

3、还原从主库备份的数据

   1)还原:

[root@mysql-bin ~]# zcat /server/backup/mysql_bak.2021-01-25.sql.gz | mysql -uroot -p'123abcABC!' -S /mysql/3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-bin ~]# zcat /server/backup/mysql_bak.2021-01-25.sql.gz | mysql -uroot -p'123abcABC!' -S /mysql/3308/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.

   2)查看是否还原:

[root@mysql-bin ~]# mysql -uroot -p -S /mysql/3307/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2_idx            |
| db3_fun            |
| mysql              |
| performance_schema |
| school             |
| stu                |
| sys                |
+--------------------+
9 rows in set (0.00 sec)
[root@mysql-bin ~]# mysql -uroot -p -S /mysql/3308/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2_idx            |
| db3_fun            |
| mysql              |
| performance_schema |
| school             |
| stu                |
| sys                |
+--------------------+
9 rows in set (0.00 sec)

 4、设定主从库同步(两个从库操作相同)

mysql> change master to
    -> master_host='192.168.74.140',
    -> master_port=3306,
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=447;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

5、启动从库同步开关

   1)启动开关:

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

   2)检查状态:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.74.140
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 447
               Relay_Log_File: mysql-bin-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes                  //确保I/O线程连接成功
            Slave_SQL_Running: Yes                  //确保SQL线程连接成功
              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: 447
              Relay_Log_Space: 531
              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: 40
                  Master_UUID: a234c25a-53f2-11eb-83bc-000c29190c65
             Master_Info_File: /mysql/3307/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)

 

测试

1、主库:school数据库中创建id表并插入数据记录

   1)创建表

mysql> create table id(
    -> x_id int(10));
Query OK, 0 rows affected (0.02 sec)

   2)插入数据记录

mysql> insert into id values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

   3)查看

mysql> select * from id;
+------+
| x_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

2、从库上查看是否同步(两个从库同步数据相同)

mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| id               |
| score            |
| student          |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from id;
+------+
| x_id |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

      测试完成!!!

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值