配置
主(master):192.168.27.133
从1(slave):192.168.27.130
从2(slave):192.168.27.134
1.在master机上为slave机添加一同步帐号
grant replication slave on *.* to tongbu1@'192.168.27.130' identified by 'tongbu1';
grant replication slave on *.* to 'tongbu2'@'192.168.27.134' identified by 'tongbu2';
flush privileges;
2.修改master机上的mysql配置文件
vi /etc/my.cnf
server-id = 1 #服务器id号
log-bin=mysql-bin #二进制日志文件
binlog-do-db=onethink #需要同步的数据库，如果没有本行，即表示同步所有的数据库
binlog-ignore-db=mysql #被忽略的数据库
binlog-ignore-db=information-schema #被忽略的数据库
character-set-server=utf8
init_connect='SET NAMES utf8'
4.查看master机的mysql状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000028 | 107 | onethink | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5.修改slave机上的mysql配置文件
vi /etc/my.cnf
server-id = 2 #服务器id (主从必须不一样)
log-bin = mysql-bin #启用二进制日志
5.在每一个从库上面执行mysql -u root -p
change master to master_host='192.168.27.133',
master_user='tongbu2',
master_password='tongbu2',
master_log_file='mysql-bin.000028',
master_log_pos=107;
7.开启同步
start slave;
8.通过mysql客户端连接主库进行调试。
mysql -h 主库 -u root -p
9.主从同步检查
show slave status\G
Slave_IO_Running,Slave_SQL_Running 都为yes都表示成功
(
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
1.查看server-id
mysql> show variables like 'server_id';
2.手动修改server-id
set global server_id=2; #此处的数值和my.cnf里设置的一样就行
slave start;
)
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.27.133
Master_User: tongbu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 107
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Connecting
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: 107
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'tongbu@192.168.27.133:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)