Linux下单表同步

相关介绍：

 

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。

为什么使用主从复制？

1、主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。

2、通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。但是不要同时在主从服务器上进行更新，这样可能引起冲突。

3、使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。

MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让主服务器发送二进制日志。主服务器创建一个线程将二进制日志中的内容发送到从服务器。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，从服务器使用此线程读取中继日志并执行日志中包含的更新。SHOW PROCESSLIST语句可以查询在主服务器上和从服务器上发生的关于复制的信息。

默认中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为host_name-relay-bin.index。在默认情况，这些文件在从服务器的数据目录中被创建。中继日志与二进制日志的格式相同，并且可以用mysqlbinlog读取。当SQL线程执行完中继日志中的所有事件后，中继日志将会被自动删除。

从服务器在数据目录中另外创建两个状态文件--master.info和relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

 

本实验环境：两台主机

主机A:主机名：NodeA

eth0 192.168.0.119

主机B:主机名：NodeB

eth0 192.168.0.120

 

 

安装Mysql：

先将软件包放在Linux服务器上/usr/local/src/mysql下

其中包含：mysql-5.1.45.tar.gz

 

l  解压软件包

# cd /usr/local/src/mysql

       # tar xzvf mysql-5.1.45.tar.gz

 

l  编译安装源码mysql

 

       # groupadd mysql

# useradd -g mysql mysql

# cd mysql-5.1.45

#./configure --prefix=/usr/local/mysql --enable-local-infile  --with-charset=utf8  --with-extra-charsets=all --enable-thread-safe-client  --enable-assembler --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock --with-plugins=innobase

 

编译参数解释：

       -------------------------------------------------------------

       --prefix=/usr/local/mysql               //指定安装位置

--enable-local-infile                  //支持本地数据

--with-charset=utf8                    //默认安装字符集

--with-extra-charsets=all            //添加多语言支持

--enable-thread-safe-client          //安全线程链接

--enable-assembler                   //一些字符串使用汇编语言版本

--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock  //使unix套接字链接提高7.5%性能

--with-plugins=innobase          //安装innodb引擎

--------------------------------------------------------

 

 

# make

       # make install

 

l  配置mysql

# cp support-files/my-medium.cnf /etc/my.cnf

# 修改 /etc.my.cnf  将skip-locking替换成skip-external-locking

# cd /usr/local/mysql

# bin/mysql_install_db --user=mysql

# mkdir tmp

# chown -R root  .

# chown -R mysql var

# chown -R mysql tmp

# chgrp -R mysql .

# bin/mysqld_safe &     //以mysql用户启动数据库。

 

l  同样步骤在slave机器上安装完毕mysql

 

 

    

配置Mysql主从单向复制：

l  配置master mysql

 

# vim /etc/my.cnf

[mysqld]段添加：

---------------------------------------------------------------

server-id=1            # server-id 是主机的标识，确定其为整数并且唯一，一般设置为 1 。

 

log-bin                # 设置需要记录log 可以设置log-bin=/var/mysql/bakmysqllog ，并且要确保，该文件可读写。设置日志文件的目录，其中mysqllog是日志文件的名称，mysql将建立不同扩展名，文件名为mysqllog的几个日志文件。

 

#binlog-do-db=testdb    # 指定需要备份的数据库，多个写多行

#read-only=0           # 主机读写都可以

#binlog-ignore-db=mysql  # 不需要备份的数据库，多个写多行

---------------------------------------------------------------

:wq!

 

配置文件修改完毕保存该配置并且删掉 mysql 的 data 目录下除数据库以外的文件。主要是 *.00000* 类似的文件以及 master.info ，为避免出错，一定要先删除。

 

l  配置slave mysql

# vim /etc/my.cnf

[mysqld]段添加：

---------------------------------------------------------------

server-id = 2                   # 是从机的标识，确定其为整数并且唯一，一般设定为 2 ；

master-host=192.168.0.119      # 指主机 master 的 IP 地址；

master-user=xp                 # 是指用于同步的帐号；

master-password=1234           # 是指同步帐号的密码；

master-port=3306               # 是指同步的两台 MySQL 服务器使用的端口号，默认为 3306

master-connect-retry=60        # 从服务器发现主服务器断掉，重新连接的时间差 ( 秒 )

replicate-do-db=testdb         # 指定要同步的数据库，有多个就写多行。

---------------------------------------------------------------

wq!

 

 

配置文件修改完毕保存该配置并且删掉 mysql 的 data 目录下除数据库以外的文件。主要是 *.00000* 类似的文件以及 master.info ，为避免出错，一定要先删除。

 

 

 

l  创建要备份的数据库(主机从机分别创建要同步数据库和表)

 

主机上：

# /usr/local/mysql/bin/mysql -uroot

mysql>create database testdb;

mysql>use testdb;

mysql>CREATE TABLE students(

 sn INT,

 name  VARCHAR(20)

 )

 

mysql>insert into students values(01,’xupeng’);

 

从机上：

# /usr/local/mysql/bin/mysql -uroot

mysql>create database testdb;

mysql>CREATE TABLE students(

 sn INT,

 name  VARCHAR(20)

 )

 

 

l  创建用于同步的用户并赋予其操作权限(主机从机都要执行)

 

# /usr/local/mysql/bin/mysql -uroot

mysql>GRANT REPLICATION SLAVE ON *.* TO 'xp'@'%' IDENTIFIED BY '1234';

 

语句解释：

grant           是赋予权限的关键字

replication     操作数据库权限：互相复制的权限

*.*             指的是 MySQL 服务器中所有的数据库下所有数据表，在此如果只需要同步数据库 testdb 下的所有表，可将其修改为 testdb.* 。

 

xp              是用于同步的帐号，其密码为 1234 。

 

% 表示的是所有 IP 地址，为了安全也可以指定某个 IP 地址，主机上写从机的 IP:192.168.0.119 ，从机上写主机的 IP 地址： 192.168.0.120

 

 

mysql> flush privileges;

 

 

l  测试同步状态

 

配置完毕后，重启主从机，首先重启主机再重启从机。然后执行如下命令测

试同步状态：

 

主机 master ：

mysql > show master status;

记住字段 file 和 position 的值，在 slave 状态中需要检验的。

 

从机 slave ：

mysql > show slave status\G;

 

 

 

特别提示：检查参数 Slave_IO_State 、 Slave_IO_Running 、 Slave_SQL_Running

 

Slave_IO_State 的状态值是由 Slave_IO_Running 的值决定的，如果

Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes ，并且

Slave_IO_State 的值为 :Waiting for master to send event ，那么说

明配置是没问题的可以进行下一步同步尝试了。

 

最后如果看到Slave的testdb.studens中有刚才在Master中

insert的数据则表示同步成功！

 

 

 

配置Mysql主从双向复制：

l  配置master mysql

 

# vim /etc/my.cnf

[mysqld]段添加：

---------------------------------------------------------------

server-id=1            # server-id 是主机的标识，确定其为整数并且唯一，一般设置为 1 。

 

log-bin                # 设置需要记录log 可以设置log-bin=/var/mysql/bakmysqllog ，并且要确保，该文件可读写。设置日志文件的目录，其中mysqllog是日志文件的名称，mysql将建立不同扩展名，文件名为mysqllog的几个日志文件。

master-host=192.168.0.120      # 指主机 master 的 IP 地址；

master-user=xp                 # 是指用于同步的帐号；

master-password=1234           # 是指同步帐号的密码；

master-port=3306               # 是指同步的两台 MySQL 服务器使用的端口号，默认为 3306

master-connect-retry=60        # 从服务器发现主服务器断掉，重新连接的时间差 ( 秒 )

replicate-do-db=testdb         # 指定要同步的数据库，有多个就写多行。

#binlog-do-db=testdb    # 指定需要备份的数据库，多个写多行

#read-only=0           # 主机读写都可以

#binlog-ignore-db=mysql  # 不需要备份的数据库，多个写多行

---------------------------------------------------------------

:wq!

 

 

 

配置文件修改完毕保存该配置并且删掉 mysql 的 data 目录下除数据库以外的文件。主要是 *.00000* 类似的文件以及 master.info ，为避免出错，一定要先删除。

 

l  配置slave mysql

# vim /etc/my.cnf

[mysqld]段添加：

---------------------------------------------------------------

server-id = 2                   # 是从机的标识，确定其为整数并且唯一，一般设定为 2 ；

master-host=192.168.0.119      # 指主机 master 的 IP 地址；

master-user=xp                 # 是指用于同步的帐号；

master-password=1234           # 是指同步帐号的密码；

master-port=3306               # 是指同步的两台 MySQL 服务器使用的端口号，默认为 3306

master-connect-retry=60        # 从服务器发现主服务器断掉，重新连接的时间差 ( 秒 )

replicate-do-db=testdb         # 指定要同步的数据库，有多个就写多行。

log-bin                        # 设置需要记录log 可以设置log-bin=/var/mysql/bakmysqllog ，并且要确保，该文件可读写。设置日志文件的目录，其中mysqllog是日志文件的名称，mysql将建立不同扩展名，文件名为mysqllog的几个日志文件。

---------------------------------------------------------------

wq!

 

 

配置文件修改完毕保存该配置并且删掉 mysql 的 data 目录下除数据库以外的文件。主要是 *.00000* 类似的文件以及 master.info ，为避免出错，一定要先删除。

 

 

 

l  创建要备份的数据库(主机从机分别创建要同步数据库和表)

 

主机上：

# /usr/local/mysql/bin/mysql -uroot

mysql>create database testdb;

mysql>use testdb;

mysql>CREATE TABLE students(

 sn INT,

 name  VARCHAR(20)

 )

 

mysql> insert into students values(01,'xupeng');

 

从机上：

# /usr/local/mysql/bin/mysql -uroot

mysql>create database testdb;

mysql>CREATE TABLE students(

 sn INT,

 name  VARCHAR(20)

 )

 

mysql> insert into students values(02,'xp');

 

 

 

l  创建用于同步的用户并赋予其操作权限(主机从机都要执行)

 

# /usr/local/mysql/bin/mysql -uroot

mysql>GRANT REPLICATION SLAVE ON *.* TO 'xp'@'%' IDENTIFIED BY '1234';

 

语句解释：

grant           是赋予权限的关键字

replication     操作数据库权限：互相复制的权限

*.*             指的是 MySQL 服务器中所有的数据库下所有数据表，在此如果只需要同步数据库 testdb 下的所有表，可将其修改为 testdb.* 。

 

xp              是用于同步的帐号，其密码为 1234 。

 

% 表示的是所有 IP 地址，为了安全也可以指定某个 IP 地址，主机上写从机的 IP:192.168.0.119 ，从机上写主机的 IP 地址： 192.168.0.120

 

 

mysql> flush privileges;

 

 

l  测试同步状态

 

配置完毕后，重启主从机，首先重启主机再重启从机。然后执行如下命令测

试同步状态：

 

主机 master ：

mysql > show master status;

记住字段 file 和 position 的值，在 slave 状态中需要检验的。

 

从机 slave ：

mysql > show slave status\G;

 

 

 

特别提示：检查参数 Slave_IO_State 、 Slave_IO_Running 、 Slave_SQL_Running

 

Slave_IO_State 的状态值是由 Slave_IO_Running 的值决定的，如果

Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes ，并且

Slave_IO_State 的值为 :Waiting for master to send event ，那么说

明配置是没问题的可以进行下一步同步尝试了。

 

最后如果看到Slave的testdb.studens中有刚才在Master中

insert的用户xupeng,并且Master中的testdb.studens中有刚才在Slave中

插入的用户xp则表示双向同步成功！

 

 

 

问题要点及其解决办法

 

   1.IP 变化导致数据不能同步问题

 

有时候在同步中，你会发现头一天同步正常，而第二天数据并没有同步的情况，这时就需要确认一下主从机的 IP 地址是否发生了变化。如果已经变更，则需要修改配置文件，并且删除 master.info 和 relay-log.info 之后重启服务。

 

 

2. 配置之后在查看 slave 状态时，三个关键参数值不对

 

Slave_IO_Running:No ----- 复制没有启动，执行命令 slave start io_thread 尝试启动 io 线程

 

Slave_SQL_Running:No ----- 执行命令 slave start sql_thread

 

执行完后，如果状态值还是不对，检查一下 master 中的 file 和 position 的值和 slave 状态中的 master_log_file 、 master_log_pos 的值分别是否对应。如果不对应 , 执行以下命令修改 slave 的状态值：

mysql > stop slave;

 

mysql> change master_log_file='mysql-bin.000001', master_log_pos=106;

 

如其他值也有问题，可在后面累加，以逗号隔开，命令结束时用分号结尾。

 

如果以上方法仍不能启动,则：

1.停止主,从机器的MYSQL服务.

2.在服务全停的情况下,将主,从端的所有   bin.00*  -relay-bin.00*  master.info  relay-log.info都删除

3.复制主机的/var/lib/mysql(或者说是你的数据库文件)到从机同样的位置(这一步很重要,我参考过官方网站,这一步其实就是一个数据库的SNAPSHOT数据快照,它要求用MYSQLDUMP生成个快照,但复制不是更省事吗:),相当于强制同步了一下)

4.启动MASTER,SLAVE

5.在从端mysql> SLAVE   START;