mysql 5.7 主从同步历史数据_MySQL 5.7 主从复制(主从同步)

1、说明：

IP 计算机名 角色

192.168.1.101 MySQL-001 master

192.168.1.102 MySQL-002 slave

系统：CentOS 6.或 7.

MySQL版本：5.7

mysql安装步骤链接

https://blog.51cto.com/10158955/1926574

注意！根据自己的安装路径不同和数据目录不同而修改！(包括配置文件)

2、master配置文件设置如下

一般mysql配置文件在/etc/my.cnf

(如果找不到的话也有可能在这些目录下：/etc/mysql/my.cnf，/usr/local/mysql/etc/my.cnf，~/.my.cnf)

[root@MySQL-001 ~]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/data/mysqldata

socket=/tmp/mysql.sock

user=mysql

port=3306

master的配置

server-id=1 # 服务器id (主从必须不一样)

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

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

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

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

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

重启数据库使配置生效：

CentOS 6.：

[root@MySQL-001 ~]# service mysqld restart

[root@MySQL-001 ~]# service mysqld status

SUCCESS! MySQL running (15607)

CentOS 7.：

[root@MySQL-001 ~]# systemctl restart mysqld.service

[root@MySQL-001 ~]# systemctl status mysqld.service

mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)

Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Main PID: 29959 (mysqld)

CGroup: /system.slice/mysqld.service

└─29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf

5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.

测试log_bin是否成功开启

[root@MySQL-001 ~]# mysql -uroot -p

mysql> show variables like '%log_bin%';

+---------------------------------+---------------------------------+

| Variable_name | Value |

+---------------------------------+---------------------------------+

| log_bin | ON |

| log_bin_basename | /opt/mysql/logs/mysql-bin |

| log_bin_index | /opt/mysql/logs/mysql-bin.index |

| log_bin_trust_function_creators | ON |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+---------------------------------+

6 rows in set (0.00 sec)

mysql>

可以看到log_bin为ON；

3、master的数据库中建立主从同步账号backup：

backup为用户名，192.168.1.%表示只允许192.168.1网段的客户端连接，123456为密码；

mysql> grant replication slave on . to 'backup'@'192.168.1.%' identified by '123456';

mysql> flush privileges;

mysql> select Host,User,authentication_string from mysql.user;

+--------------+---------------+-------------------------------------------+

| Host | User | authentication_string |

+--------------+---------------+-------------------------------------------+

| localhost | root | 6C362347EBEAA7DF44F6D34884615A35095E80EB |

| localhost | mysql.session |THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| localhost | mysql.sys | THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| 192.168.1.% | backup |9BB58B7F11A03B83C396FF506F3DF45727E79614 |

+--------------+---------------+-------------------------------------------+

5 rows in set (0.00 sec)

重启MySQL服务并设置读取锁定，读取锁定的意思是只能读取，不能更新，以便获得一个一致性的快照；

mysql> flush table with read lock; # 主库锁表；默认28800秒，即8小时自动解锁；

mysql> show master status;

1. row

File: mysql-bin.000002

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB: mysql

Executed_Gtid_Set: 3f46a487-5984-11e8-8edd-00163e000d73:1-8

1 row in set (0.00 sec)

mysql>

查看主服务器上当前的二进制日志名和偏移量值这里的file 和position 要和从上的一致；

导出master(192.168.1.101)上的数据，然后导入slave 中

master：

[root@MySQL-001 ~]# mysqldump -uroot -p employees > /opt/employees.sql # 假如employees为主库已经存在的库

[root@MySQL-001 ~]# yum install openssh-clients -y #(注：slave也需要安装)

slave：

[root@MySQL-001 ~]# yum install openssh-clients -y

[root@MySQL-001 ~]# scp /opt/employees.sql root@192.168.1.102:/opt/

root@192.168.1.102's password: #输入密码

employees.bak 100%

5、配置slave(192.168.1.102)

[root@MySQL-002 ~]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql # mysql路径

datadir=/data/mysqldata # mysql数据目录

socket=/tmp/mysql.sock

user=mysql

port=3306

slave配置

server_id=2 # MySQLid 后面2个从服务器需设置不同

skip_slave_start=1 # 复制进程不会随着数据库的启动而启动,需手动启动；

#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。

read_only = 1

master_info_repository=TABLE

relay_log_info_repository=TABLE

#relay_log_recovery=1 # 从机禁止写

#super_read_only=1 # 从机禁止写

然后导入到mysql数据库中，slave上的employees数据库不存在则先创建，然后再导入

[root@MySQL-002 ~]# mysql -uroot -p

mysql> create database employees; # 新建这个库

[root@MySQL-002 ~]# mysql -uroot -p employees < /opt/employees.sql

重启数据库

CentOS 6.：

[root@MySQL-002 ~]# service mysqld restart

[root@MySQL-002 ~]# service mysqld status

SUCCESS! MySQL running (15604)

CentOS 7.：

[root@MySQL-002 ~]# systemctl restart mysqld.service

[root@MySQL-002 ~]# systemctl status mysqld.service

mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)

Active: active (running) since 四 2018-05-17 11:42:02 CST; 2h 5min ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Main PID: 29959 (mysqld)

CGroup: /system.slice/mysqld.service

└─29959 /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf

5月 17 11:42:02 tcloud-118 systemd[1]: Started MySQL Server.

登录slave数据库，并做如下设置

[root@MySQL-002 ~]# mysql -uroot -p

mysql> stop slave;

mysql> change master to

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

-> master_user='backup', # 备份用户名

-> master_password='123456', # 密码

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

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

合写为：

mysql> change master to master_host='192.168.1.101', master_user='backup', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=154;

mysql> start slave;

mysql> show slave status \G # 查看slave从机的状态

1. row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.101

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 154

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 921

Relay_Master_Log_File: mysql-bin.000002

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: 1756

Relay_Log_Space: 1122

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: 118

Master_UUID: 3f46a487-5984-11e8-8edd-00163e000d73

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: 3f46a487-5984-11e8-8edd-00163e000d73:6-8

Executed_Gtid_Set: 3f46a487-5984-11e8-8edd-00163e000d73:6-8

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

root@tcloud-119 15:11: [(none)]>

下面对应参数相同代表设置成功，0延时；

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

6、关闭掉主数据库的读取锁定,并测试

mysql> unlock tables;

在marster中创建一个新表再查看slave中是否有数据

master:

mysql> use employees;

mysql> create table test001(id int auto_increment primary key,name varchar(20) not null);

mysql> insert into test001 values(null,'will');

mysql> insert into test001 values(null,'jim');

mysql> insert into test001 values(null,'tom');

slave:

mysql> use employees;

mysql> show tables;

有图

mysql> select * from test001;

测试2：重启关闭从数据库，主删除test001表，然后主从数据库都重启看是否正常

mysql> drop table test001;

以上实验证明主从同步成功！！！