mysql主从配置
根据网络加实践整理
一、环境
主机:IP:172.17.31.125
从机:IP:172.17.32.210
需要同步的数据库:hbws
二、master机和slave机的相关配置
1、修改master机器中mysql配置文件my.cnf,该文件在/etc目录下
在[mysqld]配置段添加如下字段
server-id = 1 #主机标示,整数
log_bin = MySQL-bin #确保此文件可写
read-only = 0 #主机,读写都可以
binlog-do-db = hbws #需要备份数据,多个写多行
binlog-ignore-db = mysql #不需要备份的数据库,多个写多行
在master机上为slave机添加一同步帐号
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'172.17.32.210' IDENTIFIED BY 'oracle'
重启master机的mysql服务:service mysqld restart
用show master status 命令看日志情况
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | MySQL-bin.000001 | 98 | hbws | mysql | +------------------+----------+--------------+------------------+ 1 row in set (0.01 sec) 2、修改slave机中mysql配置文件 同样在[mysqld]字段下添加如下内容 server-id = 2 log_bin = MySQL-bin master-host =172.17.32.210 master-user =backup master-pass =oracle master-port =3306 master-connect-retry=60 #如果从服务器发现主服务器断掉,重新连接的时间差(秒) replicate-do-db =hbws #只复制某个库 replicate-ignore-db=mysql #不复制某个库
然后重启slave机的mysql
在slave机中进入mysql
mysql>start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.17.31.125 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: MySQL-bin.000003 Read_Master_Log_Pos: 386 Relay_Log_File: mysqld-relay-bin.000007 Relay_Log_Pos: 523 Relay_Master_Log_File: MySQL-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: hbws Replicate_Ignore_DB: mysql 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: 386 Relay_Log_Space: 523 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 1 row in set (0.00 sec)
显示 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示配置正确
三、测试主从服务器是否能同步
在主服务器上面新建一个表,必须在需要同步的数据库(hbws)下
mysql> use hbws; Database changed mysql> insert into sss values ('liuliu'); Query OK, 1 row affected (0.00 sec) mysql> select * from sss; +--------+ | name | +--------+ | asdf | | liuliu | +--------+ 2 rows in set (0.00 sec)
mysql>
在从服务器查看是否同步过来
mysql> use hbws; Database changed mysql> select * from sss; +--------+ | name | +--------+ | asdf | | liuliu | +--------+ 2 rows in set (0.00 sec)