mysql5.7主从搭建--基于binlog

mysql5.7主从搭建–基于binlog

文章目录

• 一、搭建环境

• 二、修改配置文件

• • 2.1 修改Master的my.cnf文件，在末尾增加如下内容
  • 2.2 修改Slave的my.cnf文件，在末尾增加如下内容
  • 2.3 分别重启两台MySQL

• 三、修改数据库设置

• • 3.1 Master设置
  • 3.2 Slave设置

• 四、验证

• • 4.1 Master创建test库，并创建一个info表
  • 4.2 Slave查看是否有此库和info表

一、搭建环境

主(Master) ：192.168.199.103
从(Slave) ：192.168.199.23

[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core)
12

MySQL版本：Server version: 5.7.24 MySQL Community Server (GPL)
学习环境下最好关闭防火墙

卸载自带的Mariadb

yum -y remove mariadb-libs

下载mysql-5.7.27

yum install -y wget
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar

安装mysql-5.7.27

yum install -y libaio net-tools perl \ 
&& rpm -ivh mysql-community-common-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm

启动并设置开机自启

systemctl start mysqld && systemctl enable mysqld

获取初始密码

grep password /var/log/mysqld.log | sed 's/.*\(............\)$/\1/'

进入MySQL命令行，修改密码，设置访问权限

[root@localhost ~]# mysql -uroot -p 
Enter password: 

mysql> set password for root@localhost = password('123456Aa.');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

二、修改配置文件

2.1 修改Master的my.cnf文件，在末尾增加如下内容

log-bin=mysql-bin 
server-id=2  
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-do-db=test

2.2 修改Slave的my.cnf文件，在末尾增加如下内容

log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60 

参数	说明
log-bin	开启bin-log
server-id	数据库唯一ID，主从不可重复
binlog-ignore-db	Master忽略同步的库
binlog-do-db	Master需要同步的库（如果没有此项，表示同步所有的库）
replicate-do-db	指定Slave需要同步的库
log-slave-updates	链式更新
slave-skip-errors	跳过出错
slave-net-timeout	心跳时间

2.3 分别重启两台MySQL

[root@localhost ~]# systemctl restart mysqld

三、修改数据库设置

3.1 Master设置

登录mysql赋予Slave（192.168.199.23）有读取日志的File权限

mysql -uroot -p123456Aa.
mysql> grant FILE on *.* to 'root'@'192.168.199.23' identified by '123456Aa.';
mysql> grant replication slave on *.* to 'root'@'192.168.199.23' identified by '123456Aa.';
mysql> flush privileges;

重启mysql，查看Master状态获取File与Position的值

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

3.2 Slave设置

mysql -uroot -p123456Aa.
mysql> stop slave;
mysql> change master to master_host='192.168.199.103',master_user='root',master_password='123456Aa.',master_log_file='mysql-bin.000002', master_log_pos=154;
mysql> start slave;

参数	说明
master_host	Master地址
master_user	Master用户名
master_password	Master密码
master_log_file	Master的File值
master_log_pos	Master的Position

重启mysql，查看是否与MasterFile与Position的值一直

service mysqld restart
mysql> show slave status;
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State                   | Master_Host     | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File             | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File                        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | 192.168.199.103 | root        |        3306 |            60 | mysql-bin.000002 |                 154 | localhost-relay-bin.000004 |           320 | mysql-bin.000002      | Yes              | Yes               | test            | mysql               |                    |                        |                         |                             |          0 |            |            0 |                 154 |             531 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                2 | 1f25ba62-fc45-11e8-910e-000c2955ec3b | /usr/local/mysql5.7.24/data/master.info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set (0.00 sec)

四、验证

4.1 Master创建test库，并创建一个info表

mysql -uroot -p123456Aa.
mysql> create database test;
mysql> use test;
mysql> create table info (name varchar(20),sex varchar(20));
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)

4.2 Slave查看是否有此库和info表

mysql -uroot -p123456Aa.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)

主从搭建成功！！！