首页 » MySQL » 正文

腾讯云MySQL数据库同步问题

| MySQL | | 暂无评论 | 3739 views
名称版本号
操作系统CentOS 8.2
腾讯云MySQL5.7.18
MySQL slave5.7.32
故障描述：服务器采用了腾讯云的1核1000MB内存MySQL数据库基础版，基础版数据库不能创建备份策略，在出现故障时只能回到当日1点的数据，还需要提交工单解决。因操作失误，导致数据误删，还好数据丢失未造成实质性后果。故考虑使用MySQL主从同步策略进行备份，这样也能变相提取出来数据库操作日志。这样加上自己的定时数据备份脚本，可以使得数据最大化的减少丢失风险。当然这只是针对数据重要性不是很高的情况下使用。
#登陆腾讯云MySQL
grant replication slave,replication client on *.* to 'sync'@'%' identified by '123456';
flush privileges;

#服务器上安装MySQL 5.7
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/mysql

#创建配置MySQL文件
mv /etc/my.cnf /etc/my.cnf.bak
cat >> /etc/my.cnf << EOF
[mysqld]
server_id=111
log-bin=mysql-bin
relay-log = relay-bin
binlog_format = ROW
log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
EOF

#初始化数据库，空密码初始化
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql

#修改/usr/local/mysql/support-files/mysql.server文件的以下两项内容，按照初始化命令中的值修改
basedir=/usr/local/mysql
datadir=/var/lib/mysql

#复制/usr/local/mysql/support-files/mysql.server到/etc/init.d目录下
ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
#如果提示chkconfig命令未找到执行yum install chkconfig安装即可
/etc/init.d/mysqld start #启动数据库
#重置密码
mysql
alter user root@localhost identified by '123456';
flush privileges;

#登陆腾讯云MySQL数据库,锁定数据表只读
flush tables with read lock;
#查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
+------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin.000015 |  82243 |              |                  | 				|
+------------------+----------+--------------+------------------+-------------------------------------------------+
1 row in set (0.00 sec)

#备份数据库并导入本地数据库，之后登陆本地数据库
mysql> change master to \
master_host='123.123.123.123',\
master_user='sync',\
master_password='123456',\
master_log_file='mysql-bin.000015',\
master_log_pos=82243;

mysql> start slave;
执行数据库同步的时候报了一个错误信息，错误信息如下
The replication receiver thread cannot start because the master has GTID_MOD
#不能同步，因为master开启了GTID_MOD
#查看腾讯云数据库
mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------------+
| Variable_name                    | Value                                           |
+----------------------------------+-------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                              |
| enforce_gtid_consistency         | ON                                              |
| gtid_executed                    | a497d3e9-14d7-11ea-a730-525400a66cf7:1-19999829 |
| gtid_executed_compression_period | 1000                                            |
| gtid_mode                        | ON                                              |
| gtid_owned                       |                                                 |
| gtid_purged                      | a497d3e9-14d7-11ea-a730-525400a66cf7:1-19840955 |
| session_track_gtids              | OFF                                             |
+----------------------------------+-------------------------------------------------+
8 rows in set (0.01 sec)

#查看本地数据库
mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------------------------------+
| Variable_name                    | Value                                                             |
+----------------------------------+-------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                |
| enforce_gtid_consistency         | ON                                                                |
| gtid_executed                    | a497d3e9-14d7-11ea-a730-525400a66cf7:1-19987115:19997695-19999852 |
| gtid_executed_compression_period | 1000                                                              |
| gtid_mode                        | OFF                                                                |
| gtid_owned                       |                                                                   |
| gtid_purged                      | a497d3e9-14d7-11ea-a730-525400a66cf7:1-19987115:19997695-19999852 |
| session_track_gtids              | OFF                                                               |
+----------------------------------+-------------------------------------------------------------------+
8 rows in set (0.01 sec)

#可以看到腾讯云数据库开启了gtid，而本地为关闭状态，此时本地数据库需要打开gtid_mode
set global GTID_MODE = ON;
#直接执行会报以下错误
#ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

#根据提示知道需要按照OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON.的顺序执行
#在本地数据库依次执行以下命令打开
set global   ENFORCE_GTID_CONSISTENCY   = WARN;
#查看是否出现错误，没有的时候再继续，如果有则需要调整直到没有warning为止。
set global ENFORCE_GTID_CONSISTENCY = ON;
set global GTID_MODE = OFF_PERMISSIVE;
set global GTID_MODE = ON_PERMISSIVE;
set global GTID_MODE = ON;

#执行完毕再start slave即可，在本地/etc/my.cnf中加入以下两行
gtid_mode = on
enforce_gtid_consistency = on

发表评论