MySQL主从同步设置和同步错误处理

MySQL主从同步设置和同步错误处理  

注意：

1、两台服务器数据库版本应一致，如果不一致，从服务器的版本要高于主服务器的版本。

2、MySQL进行实时数据同步，本质上是将mysql动作同步到Slave服务器，而不是对实质的数据进行同步。所以同步开始前，两端的数据要保持一致。

Master服务器：192.168.0.1

Slave服务器：192.168.0.2

===  Master停止运行时，Mysql同步配置===

一、主服务器设置：

1、修改/etc/my.cnf 
在[mysqld]区段内加入参数 
server-id=1

log-bin 
sql-bin-update-same

2、为Slave服务器创建连接账户，用于同步

mysql>GRANT FILE ON *.* TO backup@192.168.1.2 IDENTIFIED BY ‘1234’;

3、重启mysql服务：service mysqld restart

此时因为有加入log-bin参数,因此开始有index产生了,在/var/lib/mysql目录下有.index档案纪录数据库的异动log.

二、Slave服务器设置：

1、修改/etc/my.cnf 
在[mysqld]区段加入 
master-host=192.168.0.1 
master-user=backup 
master-password=1234 
master-port=3306 
server-id=2 
master-connect-retry=60 预设重试间隔60秒 
replicate-do-db=vbb 告诉slave只做vbb数据库的更新
replicate-ignore-db=mysql #不同步的数据库
replicate-ignore-table=vbb.users #不同步vbb数据库的users表
log-slave-updates=1

2、重启Slave服务器mysql服务

三、Slave服务器从主服务器读取数据，并开启同步

1、mysql>FLUSH TABLES WITH READ LOCK; #首先执行这个，对主服务器加锁

2、mysql>LOAD DATA FROM MASTER  #语句的话，必须授予全局的 FILE 和 SELECT 权限，仅针对MyISAM引擎，对InnoDB表无用。

3、如果数据是InnoDB的，需要在服务器上将表结构和数据分开导出，然后拷贝至Slave服务器。

导出整个数据库结构和数据：mysqldump -u用户名 -p密码 -h主机 database > filename.sql
导出某个表的数据和结构：mysqldump -u用户名 -p密码 -h主机 database table > filename.sql
只导出数据库中表的数据：mysqldump -u用户名 -p密码 -h主机 -T database table > filename.sql
只导出数据库中表的结构：mysqldump -u用户名 -p密码 -h主机 -d database table > filename.sql

4、确保两端数据一致后，开启同步

mysql>slave start;

5、查看Slave服务器同步信息

mysql>show slave status\G;

可以看到，Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，这表明 Slave 的 I/O 和 SQL 线程都在正常运行。

6、对Master服务器释放锁

mysql>UNLOCK TABLES;

=== Master还在运行中，如何在不停止它的情况下配置Slave ===

注：主从服务器的配置同上。

1、首先在Master服务器查看同步日志信息

mysql>show master status;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000054 | 680 |                     | mysql |
+------------------+----------+--------------+------------------+

可以看到日志文件是：mysql-bin.000054，同步点是680

2、在Slave服务器配置同步

mysql>slave stop; # mysql默认同步开始

mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_USER='backup',MASTER_PASSWORD='1234',MASTER_LOG_FILE='mysql-bin.000054',MASTER_LOG_POS=690;  #配置同步点

mysql>slave start; #开启同步

3、查看同步信息

mysql>show slave status\G;

可以看到，Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，这表明 Slave 的 I/O 和 SQL 线程都在正常运行。

4、配置完成

 

 

MYSQL服务器复制配置

 

这是根据我之前看的MYSQL复制的文档然后自己亲自实验的过程.配置的功能比较简单.

环境:

主服务器:

redhat9

mysql 5.0.16

机器名:dbmaster

IP:192.168.0.111

从服务器:

redhat9

mysql5.0.16

机器名:dbslave

IP:192.168.0.100

 

配置过程:

整个过程与 MYSQL 文档中的复制设置步骤一一对应 , 只是具体化和标明了一些要注意的地方 .. 如果有兴趣的朋友可以对照阅读 , 会更加理解 .

 

1.确认两台服务器的MYSQL版本,用mysql –V命令查看

注意: MySQL 不同版本之间的 ( 二进制日志 )binlog 格式可能会不一样，所以最好采用相同的版本 . 如果达不到要求 , 必须要保证 Master 的版本不能高于 Slave 版本

 

2.在主服务器上为服务器设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。我为了简单没做这一步,直接用root账户.

正常的步骤是:

假定你的域为mydomain.com,想要创建用户名为repl的一个账户，从服务器可以使用该账户从你的域内的任何主机使用密码slavepass来访问主服务器。要创建该 账户，可使用GRANT语句：

mysql> GRANT REPLICATION SLAVE ON *.*   

->TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

如果想要在Slave 上有权限执行 " LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER" 语句的话，必须授予全局的 FILE 和 SELECT 权限：

mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO  'repl'@'%.mydomain.com' IDENTIFIED BY 'rep';

 

3.在主服务器上执行FLUSH TABLES WITH READ LOCK语句来刷新表,同时阻止写操作.：

mysql> FLUSH TABLES WITH READ LOCK；

然后对主服务器上的数据进行快照。(此时不要退出mysql的提示符,退出的话就解锁了.新开一个ssh窗口进行下面所说的归档打包操作)

创建快照最简单的途径是使用归档程序对主服务器上的数据目录中的数据库进行二进制备份。要使用tar来创建包括所有数据库的归档文件，进入主服务器的数据目录，然后执行命令：(注意下面的目录操作是在bash的提示符下进行,我用shell>来代表,还要注意执行的命令是在哪台服务器上)

shell> tar -cvf /tmp/mysql-snapshot.tar .

然后将归档文件复制到从服务器的/tmp目录。

在从服务器上，进入数据库的数据目录，并使用下述命令解压缩归档文件：

shell> tar -xvf /tmp/mysql-snapshot.tar

 

当FLUSH TABLES WITH READ LOCK所置读锁定有效时(这就是刚刚所说不要退出mysql提示符的原因)，读取主服务器上当前的二进制日志名和偏移量值,直接输入命令SHOW MASTER STATUS.

mysql > SHOW MASTER STATUS;

+------------------------+------------+-------------------+-----------------------+

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------------+------------+-------------------+------------------------+

| mysql-bin.000045  |     947 |             |                 |

+------------------------+------------+-------------------+-----------------------+

 

File列显示日志名，而Position显示偏移量。在该例子中，二进制日志值为mysql-bin.000045偏移量为947。记录该值。以后设置从服务器时需要使用这些值。它们表示复制坐标，告诉从服务器应从该点开始从主服务器上进行更新。

取得快照并记录日志名和偏移量后，可以在主服务器上重新启用写活动：

mysql> UNLOCK TABLES；

 

4. 确保主服务器上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项，其中master_id必须为1到232–1之间的一个正整数值。例如：

            [mysqld]

            log-bin=mysql-bin

            server-id=1

一般情况你打开my.cnf后,已经默认是这样的设置了,里面内容看起来像下面这样:

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

# 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       = 1

如果这些选项不存在,添加它们并且重启服务器.在binary logging(二进制日志)没有启用的情况下,服务器是不能称为复制主服务器的

 

5.停止从服务器

shell> mysqladmin -u root shutdown -p

其my.cnf文件中添加下面的行：

        [mysqld]

        server-id=slave_id

slave_id值同Master_id值一样，必须为1到232–1之间的一个正整数值。并且，从服务器的ID必须与主服务器的ID不相同。例如：

[mysqld]

server-id=2

如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。

注意:这里的设置与上面的有很大不同.其实默认的my.cnf中已经有相应的设置,前面已经说过,对于主服务器基本不用修改,对于从服务器,修改也很简单.(其实文件中说的满清楚的,E文好的应该没问题)

必须将主服务器打开的那两个选项注释掉

# Replication Master Server (default)

# binary logging is required for replication

# log-bin=mysql-bin

# 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     = 1

然后把从服务器的对应的选项打开

# Replication Slave (comment out master section to use this)

……(省略)

# 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       = 2 (对于多个从服务器,就依此类推,2,3,4…)

 

6.如果对主服务器的数据进行二进制备份，启动从服务器之前将它复制到从服务器的数据目录中。确保对这些文件和目录的权限正确。你用来运行从服务器的系统帐号必须能够读写这些文件，就如同在主服务器上一样。

 

7.启动从服务器。

mysqld_safe --user=mysql &

不同服务器的配置可能不一样,一般会在/etc/rc.d/rc.local中有写入.

如果前面已经复制了，用--skip-slave-start选项启动从服务器，以便它不立即尝试连接主服务器。你也可能想要用--logs-warnings选项启动从服务器(默认设置启用)，以便在错误日志中显示更多的问题相关的信息(例如，网络或连接问题)。放弃的连接将不会记入错误日志，除非这个option的值大于1。

 

8.如果使用mysqldump备份主服务器的数据，将转储文件装载到从服务器：

shell> mysql -u root -p < dump_file.sql

我没有采用mysqldump这种方式备份,所以这步跳过.

 

9.在从服务器上执行下面的语句：

mysql> CHANGE MASTER TO                         

    -> MASTER_HOST='192.168.0.111',         

//写主服务器的名称或者IP

    -> MASTER_USER='root',          

 //写用来复制的帐号,前面说明过我直接用的root

    -> MASTER_PASSWORD='xxx',        

 //复制帐号的密码,这里就是指root的密码

    -> MASTER_LOG_FILE='mysql-bin.000045',          

 //之前记录的日志名

    -> MASTER_LOG_POS=947;            

 //之前记录的偏移量

返回:

Query OK, 0 rows affected (0.01 sec)

下面的表显示了字符串选项的最大长度：

Master_Host	60
Master_USER	16
Master_PASSWORD	32
Master_Log_File	255

 

10.启动从服务器线程：

   mysql> START SLAVE；

执行这些程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。

 

测试:

在从服务器上查看

mysql> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.111

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000045

Read_Master_Log_Pos: 1064

Relay_Log_File: dbpi-relay-bin.000002

Relay_Log_Pos: 352

Relay_Master_Log_File: mysql-bin.000045

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: 1064

Relay_Log_Space: 352

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

红色标示显示出从服务器的两个线程已经启动.

 

在从服务器上可以查看线程运行状态

mysql> SHOW PROCESSLIST\G

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

     Id: 3

   User: root

   Host: localhost

     db: test

Command: Query

   Time: 0

  State: NULL

   Info: SHOW PROCESSLIST

*************************** 2. row ***************************

     Id: 18

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 43

  State: Waiting for master to send event

   Info: NULL

*************************** 3. row ***************************

     Id: 19

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 4294966771

  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)

 

在主服务器上做一个update的语句,从服务器立刻就会同步更新.配置完成.

 

说明:由于从服务器是通过读主服务器的二进制日志来实现自我更新的,所以对于对数据库进行修改的操作都要放在主服务器上执行,而从服务器只用来进行查询.(也就是只读不写的数据库操作).