MySQL主从配置（版本5.7）

msyqllogo.jpg

1. 主数据库配置： /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/home/software/data/mysql
socket=/tmp/mysql.sock
user=mysql
server_id=1             #服务器id (主从必须不一样)
port=3306

log-bin=mysql-bin       #打开日志(主机需要打开)，这个mysql-bin也可以自定义，这里也可以加上路径 

#binlog-do-db=          #要给从机同步的库

binlog-ignore-db=mysql  #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

expire_logs_days=7      #自动清理 7 天前的log文件,可根据需要修改

2. 重启主服务器并查看MASTER相关数据：

service mysqld restart  #重启数据库

mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------+
| Variable_name                   | Value                                     |
+---------------------------------+-------------------------------------------+
| log_bin                         | ON                                        |
| log_bin_basename                | /home/software/data/mysql/mysql-bin       |
| log_bin_index                   | /home/software/data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                       |
| log_bin_use_v1_row_events       | OFF                                       |
| sql_log_bin                     | ON                                        |
+---------------------------------+-------------------------------------------+
6 rows in set (0.02 sec)

3. 创建从库连接主库同步数据账户：

mysql> grant replication slave on *.* to 'copyusr'@'%' identified by '123456';

验证用户数据

mysql> use mysql
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> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *D93DB9B2EB76CFA26400B9F902FB7F06DE5CE686 | %         |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| copyusr       | *C4FAA3B6872CA0D7DE0F19008194BC3E718E3236 | %         |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

4. 查看主数据库状态数据：

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |     1208 |              | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

5. 从数据库配置： /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/home/software/data/mysql
socket=/tmp/mysql.sock
user=mysql
server_id=2
port=3306

read_only=1

6. 重启从数据库并与主数据库建立连接：

重启服务器

service mysqld restart

建立主数据库连接：

mysql> stop slave;

mysql> change master to

-> master_host='192.168.1.222',         # master的ip

-> master_user='copyusr',               # 备份用户账户

-> master_password='123456',            # 备份用户密码

-> master_log_file='mysql-bin.000001',  #上面截图，且要与master的参数一致

-> master_log_pos=1208;                 #上面截图，且要与master的参数一致

mysql> start slave;

查看从数据库状态：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.3.66
                  Master_User: copyusr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1208
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1095
        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: 1208
              Relay_Log_Space: 1306
              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: 0470473a-9f57-11eb-a03c-000c2930d0fc
             Master_Info_File: /home/software/data/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)

目前MySQL主从配置已经完成，
可以通过查看上面从库数据中的
Slave_IO_Running: Yes # 与主数据网络连接情况
Slave_SQL_Running: Yes # 同步数据SQL执行情况

可以在主数据建库，建表，写入数据，正常运行，从库数据应该可以同步成功~