mysql主从复制

实验环境：rhel 5 mysql5.5

1、配置环境。配置yum源和mysql

yum源配置这里不详细写，先把mysql配置写下。

  1. 配置mysql组

    groupadd -r mysql

     

    这里-r是指创建系统账户

    配置mysql账号，并且以安全方式运行进程

    useradd -g mysql -r -s /sbin/nologin -M -d /data/mydata mysql

     

    -g 指定mysql用户的组名，-r是指系统账户,-s shell的路径,-M指不创建家目录，-d 指定用户目录。

    配置权限

    chown -R mysql:myaql /data/mydata

    chown -R mysql:mysql /usr/local/mysql-5.5.48-linux2.6-i686

     

     

    初始化mysql

    /scripts/mysql_install_db --datadir=/data/mydata --user=mysql --basedir=/usr/local/mysql-5.5.48-linux2.6-i686

     

     

     

     

     

    mysql配置文件

    cp /usr/local/mysql/support-files/mysqld_small.cnf /etc/my.cnf

    且将datadir=/data/mydata 添加到配置文件的 mysqld的栏目下

     

    将mysqld添加到系统服务

    cp /usr/local/mysql/support-files/mysql.servier   /etc/rc.d/init.d/mysqld

    chmod +x /etc/rc.d/init.d/mysqld

    chkconfig --add  mysqld

     

     

 

  1. 配置主从复制。
    1. 主服务器上配置。my.cnf 中[mysqld]段落里面

启用二进制日志

log-bin = master-bin

log-bin-index = master-bin.index

选择一个唯一的server id

server-id = 111

建议对每个innodb表，每表一个文件

innodb_file_per_table = 1

登陆mysql，再创建用户。

创建具有复制权限的用户:replass,replass

GRANT REPLICATION SLAVE ON *.* TO 'replass'@'192.168.8.*' IDENTIFIED BY 'replass'

flush privileges;

 

  1. 在从服务器上配置。my.cnf中[mysqld]段落里面

添加如下参数

binlog_format=mixed

relay-log=relay-log

relay-log-index=relay-log.index

选择一个唯一的server  id

server-id=112
  1. 选择开始复制的起始位置以及启动进程

在主服务器上：的日志情况

mysql> show master status;

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

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| master-bin.000001 |      107 |              |                  |

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

1 row in set (0.01 sec)

 

我们再来看看日志事件。

mysql> show binlog events in 'master-bin.000001';

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

| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

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

| master-bin.000001 |   4 | Format_desc |       111 |         107 | Server ver: 5.5.48-log, Binlog ver: 4 |

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

1 row in set (0.00 sec)
 

 

也没什么重要信息，那么就从master-bin.000001 的107位置开始复制。在从服务器上交互模式运行

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.71',MASTER_USER='replass',MASTER_PASSWORD='replass',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=107;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G;

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

               Slave_IO_State:

                  Master_Host: 192.168.8.71

                  Master_User: replass

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000001

          Read_Master_Log_Pos: 107

               Relay_Log_File: relay-log.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: master-bin.000001

             Slave_IO_Running: No

            Slave_SQL_Running: No

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

              Relay_Log_Space: 107

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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 0

1 row in set (0.00 sec)

启动slave进程

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G;

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

               Slave_IO_State: Connecting to master

                  Master_Host: 192.168.8.71

                  Master_User: replass

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000001

          Read_Master_Log_Pos: 107

               Relay_Log_File: relay-log.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: master-bin.000001

             Slave_IO_Running: Connecting

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

              Relay_Log_Space: 107

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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 2003

                Last_IO_Error: error connecting to master 'replass@192.168.8.71:3306' - retry-              time: 60  retries: 86400

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 0

1 row in set (0.00 sec)

 

 出现无法连接错误，关闭master 上的iptables，对replass的账号连接可以是任意主机。这样就ＯＫ了。

mysql> update user set Repl_client_priv="Y" where User="replass";

Query OK, 1 row affected (0.07 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> update user set Host="%" where User="replass";

Query OK, 1 row affected (0.07 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> flush privileges;

Query OK, 0 rows affected (0.13 sec)

 

再看状态就正常了。

mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.8.71

                  Master_User: replass

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000001

          Read_Master_Log_Pos: 695

               Relay_Log_File: relay-log.000002

                Relay_Log_Pos: 842

        Relay_Master_Log_File: master-bin.000001

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

              Relay_Log_Space: 992

              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:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 111

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

 

 
  1. 验证工作是否正常.

    1. 在master的test库 上新建表

      mysql> create table students (id int unsigned not null auto_increment,name char(20) not null,age tinyint not null,primary key (id),unique key(name),index(age));

      Query OK, 0 rows affected (0.16 sec)

      在slave上查看表是否正常。可以看到已经正常了。

      mysql> show tables;

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

      | Tables_in_test |

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

      | students       |

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

      1 row in set (0.00 sec)

       

      mysql> desc students;

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

      | Field | Type             | Null | Key | Default | Extra          |

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

      | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

      | name  | char(20)         | NO   | UNI | NULL    |                |

      | age   | tinyint(4)       | NO   | MUL | NULL    |                |

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

      3 rows in set (0.02 sec)

       
  2. 为了保证数据的同步性，把从服务器设置成只读。

修改从服务器my.cnf的配置文件添加内容。且重启mysqld 服务。但是对于管理权权限的用户只读无效。

read-only=yes

查看slave状态，可以看到重启mysqld之后slave也启动了。

mysql> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.8.71

                  Master_User: replass

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000001

          Read_Master_Log_Pos: 910

               Relay_Log_File: relay-log.000004

                Relay_Log_Pos: 254

        Relay_Master_Log_File: master-bin.000001

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

              Relay_Log_Space: 404

              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:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 111

1 row in set (0.00 sec)

 

ERROR:

No query specified
  1. 配置文件安全性和一些注意设置的
    1. 配置文件安全性：

 

查看master to的信息存放的地方。

可以看到在从服务器上master.info文件里面保存了相关信息，包括账号密码，地址和端口。

[root@rhel5-2 mydata]# cat master.info

18

master-bin.000001

910

192.168.8.71

replass

replass

3306

60

0

 

 

 

 

 

0

1800.000

 

0

查看master.info的属性信息，可以看到只有mysql和mysql组的用户可以查看和修改这个文件。

[root@rhel5-2 mydata]# ls -ll

total 28816

-rw-rw---- 1 mysql mysql 18874368 Feb  7 20:55 ibdata1

-rw-rw---- 1 mysql mysql  5242880 Feb  7 20:56 ib_logfile0

-rw-rw---- 1 mysql mysql  5242880 Feb  6 22:00 ib_logfile1

-rw-rw---- 1 mysql mysql       83 Feb  7 20:56 master.info

drwx------ 2 mysql root      4096 Feb  6 21:02 mysql

drwx------ 2 mysql mysql     4096 Feb  6 21:02 performance_schema

-rw-rw---- 1 mysql mysql      150 Feb  7 20:56 relay-log.000003

-rw-rw---- 1 mysql mysql      254 Feb  7 20:56 relay-log.000004

-rw-rw---- 1 mysql mysql       38 Feb  7 20:56 relay-log.index

-rw-rw---- 1 mysql mysql       46 Feb  7 20:56 relay-log.info

-rw-r----- 1 mysql root      7724 Feb  7 20:56 rhel5-2.loony.err

-rw-rw---- 1 mysql mysql        6 Feb  7 20:56 rhel5-2.loony.pid

drwx------ 2 mysql root      4096 Feb  7 20:36 test

 

 
  1. master/slave配置一些注意事项：
    1. master:

sync-bin-log=1：用于事物安全性。

  1. slave:

read-only=yes:用于数据一致性。

  1. 配置开机不自动同步：由于某些原因，主服务器数据出现问题，需要及时停止从服务器，再从服务器上截取正确的数据，那么需要设置从服务器开机不自动启动复制。
    1. Skip-slave-start:本来应该有这个参数的，但是不知道为何show global variables like "%skip%"没有看到。
    2. 将master.info,relog.info先剪切到其地方去，然后启动slave服务器。
  2. 日志存放问题：关于slave的日志全部在err里面。data目录下的err日志。
  3. 配置半同步过程。

半同步使用的是google提供的一个semisync的插件，默认在lib下面。

一旦有一个超时，就会变更为异步模式。

ON MASTER:

mysql> install plugin rpl_semi_sync_master soname'semisync_master.so'

查看相关的变量见右表。

mysql> set global rpl_semi_sync_master_enabled = 1;

Query OK, 0 rows affected (0.04 sec)

 

mysql> set global rpl_semi_sync_master_timeout = 1000;(默认超时10秒太长，影响数据库性能，这里改成1秒。)

Query OK, 0 rows affected (0.00 sec)

 

 

mysql> show global variables like "%rpl%"

    -> ;

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

| Variable_name                      | Value |

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

| rpl_recovery_rank                  | 0     |

| rpl_semi_sync_master_enabled       | OFF   |

| rpl_semi_sync_master_timeout       | 10000 |

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

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

5 rows in set (0.00 sec)

 

ON Slave :

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

查看相关的变量见右表

mysql> set global rpl_semi_sync_slave_enabled = 1 ;

Query OK, 0 rows affected (0.03 sec)

 

mysql> stop slave IO_THREAD;

Query OK, 0 rows affected (0.05 sec)

 

mysql> start slave IO_THREAD;

Query OK, 0 rows affected (0.00 sec)

 

 

mysql> show global variables like "%rpl%";

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

| Variable_name     | Value |

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

| rpl_recovery_rank | 0     |

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

1 row in set (0.00 sec)

 

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

Query OK, 0 rows affected (0.12 sec)

 

mysql> show global variables like "%rpl%";

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

| Variable_name                   | Value |

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

| rpl_recovery_rank               | 0     |

| rpl_semi_sync_slave_enabled     | OFF   |

| rpl_semi_sync_slave_trace_level | 32    |

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

3 rows in set (0.00 sec)

 

 

查看相关状态：master:

mysql> show global status like "%rpl%";

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

| Variable_name                              | Value       |

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

| Rpl_semi_sync_master_clients               | 1           |

| Rpl_semi_sync_master_net_avg_wait_time     | 0           |

| Rpl_semi_sync_master_net_wait_time         | 0           |

| Rpl_semi_sync_master_net_waits             | 0           |

| Rpl_semi_sync_master_no_times              | 0           |

| Rpl_semi_sync_master_no_tx                 | 0           |

| Rpl_semi_sync_master_status                | ON          |

| Rpl_semi_sync_master_timefunc_failures     | 0           |

| Rpl_semi_sync_master_tx_avg_wait_time      | 0           |

| Rpl_semi_sync_master_tx_wait_time          | 0           |

| Rpl_semi_sync_master_tx_waits              | 0           |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |

| Rpl_semi_sync_master_wait_sessions         | 0           |

| Rpl_semi_sync_master_yes_tx                | 0           |

| Rpl_status                                 | AUTH_MASTER |

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

15 rows in set (0.00 sec)

 

 

Slave:

mysql> show global status like "%rpl%";

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

| Variable_name              | Value       |

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

| Rpl_semi_sync_slave_status | ON          |

| Rpl_status                 | AUTH_MASTER |

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

2 rows in set (0.01 sec)

 

 

配置文件更改。

master：

rpl_semi_sync_master_enabled  =1

rpl_semi_sync_master_timeout = 1000

重启后状态：

mysql> show global status like "%rpl%";

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

| Variable_name                              | Value       |

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

| Rpl_semi_sync_master_clients               | 1           |

| Rpl_semi_sync_master_net_avg_wait_time     | 0           |

| Rpl_semi_sync_master_net_wait_time         | 0           |

| Rpl_semi_sync_master_net_waits             | 0           |

| Rpl_semi_sync_master_no_times              | 0           |

| Rpl_semi_sync_master_no_tx                 | 0           |

| Rpl_semi_sync_master_status                | ON          |

| Rpl_semi_sync_master_timefunc_failures     | 0           |

| Rpl_semi_sync_master_tx_avg_wait_time      | 0           |

| Rpl_semi_sync_master_tx_wait_time          | 0           |

| Rpl_semi_sync_master_tx_waits              | 0           |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |

| Rpl_semi_sync_master_wait_sessions         | 0           |

| Rpl_semi_sync_master_yes_tx                | 0           |

| Rpl_status                                 | AUTH_MASTER |

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

15 rows in set (0.00 sec)

      

 

 

slave：

rpl_semi_sync_slave_enabled=1

重启后状态：

mysql> show global status like "%rpl%";

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

| Variable_name              | Value       |

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

| Rpl_semi_sync_slave_status | ON          |

| Rpl_status                 | AUTH_MASTER |

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

2 rows in set (0.02 sec)

 

 

 

 
  1. 主从工具：percona toolkit。
    1. 下载地址：https://www.percona.com/downloads/percona-toolkit/2.1.6/percona-toolkit-2.1.6-1.noarch.rpm
    2. 安装时候报错：

warning: rpmts_HdrFromFdno: Header V4 DSA signature: NOKEY, key ID cd2efd2a

 

 

Public key for percona-toolkit-2.2.1-2.noarch.rpm is not installed

安装时不使用gpgcheck即可。

[root@rhel5-2 ~]# yum localinstall percona-toolkit-2.2.1-2.noarch.rpm --nogpgcheck

  1. 命令一栏：

[root@rhel5-2 ~]# pt

pt-align                  pt-index-usage            pt-slave-find

pt-archiver               pt-ioprofile              pt-slave-restart

pt-config-diff            pt-kill                   pt-stalk

pt-deadlock-logger        pt-mext                   pt-summary

pt-diskstats              pt-mysql-summary          pt-table-checksum

pt-duplicate-key-checker  pt-online-schema-change   pt-table-sync

pt-fifo-split             pt-pmp                    pt-table-usage

pt-find                   pt-query-digest           pt-upgrade

pt-fingerprint            pt-show-grants            pt-variable-advisor

pt-fk-error-logger        pt-sift                   pt-visual-explain

pt-heartbeat              pt-slave-delay            ptx

[root@rhel5-2 ~]# pt

 pt-summary：收集信息

 pt-table-checksum：数据一致性检查

pt-ioprofile：IO性能测试。
  1. 双主模型配置：

 

 

posted @ 2016-04-17 22:26  Lkad  阅读(207)  评论(0)  编辑  收藏  举报