搜索历史
热搜词
mysql mysql 主从配置

推荐:mysql主从同步配置

[mysql主从同步配置,操作系统为windows,两台电脑。]

一、先修改服务器的配置文件

       1、Master服务器配置简单,修改my.cnf为:

server-id       = 1

log-bin=n01-bin

relay-log=ns01-relay-bin

set-variable=binlog-ignore-db=mysql

2、slave1的配置加入

master-host = 172.16.20.135

master-user = rep

master-password = cnrep

master-port = 3306

server-id       = 2

log-bin=n01-bin

replicate-do-db =mydns
replicate-ignore-db=mysql

log-slave-updates

slave-net-timeout=60

master-connect-retry=10
relay-log=ns01-relay-bin

log-slave-updates
read-only

3、slave2服务器的配置

master-host = 172.16.20.3

master-user = rep1

master-password = cnrep

master-port = 3306

server-id       = 3

log-bin=n01-bin

replicate-do-db =mydns
replicate-ignore-db=mysql

log-slave-updates

slave-net-timeout=60

 

master-connect-retry=10
relay-log=ns01-relay-bin

log-slave-updates
read-only

二、重启master数据库

 

三、然后锁定master数据库的表:

FLUSH TABLES WITH READ LOCK;

四、在master数据库中添加用于slave1同步的用户,并赋予相关权限:

GRANT REPLICATION SLAVE ON *.* TO rep@sa_cfengine1 IDENTIFIED BY ‘cnrep’;

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@sa_cfengine1 IDENTIFIED BY ‘cnrep’;

 

五、在slave1数据库中添加用于slave2同步的用户,并赋予相关权限:

GRANT REPLICATION SLAVE ON *.* TO rep1@sa_cfengine2 IDENTIFIED BY ‘cnrep’;

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep1@sa_cfengine2 IDENTIFIED BY ‘cnrep’;

 

六、同步数据库:

方法很多,可以打包之后scp,再解压,由于sa_cfengine1到mysql master服务器通道打通了,切sa_cfengine2到sa_cfengine1通道也打了,故直接scp整个数据库目录即可。

注意:此时要注意删除同步过来的日志文件,最好把与数据库无关的文件全删除(可以将非目录的文件全删了)。

 

七、重启salve1的mysql,起来之后锁定表

 

八、重启slave2的mysql,然后先后给slave1和master服务器的mysql表解锁

UNLOCK TABLES;

 

推荐:Mysql 主从配置

[linux环境mysql主从配置]

九、分别登录slave1和slave2的mysql,查看同步状态:

SHOW SLAVE STATUS\G

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 172.16.20.135

                Master_User: rep

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000051

        Read_Master_Log_Pos: 13856842

             Relay_Log_File: sa_cfengine1-relay-bin.000013

              Relay_Log_Pos: 624419

      Relay_Master_Log_File: mysql-bin.000051

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB: AliSMS,lcd,loginmanager,samis,sareport,syslog,web_speed

        Replicate_Ignore_DB: mysql,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: 13856842

            Relay_Log_Space: 624419

            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.01 sec)

注意标注为红色的地方,两个都是yes说明一切正常,否则要检查原因,可以看error log查找原因后做相应的处理。

 

十、测试:

       在master数据库中update在同步列表中的一个表的一个字段,如果slave服务器的做相应改变,则测试用过。

 

实例:

67:

mysql> show master status \G;
*************************** 1. row ***************************
File: n01-bin.000001
Position: 327
Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

ERROR: 
No query specified

66

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.221
Master_User: reply
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: n01-bin.000001
Read_Master_Log_Pos: 327
Relay_Log_File: no02-relay-bin.000039
Relay_Log_Pos: 233
Relay_Master_Log_File: n01-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydns,mydns,mydns
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: 327
Relay_Log_Space: 233
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)

ERROR: 
No query specified

.5 (既做67的从,有做10的主)

mysql> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| n01-bin.000004 |      220 |              |                  | 
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.250.67
Master_User: reply
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: n01-bin.000001
Read_Master_Log_Pos: 327
Relay_Log_File: ns01-relay-bin.000006
Relay_Log_Pos: 355
Relay_Master_Log_File: n01-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydns,mydns,mydns
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: 327
Relay_Log_Space: 355
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)

10.

CHANGE MASTER TO MASTER_LOG_FILE='n01-bin.000004', MASTER_LOG_POS=220;

推荐:mysql主从配置

[ 主服务器配置:1.建立用户grant replication slave on . to user001@192.168.0.200 identified by ‘111111'2.编辑配置文件/etc/my.cnfserver-id = 1log-bin=mysql-binb

主题:

分享:

相 关 推 荐

热 门 推 荐