mysql5.7主从复制的实现

mysql5.7主从复制的实现

（1）环境准备
三台装有mysql机子，mysql安装详见《二进制安装mysql5.7》https://blog.csdn.net/niyifan2/article/details/102494658
master：192.168.164.151
slave_1:192.168.164.153
slave_2:192.168.164.155

关闭selinux

[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Permissive

关闭防火墙

systemctl stop firewalld.service 
systemctl disable firewalld.service #禁止开机自启动
firewall-cmd --state  #显示为not running即为关闭状态

（2）master 端配置
1.vi /etc/my.cnf 修改mysql配置文件
[mysqld]
basedir=/opt/mysql5.7
datadir=/Mysql/my3306/data
port=3306
pid-file = /Mysql/my3306/data/mysql.pid
user = mysql
socket=/Mysql/my3306/tmp/mysql.sock
bind-address = 0.0.0.0
server-id = 2 #主从复制时标志必须唯一
character-set-server = utf8
log-bin=/Mysql/my3306/log/mysql-bin #开启bin-log
binlog-ignore-db=mysql #不同步mysql系统数据库
sync_binlog=1 #确保事物提交binlog写入后，立刻写入硬盘
binlog_checksum=crc32
binlog_format = mixed

[client]
port=3306
socket=/Mysql/my3306/tmp/mysql.sock

[mysql]
socket=/Mysql/my3306/tmp/mysql.sock

2.重启mysql服务
service mysql restart 重启mysql服务

3.如果是已使用的数据库，则导出数据（新安装的忽略）
mysqldump -uroot test -p ****** >/tmp/test.sql 使用mysqldump导出数据
将导出数据传到slave服务器上

4.创建同步用户repl，并赋予相应权限
192.168.164.%表示只可通过192.168.164网段的slave去连接master，也可指定具体地址，或不指定地址，不指定地址则为"%"

mysql> grant replication slave,replication client on *.* to repl@'192.168.164.%' identified by "******";
mysql> flush privileges;
mysql> show grants for repl@'192.168.164.%';#确认是否已赋予权限
+------------------------------------------------------------------------------+
| Grants for repl@192.168.164.%                                                |
+------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.164.%' |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.查看并记录master的状态

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

一定要把 File列和Position列的内容记录下来

（3）slave端的操作
1.更改配置文件 vi /etc/my.cnf
[mysqld]
basedir=/opt/mysql5.7
datadir=/Mysql/my3306/data
port=3306
pid-file = /Mysql/my3306/data/mysql.pid
user = mysql
socket=/Mysql/my3306/tmp/mysql.sock
bind-address = 0.0.0.0
server-id = 4
character-set-server = utf8
log-bin=/Mysql/my3306/log/binlog/mysql-bin #启用MySQ二进制日志系统
replicate-ignore-db=mysql #不同步mysql数据库
slave-skip-errors = all

[client]
port=3306
socket=/Mysql/my3306/tmp/mysql.sock

[mysql]
socket=/Mysql/my3306/tmp/mysql.sock

重启mysql服务
service mysqld restart
2.导入数据
如果需要导入数据先在slave上创建与主库同名的数据库
在切换到该数据库上执行导入操作，否则会报：ERROR 1046 (3D000): No database selected的错误

mysql> create database test；
mysql> use test；
Database changed
mysql> source /mnt/hgfs/vm_win_public/test.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

3.配置主库信息

mysql> stop slave;     #第一步stop slave
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  change master to   master_host='192.168.164.151',master_user='repl',master_password='*****',master_log_file='mysql-bin.000001',master_log_pos=154;     #填入之前记录的信息 （File列和Position列的内容）以及主库ip地址以及主从复制用户等信息
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;   #重启slave
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status \G;   #查看主从同步状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.164.151
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 502
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 668
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           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: 502
              Relay_Log_Space: 879
              Until_Condition: None
               Until_Log_File: 
               .........

如果看到这两个： Slave_IO_Running: Yes 、Slave_SQL_Running: Yes都为yes状态说明已经开始主从同步了。