mysql主从搭建

# 1.原理

master的I/O线程将数据写入binlog中；
slave的I/O线程从master的binlog中读取数据，写入自己的Relay_Log_File日志中；
slave的SQL线程从Relay_Log_File日志中解析sql，完成数据的复制。

# 2.应用场景

从服务器作为主服务器的实时数据备份
主从服务器实现读写分离（主写从读），从服务器实现负载均衡
把多个从服务器根据业务重要性进行拆分访问（从服务器根据业务进行拆分）

# 3.master主库配置

修改my.cnf

代码语言：javascript复制

[root@localhost mysql]# cat my.cnf 
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
port=3306

代码语言：javascript复制

mkdir /var/log/mysql
chown -R  mysql:mysql /var/log/mysql

重启数据库后，查看以下配置是否生效

代码语言：javascript复制

mysql> show variables like 'server_id';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| server_id     | 1     |
 --------------- ------- 
1 row in set (0.02 sec)

mysql> show variables like 'log_bin';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| log_bin       | ON    |
 --------------- ------- 
1 row in set (0.00 sec)

mysql> show variables like '%skip_networking%';
 ----------------- ------- 
| Variable_name   | Value |
 ----------------- ------- 
| skip_networking | OFF   |
 ----------------- ------- 
1 row in set (0.00 sec)

mysql>

在主库上建立用于主从复制的账号

代码语言：javascript复制

mysql> CREATE USER 'rep1'@'%';
mysql> GRANT REPLICATION SLAVE ON *.*  TO  'rep1'@'%'  identified by 'coolsummermoon';

查看主库的二进制日志的名称 这里的Position和File后面会用到

代码语言：javascript复制

mysql> show master statusG
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql>

# 4.slave从库配置

在从库上面测试之前建立的复制账号是否可以连接主库

代码语言：javascript复制

[root@localhost mysql]# mysql -u rep1 -h 192.168.0.66 -P 3306 -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1036
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

修改my.cnf

代码语言：javascript复制

[root@localhost mysql]# cat my.cnf 
[mysqld]
server-id=2
port=3306

重启数据库后，查看以下配置是否生效

代码语言：javascript复制

mysql> show variables like 'server_id';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| server_id     | 2     |
 --------------- ------- 
1 row in set (0.01 sec)

mysql> show variables like 'log_bin';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| log_bin       | OFF   |
 --------------- ------- 
1 row in set (0.01 sec)

mysql>

配置复制参数

代码语言：javascript复制

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.66',MASTER_USER='rep1',MASTER_PASSWORD='coolsummermoon',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154;
mysql> start slave;

查看slave的状态

代码语言：javascript复制

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.66
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1566853
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1567019
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

若出错，则清理掉之前的配置，执行以下命令

代码语言：javascript复制

mysql> stop slave;
mysql> reset slave all;

# 5.验证

主上创建数据库插入数据等操作，从上能同步即可

sql 数据备份 file position

0 人点赞