MySQL AB复制

最新推荐文章于 2023-11-23 16:15:30 发布

秦庚辰

最新推荐文章于 2023-11-23 16:15:30 发布

阅读量204

点赞数 1

本文链接：https://blog.csdn.net/qq_43237999/article/details/90897921

版权

MySQL AB复制

问题

1)配置2台MySQL服务器，实现主–>从同步。
2)其中Master服务器允许SQL查询、写入，Slave服务器只允许SQL查询

方案

使用2台RHEL 6虚拟机，如图所示。其中192.168.4.10是MySQL主服务器，负责提供同步源；另一台192.168.4.20作为MySQL从服务器，通过调取主服务器上的binlog日志，在本地重做对应的库、表，实现与主服务器的AB复制（同步）。
在这里插入图片描述
提前为两台MySQL服务器安装好MySQL-server、MySQL-Client软件包，并为数据库用户root修改密码；Linux客户机上则只需安装MySQL-Client软件包即可。

步骤

实现此案例需要按照如下步骤进行。
步骤一：初始化现有库
为了在启用binlog日志及同步之前保持主、从库的一致性，建议进行初始化——备份主服务器上现有的库，然后导入到从服务器上。
当现有库、表都采用MyISAM引擎时，可执行离线备份、恢复，这样更有效率；否则，可通过mysqldump等工具来实现库的导出、导入。
1）备份MySQL Master（192.168.4.10）上现有的库
如果服务器已经启用binlog，建议对日志做一次重置，否则可忽略：

[root@dbsvr1 ~]# mysql -u root -p
Enter password:  								//以数据库用户root登入
.. ..
mysql> RESET MASTER;  							//重置binlog日志
Query OK, 0 rows affected (0.06 sec)
mysql> quit  									//退出mysql> 环境
Bye

以备份mysql库、test库为例，导出操作如下：

[root@dbsvr1 ~]# mysqldump -u root -p -B mysql test > /root/mytest.sql
Enter password:  									//验证口令
[root@dbsvr1 ~]# ls -lh /root/mytest.sql  			//确认备份结果
-rw-r--r--. 1 root root 577K 1月  16 11:50 /root/mytest.sql

2）在MySQL Slave（192.168.4.20）上导入备份的库
先清理目标库，避免导入时冲突。主要是采用InnoDB引擎的库，授权库mysql多采用MyISAM引擎，可不做清理。

[root@dbsvr2 ~]# mysql -u root -p
Enter password:  								//以数据库用户root登入
.. ..
mysql> DROP DATABASE test;  						//删除test库
Query OK, 0 rows affected (0.03 sec)
mysql> quit  									//退出mysql> 环境
Bye

使用scp工具下载备份文件：

[root@dbsvr2 ~]# scp dbsvr1:/root/mytest.sql ./
root@dbsvr1's password:  						//验证对方系统用户root的口令
mytest.sql                                    100%  577KB 576.6KB/s   00:01
[root@dbsvr2 ~]# ls -lh mytest.sql  			//确认下载结果
-rw-r--r--. 1 root root 577K 1月  16 12:00 mytest.sql

执行导入操作：

[root@dbsvr2 ~]# mysql -u root -p < mytest.sql
Enter password:  								//验证口令

导入成功后，可重新登入 mysql> 环境，确认清理的目标库已恢复：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |  							//目标库test已恢复
+--------------------+
4 rows in set (0.00 sec)

步骤二：配置MySQL Master（主服务器，192.168.4.10）
1）修改/etc/my.cnf配置，重新启动MySQL服务程序
指定服务器ID号、允许日志同步：

[root@dbsvr1 mysql]# vim /etc/my.cnf
[mysqld]
log_bin=dbsvr1-bin  					//启用binlog日志，并指定文件名前缀
server_id = 10  						//指定服务器ID号
innodb_flush_log_at_trx_commit=1  	//优化设置
sync-binlog=1  						//允许日志同步
.. ..
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=1

重启mysql服务：

[root@dbsvr1 ~]# service mysql restart
Shutting down MySQL..                                      [确定]
Starting MySQL..                                           [确定]

2）新建一个备份用户，授予复制权限
需要的权限为REPLICATION SLAVE，允许其从Slave服务器访问：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicater'@'192.168.4.%' IDENTIFIED BY 'pwd123';
Query OK, 0 rows affected (0.04 sec)

3）检查Master服务器的同步状态
在已经初始化现有库的情况下，查看MASTER状态，记录下当前的日志文件名、偏移的位置（下面SLAVE发起复制时需要用到）：

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: dbsvr1-bin.000002  			//记住当前的日志文件名
         Position: 334  						//记住当前的位置
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.04 sec)

步骤三：配置MySQL Slave（从服务器，192.168.4.20）
1）修改/etc/my.cnf配置，重新启动MySQL服务程序
指定服务器ID号、允许日志同步：

[root@dbsvr2 ~]# vim /etc/my.cnf
[mysqld]
log_bin=dbsvr2-bin  					//启动SQL日志，并指定文件名前缀
server_id = 20  						//指定服务器ID号，不要与Master的相同
innodb_flush_log_at_trx_commit=1  	//优化设置
sync-binlog=1  						//允许日志同步
log_slave_updates=1  				//记录从库更新，便于实现“主-从-从”链式复制
.. ..
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=1

在生产环境中，还可以根据需要设置更详细的同步选项。比如，指定当主、从网络中断时的重试超时时间（slave-net-timeout=60 ）等，具体可参考MySQL手册。
配置完成后，重启mysql服务：

[root@dbsvr2 ~]# service mysql restart
Shutting down MySQL..                                      [确定]
Starting MySQL..                                           [确定]

2）登入 mysql> 环境，发起同步操作
通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.4.10',
    -> MASTER_USER='replicater',
    -> MASTER_PASSWORD='pwd123',
    -> MASTER_LOG_FILE='dbsvr1-bin.000002',  	//对应Master的日志文件
    -> MASTER_LOG_POS=334;  						//对应Master的日志偏移位置
Query OK, 0 rows affected, 2 warnings (0.12 sec)

然后执行START SLAVE（较早版本中为SLAVE START）启动复制：

mysql> START SLAVE;  							//启动复制
Query OK, 0 rows affected (0.06 sec)

注意：一旦启用SLAVE复制，当需要修改MASTER信息时，应先执行STOP SLAVE停止复制，然后重新修改、启动复制。
通过上述连接操作，MASTER服务器的设置信息自动存为master.info文件，以后每次MySQL服务程序时会自动调用并更新，无需重复设置。查看master.info文件的开头部分内容，可验证相关设置：

[root@dbsvr2 ~]# ls -lh /var/lib/mysql/master.info
-rw-rw----. 1 mysql mysql 132 1月  16 13:53 /var/lib/mysql/master.info 
[root@dbsvr2 ~]# head /var/lib/mysql/master.info
23
dbsvr1-bin.000002
334
192.168.4.10
replicater
pwd123
3306
60
0

3）检查Slave服务器的同步状态
通过SHOW SLAVE STATUS语句可查看从服务器状态，确认其中的IO线程、SQL线程正常运行，才能成功同步：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.10
                  Master_User: replicater
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: dbsvr1-bin.000002
          Read_Master_Log_Pos: 334
               Relay_Log_File: dbsvr2-relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: dbsvr1-bin.000002
             Slave_IO_Running: Yes  			//IO线程应该已运行
            Slave_SQL_Running: Yes  			//SQL线程应该已运行
              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: 334
              Relay_Log_Space: 458
              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: 10
                  Master_UUID: e9f37060-6baf-11e3-8112-000c29aa7715
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.06 sec)

若START SLAVE直接报错失败，请检查CHANGE MASTER相关设置是否有误，纠正后再重试；若IO线程或SQL线程有一个为“No”，则应检查服务器的错误日志，分析并排除故障后重启主从复制。
步骤四：测试主从同步效果
1）在Master上操作数据库、表、表记录
新建newdb库、newtable表，随意插入几条表记录：

mysql> CREATE DATABASE newdb;  						//新建库newdb
Query OK, 1 row affected (0.04 sec)

mysql> USE newdb;  									//切换到newdb库
Database changed

mysql> CREATE TABLE newtable(id int(4));  			//新建newtable表
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO newtable VALUES(1234),(5678);  	//插入2条表记录
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM newtable; 						//确认表数据
+------+
| id   |
+------+
| 1234 |
| 5678 |
+------+
2 rows in set (0.00 sec)

2）在Slave上确认自动同步的结果
直接切换到newdb库，并查询newtable表的记录，应该与Master上的一样，这才说明主从同步已经成功生效：

mysql> USE newdb;  									//直接切换到newdb库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT * FROM newtable;  					//输出表记录
+------+
| id   |
+------+
| 1234 |
| 5678 |
+------+
2 rows in set (0.02 sec)

3）在Master服务器上可查看Slave主机的信息

mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        20 |      | 3306 |        10 | 2f62f59d-7e71-11e3-bb5d-000c299422aa |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.01 sec)

步骤五：将Slave服务器设为只读
一般来说，为了避免写入冲突，采用主、从复制结构时，不应该允许用户从Slave执行数据库写入操作，这样会导致双方数据的不一致性。
正因为如此，我们可以把Slave数据库限制为只读模式，这种情况下有SUPER权限的用户和SLAVE同步线程才能写入。相关验证操作及效果可参考以下过程。
1）新建一个测试用户rwuser（不能用root测试）
在Master上建立即可，会自动同步到Slave上：

mysql> GRANT all ON newdb.* TO rwuser@localhost IDENTIFIED BY '1234567';
Query OK, 0 rows affected (0.14 sec)

2）未启用只读前，验证从Slave写入
在Slave上以rwuser登入（不要用root哦）：

[root@dbsvr2 ~]# mysql -u rwuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

然后向newdb库中新建一个booker表：

mysql> USE newdb;  								//切换到newdb库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE TABLE booker(id int(12));  		//成功创建booker表
Query OK, 0 rows affected (0.08 sec)

在Slave上可看到新建的booker表：

mysql> SHOW TABLES;
+-----------------+
| Tables_in_newdb |
+-----------------+
| booker          |
| newtable        |
+-----------------+
2 rows in set (0.00 sec)

但是在Master上却看不到，导致主、从上的newdb出现不一致：

mysql> USE newdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;  					//看不到Slave上新建的表
+-----------------+
| Tables_in_newdb |
+-----------------+
| newtable        |
+-----------------+
1 row in set (0.00 sec)

完成上述验证后，在Slave上删除booker表，确保双方一致：

mysql> DROP TABLE booker;
Query OK, 0 rows affected (0.01 sec)

3）修改/etc/my.cnf文件，重载配置

[root@dbsvr2 ~]# vim /etc/my.cnf
[mysqld]
.. ..
read_only=1  									//启动只读模式

[root@dbsvr2 ~]# service mysql restart  		//重启服务
Shutting down MySQL.. 								[确定]
Starting MySQL... 									[确定]

4）再次在Slave上验证数据库写入操作
仍然是以rwuser登入（不要用root哦）来验证，当尝试创建新表时会被拒绝：

mysql> USE newdb;  									//切换到newdb库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE booker(id int(12)); 	//新建表的写入操作失败
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> DROP TABLE mytable;  					//删除表的写入操作一样会失败
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

秦庚辰

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL AB复制

MySQL AB复制问题1)配置2台MySQL服务器，实现主–>从同步。2)其中Master服务器允许SQL查询、写入，Slave服务器只允许SQL查询方案使用2台RHEL 6虚拟机，如图所示。其中192.168.4.10是MySQL主服务器，负责提供同步源；另一台192.168.4.20作为MySQL从服务器，通过调取主服务器上的binlog日志，在本地重做对应的库、表，实现...
复制链接

扫一扫