一步一步搭建MySQL主从复制(Linux7.4_MySQL5.7)

1、环境介绍

OS环境：Linux7.4

MySQL环境：5.7

主库IP：192.168.59.21
从库IP：192.168.59.22

搭建主从库前确保主从机都搭建了MySQL

2、主库启用二进制日志

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.01 sec)

**可以使用默认数据库安装位置/var/lib/mysql,也可以自己创建**

[root@mysql57 mysql]# mkdir bin_log
[root@mysql57 mysql]# chown -R mysql:mysql /var/lib/mysql/bin_log
[root@mysql57 mysql]# ls -ld /var/lib/mysql/bin_log
drwxr-xr-x. 2 mysql mysql 4096 Jan 19 19:39 /var/lib/mysql/bin_log

[root@mysql57 ~]# cat /etc/my.cnf

log-bin=/var/lib/mysql/bin_log/mysql-bin.log
binlog_format=ROW
expire_logs_days=10
server_id=1

mysql> show variables like 'log_bin%';

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

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

[root@mysql57 bin_log]# ls -lrt /var/lib/mysql/bin_log
total 12
-rw-r-----. 1 mysql mysql 177 Jan 19 19:44 mysql-bin.000001
-rw-r-----. 1 mysql mysql  80 Jan 19 19:44 mysql-bin.index
-rw-r-----. 1 mysql mysql 154 Jan 19 19:44 mysql-bin.000002

**主从库都创建用于测试的DB：**

mysql> create database test_slave_db;
Query OK, 1 row affected (0.01 sec)

**主库指定用于二进制日志的数据库**

将binlog-do-db=test_slave_db添加到/etc/my.cnf中然后重启mysqld服务

mysql> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: log

+------------------+----------+---------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+------------------+-------------------+
| mysql-bin.000003 |      154 | test_slave_db |                  |                   |
+------------------+----------+---------------+------------------+-------------------+

3、主库创建用于同步的账号

mysql> create user 'slave'@'192.168.59.%' identified by 'slave';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password_policy=0
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'slave'@'192.168.59.%' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'slave'@'192.168.59.%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| lissen        | %            |
| slave         | 192.168.59.% |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
+---------------+--------------+
5 rows in set (0.00 sec)

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

4、从库启动二进制日志并设置数据库只读，并指定要复制的数据库

从库：

[root@mysql57-slave ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#添加的部分
server-id=2
log-bin=mysql-bin.log
replicate-do-db=test_slave_db
read_only=1

#read_only参数表示普通用户不能修改数据库，超级用户可以修改数据库

重启mysqld

systemctl restart mysqld

5、从库连接主库并开始接受二进制日志

查看主库当前使用的二进制的日志文件

主库：

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

连接主库并启动slave

从库：

[root@mysql57-slave ~]# mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> change master to master_host='192.168.59.21',master_user='slave',master_password='slave',master_log_file='mysql-bin.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.59.21
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql57-slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003

				主要是这2个参数
				
             	Slave_IO_Running: No
            	Slave_SQL_Running: No

              Replicate_Do_DB: test_slave_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: 154
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.59.21
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql57-slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003

			**这2个参数变成Yes表明配置成功**
			
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

              Replicate_Do_DB: test_slave_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: 154
              Relay_Log_Space: 535
              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: b8d8cf7b-39c9-11ea-993f-000c294eaa12
             Master_Info_File: /var/lib/mysql/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)

6、测试主从是否同步

主库：

mysql> use test_slave_db
Database changed

mysql> create table t1(id int,name varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1' at line 1

mysql> show tables;
+-------------------------+
| Tables_in_test_slave_db |
+-------------------------+
| t1                      |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 values(1,'xiaolong'),(2,'xiaoming');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
ERROR 1146 (42S02): Table 'test_slave_db.t2' doesn't exist
mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaolong |
|    2 | xiaoming |
+------+----------+
2 rows in set (0.00 sec)

从库：

mysql> use test_slave_db;
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_test_slave_db |
+-------------------------+
| t1                      |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaolong |
|    2 | xiaoming |
+------+----------+
2 rows in set (0.00 sec)