环境配置：
A：192.168.11.11
B：192.168.11.12
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.12-log |
+------------+
CentOS release 5.4 (Final)
主服务器配置
vi /etc/my.cnf
在 Logging and Replication 后面增加:
# Replication Master Server (default)
server-id = 1//服务器ID号，整数值，保证唯一标识一台服务器就可以
log-bin=mysql-bin //打开二进制日志
binlog-do-db=repl //需要同步的数据库，如果没有本行，即表示同步所有的数据库
binlog-ignore-db=mysql //忽略mysql数据库复制
#mysql -uroot -pivgivgivg //登陆
mysql>grant replication slave on *.* to 'copy'@'192.168.11.12' identified by 'ivgivgivg';//在master机上为slave机添加一同步帐号
mysql>flush privileges; //生效
重启master机的mysql服务：service mysqld restart
#mysql -uroot -pivgivgivg //登陆
mysql>show master status //查看主日志情况
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107 | test | mysql |
+------------------+----------+--------------+------------------+
从服务器配置
mysql5.1.7版本以后已经不支持把master配置属性写入my.ini文件中了，只需把同步的数据库和需要忽略的数据库写入
vi /etc/my.cnf
# Replication Slave
server-id=2
log-bin=mysql-bin
replicate-do-db=test
replicate-ignore-db=mysql
重启mysql服务：service mysqld restart
#mysql -uroot -pivgivgivg //登陆
mysql> stop slave; //停止从服务线程
mysql> change master to master_host='192.168.11.11',master_user='copy',master_password='ivgivgivg'; //配置连接主服务器配置
mysql> start slave;//启动从服务线程
mysql> show slave status\\G;//查看从服务器状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.11
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: node2-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 555
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR:
No query specified
-------------------------------------------------------
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这俩个都是yes就表示正常
对于已经存在的库的操作:
主数据库机:
登陆mysql,主机阻断写操作:
mysql>FLUSH TABLES WITH READ LOCK;
查看主服务器的状态:
mysql>SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1529881 | openser | mysql,test |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
导出需要同步的库:
mysqldump -u root -p123456 --opt -R openser > openser20121203.sql
解锁:
mysql> UNLOCK TABLES;
把备份好的库传到备用数据库
备数据库机:
登陆mysql,创建同名库:
mysql> CREATE DATABASE `openser` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
导入数据库:
mysql -u root -p123456 openser < openser20121127.sql
mysql>SLAVE STOP;
mysql>reset slave;
mysql>CHANGE MASTER TO MASTER_HOST='192.168.21.26',
mysql>MASTER_USER='repl_user',
mysql>MASTER_PASSWORD='123456',
mysql>MASTER_LOG_FILE='mysql-bin.000002',
mysql>MASTER_LOG_POS=1529881;
mysql>start slave;
mysql>show slave status\\G
