Mysql主从机配置2

Mysql Duplicate（主从机）配置

 

好久没来写博客了，发现人都懒了。呵呵！记得去年研究完mysql duplicate之后就懒得再弄，最近把它用在生产环境，发现还不错。数据库安全了很多。

由于mysql网上说集群需要至少要5－6台机器，而且性能提高不大，不适合规模较小的应用。所以我们采用的是主从机方案，可以一定程度上满足要求，从机平时用来作备份，及数据分析，主机用来生产。以下详细记录一下过程，为已后留个记录。

一、首先需要做的是在两台机器上安装mysq，版本要一致，mysql官方说duplicate在不断改进过程中，所以版本要一致。

二、需要确保数据库及表内容一致。我当时直接将生产机上(需要程序都停下来)的所有内容，连数据库，及表，scp拷贝到从机上。

三、在主机上创建主从机专用用户。命令如下：

GRANT REPLICATION SLAVE ON *.* TO 'xxx'@'%192.168.1.57' IDENTIFIED BY 'xxx‘;

FLUSH PRIVILEGES;

四、主机修改配置文件（实际上不修改）

server-id = 1

log-bin=mysql-bin

五、从机修改配置文件

由于从机原来配置也是主机形式，需要注掉上面俩行。

#server-id = 1

#log-bin=mysql-bin

server-id = 2

master-host = 192.168.1.1

master-user = xxx

master-password = xxx

master-port = 3306

report-host = 192.168.1.2 

六、启动主从机，查寻状态

SHOW MASTER STATUS

SHOW SLAVE STATUS

如果主机显示类似如下状态Position不为0，表示正确。

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

|mysql-bin.000006 | 7809 | |

如果发现从机 Last_SQL_Errno， Last_SQL_Error: 出现什么异常，需要设定忽略。

STOP SLAVE

SET GLOBAL SQL_slave_SKIP_COUNTER = n ；

START SLAVE

如果看见如下俩行内容，说明slave从机工作正常。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

六、其他命令：

显示主机状态命令

SHOW MASTER STATUS

显示主从状态命令

SHOW SLAVE STATUS

开始从机工作

START SLAVE

停止从机工作

STOP SLAVE

设定跳过

SET GLOBAL SQL_slave_SKIP_COUNTER = n ；

显示连入主机的从机

show slave hosts

 

七、还找其它配置及命令

配置：

report-host xxx.xxx.xx.xxx 设置之后，主机可以用show slave hosts查到从机

忽略错误

slave-skip-errors=1062,1053

忽略所有错误

slave-skip-errors=all

复制特定数据库（可以设定多个）

replicate-do-db=db_name

忽略 复制特定数据库

replicate-ignore-db=db_name

复制特定表

replicate-do-table=db_name.tbl_name

忽略复制特定表

replicate-ignore-table=db_name.tbl_name

 

同上但可以加模糊匹配‘% ’和‘_ ’

replicate-wild-do-table=db_name.tbl_name

replicate-wild-ignore-table=db_name.tbl_name

 

 

mysql  主从复制读写分离实现

作者:田逸(

[e-mail]

)

 

mysql主从复制

(一)安装mysql(主从服务器皆相同) 先创建用户 useradd mysql -s /sbin/nologin

 

tar zxvf mysql-5.0.45.tar.gz

cd mysql-5.0.45

./configure --prefix=/usr/local/mysql  --localstatedir=/opt/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client

注:配置过程指定数据文件的位置及另加的字符集.

make

make install

cp support-files/my-large.cnf   /etc/my.cnf

cd /usr/local/mysql

chgrp -R mysql .

生成基本的数据库和表:  /usr/local/mysql/bin/mysql_install_db --user=mysql

成功执行后察看数据目录/opt/data,看是否有文件或目录生成.

chown -R mysql:mysql /opt/data

记得给每个数据库设置root密码.

(二)修改配置文件

不同的地方就是server-id,主服务器配置文件不用修改,从服务器的配置文件server-id=10.其他的内容基本相同.

(三)启动服务

/usr/local/mysql/bin/mysqld_safe --user=mysql&

这个过程主辅服务器都相同.

(四)授权(在主服务器上进行)

GRANT REPLICATION SLAVE ON *.* to 

[url=mailto:]'rep1'@'192.168.8.100[/url]

identified by 'mylqs';

(五)查询主数据库状态(主服务器上进行)

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 |      235 |              |                  | 

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

记下file及position的值,后面做从服务器操作的时候需要用.

(六)配置从服务器

mysql> change master to master_host='192.168.8.101', master_user='rep1', master_password='mysql', master_log_file='mysql-bin.000003', master_log_pos=235;

正确执行后再执行:

mysql> start slave;

就启用了复制功能.这里我们运行一下 mysql> show slave status/G 来检查一下,一个正常的输出结果应该如下面的形式:

mysql> show slave status/G

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

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.8.101

                Master_User: rep1

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000003

        Read_Master_Log_Pos: 235

             Relay_Log_File: -relay-bin.000009

              Relay_Log_Pos: 235

      Relay_Master_Log_File: mysql-bin.000003

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

            Relay_Log_Space: 235

            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

1 row in set (0.00 sec)

请注意:slave_IO进程及slave_SQL进程都必须正常运行,在状态输出重表现为:   Slave_IO_Running: Yes  及Slave_SQL_Running: Yes 否则都是不正确的状态(如一个值Yes,另外一个是NO则不行).

(七)主数据库有数据的情况: 

1、数据库锁表操作，不让数据再进行写入动作。mysql> FLUSH TABLES WITH READ LOCK；

2、察看主数据库的状态 mysql> show master status; 照第（五）步记录输出值。

3、把主服务器数据文件复制到从服务器，最好先用tar处理一下。

4、取消主数据库锁定 mysql> UNLOCK TABLES； 

5、从服务器的操作。跟前面的步骤一样（略过）

 

mysql代理安装配置

 

一、安装mysql-proxy.需要按下列顺序安装其所依赖的包:

(一)安装LUA

tar zxvf lua-5.1.tar.gz

cd lua-5.1

用vi修改Makefile,使"INSTALL_TOP=/usr/local/lua",这样做的目的是为了是lua的所有文件都安装在目录/usr/local/lua/

make posix

make install

 

(二)安装 libevent

tar zxvf libevent-1.1a.tar.gz

cd libevent-1.1a

./configure --prefix=/usr/local/libevent

make

make install

 

(三)安装check

tar zxvf check-0.8.4.tar.gz

cd check-0.8.4

./configure

make

make install

 

(四)设置安装mysql-proxy所需的环境变量.把下面的内容追加到/etc/profile中

export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm"

export CPPFLAGS="-I/usr/local/libevent/include"

export CFLAGS="-I/usr/local/libevent/include"

然后执行 source /etc/profile (安装完mysql-proxy不再需要这些变量,可以删除之)

 

(五)安装mysql(只安装mysql客户端即可)

tar zxvf  mysql-5.0.45.tar.gz

cd mysql-5.0.45

./configure --prefix=/usr/local/mysql  --without-server

make

make install

 

(六)安装mysql-proxy

tar zxvf mysql-proxy-0.5.0.tar.gz

cd mysql-proxy-0.5.0

./configure –prefix=/usr/local/mysql-proxy  --with-mysql=/usr/local/mysql  --with-lua

Make

Make install

 

二、主要的命令行选项

--help-all显示所有的帮助选项

--admin-address=host:port 管理主机及端口，默认是4041

--proxy-address=host:port 代理服务器的监听地址及端口，默认4040

--proxy-read-only-address=host:port 只读连接时，代理服务器的监听地址及端口。默认4042

--proxy-backend-addresses=host:port连接真实服务器的地址及监听端口，默认是3306，这是mysql代理最重要的选项，多个主机之间用空格隔开。使用rr算法。

--proxy-lua-script=file 指定lua脚本的名称

 

三、 使用方法

2个mysql服务器的情形

mysql-proxy /

     --proxy-backend-addresses=mysql_ip1:3306 /

     --proxy-backend-addresses=mysql_ip2:3306

3个服务器的情形：一个主服务器，负责写入；2个从服务器，负责查询。

mysql-proxy --proxy-backend-addresses= :3306/

         --proxy-read-only-address=:3306 /

         --proxy-read-only-address=:3306

 

四、mysql-proxy启动

编写脚本/usr/local/bin/mysql-proxy.sh其内容如下：

#！/bin/bash

LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" mysql-proxy --proxy-read-only-backend-addresses=192.168.1.38:3306 --proxy-

backend-addresses=192.168.1.33:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua >> /var/log/mysql-proxy.log &

chmod 755 /usr/local/bin/mysql-proxy.sh; 执行命令/usr/local/bin/mysql-proxy.sh启动服务

 

 

配置验证

1、主从复制测试：在主数据库服务器上创建库和表，然后再插入记录，再登陆到从服务器，看是否也建立相一致的库和表以及记录。

mysql> create database first_db;

Query OK, 1 row affected (0.01 sec)

在主数据库服务器创建库first_db

 

mysql> create table first_tb(id int(3),name char(10));

Query OK, 0 rows affected (0.00 sec)

在主数据库服务器创建表first_db

 

mysql> insert into first_tb values (001,'myself');

Query OK, 1 row affected (0.00 sec)

在主数据服务器的表first_db中插入记录

现在转到从数据库服务器，看是否同步了上面主数据库的数据

mysql> show databases;

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

| Database           |

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

| information_schema | 

| first_db           | 

| mysql              | 

| test               | 

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

4 rows in set (0.01 sec)

数据库自动生成了

 

mysql> use first_db;

Database changed

mysql> show tables;

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

| Tables_in_first_db |

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

| first_tb           | 

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

1 row in set (0.00 sec)

表也自动生成了

 

mysql> select * from first_tb;

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

| id   | name   |

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

|    1 | myself | 

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

1 row in set (0.00 sec)

记录也按照我们的意愿存在了

2、读写分离：用mysql客户端程序如mysql登陆到mysql代理，然后执行读写操作，以测试读写分离的正确性。

 

 

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。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

 

设置主从复制：

 

1、确保在主服务器和从服务器上安装的MySQL版本相同，并且最好是MySQL的最新稳定版本。

 

2、在主服务器上为复制设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。如果账户仅用于复制(推荐这样做)，则不需要再授予任何其它权限。

 

mysql> GRANT REPLICATION SLAVE ON *.*

 

-> TO 'replication'@'%.yourdomain.com' IDENTIFIED BY 'slavepass';

 

3、执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句：

 

mysql> FLUSH TABLES WITH READ LOCK；

 

保持mysql客户端程序不要退出。开启另一个终端对主服务器数据目录做快照。

 

shell> cd /usr/local/mysql/

 

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

 

如果从服务器的用户账户与主服务器的不同，你可能不想复制mysql数据库。在这种情况下，应从归档中排除该数据库。你也不需要在归档中包括任何日志文件或者master.info或relay-log.info文件。

 

当FLUSH TABLES WITH READ LOCK所置读锁定有效时（即mysql客户端程序不退出)，读取主服务器上当前的二进制日志名和偏移量值：

 

mysql > SHOW MASTER STATUS;

 

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

 

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

 

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

 

| mysql-bin.003 | 73 | test | manual,mysql |

 

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

 

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

 

如果主服务器运行时没有启用--logs-bin，SHOW MASTER STATUS显示的日志名和位置值为空。在这种情况下，当以后指定从服务器的日志文件和位置时需要使用的值为空字符串('')和4.

 

取得快照并记录日志名和偏移量后，回到前一中端重新启用写活动：

 

mysql> UNLOCK TABLES；

 

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

 

[mysqld]

 

log-bin

 

server-id=1

 

如果没有提供那些选项，应添加它们并重启服务器。

 

5、停止从服务器上的mysqld服务并在其my.cnf文件中添加下面的行：

 

[mysqld]

 

server-id=2

 

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

 

6、将数据备据目录中。确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件，如同在主服务器上一样。

 

Shell> chown -R mysql:mysql /usr/local/mysql/data

 

7、启动从服务器。在从服务器上执行下面的语句，用你的系统的实际值替换选项值：

 

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;

 

8、启动从服务器线程：

 

mysql> START SLAVE；

 

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

 

9、如果出现复制错误，从服务器的错误日志（HOSTNAME.err）中也会出现错误消息。

 

10、从服务器复制时，会在其数据目录中发现文件master.info和HOSTNAME- relay-log.info。从服务器使用这两个文件跟踪已经处理了多少主服务器的二进制日志。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。

 

 

环境:

主服务器:centos 5.2 mysql 5.1.35 源码 IP:192.168.1.22

从服务器:centos 5.2 mysql 5.1.35 源码 IP:192.168.1.33

配置:

一、主服务器

1.1、创建一个复制用户，具有replication slave 权限。

mysql>grant replication slave on *.* to ‘repl’@’192.168.1.22′ identified by ‘repl’;

1.2、编辑my.cnf文件

vi /etc/my.cnf

添加

server-id=1

并开启log-bin二进制日志文件

log-bin=mysql-bin

注:需要把默认的server-id=1去掉

1.3、启动mysql数据库

mysqld_safe –user=mysql &

1.4、设置读锁

mysql>flush tables with read lock;

1.5、得到binlog日志文件名和偏移量

mysql>show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.0000010 | 106| | |

+——————+———-+————–+——————+

1.6、备份要同步的数据库

mysqldump test > test.sql

1.7、解锁

mysql>unlock tables;

 

二、从服务器

2.1、编辑my.cnf文件

vi /etc/my.cnf

添加

server-id=2

注:需要把默认的server-id=1去掉

2.2、启动从数据库

mysqld_safe –user=mysql &

2.3、对从数据库进行相应设置

mysql> change master to

-> master_host=’192.168.1.22′

-> master_user=’repl’

-> master_password=’repl’

-> master_log_file=’mysql-bin.0000010′

-> master_log_pos=106;

2.4、启动从服务器slave线程

mysql>start slave;

执行show processlist命令显示以下进程:

mysql>show processlist/G

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

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 2579

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL表示slave已经连接上master，开始接受并执行日志

2.5、查看slave线程状态

mysql>show slave status;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.22

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.0000010

Read_Master_Log_Pos: 106

Relay_Log_File: centos-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.0000010

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

Relay_Log_Space: 830

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

 

验证是否配置正确

在从服务器上执行

show slave status/G;

Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如以上二行同时为Yes 说明配置成功

 

测试

1、在主服务器test数据库中创建user表

mysql>use test;

mysql>create table user(id int);

2、在从服务器中查看user表

mysql>use test;

mysql> show tables like ‘user’;

+———————-+

| Tables_in_test(user) |

+———————-+

| user |

+———————-+

1 row in set (0.00 sec)

说明主从数据同步成功。

 

问题?

1.在从数据库中查看slave状态时出现

The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

说明方从服务器里my.cnf中的server-id有相同的。

解决办法:

修改my.cnf里的server-id，并重启数据库服务。my.cnf文件默认有server-id=1

 

其它说明

主服务器my.cnf

#binlog-do-db=需要备份的数据库名，可写多行

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

从服务器my.cnf

# replicate-do-db=test 需要备份的数据库名

# replicate-ignore-db=mysql 忽略的数据库

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

以下设置也可直接修改my.cnf配置文件

log-bin=mysql-bin

master-host=192.168.1.22

master-user=repl

master-password=repl

master-port=3306

 

主从服务器同步维护

由于各种原因，导致主从数据不一致，在负载低的时候，进行手动同步.

在主服务器上执行

 

mysql>flush tables with read lock;

Query OK,rows affected (0.01 sec)

mysql>show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.0000011 | 260| | |

+——————+———-+————–+——————+

在从服务器上执行

先得到当前主服务器的二进制文件名和偏移量,执行命令使从服务器与主服务器同步

mysql>select master_pos_wait(‘mysql-bin.0000011′,’260′);

+————————————————–+

| master_pos_wait(‘mysql-bin.0000011′,’260′) |

+————————————————–+

| 0 |

+————————————————–+

1 row in set (0.01 sec)

同步完成后，在主服务器上执行解锁

mysql>unlock tables;

 

切换主从服务器

 

当主服务器出现故障时，可将从服务器当主服务器来使用.步骤如下:

1、保证所有从数据库都已经执行了relay log中的全部更新，在从服务器中执行

stop slave io_thread,用show processlist检查，查看状态是否是Has read all relay log,表示更新完成.

mysql>stop slave io_thread;

Query OK,0 affected (0.00 sec)

mysql>show processlist/G;

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

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 4757

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

2、在从服务器上执行stop slave，reset master命令，重置成主数据库

mysql>stop slave;

Query OK,0 affected (0.00 sec)

mysql>reset master;

Query OK,0 affected (0.00 sec)

3、删除新的主服务器数据库目录中的master.info和relay-log.info文件，否则下次重启时还会按照从服务器来启动.