euserv安装mysql_MySQL 8.0.17 clone plugin 本地远程备份，并搭建主从总结

最新推荐文章于 2023-11-07 15:25:14 发布

安会怡

最新推荐文章于 2023-11-07 15:25:14 发布

阅读量131

点赞数

文章标签： euserv安装mysql

本文链接：https://blog.csdn.net/weixin_32620855/article/details/114981116

版权

克隆插件允许在本地或从远程MySQL服务器实例克隆数据。也可以看作一种备份方法，所以我归到备份整理里面了。克隆数据是InnoDB中存储的数据的物理快照，包括模式，表，表空间和数据字典元数据。克隆的数据包含一个功能齐全的数据目录，允许使用克隆插件进行MySQL服务器配置。

本地克隆操作

本地克隆操作将启动克隆操作的 MySQL 服务器实例中的数据克隆到同服务器或同节点上的一个目录里(要注意这个配置secure_file_priv)。这个功能让我想起tukodb的hotbackup，我前面文章有写到。

远程克隆

默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。(可选)您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。

远程克隆操作和本地克隆操作克隆的数据没有区别，数据是相同的。

克隆插件支持复制。除克隆数据外，克隆操作还从捐赠者中提取并传输复制位置信息，并将其应用于接受者，从而可以使用克隆插件来配置组复制或主从复制。使用克隆插件进行配置比复制大量事务要快得多，效率更高。

这个功能让我想起postgresql 的pg_basebackup，以后的文章会写

1、两台机器分别安装MySQL192.168.56.16 es3

192.168.56.15 es2

[root@es2 ~]# yum -y install mysql-community-*

[root@es3 ~]# yum -y localinstall mysql-community-*

2、修改配置文件[root@es2 ~]# grep -Ev "^$|^[#;]" /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

gtid-mode = ON

enforce-gtid-consistency = ON

log-slave-updates = ON

server-id=1

[root@es2 ~]#[root@es3 ~]# grep -Ev "^$|^[#;]" /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

gtid-mode = ON

enforce-gtid-consistency = ON

log-slave-updates = ON

server-id=2

[root@es3 ~]#

3、安装clone 插件，并创建克隆用户和复制用户mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

mysql> CREATE USER clone_user@'%' identified by 'iwSeuFagt0&31';

mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

mysql> CREATE USER repl_user@'%' identified by 'iwSeuFagt0&31';

mysql> grant replication slave on *.* to repl_user;

mysql> flush privileges;

4、查看secure_file_priv并开始第一次本地克隆，注意/var/lib/mysql-files/clone_dir1中的clone_dir1要不存在mysql> show variables like 'secure_file_priv';

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

| Variable_name | Value |

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

| secure_file_priv | /var/lib/mysql-files/ |

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

1 row in set (0.00 sec)

mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql-files/clone_dir1';

5、通过创建不同标识，进行多次克隆测试mysql> create database after_clone1;

mysql> CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql-files/clone_dir2';

mysql> create database after_clone2;

6、切换克隆数据目录，查看相关标识情况mysql> show variables like 'datadir';

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

| Variable_name | Value |

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

| datadir | /var/lib/mysql-files/clone_dir1/ |

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

1 row in set (0.01 sec)

mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;

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

| BINLOG_FILE | BINLOG_POSITION |

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

| binlog.000001 | 1741 |

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

1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.GTID_EXECUTED;

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

| @@GLOBAL.GTID_EXECUTED |

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

| dbda28c9-c970-11e9-b268-0800275c8ec3:1-6 |

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

1 row in set (0.00 sec)

mysql> exitmysql> show variables like 'datadir';

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

| Variable_name | Value |

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

| datadir | /var/lib/mysql-files/clone_dir2/ |

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

1 row in set (0.01 sec)

mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;

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

| BINLOG_FILE | BINLOG_POSITION |

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

| binlog.000001 | 1950 |

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

1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.GTID_EXECUTED;

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

| @@GLOBAL.GTID_EXECUTED |

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

| dbda28c9-c970-11e9-b268-0800275c8ec3:1-7 |

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

1 row in set (0.00 sec)

mysql> exitmysql> show variables like 'datadir';

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

| Variable_name | Value |

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

| datadir | /var/lib/mysql/ |

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

1 row in set (0.01 sec)

mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;

Empty set (0.00 sec)

mysql> SELECT @@GLOBAL.GTID_EXECUTED;

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

| @@GLOBAL.GTID_EXECUTED |

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

| dbda28c9-c970-11e9-b268-0800275c8ec3:1-8 |

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

1 row in set (0.00 sec)

mysql> exit

远程克隆

7、远程克隆数据接收端环境准备，创建接收者用户和授权。 CLONE_ADMIN权限 = BACKUP_ADMIN权限 + SHUTDOWN权限。SHUTDOWN仅限允许用户shutdown和restart mysqld。授权不同是因为，接受者需要restart mysqld，特别提示，本地克隆用户使用的是BACKUP_ADMIN即可。这里我们将上面创建的本地克隆用户作为捐赠者用户使用。mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

Query OK, 0 rows affected (0.09 sec)

mysql> CREATE USER clone_user@'%' identified by 'iwSeuFagt0&31';

Query OK, 0 rows affected (0.32 sec)

mysql> GRANT CLONE_ADMIN on *.* to clone_user;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

8、尝试远程克隆，注意clone_valid_donor_list设置，当然不设置会提醒mysql> set global clone_valid_donor_list ='192.168.56.15:3306';

mysql> clone instance from clone_user@'192.168.56.15':3306

-> identified by 'iwSeuFagt0&31';

mysql> show databases;

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

| Database |

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

| after_clone1 |

| after_clone2 |

| information_schema |

| mysql |

| performance_schema |

| sys |

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

mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;

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

| BINLOG_FILE | BINLOG_POSITION |

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

| binlog.000002 | 195 |

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

1 row in set (0.01 sec)

mysql> SELECT @@GLOBAL.GTID_EXECUTED;

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

| @@GLOBAL.GTID_EXECUTED |

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

| dbda28c9-c970-11e9-b268-0800275c8ec3:1-8 |

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

1 row in set (0.00 sec)

9、尝试做主从复制，注意修改主库的密码验证插件

主库alter USER repl_user@'%' identified WITH mysql_native_password by 'iwSeuFagt0&31' ;

从库mysql> CHANGE MASTER TO MASTER_HOST = '192.168.56.15',

mysql> MASTER_PORT = 3306,MASTER_AUTO_POSITION = 1;

mysql> START SLAVE USER = 'repl_user' PASSWORD = 'iwSeuFagt0&31' ;

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.15

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000003

Read_Master_Log_Pos: 195

Relay_Log_File: es3-relay-bin.000004

Relay_Log_Pos: 403

Relay_Master_Log_File: binlog.000003

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

Relay_Log_Space: 1271

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

Master_UUID: dbda28c9-c970-11e9-b268-0800275c8ec3

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: dbda28c9-c970-11e9-b268-0800275c8ec3:9-10

Executed_Gtid_Set: dbda28c9-c970-11e9-b268-0800275c8ec3:1-10

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set (0.00 sec)

mysql> show databases;

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

| Database |

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

| after_clone1 |

| after_clone2 |

| information_schema |

| mysql |

| performance_schema |

| sys |

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

6 rows in set (0.00 sec)

mysql> exit

10、同步测试

10.1、主库操作[root@es2 ~]# mysql -p'iwSeuFagt0&31'

mysql> show databases;

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

| Database |

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

| after_clone1 |

| after_clone2 |

| information_schema |

| mysql |

| performance_schema |

| sys |

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

6 rows in set (0.00 sec)

mysql> use after_clone2

Database changed

mysql> create table t_clone(id int not null auto_increment primary key ,name varchar(255));

Query OK, 0 rows affected (0.36 sec)

mysql> insert into t_clone(name) values('c'),('l'),('o'),('n'),('e');

Query OK, 5 rows affected (0.29 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from after_clone2.t_clone;

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

| id | name |

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

| 1 | c |

| 2 | l |

| 3 | o |

| 4 | n |

| 5 | e |

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

5 rows in set (0.00 sec)

mysql> drop database after_clone1;

Query OK, 0 rows affected (0.31 sec)

mysql> exit

10.2、从库观察[root@es3 ~]# mysql -p'iwSeuFagt0&31';

mysql> show databases;

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

| Database |

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

| after_clone2 |

| information_schema |

| mysql |

| performance_schema |

| sys |

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

5 rows in set (0.00 sec)

mysql> select * from after_clone2.t_clone;

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

| id | name |

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

| 1 | c |

| 2 | l |

| 3 | o |

| 4 | n |

| 5 | e |

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

5 rows in set (0.00 sec)

mysql> exit

11、远程克隆先决条件要执行远程克隆操作，数据提供方和数据接收方的MySQL服务器实例都处于活动状态

执行远程克隆操作需要数据提供方和数据接收方上的MySQL用户：

数据提供方上，克隆用户需要BACKUP_ADMIN访问和传输来自捐赠者的数据的特权，

以及在克隆操作期间阻止DDL 的特权；

数据接收方上，克隆用户需要具有CLONE_ADMIN替换收件人数据，

在克隆操作期间阻止DDL以及自动重新启动服务器的权限，

该 CLONE_ADMIN权限包括隐式 BACKUP_ADMIN和 SHUTDOWN特权。

数据提供方和数据接收方必须具有相同的MySQL服务器版本。MYSQL 8.0.17及更高版本支持克隆插件。

数据提供方和数据接收方必须在同一操作系统和平台上运行。

例如，如果捐赠者实例在Linux 64位平台上运行，则收件人实例也必须在该平台上运行。

数据提供方和数据接收方必须具有相同的MySQL服务器字符集和排序规则

克隆插件仅克隆存储的数据 InnoDB。不克隆其他存储引擎数据。

MyISAM并且 CSV存储在包括sys模式的任何模式中的表都被克隆为空表。

克隆插件不支持克隆MySQL服务器配置my.cnf；克隆插件不支持克隆二进制日志；

不支持通过MySQL router连接到捐赠者实例。

克隆加密或页面压缩数据，则捐赠者和接收者必须具有相同的文件系统块大小；

克隆加密数据，则需要安全连接

默认情况下，克隆数据后会自动重新启动(停止并启动)数据接收方MySQL服务器实例。

要自动重新启动，必须在接收方上提供监视进程以检测服务器是否已关闭。

否则，在克隆数据后，克隆操作将停止并出现以下错误，并且关闭数据接收方MySQL服务器实例：

ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

安会怡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫