MySQL 主从复制

NetSeek

mysql的安装过程比较简单，这里略过

1.在maste主机上，配置my.cnf
在[mysqld]配置段添加如下字段中增加

log-bin=mysql-bin.log
binlog-do-db=blog #要同步的数据库的名字
binlog-do-db=index #要同步的数据库的名字

server-id=1


重启mysql


#service mysqld restart
进入mysql中


#mysql -u root -ppassword
增加同步帐号

mysql>grant replication slave on *.* to 'repication'@'%' identified by 'password';
mysql>use database;
mysql>flush tables with read lock;
mysql>show master status:



---------------------------------------------------------------------
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000002 |      1448 | blog,index   |                  |
+------------------+----------+------------------+------------------+

此时不要关闭此终端！
记住上表前两个字段的信息 mysql-bin.000002 和 1448 。
另开一个终端：

拿到主数据库上的“数据库快照”


#tar -czvf database.tar.gz /data/database


切换到上一个终端
mysql>unlock tables; #解锁，让主数据库192.168.1.10上的数据库正常更新

2.配置slave数据库

首先创建数据库


#mysql -uroot -ppassword
mysql>create database databasename #建一个与主服务器的数据库名字相同的数据库
mysql>exit;



3.在两个从数据库的[mysqld]配置段添加如下字段中增加


# 192.168.1.11
server-id=2
master-host=192.168.1.10
master-user=replication
master-password=password
master-connect-retry=60
replicate-do-db=blog
replicate-do-db=index




#192.168.1.12
server-id=3
master-host=192.168.1.10
master-user=replication
master-password=password
master-connect-retry=60
replicate-do-db=blog
replicate-do-db=index


3.分别重启两个数据库

#service mysqld restart


4.分别进入两个从数据库中

在salve2上

#mysql -uroot -ppassword
mysql>slave stop;
> CHANGE MASTER TO
> MASTER_HOST='192.168.1.10',
> MASTER_USER='replication',
> MASTER_PASSWORD='password',
> MASTER_LOG_FILE='mysql-bin.000002',
> MASTER_LOG_POS=1448;

MYSQL>START SLAVE;  



在salve2上

#mysql -uroot -ppassword
mysql>slave stop;
> CHANGE MASTER TO
> MASTER_HOST='192.168.1.10',
> MASTER_USER='replication',
> MASTER_PASSWORD='password',
> MASTER_LOG_FILE='mysql-bin.000002',
> MASTER_LOG_POS=1448;

MYSQL>START SLAVE;  

在slave端 ：

mysql> show processlist;
+----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time       | State                                                                 | Info             |
+----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+
|  4 | system user |           | NULL | Connect |         48 | Waiting for master to send event                                      | NULL             |
|  5 | system user |           | NULL | Connect | 4294923022 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  6 | root        | localhost | NULL | Query   |          0 | NULL                                                                  | show processlist |
+----+-------------+-----------+------+---------+------------+----------------------------------------------------------
以上信息表示同步成功！

4.同步测试过程
分别在主数据库上建立一个表，插入一条数据
在两个从数据库上看看是否更新
过程略。

linuxsir1987

很好 很清晰 呵呵～～～

edison7500

不错，学习了～～

NetSeek

转：比较详尽的MySQL Replication ,my.cnf相关配置解释:


MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器(Master)，而一个或多个其它服务器充当从服务器(Slave)，利用该特性实现读写分离，是很多大型网站常用的数据库架构。MySQL的replication的配置相对于 Oracle来说，要简单的多。本文演示了在同一台windows机器中配置单向异步复制的过程。这里的Replication是异步复制。MySQL的同步复制是MySQL Cluster中的一个特性。

要启用复制特性，MySQL必须使用二进制日志。关于二进制日志的特性，请参考官方手册（5.0,5.1,6.0）。

本例中MySQL的版本：
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.0.37-community-nt-log |
+-------------------------+
1 row in set (0.00 sec)

主库配置文件my.ini
port=3306
datadir="D:/Program Files/MySQL/MySQL Server 5.0/Data/"

server-id=1
log-bin=mysql-bin.log

从库配置文件my2.ini
port=3307
datadir="D:/Program Files/MySQL/MySQL Server 5.0/Data2/"

server-id=2

#启用从库日志，这样可以进行链式复制
log-slave-updates

#从库是否只读，0表示可读写，1表示只读
read-only=1

#只复制某个表
replicate-do-table=tablename

#只复制某些表（可用匹配符）
replicate-wild-do-table=tablename%

#只复制某个库
replicate-do-db=dbname

#只复制某些库
replicte-wild-do-db=dbname%

#不复制某个表
replicate-ignore-table=tablename

#不复制某些表
replicate-wild-ignore-table=tablename%

#不复制某个库
replicate-ignore-db=dbname

#复制完的sql语句是否立即从中继日志中清除，1表示立即清除
relay-log-purge=1

#从服务器主机，用于show slave hosts生成从库清单
report-host=hostname

启动主库
mysqld-nt --defaults-file=my.ini

连接到主库中，创建复制用户
D:\>mysql -uroot -ppassword -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.37-community-nt-log MySQL Community Edition (GPL)

mysql> grant replication slave on *.* to 'rep'@'localhost' identified by 'rep';
Query OK, 0 rows affected (0.00 sec)

锁住主库的table，以便备份数据文件到从库进行初始化
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

显示主库状态，注意记下当前二进制日志文件名和position
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      228 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

将D:/Program Files/MySQL/MySQL Server 5.0/Data/下的内容打包复制到D:/Program Files/MySQL/MySQL Server 5.0/Data2/下，执行从库的初始化。当然，初始化也可以使用mysqldump来完成。

另外开启一个cmd，启动从库
mysqld-nt --defaults-file=my2.ini

连接到从库进行配置
D:\>mysql -uroot -ppassword -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='localhost',
    -> MASTER_USER='rep',
    -> MASTER_PASSWORD='rep',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=228;
Query OK, 0 rows affected (0.01 sec)

注意到这里master_log_file和master_log_pos就是前面show master status的结果。

启动复制进程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

至此配置基本完成，在主库解开table的锁定
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

创建测试table，插入数据
mysql> use test
Database changed

mysql> create table testrep(i int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into testrep values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

然后再到从库查询
mysql> select * from testrep;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

可见数据已经从主库复制到从库。

在从库的数据目录下，有几个和复制相关的文件需要说明一下：
*-reloay-bin.* 从主库同步过来的Bin log文件，也叫中继日志
master.info 主库帐号信息和同步信息，这里记录了复制用户名和密码，需要保护好权限。
relay-log.info 跟踪执行同步过来的Bin log的执行情况

通过show processlist可以查看主从库用于复制的相关进程(在windows上实际实现为线程)的信息
主库：
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:3736
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: rep
   Host: localhost:3745
     db: NULL
Command: Binlog Dump
   Time: 68
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
2 rows in set (0.00 sec)

可以看到Id为2的进程是用于复制的进程，state可用于监控复制的状态，具体含义参考官方文档。

从库：
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:3741
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 88
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 3
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 51
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
3 rows in set (0.00 sec)

可以看到从库启动了两个复制进程，一个用于和主库交互，取得日志，另外一个则用于应用日志到从库。MySQL的复制主要是通过解析主库的二进制日志，然后再在从库应用来实现的。这种方式和Oracle Streams的本质思想是一致的。通过MySQL自带的工具mysqlbinlog，可以dump出二进制日志中的具体内容，实际上就是一条条的sql 语句：
D:\Program Files\MySQL\MySQL Server 5.0\data>mysqlbinlog mysql-bin.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#071201 13:17:31 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.37-community-nt-log created 071201 13:17
:31 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 98
#071201 13:18:43 server id 1  end_log_pos 228   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1196486323/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
grant replication slave on *.* to 'rep'@'localhost' identified by 'rep'/*!*/;
# at 228
#071201 13:24:43 server id 1  end_log_pos 89    Query   thread_id=1     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1196486683/*!*/;
insert into test values(6)/*!*/;
# at 317
#071201 13:24:43 server id 1  end_log_pos 344   Xid = 14
COMMIT/*!*/;
# at 344
#071201 13:28:00 server id 1  end_log_pos 434   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1196486880/*!*/;
create table testrep(i int)/*!*/;
# at 434
#071201 13:28:11 server id 1  end_log_pos 92    Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1196486891/*!*/;
insert into testrep values(1)/*!*/;
# at 526
#071201 13:28:11 server id 1  end_log_pos 553   Xid = 19
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

来源:http://www.ningoo.net/html/2007/ ... _configuration.html

anywhere

很祥细。学习中。

chenlinjie

请问下如果相互更新呢！

NetSeek

楼上的是需要双master相关的文档吧，你可以先google一下双master相关文档，仍后试验一下，遇到问题上坛子和大家一起讨论。

chenlinjie

这是我的配置文件
Alog-bin=mysql-bin.log
master-host=192.168.1.105
master-user=backup
master-password=1234
master-port=3306
binlog-do-db=expo3
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |       98 | expo3,expo3  |                  |
+------------------+----------+--------------+------------------+
stop slave；
change master to
   master_host='192.168.1.105',
    master_user='backup',
    master_password='1234',
    master_log_file='mysql-bin.000001',
    master_log_pos=98;
stop start； 执行这个的时候起不来
The server is not configured as slave; fix in config file or with CHANGE MASTER TO

B
log-bin=mysql-bin.log
master-host=192.168.1.103
master-user=backup
master-password=1234
master-port=3306
binlog-do-db=expo3
replicate-do-db=expo3
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 | expo3,expo3  |                  |
+------------------+----------+--------------+------------------+
change master to
   master_host='192.168.1.103',
    master_user='backup',
    master_password='1234',
    master_log_file='mysql-bin.000001',
    master_log_pos=98;

hover_sky

1. 090619  3:09:42 [Note] Slave I/O thread killed while connecting to master
   
2. 090619  3:09:42 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 295
   
3. 090619  3:09:42 [Note] Error reading relay log event: slave SQL thread was killed
   
4. 090619  3:11:34 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 295, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
   
5. 090619  3:11:34 [ERROR] Slave I/O thread: error connecting to master 'replication@192.168.1.168:3306': Error: 'Access denied for user 'replication'@'192.168.1.68' (using password: YES)'  errno: 1045  retry-time: 60  retries: 86400

复制代码

这是怎么回事呢？谢谢

hover_sky

9# hover_sky

执行grant replication slave on *.* to 'repication'@'%' identified by 'password'
弄少了一个l，哎，真粗心

hover_sky

拿到主数据库上的“数据库快照”


#tar -czvf database.tar.gz /data/database

这个“快照”有啥用途？没有用上吧？

jnulvs

楼主的，用户在设的时候写错了

moartel

想知道 生产环境中怎么实现读写分离的?
mysql-proxy   生产环境中有人用吗?

topkey

按照老大的设置。没有同步，不知道为什么？
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   75 | Connecting to master                                                  | NULL             |
|  2 | system user |           | NULL | Connect |   75 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  4 | root        | localhost | blog | Query   |    0 | NULL                                                                  | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

在第一行进程里面，楼主显示的是
Waiting for master to send event      

我的这里只显示了

Connecting to master        


我的mysql版本是Server version: 5.0.77 Source distribution

shushu1984

不能全照楼主的文档来，如果真要全照文档来的话，请检查replication 这个用户名称，楼主在配置文档中有写错的 ，楼上的可能就是这原因，表和数据没有同步过来。