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

[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

1
2
3
4
5
6
7

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

1
2

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

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>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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

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

1
2

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

mysql> show master status\G
*************************** 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>

1
2
3
4
5
6
7
8
9
10

# 4.slave从库配置

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

[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>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

修改my.cnf

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

1
2
3
4

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

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>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

配置复制参数

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;

1
2

查看slave的状态

mysql> show slave status\G
*************************** 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

mysql> stop slave;
mysql> reset slave all;

1
2

# 5.验证

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

← mysql8安装 redis5安装说明→