Mysql5.7主从配置保姆级别教程-伊成个人站

MySQL主从配置

指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。

对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

一句话表示就是，主数据库做什么，从数据库就跟着做什么。

主从复制的工作原理

① Master 数据库只要发生变化，立马记录到Binary log 日志文件中。
② Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志。
③ Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。
④ Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据。

三种复制方式

（1）STATEMENT模式：基于语句的复制，在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选择基于行的复制。

（2）ROW模式：基于行的复制，把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从MySQL5.0开始支持。

（3）MIXED模式：混合类型复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

主从配置的优点

提高数据库系统的可用性。
读写分离，提高查询访问性能，有效减少主数据库访问压力。
数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。
实时灾备，主数据库出现故障时，可快速切换到从数据库。

环境准备

建议本机安装虚拟机，准备两个MySQL，具体环境详情如下：

宿主机 centos7
MySQL版本 5.7
MySQL Master: 192.168.191.130
MySQL Slave : 192.168.191.131

主库配置

① 修改主库的 my.cnf 文件，添加如下配置。

1	vim /etc/my.cnf

server-id=1       #服务器id
log-bin=mysql-bin #打开日志，master需要打开

binlog-do-db=db_ly #这里写你需要同步的数据库名！这里一定要写对你要同步的数据库名
binlog-ignore-db=mysql #忽略不需要同步给从库的数据库名
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理7天前的log文件,可根据需要修改

tips ：修改完成后按esc 按：wq 回车保存并退出！

② 重启mysql服务，输入以下命令。

1	service mysqld restart

③ 登录MySQL查看测试log_bin是否成功开启（ON 代表已开启）

1	mysql -uroot -proot # 登录mysql

执行以下命令：

1	show variables like '%log_bin%';

[root@jenkins /]# mysql -uroot -proot 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/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.01 sec)

④ 创建master的数据库中建立备份账号：root 为用户名，%表示任何远程地址。
如下表示密码为 root 的任何远程地址的root都可以连接master主机。

1	grant replication slave on . to 'root'@'%' identified by 'root';

⑤ 查看刚刚创建的账号是否存在。

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          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %         |
+---------------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)

⑥ 查看主服务器上当前的二进制日志名。

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

Tips: 到此MySQL master的设置就设置完毕！

从库设置

① 修改从库的 my.cnf 文件，添加如下配置。

1	vim /etc/my.cnf

1
2
3

server_id=2 #从库的服务器id，不能和主库的一样！切记！！！
master_info_repository=TABLE
relay_log_info_repostitory=TABLE

② 登录MySQL，设置上面主库对应的参数。

1	[root@localhost /]# mysql -uroot -proot

执行以下命令：

1
2
3

change master to master_host='192.168.191.130',
master_user='root',master_password='root',
master_log_file='mysql-bin.000001',master_log_pos=439;

mysql> stop slave #记得先关闭
    -> ;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql>  change master to master_host='192.168.191.130',
    ->  master_user='root',master_password='root',
    ->  master_log_file='mysql-bin.000001',master_log_pos=439;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;#设置好了之后记得从新start slave

③ 查看slave 的状态，执行一下命令。

1	show slave status \G

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.191.130
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 847
               Relay_Log_File: localhost-relay-bin.000013
                Relay_Log_Pos: 1060
        Relay_Master_Log_File: mysql-bin.000008
             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: 847
              Relay_Log_Space: 1437
              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: 99e35046-6492-11ec-b188-000c2902ba69
             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: 
1 row in set (0.01 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
看到 Slave_IO_Running 和 Slave_SQL_Running 均为YES 则说明我们配置的主从已经成功了！

测试主从

① 切换至主库，创建主库中指定的 db_ly 数据库测试一下。

mysql> use db_ly;
Database changed
mysql> 
mysql> 
mysql> 
mysql> create table `tb_001`(`id` int(11) not null,`tb_user` int(11) not null,primary key(`id`)) engine=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_001 value('1','1');
Query OK, 1 row affected (0.09 sec)

mysql>

② 查看同步后的数据。

“伊成博客”

到此主从配置就成功了！

常见问题集合

常见问题一： ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.191.130’ (113)

解决方法：
① 尝试远程连接数据库 (通常都是这个原因无法远程连上MySQL)

1	mysql -h192.168.191.130 -uroot -proot

如果无法登录则使用下面命令：
重新复权

grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)

#这条命令也别忘记了

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

然后可以再次尝试远程登录MySQL。

② 测试能否ping通远程IP

1	ping 192.168.191.130

③ 如果IP是通的，则测试能否访问到端口。

1	telnet 192.168.191.130 3306

如果通不了，则说明防火墙是开启的，直接关闭防火墙即可（生产环境不推荐这么玩啊~）。

④ 查看my.cnf文件是不是没有注释 bind-address = 127.0.0.1

常见问题二：Last_IO_Errno: 1593

① 可能是server-id重复。

解决方法：
任意选一个服务器修改对应的server-id 即可，
输入命令如下，找到对应的 server-id 修改保存即可。

1	vim /etc/my.cnf

② 可能是server-uuid重复。
一般导致这个问题就是在虚拟机中装好MySQL之后，直接copy导致的。

解决方法：
先登录MySQL使用命令，产生一个uuid。

mysql> select  uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 0eec5819-78a2-11e9-84d0-525400534aff |
+--------------------------------------+
1 row in set (0.04 sec)

然后修改其中一个MySQL的auto.cnf配置文件，修改完成后保存退出。

[root@centos-cluster-s19423 ~]# vim  /var/lib/mysql/auto.cnf 
[auto]
server-uuid=b1bfa5f4-6a95-11e9-8049-525400534aee
~
~

重启这个MySQL即可。

tips: 如果主从配置有问题通常 Last_IO_Errno 和 Last_IO_Error 会有对应提示的错误码和错误信息。

The end.