master:10.11.123.241
slave:10.11.123.242
master 端:
vim /etc/my.cnf (如果没有 到/usr/share/mysql/ 里复制 my*.cnf 到 /etc/下)
[mysqld]
- server-id = 1
- log-bin = log
- binlog-do-db = test1 //需要同步的数据库,如果没有本行,即表示同步所有数据库
- binlog-ignore-db = mysql //被忽略的数据库
在masters机上为slave机添加一同步账号
Grant replication slave on *.* to
'slave'@'10.11.123.241'
identified by '123456';
此步如果后面的slave无法远程的话 要进行如下操作
- mysql>use mysql;
- mysql>select host,user from user;
- mysql>update user set host='%' where user='slave';
- mysql>flush privileges;
重启master机的mysql服务
Service mysqld restart
用show master status命令看日志情况
mysql> show master status;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| log.000011 | 3398273 | repldb | mysql |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
slave 端
- vim /etc/my.cnf
- server-id = 2 slave的ID号,此处一定要大于master端。
- 保存退出。
- mysql>stop slave;
- mysql>change master to
- >master_host='192.168.2.67',
- >master_user='rsync', #master端创建的用于主从同步的账户和密码
- >master_password='123456',
- >master_port='3306', #master端设置的client端使用的端口号。
- >master_log_file='mysql-bin.000047', #master端记录的file值 这步可以不加
- >master_log_pos=391592414; #master端记录的position值 这步可以不加
- mysql>start slave;
- mysql>show slave status \G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.2.67
- Master_User: rsync
- Master_Port: 3306
- Connect_Retry: 30
- Master_Log_File: mysql-bin.000047
- Read_Master_Log_Pos: 413641446
- Relay_Log_File: backter-relay-bin.000002
- Relay_Log_Pos: 13433937
- Relay_Master_Log_File: mysql-bin.000047
- 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: 405026116
- Relay_Log_Space: 22049267
- 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: 148
- 1 row in set (0.00 sec)
如果slave_IO_running,slave_SQL_Running状态为Yes则表明设置成功。
转载于:https://blog.51cto.com/guichina/775456