MySQL主从复制的配置

发表于 | 更新于: | 分类于

MySQL Replication又称AB复制或者主从复制，它主要用于MySQL的实时备份或者读写分离。在配置之前先做一下准备工作：配置两台MySQL服务器，或者在一台服务器上配置两个端口。在下面的实验中，就是在一台服务器上运行了两个MySQL。

MySQL主从原理非常简单，总结如下：

  • 每个从仅可以设置一个主
  • 主在中SQL之后，记录二进制log文件（bin-log）
  • 从连接主，并从主获取binlog，存于本地relay-log，并从上次记住的位置起执行SQL，一旦遇到错误则停止同步

从这几条Relocation原理来看，可以有这些推论：

  • 主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致
  • 如果主从的网络断开，从会在网络正常后，批量同步
  • 如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，这个是很危险的操作。所以一般情况下，非常小心的修改从上的数据
  • 一个衍生的配置是双主，互为主从配置，只要双方的修改不冲突，可以工作良好
  • 如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点

可以应用在读写分离的场景中，用以降低单台MySQL服务器的IO：

  • 可以实现MySQL服务的HA集群
  • 可以是一主多从，也可以是互相主从（主主）

1. 在一台服务器上配置两个MySQL服务

配置MySQL服务的详细步骤请参考LAMP源码方式安装的那篇学习笔记。在这里，假设已经搭建好了一个MySQL服务，使用的是3306端口，那么下面再搭建一个3307端口的MySQL，方法如下：

1
2
3
4
# cd /usr/local
# cp -r mysql mysql_2
# cd mysql_2
# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql2

初始化数据库目录，显示两个OK并且生成/data/mysql2目录才正确；否则请仔细查看错误信息。

复制配置文件到mysql_2目录下，并修改相关项目，如下所示：

1
2
# cp /etc/my.cnf /usr/local/mysql_2/
# vim /usr/local/mysql_2/my.cnf

需要修改一些参数，更改的参数如下：

1
2
3
4
5
6
log_bin = aminglinux2
basedir = /usr/local/mysql_2
datadir = /data/mysql2
port = 3307
server_id = 129
socket = /tmp/mysql2.sock

保存配置文件后，复制启动脚本并编辑，操作过程如下：

1
2
3
4
5
6
7
8
# cp support-files/mysql.server /etc/init.d/mysqld2
# vim /etc/init.d/mysqld2
#<==需要更改的地方有
basedir=/usr/local/mysql2
datadir=/data/mysql2
$bindir/mysqld_safe --defaults-file=$basedir/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args > /dev/null &

上面最后的特别长的为启动命令，增加-defaults-file参数，若不增加则不能正确找到mysq_2的配置文件。

然后启动两个MySQL：

1
2
# /etc/init.d/mysqld start	#<==若之前的MySQL已经启动，则不用执行这一步
# /etc/init.d/mysqld2 start

到此，已经成功在一台Linux服务器上面启动了两个MySQL，检查命令如下：

1
2
3
4
5
# netstat -lnp | grep mysql
tcp6  0      0 :::3306   :::*       LISTEN       12910/mysqld
tcp6  0      0 :::3307   :::*       LISTEN       13122/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     37251    12910/mysqld         /tmp/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     37331    13122/mysqld         /tmp/mysql2.sock

2. 配置Replication

现在要把3307端口的MySQL作为主（master），而把3306的MySQL作为从（slave）。为了让实验更加接近生产环境，先在master上创建一个库aming，如下所示：

1
2
3
4
5
6
# mysql -uroot -S /tmp/mysql2.sock
mysql> create database aming;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye

其中，-S（大写字母S）后面指定MySQL的socket文件路径，这也是登陆MySQL的一种方法。因为在这台服务器上面运行了两个MySQL端口，所以用-S这样的方法来进行区分。

然后把mysql库的数据复制给aming库，如下所示：

1
2
# mysqldump -uroot -S /tmp/mysql2.sock mysql > /tmp/aming.sql
# mysql -uroot -S /tmp/mysql2.sock aming < /tmp/aming.sql

2.1. 设置主（master）

在上面的操作中，已经将mysql_2的配置文件设置过相关的参数，如果你没有设置，请添加：

1
2
server_id=129
log_bin=aminglinux2

另外还有两个参数你可以选择性地使用，如下所示：

1
2
binlog-do-db=databasename1,databasename2
binlog-ignore-db=databasename1,databasename2

其中：

  • binlog-do-dv=定义需要复制的数据库，多个数据库用英文的逗号分隔
  • binlog-ignore-db=定义不需要复制的数据库
  • 这两个参数用其中一个即可

如果修改过配置文件，需要重启MySQL服务。重启服务的方法如下：

1
# /etc/init.d/mysqld2 restart

刚安装的mysql_2的root密码为空，所以需要设置一下root用户的访问密码，如下所示：

1
2
3
4
# mysqladmin -uroot -S /tmp/mysql2.sock password 'aminglinux.com'
# mysql -uroot -S /tmp/mysql2.sock -p
Enter password:	#<==输入正确的密码后可登陆
mysql> grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123lalala';

说明：这里的repl是为从（slave）端设置的访问主（master）端的用户，也就是要完成主从复制的用户，密码为123lalala，这里的127.0.0.1为slave的IP。（在本实验中，所配置的master和slave都在本机，所以用127.0.0.1）

1
mysql> flush tables with read lock;

说明：上面这一步的操作是锁定数据库的写操作。

1
2
3
4
5
6
7
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| aminglinux2.000001 |   652888 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

说明：上面的操作是擦好看master的状态，这些数据都是要记录的，一会要在slave端用到。

2.2. 设置从（slave）

首先修改slave的配置文件my.cnf，执行如下命令：

1
# vim /etc/my.cnf

找到server_id=，设置成和master不一样的数字，若一样会导致后面的操作不成功。另外，在slave上，你也可以选择性地增加如下两行，对应master上增加的两行：

1
2
replicate-do-db=databasename1,databasename2
replicate-ignore-db=databasename1,databasename2

保存修改后重启MySQL，执行如下命令：

1
# /etc/init.d/mysqld restart

然后复制master上aming库的数据到slave上。因为master和slave都在一台服务器上，所以操作起来很简单。如果是在不同的机器上，就需要远程复制了（使用scp或rsync）。

1
2
3
# mysqldump -uroot -S /tmp/mysql2.sock -p'aminglinux.com' aming > /tmp/aming1.sql
# mysql -uroot -S /tmp/mysql.sock -p123456 -e "create database aming"
# mysql -uroot -S /tmp/mysql.sock -p123456 aming < /tmp/aming1.sql

上面的第二行，使用了-e选项，它用来把MySQL的命令写到shell命令行下，其格式为：-e "command"。这个选项很实用，在工作中会经常用到，请熟记。

复制完数据后，就需要在slave上配置了，如下所示：

1
2
3
4
5
6
7
8
# mysql -uroot -S /tmp/mysql.sock -p123456
mysql> stop slave;
mysql> change master to master_host='127.0.0.1',
master_port=3307, master_user='repl',
master_password='123lalala',
master_log_file='aminglinux2.000001',
master_log_pos=652888;
mysql> start slave;

说明：

  • change master这个命令是一条，打完逗号后可以按回车，直到你打分号才算结束。
  • 其中master_log_file和master_log_pos是在前面使用show master status命令查到的数据。

  • 执行完这一步后，需要在master上执行下面一步（建议打开两个终端，分别连两个MySQL）：

    1
    # mysql -uroot -S /tmp/mysql2.sock -p'aminglinux.com' -e "unlock tables"

  • 然后在slave端查看slave的状态，执行如下命令：

    1
    mysql> show slave status\G;

  • 确认以下两项参数都为Yes，如下所示：

    1
    2
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

  • 还需要关注的地方有：

    1
    2
    3
    4
    5
    Seconds_Behind_Master: 0	#<==为主从复制延迟的时间
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:

  • 如果主从不正常了，需要看这里的error信息。

2.3. 测试主从

在master上执行如下命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# mysql -uroot -S /tmp/mysql2.sock -p'aminglinux.com' aming
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
mysql> truncate table db;
Query OK, 0 rows affected (0.01 sec)
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

这样就清空了aming.db表的数据。下面查看slave上该表的数据，执行如下命令：

1
2
3
4
5
6
7
8
# mysql -uroot -S /tmp/mysql.sock -p123456 aming
mysql> select count(*) from db;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

可以看到slave上改表的数据也被清空了，但好像不太明显，我们不妨在master上继续删除db表，如下所示：

1
2
mysql> drop table db;
Query OK, 0 rows affected (0.00 sec)

再从slave上看：

1
2
mysql> select * from db;
ERROR 1146 (42S02): Table 'aming.db' doesn't exist

这次就很明显了，db表已经不存在了。

主从配置起来虽然很简单，但这种机制非常脆弱，一旦我们不小心在slave上写了数据，那么主从复制也就被破坏了。另外，如果重新master，务必要先关闭slave，即在slave上执行slave stop命令，然后再去重启master的MySQL服务，否则主从复制很有可能就会中断。当然重启master后，我们还需要执行start slave命令开启主从复制的服务。

3. 设置主主

MySQL主主复制结构区别于主从复制结构。在主主复制结构中，两台服务器的任何一台上面的数据库存发生了改变都会同步到另一台服务器上，这样开两台服务器互为主从，并且都能向外提供服务。这就比使用主从复制具有更好的性能。

接下来，将使用两个同样的服务器来实现这个效果：

  • server1_mysql:192.168.1.108
  • server2_mysql:192.168.1.110

3.1. 创建用户并授权

  • server1:

    1
    2
    3
    mysql> grant replication slave on *.* to 'server2'@'192.168.1.110'
    IDENTIFIED BY 'server2';

  • server2:

    1
    2
    3
    mysql> grant replication slave on *.* to 'server1'@'192.168.1.108'
    IDENTIFIED BY 'server1';

3.2. 修改MySQL的主配置文件

  • server1

    1
    2
    3
    4
    5
    6
    [mysql]
    server-id=10
    log-bin=mysql-bin
    replicate-do-db=mydb
    auto-increment-increment=2	//#<==每次增长2
    auto-increment-offset=1		//#<==设置自动增长的字段的偏移量，即初始值为2

  • 启动server1

    1
    # service mysqld restart

  • server2

    1
    2
    3
    4
    5
    6
    [mysql]
    server-id=20
    log-bin=mysql-bin
    replicate-do-db=mydb
    auto-increment-increment=2	//#<==每次增长2
    auto-increment-offset=2		//#<==设置自动增长的字段的偏移量，即初始值为2

  • 启动server2

    1
    # service mysqld restart

注：

  • 二只有server-id和auto-increment-offset不同。
  • auto-increment-increment的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2.

3.3. 重启两台服务器

1
# service mysqld restart

3.4. 测试准备

为了让两个数据库一样，我们备份其中一个数据库，然后在另一个数据库上恢复，这样是两个数据库一开始就是一样的。

在server1上操作：

  • 备份：mysqldump --databases luowei > /tmp/luowei.sql
    在server2上操作：
  • 创建一个与mydb同名的空数据库：
    1
    2
    3
    4
    5
    mysql> create database mydb;
    mysql> \q
    # scp 192.168.1.108:/tmp/mydb.sql ./
    # mysql -uroot -p mydb < /tmp/luowei.sql

然后两个服务器相互通告二进制日志的位置并启动复制功能：

  • 在server1上：

    1
    2
    mysql> change master to master_host='192.168.1.110',master_user='server2',master_password='server2';
    mysql> start slave;

  • 在server2上：

    1
    2
    mysql> change master to master_host="192.168.1.108",master_user='server1',master_password='server1';
    mysql> start slave;

3.5. 查看，并验证

分别在两个数据库服务器上查看：

1
mysql> show slave status\G;

然后查看数据库和表，你会发现内容是一样的，这样就是整个主主MySQL的架构的配置过程。

0%