MySQL Master Slave同步配置

环境：

PC：ubuntu 10.10 192.168.1.112（master） 192.168.10.245（slave）

MySQL : 5.1.49-1ubuntu8.1-log

在master中已经存在数据库test

首先修改mysql配置文件：/etc/mysql/my.cnf

[master]

#author:zhxia

1 #master 同步设置
2 server-id                = 1
3 log_bin                  = /var/log/mysql/mysql-test-bin.log
4 expire_logs_days         = 10
5 max_binlog_size          = 100M
6 binlog_format            = mixed

[slave]

#author:zhxia

1 server-id                = 2
2 replicate-do-db = test
3 replicate-do-db = blog
4 log_bin                  = /var/log/mysql/mysql-bin.log
5 relay_log                = /var/log/mysql/mysql-relay-bin.log
6 expire_logs_days         = 10
7 max_binlog_size          = 100M
8 #binlog_do_db            = test
9 #binlog_ignore_db        = include_database_name
10 binlog_format            = mixed

11 slave-net-timeout=60

12 master-connect-retry=10

接着在master上创建备份帐号

1 grant replication slave, replication client on * . * to ' slave ' @ ' 192.168.10.245 ' identified by ' 123456 ' ;

将master中的数据库导入到slave中，

先锁表，禁止写入操作

flush tables with read lock;

先从master导出：mysqldump -uroot -p test > /tmp/test.sql

再导入到slave： mysql -uroot -p test < /tmp/test.sql ,记得需要先建库test

进入master上的mysql,查看master状态

#author:zhxia

进入slave上的Mysql

#author:zhxia

1 change master to master_host = ' 192.168.1.112 ' , master_user = ' slave ' , master_password = ' 123456 ' , master_log_file = ' mysql-test-bin.000022 ' , master_log_pos = 106 ;

然后启动salve，并查看状态：

#author:zhxia

1 start slave;
2
3 mysql > show slave status\G;
4 *************************** 1 . row ***************************
5                Slave_IO_State: Waiting for master to send event
6                   Master_Host: 192.168 . 1.112
7                   Master_User: slave
8                   Master_Port: 3306
9                 Connect_Retry: 60
10               Master_Log_File: mysql - test - bin. 000022
11           Read_Master_Log_Pos: 624
12                Relay_Log_File: mysql - relay - bin. 000005
13                 Relay_Log_Pos: 533
14         Relay_Master_Log_File: mysql - test - bin. 000022
15               Slave_IO_Running: Yes
16              Slave_SQL_Running: Yes
17               Replicate_Do_DB: test,blog
18           Replicate_Ignore_DB:
19            Replicate_Do_Table:
20        Replicate_Ignore_Table:
21       Replicate_Wild_Do_Table:
22   Replicate_Wild_Ignore_Table:
23                    Last_Errno: 0
24                    Last_Error:
25                  Skip_Counter: 0
26           Exec_Master_Log_Pos: 624
27               Relay_Log_Space: 688
28               Until_Condition: None
29                Until_Log_File:
30                 Until_Log_Pos: 0
31            Master_SSL_Allowed: No
32            Master_SSL_CA_File:
33            Master_SSL_CA_Path:
34               Master_SSL_Cert:
35             Master_SSL_Cipher:
36                Master_SSL_Key:
37         Seconds_Behind_Master: 0
38 Master_SSL_Verify_Server_Cert: No
39                 Last_IO_Errno: 0
40                 Last_IO_Error:
41                Last_SQL_Errno: 0
42                Last_SQL_Error:
43 1 row in set ( 0.00 sec)
44
45 ERROR:
46 No query specified

最后将master上的表解锁

unlock tables;

秒客网

MySQL Master Slave同步配置

相关文章