MySQL Master Slave同步配置

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

 

接着在master上创建备份帐号

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

 

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

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

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

分别重启master 和 slave 上的mysql

sudo service mysql restart

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

#author:zhxia 

1 mysql> show master status;
2 +-----------------------+----------+--------------+------------------+
3 | File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
4 +-----------------------+----------+--------------+------------------+
5 | mysql-test-bin.000022 |      624 |              |                  |
6 +-----------------------+----------+--------------+------------------+
7 1 row in set (0.02 sec)

 

进入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