主库服务器环境:
操作系统:Centos6.7
数据库:Mysql5.6.34
IP: 192.168.10.1
从库服务器环境:
操作系统:Centos6.7
数据库:Mysql5.6.34
IP:192.168.10.2
查看centos版本
[sparkadmin@hadoop4 ~]$ cat /etc/redhat-release
CentOS release 6.7 (Final)
查看mysql版本
[sparkadmin@hadoop4 ~]$ mysql -V
mysql Ver 14.14 Distrib 5.6.34, for Linux (x86_64) using EditLine wrapper
首先在机器分别安装mysql
配置:
1、主服务器
1.1、创建一个复制用户,具有replication slave 权限。
mysql> CREATE USER repl IDENTIFIED BY 'repl';
mysql> grant replication slave on *.* to 'repl'@'192.168.10.2' identified by 'repl';
在从服务器上进行登录验证
[sparkadmin@hadoop4 ~]$ mysql -h192.168.10.1 -urepl -prepl
mysql>
1.2、编辑my.cnf文件
# vim /etc/my.cnf
添加
server-id=1
log-bin=mysql-bin 并开启log-bin二进制日志文件
1.3、重启mysql数据库
[sparkadmin@hadoop3 ~]$ sudo service mysqld restart
[sparkadmin@hadoop3 ~]$ mysql -uroot -proot
查看权限:
mysql> show grants for 'repl'@'192.168.10.2';
查看binlog 日志文件名和偏移量
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
2、从服务器
2.1、编辑my.cnf文件
# vim /etc/my.cnf
添加
server-id=2
2.2、重启mysql数据库
[sparkadmin@hadoop4 ~]$ sudo service mysqld restart
2.3、对从数据库进行相应设置
[sparkadmin@hadoop4 ~]$ mysql -uroot -proot
mysql> CHANGE MASTER TO
-> master_host='192.168.10.1',
-> master_user='repl',
-> master_password='repl',
-> master_port=3306,
-> master_log_file='mysql-bin.000007',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
2.4、启动从服务器slave线程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
执行show processlist命令显示以下进程:
mysql> show processlist\G
*************************** 5. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Connect
Time: 267
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
5 rows in set (0.00 sec)
表示slave已经连接上master,开始接受并执行日志
2.5、查看slave线程状态
在从服务器上执行
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
出现上面的信息说明配置成功
测试
1、在主服务器test数据库中创建user表
mysql>use test;
mysql>create table user(id int);
2、在从服务器中查看user表
mysql>use test;
mysql> show tables like 'user';
+----------------------+
| Tables_in_test(user) |
+----------------------+
| user |
+----------------------+
1 row in set (0.00 sec)
说明主从数据同步成功。
在主服务器上创建数据库
mysql> create database aa;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aa |
| mysql |
| test |
+--------------------+
在从服务器上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aa |
| mysql |
| test |