环境:
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
11slave-net-timeout=60 12master-connect-retry=10
接着在master上创建备份帐号
1 grantreplicationslave,replicationclienton*.*to'slave'@'192.168.10.245'identifiedby'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
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
最后将master上的表解锁
unlock tables;