about 1 results (0.01 seconds)

MySQL主从同步复制

by LauCyun Jun 26,2016 13:30:13 11,587 views

实验环境:

  • 系统:CentOS 6.5
  • MySQL版本:MySQL 5.5.48
  • master:172.17.0.2
  • slave:172.17.0.3

注意下面几点:

  • 要保证同步服务期间之间的网络联通。即能相互ping通,能使用对方授权信息连接到对方数据库(防火墙开放3306端口)。
  • 关闭selinux。
  • 同步前,双方数据库中需要同步的数据要保持一致。这样,同步环境实现后,再次更新的数据就会如期同步了。

1 配置master

1.1 修改master数据库的my.cnf文件

[root@master ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# master
#
server-id = 1
log_bin = mysql-bin
binlog-do-db = demo
binlog-ignore-db = mysql,information_schema
replicate-do-db = demo
replicate-ignore-db = mysql,information_schema
expire_logs_days = 365
sync_binlog = 1
binlog_format = mixed

参数说明:

  • server-id:数据库唯一标识,主从的标识号绝对不能重复。
  • log_bin:开启bin-log,并指定文件目录和文件名前缀。
  • binlog-do-db:只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
  • binlog-ignore-db:不同步的数据库。如果有多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开。
  • replicate-do-db:需要同步的数据库。如果不指明同步那些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。
  • replicate-ignore-db:不需要同步的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项。一般为了保证主主同步不冲突。
  • expire_logs_days:日志文件过期天数,默认是 0,表示不过期。
  • sync_binlog:确保binlog日志写入后与硬盘同步。
    • 注意:

      在主服务器上最重要的二进制日志设置是sync_binlog,这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上,即使服务器崩溃也会把事件写入日志中。

      sync_binlog这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于sync_binlog参数的各种设置的说明如下:

      • sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
      • sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
    • 在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为sync_binlog=1的时候,是最安全但是性能损耗最大的设置。因为当设置为sync_binlog=1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。

      从以往经验和相关测试来看,对于高并发事务的系统来说,sync_binlog设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

  • binlog_formatbin-log日志文件格式,设置为MIXED可以防止主键重复。

1.2 设置数据同步权限

创建一个复制用的用户:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

检查是否创建成功,如果成功,则如下;反之:

mysql> show grants for repl@'%';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for repl@%                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.3 重启mysql

[root@master ~]# service mysqld restart

1.4 查看master状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 |      107 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

注意FilePosition项,从服务器需要这两项参数。

到此为止,master配置完成,接下来就是配置slave。

2 配置slave

2.1 修改slave数据库的my.cnf文件

[root@slave ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# slave
#
server-id = 2
log_bin = mysql-bin
binlog-do-db = demo
binlog-ignore-db = mysql,information_schema
replicate-do-db = demo
replicate-ignore-db = mysql,information_schema
slave-skip-errors = all

只针对某些库的某张表进行同步时。如下,只同步demo库的users表:

replicate-do-db = demo
replicate-wild-do-table = demo.users  # 当只同步几个或少数表时,可以这样设置;如果同步的库的表比较多时,就不能这样。

2.2 配置主从同步

先在slave用master授权用户连接master:

mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_USER='repl', MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.04 sec)

启动slave同步:

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看slave的状态:

mysql> SHOW SLAVE STATUS \G;   -- \G用来代替";",能把查询结果按键值的方式显示
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-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: 107
              Relay_Log_Space: 410
              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: 1
1 row in set (0.00 sec)

看到以下内容表示配置成功:

...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

到此为止,slave配置完成。

3 主从同步复制测试

3.1 新建数据库

在master新建数据库demo:

mysql> create database demo;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

在slave上验证:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

说明同步成功!

3.2 新建数据库表和插入数据

在master的数据库demo中新建表users,并插入一条数据:

mysql> create table users(
    -> id int primary key auto_increment,
    -> name varchar(50) not null,
    -> email varchar(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

mysql> insert into users(name,email) values('laucyun','liu@liuker.xyz');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

在slave上验证:

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

说明同步成功!

现在,主从同步环境已经实现了!

...

Tags Read More..