系统:centos6.5
mysql版本:5.1.37
mysql安装模式:yum
主服务器:192.168.0.5
从服务器:192.168.0.6
selinux:关闭
iptables: iptables -F 模式
mysql均未设置root密码
配置主服务器:
vi /etc/my.cnf
在[mysqld]下增加以下内容:
server-id=1
log-bin=mysql-bin
重启mysql:
service mysqld restart
进入mysql,查看master状态:
mysql -u root -p
mysql>show master status;
显示结果如下:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 242 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
这里要记住File:mysql-bin.000010 和 Position:242,下面从服务器配置时会用到。
授权给从服务器访问:
mysql>grant replication slave on *.* to 'onepage'@'192.168.0.6' identified by '123456';
在从服务器上测试:
mysql -h192.168.0.5 -uonepage -p123456
能连上则视为授权生效。
配置从服务器:
vi /etc/my.cnf
在[mysqld]下增加以下内容:
server-id=2
log-bin=mysql-bin
重启mysql:
service mysqld start
进入mysql:
mysql -u root -p
执行SQL语句:
mysql>change master to master_host='192.168.0.5', master_user='onepage', master_password='123456', master_log_file='mysql-bin.000010', master_log_pos=242;
启动slave同步进程:
mysql>slave start;
主从配置检查:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.90
Master_User: onepage
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 242
Relay_Log_File: mysqld-relay-bin.000007
Relay_Log_Pos: 387
Relay_Master_Log_File: mysql-bin.000010
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: 242
Relay_Log_Space: 688
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:
1 row in set (0.00 sec)
ERROR:
No query specified
这里要留意,Slave_IO_Running 和 Slave_SQL_Running 必须为 Yes状态。
测试:
在主服务器上创建数据库world:
mysql>create database world;
mysql>use world;
mysql>create table book (id int(4), name varchar(20));
mysql>insert into book(name) values('fire'),('春天'),('夏天'),('秋天'),('冬天');
在从服务器上登录查看是否数据已同步:
mysql -u root -p
mysql>show databases;
mysql>use world;
mysql>select * from book;