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安装
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench
3.2、创建一些测试的用户信息
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、单独准备一些数据
#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参数
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参数
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参数已经移除了
[Server] 'binlog_format' is deprecated and will be removed in a future release.
3.5、sysbench准备数据并测试
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创建复制用户
create user 'userreplica'@'%' identified by 'userreplicapw';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
5、在主库使用mysql shell备份并拷贝数据到从库
[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、解压备份文件
[root@mydb ~]# cd /mysql/backup/
[root@mydb backup]# unzip -q alldb.zip
6.2、导入备份文件第一坑
[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、导入备份文件
[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也是需要
[root@mydb shellbk]# cat @.json | grep -i binlog
"binlogFile": "mydb-binlog.000007",
"binlogPosition": 120455816,
7.2、从库配置replica信息
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 STATUS\G
又是坑
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;
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 STATUS\G
*************************** 1. row ***************************
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
这里也可以改复制用户的认证方式为mysql_native_password，不过这个已经弃用了。
8、数据测试
8.1、主库5.130
[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
[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、概述和语法
util.dumpInstance(outputUrl[, options]) ---导出整个mysql数据库实例
util.dumpSchemas(schemas, outputUrl[, options])---导出某个数据库
util.dumpTables(schema, tables, outputUrl[, options])---导出表
9.2、案例
2.1、导出整个mysql数据库实例
util.dumpInstance("/mysql/backup/shellbk", {dryRun: true, ocimds: true, compatibility: ["ignore_missing_pks"]})
util.dumpInstance("/mysql/backup/shellbk")
这里compatibility:的参数要用[]号
2.2、导出某个数据库
dump一个数据库
util.dumpSchemas(["db01"],"/mysql/backup/shellbk")
dump多个数据库
util.dumpSchemas(["db01","zimuzu"],"/mysql/backup/shellbk")
2.3、导出表
导出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、导入数据(实例/数据库/表都是使用使用的命令)
试运行检查是否有错误
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备份有很多的参数以及坑，这就需要查看官网文档了。
欢迎关注公众号：DBA札记，一起交流数据库技术。后台回复“交流群”可添加技术交流群。
原文作者：DBA札记；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
