mysql 主从复制

MySQL是开源的关系型数据库系统。复制(Replication)是从一台MySQL数据库服务器（主服务器master）复制数据到另一个服务器（从服务器slave）的一个进程

首先确保主从服务器上的Mysql版本相同

系统环境 centos-5.5

mater   192.168.0.104

slave   192.168.0.102

master配置设置

yum install -y mysql*

service mysqld restart

Stopping mysqld:  [  OK  ]

Starting mysqld:  [  OK  ]        看到这个提示，证明mysql启动成功

mysqladmin  -uroot password  密码 数据库设置密码  

mysql -uroot -p

Enter password:                   输入设置的数据库密码

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.0.95 Source distribution   这一行很重要，主从mysql服务器必须版本一致

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave001‘@‘192.168.0.102‘ IDENTIFIED BY  ‘123456‘;    给与从服务器授权

mysql> flush privileges;  刷新数据库

vim /etc/my.cnf

找到   user=mysql

修改为  

user=mysql

log_bin=/var/log/mysql/mysql-bin.log  确保有这个配置文件、可写

server-id=1

read-only=0

binlog-do-db=test

binlog-ignore-db=mysql

service mysqld restart

Stopping mysqld:  [  OK  ]

Starting mysqld:  [  OK  ] 如果看到这个提示，证明主数据库配置完毕

slave服务器设置

yum install -y mysql*

service mysqld restart

Stopping mysqld:  [  OK  ]

Starting mysqld:  [  OK  ]        看到这个提示，证明mysql启动成功

mysqladmin  -uroot password  密码 数据库设置密码  

mysql -uroot -p

Enter password:                   输入设置的数据库密码

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.0.95 Source distribution   这一行很重要，主从mysql服务器必须版本一致

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

vim /etc/my.cnf

找到   user=mysql

修改为  

server-id=2

log_bin=/var/log/mysql/mysql-bin.log

master-host=192.168.0.104

master-user=slave001

master-pass=123456

master-port=3306

master-connect-retry=60

replicate-do-db =test

replicate-ignore-db=mysql

Stopping mysqld:  [  OK  ]

Starting mysqld:  [  OK  ] 如果看到这个提示，证明从数据库配置完毕

到了这一步mysql主从复制就做完了

验证

master  mysql> show master status\G;

slave   mysql> show master status\G;

会看到

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

如果看到证明，主从复制完成

mysql 主从复制,古老的榕树,5-wow.com