【MySQL】MySQL搭建主从复制环境

一 软件环境

• Mysql 5.7.21
• Oracle Linux 7.1

二 主机设置

• Master IP：10.24.33.186
• Slave IP：10.24.33.188

三 主从配置

1、Master配置

创建复制用户并授予适当权限：

mysql> create user 'repl'@'10.24.33.188' identified by 'repl';
Query OK, 0 rows affected (0.02 sec)

mysql> grant replication slave on *.* to 'repl'@'10.24.33.188';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'repl'@'10.24.33.188';
+---------------------------------------------------------+
| Grants for repl@10.24.33.188                            |
+---------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.24.33.188' |
+---------------------------------------------------------+
1 row in set (0.00 sec)

启用Binlog：

[root@strong ~]# more /etc/my.cnf 
[mysqld]

character-set-server=utf8

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

log-bin=/usr/local/mysql/binlog/mysql-bin  --启用Binlog
server-id=1                                                --设置唯一标识

[mysql]

default-character-set=utf8

锁定主库，获得数据库：

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

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

拷贝数据库至从库：

[root@strong mysql]# tar -cvf data.tar data/
[root@strong mysql]# scp data.tar root@10.24.33.188:/usr/local/mysql
root@10.24.33.188's password: 
data.tar                                                                                                                                                                  100%  133MB  33.3MB/s   00:04    
[root@strong mysql]#

解锁主库：

mysql> unlock tables;
Query OK, 0 rows affected (0.03 sec)

2、Slave配置

启用从库Binlog：

[root@strong ~]# more /etc/my.cnf 
[mysqld]

character-set-server=utf8

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

log-bin=/usr/local/mysql/binlog/mysql-bin
server-id=2

解压缩主库：

[root@strong mysql]# tar -xvf data.tar 

启动从库：

[root@strong ~]# mysqld_safe --skip-slave-start &
[1] 17322
[root@strong ~]# 2018-02-01T03:07:04.771630Z mysqld_safe Logging to '/usr/local/mysql/data/strong.slave.com.err'.
2018-02-01T03:07:04.804831Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@strong ~]# 

设置从库服务器：

mysql> change master to 
    -> master_host='10.24.33.186',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='repl',
    -> master_log_file='mysql-bin.000005',
    -> master_log_pos=888;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

启动从库线程：

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

查看从库线程：

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | root        | localhost | test | Query   |    0 | starting                                               | show processlist |
|  3 | system user |           | NULL | Connect |  765 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect |  724 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

注意：

在主从库的复制过程中，有时数据不能正确的复制，出现以下错误：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.出现此错误的原因是主从库的UUID一样，解决办法如下：

[root@strong data]# rm /usr/local/mysql/data/auto.cnf

然后重启MySQL即可。

四 验证主从同步

1、主库

mysql> select @@hostname;
+-------------------+
| @@hostname        |
+-------------------+
| strong.master.com |
+-------------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table repl (id int, name varchar(100));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into repl values (1,'Hello , Mysql !');
Query OK, 1 row affected (0.02 sec)

2、从库

mysql> select @@hostname;
+------------------+
| @@hostname       |
+------------------+
| strong.slave.com |
+------------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> select *from repl;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | Hello , Mysql ! |
+------+-----------------+
1 row in set (0.00 sec)

至此，搭建主从库复制过程完成！