一：实验环境
--这两台机器都是新装好的mysql,并没有真正的应用数据。
Linux下安装mysql，请参考文章：http://blog.csdn.net/yabingshi_tech/article/details/39396005
二：实验步骤
2.1：配置master
2.1.1：修改my.cnf文件
在配置文件my.cnf加入如下值：
[mysqld]
log-bin=mysql-bin
server-id=1
--含义分别为：打开二进制日志，指定唯一的servr ID。
--查找my.cnf文件位置：
mysql> select @@basedir;
+-----------+
| @@basedir |
+-----------+
| /usr |
+-----------+
1 row in set (0.00 sec)
[root@T2 mysql]# cd /usr
[root@T2 usr]# ls -l | grep my.cnf
-rw-r--r-- 1 root root 983 Apr 22 09:50 my.cnf
2.1.2 重启master
[root@T2 usr]# service mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL.. [ OK ]
2.1.3 验证
--查看此时master状态：
[root@T2 usr]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--二进制日志已开启。
2.2：配置slave
2.2.1：修改my.cnf文件
在配置文件my.cnf加入如下值：
[mysqld]
log-bin=mysql-bin
server-id=2
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1
server_id是必须的，而且唯一。
slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。
relay_log配置中继日志。
log_slave_updates表示slave将复制事件写进自己的二进制日志。
有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。
2.2.2 重启slave
[root@T3 ~]# service mysql restart
Shutting down MySQL.... [ OK ]
Starting MySQL. [ OK ]
2.3：在master创建复制用户
mysql> grant replication slave on *.* to 'rep_user' identified by 'beijing';
Query OK, 0 rows affected (0.02 sec)
2.4：slave连接master
2.4.1：记录二进制日志文件名称及Position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 322 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.4.2 连接
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.242',
-> MASTER_USER='rep_user',
-> MASTER_PASSWORD='beijing',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=322;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
2.4.3 启动slave复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
2.4.4 主从同步状态检查
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.242
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 322
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 322
Relay_Log_Space: 456
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: 1
Master_UUID: 6b2de5b2-e4e5-11e4-b3d2-90b11c4b26eb
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。
2.4.5 验证主从复制效果
在master机器上：
mysql> create database jiao;
Query OK, 1 row affected (0.00 sec)
mysql> use jiao;
Database changed
mysql> create table t(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t(id) values(1);
Query OK, 1 row affected (0.01 sec)
在slave 机器上查看jiao数据库是否同步：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jiao |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use jiao;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
--说明复制成功
--本篇文章参考自：http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html
扫一扫
816
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
