用mysql shell实战搭建MySQL主从集群

2023-08-04 12:12:01 浏览数 (1)
1、前言

mysql 8.0.34和8.1发布了，好像有很大变化的样子，但是又没有，所以研究了一下Release Notes ,其中有一条非常有意思，

Deprecation and Removal Notes

  • Important Change: Since MySQL provides other means of performing database dumps and backups with the same or additional functionality, including mysqldump and MySQL Shell Utilities, the mysqlpump client utility program has become redundant, and is now deprecated. Invocation of this program now produces a warning. You should keep in mind that mysqlpump is subject to removal in a future version of MySQL, and move applications depending on it to another solution, such as those mentioned previously.

重要更改： 由于 MySQL 提供了具有相同或附加功能的其他执行数据库转储和备份的方法，包括 mysqldump和 MySQL Shell 实用程序， 因此mysqlpump客户端实用程序已变得多余，现已弃用。现在调用该程序会产生警告。您应该记住， mysqlpump可能会在 MySQL 的未来版本中被删除，并将依赖于它的应用程序移动到另一个解决方案，例如前面提到的那些。

那么我们就是用mysql shell来完成我们常规的一些操作，比如搭建主从复制

2、项目规划

IP地址

mysql版本

备注

192.168.5.130

8.0.34

Master

192.168.5.140

8.0.34

Slave

两个数据库实例都是完成初始化.

3、主库5.130使用sysbench准备一波数据
3.1、sysbench安装
代码语言：javascript复制
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench
3.2、创建一些测试的用户信息
代码语言：javascript复制
create database test_db;
create user test_user@'%' identified with mysql_native_password by 'test_user';
create user test_user@'localhost' identified with mysql_native_password by 'test_user';
grant all privileges on test_db.* to test_user@'%';
grant all privileges on test_db.* to test_user@'localhost';
flush privileges;


create user root@'%' identified by 'root';
grant all privileges on *.* to root@'%';
flush privileges;
3.3、单独准备一些数据
代码语言：javascript复制
#create db and table for test
create database db01;
use db01;
create table t1(id int primary key auto_increment,name varchar(20));
create table t2(id int primary key auto_increment,name varchar(20));


#create trigger
delimiter //
create trigger tt123 after
insert on t1
for each row
begin
replace into db01.t2(id,name) select id,name from db01.t1 where id%2=0;
end
//
delimiter ;


#insert data
insert into t1(name) select substring(md5(rand()), 1, 10);
select * from t1;
commit;
flush logs;


#create procedure
delimiter //
create procedure tp123()
begin
insert into t1(name) select substring(md5(rand()), 1, 10);
end
//
delimiter ;

call tp123;
commit;
flush logs;


#create event
create event if not exists te123
        on schedule every 1 SECOND
        STARTS CURRENT_TIMESTAMP   INTERVAL 3 SECOND
        ENDS CURRENT_TIMESTAMP   INTERVAL 10 SECOND
        on completion preserve
        do
        call tp123;

alter event  te123 ON COMPLETION PRESERVE ENABLE;
set global event_scheduler =1;
commit;
flush logs;


#create view
create view tv123 as select * from t1 limit 5;
commit;
flush logs;

create user jerry@'localhost' identified by 'jerrypw';
create user jerry@'%' identified by 'jerrypw';

grant all privileges on *.* to jerry@'localhost';
grant all privileges on *.* to jerry@'%';
flush privileges;

commit;
flush logs;

select sleep(10);

select * from db01.t1;
select * from db01.t2;
3.4、主库和从库准备参数并重启

最简单的基于二进制日志的异步复制，这里贴一些重要参数(非完整版)

1.主库

主要是binlog参数

代码语言：javascript复制
log_bin=/mysql/binlog/mydb-binlog
log_bin_index=/mysql/binlog/mydb-binlog.index
binlog_rows_query_log_events=on
server_id=130

2.从库

主要是binlog参数和relay_log参数

代码语言：javascript复制
log_bin=/mysql/binlog/mydb-binlog
log_bin_index=/mysql/binlog/mydb-binlog.index
binlog_rows_query_log_events=on
server_id=140
relay_log = /mysql/relaylog/mydb-relay.log
log-slave-updates = 1
read_only=1

3.变化

binlog_format参数已经移除了

代码语言：javascript复制
[Server] 'binlog_format' is deprecated and will be removed in a future release.
3.5、sysbench准备数据并测试
代码语言：javascript复制
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare


sysbench --db-driver=mysql --time=9999 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=10 --table_size=1000000 oltp_read_write --db-ps-mode=disable run
4、主库5.130创建复制用户
代码语言：javascript复制
create user 'userreplica'@'%' identified by 'userreplicapw';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
5、在主库使用mysql shell备份并拷贝数据到从库
代码语言：javascript复制
[root@mydb01 3306]# mysqlsh --mysql -uroot -proot -hlocalhost -P3306

MySQL  localhost:3306 ssl  JS > util.dumpInstance("/mysql/backup/shellbk",{"compression":"none", "threads":6, chunking:false})

 MySQL  localhost:3306 ssl  JS > q
Bye!
[root@mydb01 3306]# cd /mysql/backup/

[root@mydb01 backup]# zip -q -r alldb.zip shellbk
[root@mydb01 backup]# scp alldb.zip 192.168.5.140:/mysql/backup
root@192.168.5.140's password:
alldb.zip
6、从库5.140导入数据
6.1、解压备份文件
代码语言：javascript复制
[root@mydb ~]# cd /mysql/backup/
[root@mydb backup]# unzip -q alldb.zip
6.2、导入备份文件第一坑
代码语言：javascript复制
[root@mydb backup]# mysqlsh --mysql -uroot -proot -h localhost -P3306
 MySQL  localhost:3306 ssl  JS > util.loadDump("/mysql/backup/shellbk")
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.loadDump: local_infile disabled in server (MYSQLSH 53025)
 MySQL  localhost:3306 ssl  JS > sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 
 MySQL  localhost:3306 ssl  SQL > set global local_infile=ON;
Query OK, 0 rows affected (0.0005 sec)
 MySQL  localhost:3306 ssl  SQL > q
Bye!
6.3、导入备份文件
代码语言：javascript复制
[root@mydb backup]# mysqlsh --mysql -uroot -proot -h localhost -P3306
 MySQL  localhost:3306 ssl  JS > util.loadDump("/mysql/backup/shellbk")
Loading DDL and Data from '/mysql/backup/shellbk' using 4 threads.
Opening dump...
Target is MySQL 8.0.34. Dump was produced from MySQL 8.0.34
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
1 thds loading  100% (37.98 MB / 37.98 MB), 7.48 MB/s, 21 / 22 tables done
Recreating indexes - done
Executing common postamble SQL
22 chunks (200.01K rows, 37.98 MB) for 22 tables in 2 schemas were loaded in 5 sec (avg throughput 7.47 MB/s)
0 warnings were reported during the load.
 MySQL  localhost:3306 ssl  JS > q
7、配置主从复制信息
7.1、找到binlog信息

熟悉mysqldump的大佬都知道，在这种情况是需要去dump文件中找binlog的信息的。同理mysql shell也是需要

代码语言：javascript复制
[root@mydb shellbk]# cat @.json | grep -i binlog
    "binlogFile": "mydb-binlog.000007",
    "binlogPosition": 120455816,
7.2、从库配置replica信息
代码语言：javascript复制
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.5.130", SOURCE_USER="userreplica",SOURCE_PASSWORD="userreplicapw", SOURCE_LOG_FILE='mydb-binlog.000007',SOURCE_LOG_                     POS=120455816;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start replica;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW REPLICA STATUSG

又是坑

代码语言：javascript复制
         Slave_IO_Running: Connecting
        Slave_SQL_Running: Yes
            Last_IO_Errno: 2061
            Last_IO_Error: Error connecting to source 'repuser@192.168.5.130:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Messag                     e: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决问题需要加参数get_master_public_key=1;

代码语言：javascript复制
mysql> stop replica;
mysql> reset slave all;

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.5.130", SOURCE_USER="repuser",SOURCE_PASSWORD="repuser123", SOURCE_LOG_FILE='mydb-binlog.000007',SOURCE_LOG_POS=120455816,get_master_public_key=1;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

mysql> start replica;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW REPLICA STATUSG
*************************** 1. row ***************************
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes

这里也可以改复制用户的认证方式为mysql_native_password，不过这个已经弃用了。

8、数据测试
8.1、主库5.130
代码语言：javascript复制
[root@mydb01 backup]# mysql -uroot -proot
mysql> create database db02;
mysql> use db02
mysql> create table t2(id int primary key);
mysql> insert into t2 values(1),(2);
mysql> select * from t2;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
 ---- 
2 rows in set (0.00 sec)
mysql> commit;
mysql> flush logs;
8.2、从库5.140
代码语言：javascript复制
[root@mydb02 backup]# mysql -uroot -proot
mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| db01               |
| db02               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
 -------------------- 
7 rows in set (0.00 sec)

mysql> use db02;
mysql> select * from t2;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
 ---- 
2 rows in set (0.00 sec)
9、mysql shell备份扩展
9.1、概述和语法
代码语言：javascript复制
util.dumpInstance(outputUrl[, options]) ---导出整个mysql数据库实例
util.dumpSchemas(schemas, outputUrl[, options])---导出某个数据库
util.dumpTables(schema, tables, outputUrl[, options])---导出表
9.2、案例

2.1、导出整个mysql数据库实例

代码语言：javascript复制
util.dumpInstance("/mysql/backup/shellbk", {dryRun: true, ocimds: true, compatibility: ["ignore_missing_pks"]})
util.dumpInstance("/mysql/backup/shellbk")

这里compatibility:的参数要用[]号

2.2、导出某个数据库

代码语言：javascript复制
dump一个数据库
util.dumpSchemas(["db01"],"/mysql/backup/shellbk")
dump多个数据库
util.dumpSchemas(["db01","zimuzu"],"/mysql/backup/shellbk")

2.3、导出表

代码语言：javascript复制
导出db01下的所有表
util.dumpTables("db01", [], "/mysql/backup/shellbk", { "all": true })
导出db01下的一张表
util.dumpTables("db01", ["t1"], "/mysql/backup/shellbk")
导出db01下的多张表
util.dumpTables("db01", ["t1","t22"], "/mysql/backup/shellbk")

2.4、导入数据(实例/数据库/表都是使用使用的命令)

代码语言：javascript复制
试运行检查是否有错误
MySQL 192.168.5.130:3306 ssl JS > util.loadDump("/mysql/backup/shellbk", {dryRun: true})
正式导入数据
MySQL 192.168.5.130:3306 ssl JS > util.loadDump("/mysql/backup/shellbk")
9.3、备注

mysql shell备份有很多的参数以及坑，这就需要查看官网文档了。

数据库 shell mysql 集群 数据

0 人点赞