MySQL replication在实际中使用十分广泛。本文,采用最简单的方法来搭建MySQL repllication——master和slave都没有数据来开始搭建。
1. 在两台CentOS上分别安装好MySQL数据,最好版本一致(本文采用的是5.6.21)。此时master和slave上都没有数据。
2. 在master的mysql配置文件my.cnf中启用二进制日志:log_bin=mysql-bin; 设置master上的mysql数据库的server_id:server_id=1
3. 在slaver的mysql配置文件my.cnf中启用中继日志:relay_log=relay-bin; 设置slaver上的mysql数据库的server_id:server_id=101;
master和slave上的配置文件my.cnf修改之后,需要重启mysql。(slave上的log_bin一般不打开)
4. 在master上建立一个具有复制权限的用户:
grant replication slave, replication client on *.* to repl@'192.168.137.%' identified by '123456';
5. 在master上执行:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000008
Position: 355
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
我们得到了复制的日志文件盒日志文件的复制开始位置。因为我们的master上的mysql是最新安装的,没有生产数据,所以可以用这种方式来得到复制的开始位置。
6. 在slave上执行:
change master to master_host='192.168.137.8', master_user='repl', master_password='123456',
master_log_file='mysql-bin.000009', master_log_pos=120;
接着启动复制:start slave;
接着查看复制:show slave status\G;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.137.8
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000009
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
... ....
看到Slave_IO_Running: Yes Slave_SQL_Running: Yes表示配置成功。
可以继续查看slave上的复制线程的情况:
mysql> show full processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+-----------------------+
| 1 | system user | | NULL | Connect | 178 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 2 | system user | | NULL | Connect | 385 | Waiting for master to send event | NULL |
| 3 | root | localhost | demo | Sleep | 151 | | NULL |
| 4 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+-----------------------
查看master上的复制相关线程:
在master上执行:
mysql> show full processlist;
+----+------+---------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+
| 3 | repl | 192.168.137.9:58277 | NULL | Binlog Dump | 3599 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 4 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+---------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+
2 rows in set (0.01 sec)
7. 在master上进行数据库的增删改查,然后查看slave上是否进行了复制。
到此,最简单的MySQL replication配置就搞定了。但是实际上,这种最简单的配置在实际生产中比较少见,一般都是master上已经有了很多数据,在次基础上来配置一个活多个slave。此种情况一般都要使用mysqldump等工具将master上的数据线迁移到slave上,然后再来搭建复制。
阅读(7626) | 评论(0) | 转发(0) |