<转>四条命令搞定mysql主从

一 . 环境准备

　　先上拓扑图

两个虚拟机 129和130.CentOS 6.3 安装mysql 版本5.1 .yum 确保安装配置一样.

1 yum -y install mysql mysql-devel mysql-server

service mysqld start

二 环境.

　　实验环境 service iptables stop  || setenforce 0

三 主从配置

　　vi /etc/my.cnf

1 主服务器
2 log-bin
3 server-id=129
4 从服务器
5 log-bin
6 server-id=130

主服务器上

1 grant replication slave  on *.*  to 'testsync'@'*' identified by '123456'

创建账号给从服务器用.

** use mysql; select * from user='testsync'  能看到账号建立.这一步一般不会有问题.

1 mysql> show master status\G
2 *************************** 1. row ***************************
3             File: mysqld-bin.000001
4         Position: 106
5     Binlog_Do_DB: 
6 Binlog_Ignore_DB: 
7 1 row in set (0.00 sec)

记录信息,从服务器会用到

 

从服务器上

1 change master to master_host = '192.168.137.129',master_user = 'testsync',master_password = '123456',
2 master_log_file = 'mysqld-bin.000001',master_log_pos = 106;  

启动 slave start; 查看信息

 1 mysql> show slave status\G
 2 *************************** 1. row ***************************
 3                Slave_IO_State: 
 4                   Master_Host: 192.168.137.129
 5                   Master_User: testsync
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: mysqld-bin.000001
 9           Read_Master_Log_Pos: 106
10                Relay_Log_File: mysqld-relay-bin.000002
11                 Relay_Log_Pos: 4
12         Relay_Master_Log_File: mysqld-bin.000001
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 106
25               Relay_Log_Space: 106
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: NULL
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 1593
38                 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41 1 row in set (0.00 sec)

IO yes ,sql yes .其实主从配置还是很简单的.

 

** server-id 必须有区别

    server 之间网络可达,之间3306端口可达

　log-bin 打开, log-position 一致. 主从服务器版本,初始数据信息一致.　

 

done . that's all .

 

may tomorrow a clear morning.

本站博客皆为原创,转载请标记下 小三爷.多谢~~!