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 上的mysqlsudo 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 statusG;
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 specifiedMySQL Master Slave 数据同步，集群利用MySQL的master and slave功能实现实时数据同步相关资讯 MySQL教程

30分钟带你快速入门MySQL教程（02月03日）
MySQL教程：关于I/O内存方面的一些（01月24日）
CentOS上开启MySQL远程访问权限（01/29/2013 10:58:40）

MySQL教程：关于checkpoint机制（01月24日）
MySQL::Sandbox （04/14/2013 08:03:38）
生产环境MySQL 5.5.x单机多实例配（11/02/2012 21:02:36）

本文评论查看全部评论（0）