标题: [转载] Mysql的复制功能Replication Database
  本主题被作者加入到个人文集中  
darkness (暗夜之王)
资源发布者
Rank: 8Rank: 8
For Everyone~


UID 622
精华 5
积分 9233
帖子 2030
威望 0
金钱 3629
阅读权限 100
注册 2007-3-29
状态 离线
发表于 2008-10-16 17:44  资料  个人空间  短消息  加为好友  QQ
Mysql的复制功能Replication Database

Mysql的复制功能Replication Database
Mysql 数据库相信大家已经投入了生产使用。很多人都将他和 PHP 集成在 Apache 中,为WebSite 服务。的确,他们在WebSite 中的应用比较多,而且PhpMyAdmin 又是一个PHP+Mysql 的最好应用例子。
那么Mysql 能不能实现两个系统之间通过TCP/IP去复制数据库?能不能实现实时复制呢?也就是说能不能实现同步(Synchronization)的问题。先概括介绍一下Mysql 的Replication Database功能。
复制(Replication)类似于拷贝数据库到另一台服务器上,但它是通过定义Master 和Slave的关系去实时地保证两个数据库的完全同步。这个功能在Mysql的3.23版中开始出现。
下面大家一起来测试一下Mysql的Replication 功能。

作者的平台是:

Master:Mysql 3.23.53-log on FreeBSD 4.7 Release IP:192.168.10.100
Slave: Mysql 3.23.56-log on FreeBSD 4.8 Stable IP:192.168.10.200
1、Master 机器设置权限,赋予Slave Relication 权利,并打包要同步的数据库结构。

MasterBSD# pwd
/usr/local/mysql/bin
MasterBSD#./mysql –u root –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.53-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT FILE ON *.* TO replication@192.168.10.200 IDENTIFIED BY ‘repplication’;

(赋予192.168.10.200也就是Slave 机器有File权限)
然后打包要复制的数据库

MasterBSD# cd var
MasterBSD# tar czvf repdatabase.tar.gz repdatabase
这样,我们的到一个repdatabase数据库的打包文件repdatabase.tar.gz
2设置主服务器Master的my.cnf,启动Mysql服务

MasterBSD# vi /etc/my.cnf
在[mysqld]添加或修改以下的

[mysqld]
log-bin
server-id=1
sql-bin-update-same
binlog-do-db= repdatabase
针对repdatabase 库做replication 功能
然后把Master主服务器的Mysql重启。

MasterBSD# /usr/local/mysql/bin/mysqladmin –u root –p shutdown
MasterBSD# /usr/local/mysql/bin/safe_mysqld --user=mysql &
3、建立Slave数据库
刚才我们在Master中打包了repdatabase.tar.gz,它的作用就是要在Slave恢复成一样的数据库。先把Master 的repdatabase.tar.gz文件传到Slave机器中去。然后

SlaveBSD# tar zxvf repdatabase.tar.gz -C /usr/local/mysql/var/
4、修改Slave服务器的my.cnf

SlaveBSD# vi /etc/my.cnf
在[mysqld]添加或修改以下的

master-host=192.168.10.100
master-user=replication
master-password=replication
master-port=3306
server-id=2
master-connect-retry=60
replicate-do-db=reldatabase    
log-slave-updates
5、重启动Slave的slave start。

SlaveBSD# /usr/local/mysql/bin/mysqladmin –u root –p shutdown
SlaveBSD# /usr/local/mysql/bin/safe_mysqld --user=mysql &
6、测试
先检测两个Mysql数据库中的repdatabase是否正常。
正常情况应该是Master和Slave 中的Mysql 都有相同的repdatabase 数据库,并且里面的数据都一样。
然后我们测试replication 功能是否起用。
在Master中的repdatabas数据库添加一笔数据:

MasterBSD# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 3.23.53-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use repdatabase;
Database changed
mysql> INSERT INTO `rep_table` ( `name` , `num` , `selectd ` ) VALUES ('test1', '4321', 'Y');
Query OK, 1 row affected (0.00 sec)
mysql>

在Slave的数据库中应该也会同样有这样一条数据

SlaveBSD# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 3.23.56-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from repdatabase.rep_table;
+--------+-----------+------------+
| name | num | selectd |
+--------+-----------+------------+
| aaa | 44444 | N |
| ddd | 111112222 | N |
| insert | 1234 | N |
| test | 12345 | N |
| test1 | 4321 | Y |    这一行就是Master插入的时候Slave 同步得回来的数据。
+--------+-----------+------------+
5 rows in set (0.01 sec)
mysql>

到此,我们的两个数据库replication 功能实验成功。

7、互为replication
在Mysql 的文档资料中也指出了,一台Mysql机器同样可以作为Master也可以作为Slave的,也可以互相replication数据。

8、应用
replication可以用在那方面呢?我的想法是,一台Mysql生产机器在提供繁忙的SQL查询,比如说是股市的查询,那它仅仅是作为查询而已。那么我们就可以通过两台机器,提供查询的机器为Slave,那么数据录入的机器是Master,通过双网卡去进行,请看下图:

[attach]8117[/attach]


这样的好处是显而易见,Slave作为大量SQL查询的服务器,繁忙、任务大!而且对着Internet,破坏可能性也大!因此,引入Mysql的replication我们可以方便、安全地管理数据库!



 附件: 您所在的用户组无法下载或查看附件




You cannot teach a man anything; you can only help him find it within himself.
顶部
darkness (暗夜之王)
资源发布者
Rank: 8Rank: 8
For Everyone~


UID 622
精华 5
积分 9233
帖子 2030
威望 0
金钱 3629
阅读权限 100
注册 2007-3-29
状态 离线
发表于 2008-10-16 17:45  资料  个人空间  短消息  加为好友  QQ
做数据库的主辅库,加强数据库中数据安全性

动作:
1、主库服务器上root进入mysql
>reset master;
>start master;
>show master status;
//查看是否有错误

2、辅库服务器上root进入mysql
>stop slave;
>reset slave;
>start slave;
>show slave status\G
//查看是否已经连接成功





You cannot teach a man anything; you can only help him find it within himself.
顶部
darkness (暗夜之王)
资源发布者
Rank: 8Rank: 8
For Everyone~


UID 622
精华 5
积分 9233
帖子 2030
威望 0
金钱 3629
阅读权限 100
注册 2007-3-29
状态 离线
发表于 2008-10-16 17:50  资料  个人空间  短消息  加为好友  QQ
MySQL Master Master Replication

原文:http://www.howtoforge.com/mysql_master_master_replication
MySQL Master Master Repliction TutorialThis tutorial describes how to set up MySQLmaster-master replication. We need to replicate MySQL servers toachieve high-availability (HA). In my case I need two masters that aresynchronized with each other so that if one of them drops down, othercould take over and no data is lost. Similarly when the first one goesup again, it will still be used as slave for the live one.
Here  is a basic step by step tutorial, that willcover the mysql master and slave replication and also will describe themysql master and master replication.
Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.
Step 1: Install mysql on master 1 and slave 1. configure network services on both system, like

Master 1/Slave 2 ip: 192.168.16.4
Master 2/Slave 1 ip : 192.168.16.5

Step 2: On Master 1, make changes in my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Step 3: On master 1, create a replication slave account in mysql.
mysql> grant replication slave on *.* to 'replication'@192.168.16.5 \
identified by 'slave';
and restart the mysql master1.

Step 4: Now edit my.cnf on Slave1 or Master2 :
  [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
  
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Step 5: Restart mysql slave 1 and at
mysql> start slave;
mysql> show slave status\G;

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.4
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: MASTERMYSQL01-bin.000009
        Read_Master_Log_Pos: 4
             Relay_Log_File: MASTERMYSQL02-relay-bin.000015              Relay_Log_Pos: 3630
      Relay_Master_Log_File: MASTERMYSQL01-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         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: 4
            Relay_Log_Space: 3630
            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: 1519187
1 row in set (0.00 sec)


Above highlighted rows must be indicate related log files and  Slave_IO_Running and   Slave_SQL_Running: must be to YES.

Step 6:On master 1:
mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 |      410 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Theabove scenario is for master-slave, now we will create a slave masterscenario for the same systems and it will work as master master.

Step 7: On Master2/Slave 1, edit my.cnf and master entries into it:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-bin                     #information for becoming master added
binlog-do-db=adam

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 8: Create a replication slave account on master2 for master1:
mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave2';

Step 9:Edit my.cnf on master1 for information of its master.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

[mysql.server]user=mysqlbasedir=/var/lib

Step 10: Restart both mysql master1 and master2.
On mysql master1:
mysql> start slave;
On mysql master2:
mysql > show master status;
On mysql master 1:
mysql> show slave status\G;

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.5
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: Mysql1MYSQL02-bin.000008
        Read_Master_Log_Pos: 410
             Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
              Relay_Log_Pos: 445
      Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         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: 410
            Relay_Log_Space: 445
            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: 103799
1 row in set (0.00 sec)

ERROR:
No query specified
Check for the hightlighted rows, make sure its running. Now you cancreate tables in the database and you will see changes in slave. Enjoy!!





You cannot teach a man anything; you can only help him find it within himself.
顶部
darkness (暗夜之王)
资源发布者
Rank: 8Rank: 8
For Everyone~


UID 622
精华 5
积分 9233
帖子 2030
威望 0
金钱 3629
阅读权限 100
注册 2007-3-29
状态 离线
发表于 2009-7-2 10:54  资料  个人空间  短消息  加为好友  QQ
MySQL Replication Monitor

What is MySQL Replication

MySQL Replication is a great way to improve database performance andavailability, by creating live copies of the database on multiplemachines.

Read our article about How to setup MySQL Replication for more information about setting up replication.

There are two primary issues you have to deal with, when using MySQL replication:

#1. Replication can often break

When a query cannot process properly on a slave (due to duplicate keyor another MySQL error), replication will stop running until youmanually resume it.

To avoid replication breaking due to duplicate keys, make sure your /etc/my.cnf configuration file includes these two lines:

          
slave_exec_mode
= IDEMPOTENT
slave
-skip-error =1062  

#2. Replication can lag

Since replication is serialized (slave runs all queries on a singlethread, while master is multi threaded), the slave machine can oftenfall behind the master. This leads to inconsistency in data, especiallywhen using a master-master replication setup.

-

The issue with monitoring MySQL Replication

The most popular method to monitor MySQL replication, is to use MySQL built-in SHOW SLAVE STATUS command.

        Quote:
       
                                                        show slave status ;

               Slave_IO_State: Waiting for master to send event
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 572720266
               Relay_Log_File: api4-relay-bin.000034
                Relay_Log_Pos: 2424716
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 572720266
              Relay_Log_Space: 97695432
        Seconds_Behind_Master: 0
                                       


The two important fields are 'Slave_IO_Running' and'Seconds_Behind_Master', indicating whether the slave thread is runningand how many seconds it is lagging behind the master.

Unfortunately, the 'Seconds_Behind_Master' estimate is very unreliable,especially when you have a slow network or when the master/slaveservers are not running on the same LAN.

The reason for the inaccuracy lies in the way MySQL calculates'Seconds_Behind_Master'. All it is doing is subtracting the position ofits internal SQL log from the IO log.

In our tests, we measured cases where a slave was 10 minutes behind themaster and yet 'Seconds_Behind_Master' returned 0 seconds.

A better approach to Monitoring MySQL Replication

A more reliable approach to measure replication lag,take advantage of MySQL SYSDATE function.

If you insert NOW at 12:00:04 on the master the row will hold exactly12:00:04 on the slave, regardless of the slave local time. However, theSYSDATE function does not follow this behavior. It always uses thevalue of the slave's system clock.

If you insert a row with one column holding the value of NOW orCURRENT_TIMESTAMP and the other holding the value of SYSDATE into themaster, you can use the difference between the two values on the slaveto see how far behind it is. If the slave is in sync the two valueswill be identical. If the slave is one second behind the column holdingSYSDATE will be one second ahead of the column holding NOW. No pollingis required to determine the current lag.

Implementation

Step 1: Create Heartbeat table

          
CREATE TABLE mysql_heartbeat
(master_host char(50) default '' not null, master_time timestamp default CURRENT_TIMESTAMP, slave_time timestamp not null, index(master_host));  

Step 2: Setup heartbeat cron

Install a script on a cronjob that will run this query on the master server, every 60 seconds

          
insert into mysql_heartbeat
(master_host, slave_time) values('127.0.0.1',SYSDATE());  

(Replace 127.0.0.1 with the master host name or ip address)

Step 3: Monitor script

Run this script to display server lag over a given time frame

          
select master_host
,master_time,timediff(slave_time,master_time)  from mysql_heartbeat where date(master_time)=date(now()) group by master_host,master_time order by master_host,master_time;  





You cannot teach a man anything; you can only help him find it within himself.
顶部
watteye
高考冲刺班
Rank: 1



UID 33931
精华 0
积分 45
帖子 2
威望 0
金钱 3
阅读权限 255
注册 2010-7-10
状态 离线
发表于 2010-12-6 14:57  资料  个人空间  短消息  加为好友 
很不错,了解下了





The roof after the rain cheap wow gold
顶部


当前时区 GMT+8, 现在时间是 2021-1-26 19:33
本论坛支付平台由支付宝提供
携手打造安全诚信的交易社区 Powered by Discuz! 5.5.0 Licensed 京ICP备17069522号-2 海淀公安分局1101082153号 © 2001-2006 Comsenz Inc.
当前时区 GMT+8, 现在时间是 2021-1-26 19:33
清除 Cookies - 联系我们 - 向北航行