一 软件环境
- 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)
至此,搭建主从库复制过程完成!