mysql数据库同步

数据库的同步一直是个很重要的问题，也是一个难题，所幸mysql提供了多种方法可以用来同步
在本文中我先给出mysql本身自带的方式master-slave方式，详尽的步骤如下：

1、prepare

### 需要修改主、从服务器的my.cnf文件 ###

1) master
mysql> grant replication client,replication slave on *.* to ‘repl’@'192.168.0.*’ identified by ‘xxxxxx’

=========================================================================
### 主服务器 ###
# Replication Master Server (default)
# binary logging is required for replication
log-bin=/mysql/bin-log
log-bin-index=/mysql/bin-log.index
#binlog_cache_size = 1M
binlog_do_db = test1
binlog_do_db = test2
#binlog_ignore_db =

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 2370
=========================================================================

2) slave
mysql> grant replication client,replication slave on *.* to ‘repl’@'192.168.0.*’ identified by ‘xxxxxx’

=========================================================================
### 从服务器 ###
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
server-id = 2379
#
# The replication master for this slave - required
master-host = 192.168.0.240
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = repl
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = xxxxxx
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3306
#
# binary logging - not required for slaves, but recommended
#log-bin=/mysql/log/slavebin-log

master-info-file = /mysql/log/master.info
relay-log-info-file = /mysql/log/relay-log.info

replicate_do_db = test1
replicate_do_db = test2
#replicate_ignore_db = …
#replicate_do_table =
#replicate_ignore_table =
#replicate_wild_do_table =
#replicate_wild_ignore_table = …
#replicate_wild_ignore_table = temp\_subpost\_%
#replicate_rewrite_db=->
# 1062: dup key entry
# 1064: sql syntax
#slave_skip_errors = 1062,1064
slave_skip_errors = 1062
relay-log = /mysql/log/relay-log
relay-log-index = /mysql/log/relay-log.index
=========================================================================

### below step will start the work ###
=========================================================================
cd /opt/mysql/bin
ln -s /opt/mysql/share/mysql/mysql.server mysqlctl
=========================================================================

2、stop mysql
1) master: mysqlctl stop && ps auxww|grep mysql
2) slave: mysqlctl stop && ps auxww|grep mysql

3、start master’s mysql
mysqlctl start
ps auxww|grep mysql
mysql >flush tables;
mysql >show master status \G
mysql >reset master
mysqlctl stop

4、start slave’s mysql
mysqlctl start
ps auxww|grep mysql
mysql >stop slave
mysql >show slave status \G
mysql >reset slave;
mysqlctl stop

5、start master’s mysql
mysqlctl start
mysql >show master status \G
mysql >flush tables with read lock
mysql >show master status \G

## record the bin-log and positon ##

6、synchronization master’s datas to slave
rsync -avP “master’s datas” “slave’s datas directory”

7、start slave’s mysql
mysqlctl start –skip-slave-start
mysql >show slave status \G
mysql >change master to
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’replication_user_name’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;

## Note:slave’s file and pos must be the same with with master’s ##

8、master
mysql >unlock tables;

9、slave
mysql >show slave status \G

## Note: Seconds_behind_master=0
## Slave_IO_Running=YES,Slave_SQL_Running=YES

10、checking the slave’s database updated real-time whether or not ??

---

6 Comments

1. Trackback by

   Underage lolita pics….

   Underage pedo incest gir. Underage models. Underage girls in thongs….

2. Trackback by Shemale zipper s movie blog.

   Shemale movie post….

   Shemale sample movie clip shemale movies clips….

3. Trackback by

   Xanax no prescription….

   Buy xanax online….

4. Trackback by

   Gay animal sex….

   Gay animal sex….

5. Trackback by

   Free animal porn sites….

   Free animal porn….

6. Trackback by

   Xanax….

   Xanax presciption. Half-life of xanax. Xanax online. Xanax….