db

mysql8主从设置

2021-03-21  本文已影响0人  virtual灬zzZ

使用两个机器,分别ip为
192.168.1.104(主)
192.168.1.105(从)

MySQL的主从复制,可以一个主库,n 个从库,从库的数据能够自动和主库的数据保持一致。主从复制的架构有两个好处,一个是进行数据自动的备份,数据在主库和从库上都有一份,如果主库挂了,从库还有一份完整的数据。另一个是可以实现数据库访问的读写分离,提高数据库的吞吐量。也就是对数据的更新操作是在主库中完成的,而读取操作在多个从库中完成的,从而实现了负荷分担的效果,提高数据库的访问效率。

MySQL 的主从复制是通过他的归档日志(binlog) 来实现的。基本的过程就是从库在一个线程中和主库建立一个长连接,告诉主库从主库同步的 binlog 的位置,然后主库将这个位置之后的 binlog 日志内容发送给从库,从库获取到主库发送过来的 binlog 内容后先写入到本地的中转日志(relaylog)中,然后从库另一个进程在从中转日志中读取内容并解析成为 sql 语句在从库中执行,从而保证了主从的同步。具体的架构如下图所示

image.png

步骤:

修改my.cnf文件

vi /etc/my.cnf (注意:server-id不能为0,两个节点的server-id配置必须不同)

[mysqld]
log-bin=mysql-bin
server-id=1

在主节点创建从节点用来同步的用户,并授权

mysql> CREATE USER 'syncer'@'192.168.1.104' IDENTIFIED WITH mysql_native_password BY 'sync_pwd';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'syncer'@'192.168.199.104';

mysql> flush privileges;

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

从节点

mysql>change master to master_host='192.168.1.104',master_port=3306,master_user='syncer',master_password='sync_pwd',master_log_file='mysql-bin.000001',master_log_pos=576;

查看主从同步状态
mysql> show slave status\G;

开启主从同步
mysql> start slave;

mysql> show variables like 'server_id';

再查看主从同步状态
mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.104
                  Master_User: syncer
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 7592
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 2361
        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: 7592
              Relay_Log_Space: 2574
              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: 307e53d7-8a03-11eb-884e-0800276e31f7
             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: 

Slave_IO_State 的值为 Waiting for master to send event ,表示已经准备好接受主库发送过来的归档日志进行处理了。

同步指定的数据库

上面的设置中,是将主库中的所有数据库都同步到从库中来了。实际上,我们还可以通过命令的方式同步指定的数据库或者数据库的数据表。在从库中执行下面的命令将会指定只同步数据库 test

mysql> stop slave;
Query OK, 0 rows affected (0.16 sec)
mysql> change replication filter replicate_do_db=(test);
Query OK, 0 rows affected (0.00 sec)
ysql> start slave;
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status\G;

这样,只有主库中 test 数据库中的变化会同步到从库中来,其余的数据库的变化则不会同步。

这个命令产生的效果没有保存下来,如果数据库重启了,那么就会失效。如果要生效就需要将配置写在 my.cnf 文件中。在从库中,将 my.cnf 文件内容更改为如下即可。

[mysqld]
replicate_do_db=test1

加入了新行 replicate_do_db=test1。然后重新启动数据库即可。

非空主库主从设置

很多时候,设置主从复制的时候,主库已经运行了一段时间,有了一些业务数据,那么这个时候我们首先将主库设置为只读状态,不允许新数据写入,然后查看当前的归档日志状态,记录下来后将数据库备份出来,恢复从库的可写状态,最后把备份出来的数据库恢复到从库中,再设置为从刚才查询出来的归档日志和归档日志的位置开始进行同步即可。

上一篇下一篇

猜你喜欢

热点阅读