mysql 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 同步设置2server-id=13 log_bin=/var/log/mysql/mysql-test-bin.log4 expire_logs_days=105max_binlog_size=100M6binlog_format=mixed

[slave]

#author:zhxia

1 server-id=22 replicate-do-db=test3 replicate-do-db=blog4 log_bin=/var/log/mysql/mysql-bin.log5 relay_log=/var/log/mysql/mysql-relay-bin.log6 expire_logs_days=107 max_binlog_size=100M8 #binlog_do_db=test9 #binlog_ignore_db=include_database_name10 binlog_format=mixed

接着在master上创建备份帐号

1 grantreplicationslave,replicationclienton*.*to'slave'@'192.168.10.245'identifiedby'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 1rowinset(0.02sec)

进入slave上的Mysql

#author:zhxia

1 change mastertomaster_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: Waitingformastertosend event6 Master_Host:192.168.1.1127 Master_User: slave8 Master_Port:33069 Connect_Retry:6010 Master_Log_File: mysql-test-bin.00002211 Read_Master_Log_Pos:62412 Relay_Log_File: mysql-relay-bin.00000513 Relay_Log_Pos:53314 Relay_Master_Log_File: mysql-test-bin.00002215 Slave_IO_Running: Yes16 Slave_SQL_Running: Yes17 Replicate_Do_DB: test,blog18 Replicate_Ignore_DB:19 Replicate_Do_Table:20 Replicate_Ignore_Table:21 Replicate_Wild_Do_Table:22 Replicate_Wild_Ignore_Table:23 Last_Errno:024 Last_Error:25 Skip_Counter:026 Exec_Master_Log_Pos:62427 Relay_Log_Space:68828 Until_Condition: None29 Until_Log_File:30 Until_Log_Pos:031 Master_SSL_Allowed: No32 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:038 Master_SSL_Verify_Server_Cert: No39 Last_IO_Errno:040 Last_IO_Error:41 Last_SQL_Errno:042 Last_SQL_Error:43 1rowinset(0.00sec)44 45 ERROR:46 No query specified